PySQL
PySQL is a Python library for interacting with SQL Server databases. It provides a simplified interface for common database operations.
## Installation
pySQL requires the following packages:
- pandas
- numpy
- sqlalchemy
- pyodbc
pip install pandas numpy sqlalchemy pyodbc
Usage
Import PySQL and Table_analyzer:
from pySqlServer import PySQL, Table_analyzer
Table_analyzer usage:
A helper class for analyzing Pandas DataFrames to determine optimal SQL datatypes.
df = pd.read_csv('test.csv')
TA = Table_analyzer()
dtype_dict = TA.analyze(df,texts_buffer=0.2)
- for texts_buffer > 1 : N in nvarchar(N) sets to len_max_length + texts_buffer
- for 0 < texts_buffer < 1 : N in nvarchar(N) sets to len_max_length + texts_buffer*len_max_length (extra percentage)
PySQL usage:
- Create a connection
pysql = PySQL()
pysql.create_connection(server='host_ip', database='mydb', username='myuser', password='mypassword', local_sql=False)
your user must have 'db_datareader', 'db_datawriter', 'db_ddlAdmin' permissions to module works perfectly set local_sql=True for windows auth in local SQL Server
- at first use (for every tables) you must call create_dtypes()
dtype_dict = {'col1':sql.sqltypes.INTEGER , 'col2':sql.sqltypes.NVARCHAR(100)} # it's suggested to use Table_analyzer to calculate optimal dtype_dict
# dtype_dict = TA.analyze(df,texts_buffer=0.2)
pysql.create_dtypes(dtype_dict=dtype_dict, table_name='Test_table', schema='Test_schema')
- at all next usages you must call load_dtypes and next you can use to_sql method to send data
pysql.load_dtypes(table_name='Test_table', schema='Test_schema') # created before
pysql.to_sql(df,'Test_table', schema='Test_schema', if_exists='append', text_cutter=True, date_normalizer=True, method='multi', verbos=True)
- you can use primary_key='column_name' to set tables primary_key
- in next usages it's not allowed to use this
- 'text_cutter' trys to cut new text if those length was taller than column capacity
- 'date_normalizer' trys to make date format colums suitable for sql server
- method='multi' for insert multi row in ine query
- verbos=True --> show progress bar for your data transfer (default:False)
- and there is some read data methods in order to read data from your database (returns pandas dataframe)
pysql.tables_list(schema=None)
pysql.read_sql_table(table_name, schema=None)
pysql.read_sql_query(query='SELECT * FROM TABLE_NAME')
- also you can update your table with a update_key and update_value
this line should update age of someone in the table named Bob Lookie to 83 YO! in other way you can set update_key and update_value with list of dicts and it update all queries in a row (len(update_key) should be equal to len(update_value))
pysql.update_table(table_name='personal_data', schema='persons', update_key={'name':'Bob', 'last_name':'Looki'}, update_value={'age':83})
- logger is a common method to use ( it logs datetime and process_id and actor_user in order to make your actions trackable )
every to_sql calls can join with logs with 'process_id' column , so you can find who and when it's started to store data and how long takes it process
pysql.logger('create_connection', 'success', 'connected')
- auto log system logs all your method calls like bellow sample:
auto_log sample:
| function | state | log | connection_user | process_id | datetime |
|---|---|---|---|---|---|
| create_connection | success | connected | pysql_user | -1 | 2023-08-12 16:04:22.000 |
| read_sql_table | start | success | pysql_user | 0 | 2023-08-12 16:04:22.000 |
| read_sql_table | end | success | pysql_user | 0 | 2023-08-12 16:04:23.000 |
| create_dtypes | start | success | pysql_user | 1 | 2023-08-12 16:04:23.000 |
| create_dtypes | end | success | pysql_user | 1 | 2023-08-12 16:04:23.000 |
| load_dtypes | start | success | pysql_user | 2 | 2023-08-12 16:04:24.000 |
| load_dtypes | end | success | pysql_user | 2 | 2023-08-12 16:04:24.000 |
| to_sql | start | success | pysql_user | 3 | 2023-08-12 16:04:24.000 |
| to_sql | end | success | pysql_user | 3 | 2023-08-12 16:04:32.000 |
data to_sql sample:
| user_id | id | created_at | lang | favorite_count | quote_count | reply_count | retweet_count | views_count | bookmark_count |
|---|---|---|---|---|---|---|---|---|---|
| *****17323920629770 | 1647162******634625 | 2023-04-15 08:57:34.000 | en | 2 | 0 | 0 | 0 | 177 | 1 |
| *****0417 | 1647153******613570 | 2023-04-15 08:23:18.000 | en | 1 | 0 | 1 | 0 | 12 | 0 |
| *****49585152565249 | 1682090******974593 | 2023-07-20 18:08:18.000 | en | 642 | 83 | 65 | 213 | 1749178 | 8 |
| *****87375859568642 | 1647152******957248 | 2023-04-15 08:18:00.000 | ar | 22 | 8 | 0 | 0 | 7 | 0 |
| *****0013028323329 | 1647127******033537 | 2023-04-15 06:40:00.000 | en | 2 | 0 | 1 | 0 | 84 | 2 |
dtypes table sample:
| table | schema | dtypes_str | proccess_id |
|---|---|---|---|
| T1 | {'user_id': 'types.BIGINT()', ... , 'bookmark_count': 'types.INTEGER()'} | 1 | |
| T2 | {'user_profile_banner_url': 'types.NVARCHAR(88)',..., 'user_description_urls': 'types.NVARCHAR(443)'} | 8 | |
| T3 | {'in_reply_to_status_id_str': 'types.BIGINT()',..., 'is_quote': 'types.BOOLEAN()'} | 11 |
1 maintainer
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Filter files by name, interpreter, ABI, and platform.
If you're not sure about the file name format, learn more about wheel file names.
Copy a direct link to the current filters
File details
Details for the file pySqlServer-3.7.1.tar.gz.
File metadata
- Download URL: pySqlServer-3.7.1.tar.gz
- Upload date:
- Size: 11.8 kB
- Tags: Source
- Uploaded using Trusted Publishing? No
- Uploaded via:
twine/4.0.2 CPython/3.11.3
File hashes
| Algorithm | Hash digest | |
|---|---|---|
| SHA256 |
1d2570a50d5a7fb133632c9e8b2e566c008a1fb946860ad6369eb22e9fb1b4e7
|
|
| MD5 |
fff34dd4984e6e07ceb64d26e62fc0e1
|
|
| BLAKE2b-256 |
f09fc6890a836e18561af04b589da529f4000a02c0b2b66a2cd1b83cb14308c6
|
File details
Details for the file pySqlServer-3.7.1-py3-none-any.whl.
File metadata
- Download URL: pySqlServer-3.7.1-py3-none-any.whl
- Upload date:
- Size: 18.8 kB
- Tags: Python 3
- Uploaded using Trusted Publishing? No
- Uploaded via:
twine/4.0.2 CPython/3.11.3
File hashes
| Algorithm | Hash digest | |
|---|---|---|
| SHA256 |
26f3047aad812d225f73c9c93f61b03520becbf1e9a19521854bb3f79a3f1f14
|
|
| MD5 |
204a788543b2622e219fad958880e16a
|
|
| BLAKE2b-256 |
73121f37c9783719f5d1feb091d4099cf440aa2fdb9dbccbe014f35ce8780bff
|
