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
Metadata
1 maintainer
Release files for pySqlServer 3.7.1
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | |
|---|---|---|---|
| pySqlServer-3.7.1.tar.gz | 11.8 kB | Details |
Built distribution (wheel)
| File | Interpreter | ABI | Platform | Reset |
|---|---|---|---|---|
| pySqlServer-3.7.1-py3-none-any.whl | Python 3 | none | any | Details |
Total release size: 30.6 kB
Release files / pySqlServer-3.7.1.tar.gz
| Download URL | pySqlServer-3.7.1.tar.gz |
|---|---|
| Size | 11.8 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
1d2570a50d5a7fb133632c9e8b2e566c008a1fb946860ad6369eb22e9fb1b4e7
|
|
BLAKE2b-256 checksum How to use checksums |
f09fc6890a836e18561af04b589da529f4000a02c0b2b66a2cd1b83cb14308c6
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
No |
| Uploaded via |
twine/4.0.2 CPython/3.11.3
|
Release files / pySqlServer-3.7.1-py3-none-any.whl
| Download URL | pySqlServer-3.7.1-py3-none-any.whl |
|---|---|
| Size | 18.8 kB |
| Tags | Python 3 |
|
SHA-256 checksum How to use checksums |
26f3047aad812d225f73c9c93f61b03520becbf1e9a19521854bb3f79a3f1f14
|
|
BLAKE2b-256 checksum How to use checksums |
73121f37c9783719f5d1feb091d4099cf440aa2fdb9dbccbe014f35ce8780bff
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
No |
| Uploaded via |
twine/4.0.2 CPython/3.11.3
|
