# SQL+Python
A collection of utils to effortlessly and reliably interact between Python and your SQL database — purpose‑built for time‑series workflows.
This package streamlines reading, writing, and maintaining SQL tables using familiar Python structures like pandas DataFrames, dictionaries, and other Python objects. It’s user-friendly, strict about data consistency, and tuned for time-series data pipelines.
Intended for MariaDB/MySQL via SQLAlchemy - pre-configured docker environment included.
### Design Principles
- Predictable schemas: deterministic dtype mapping and optional auto-alter
- Strong safeguards: explicit checks, clear assertions, and helpful errors
- Pandas-friendly: minimal friction between DataFrame types and SQL
- Maintainability: composable helpers for upload, fetch, and schema operations
## Key Features
- Focus on time-series data
- Append-only patterns with optional “update latest” logic
- Automatic handling of date/symbol index columns
- Fast retrieval by symbol and date with optional indexing
- Utilities to query by symbol(s), fetch latest dates, and union columns across tables
- Works with your data structures
- Upload pandas DataFrames with automatic dtype mapping
- Store/retrieve dictionaries as rows
- Persist arbitrary Python objects via pickling (e.g., models, configs)
- Minimal boilerplate for table creation and updates
- Smart dtype defaults for SQL schema generation
- Safety and consistency checks
- NaN/Inf coercion and validation for key columns
- Duplicate/consistency guards when updating recent rows
- Table introspection: add missing columns automatically (optional)
- Environment validation and connection checks
### Typical Use Cases
- Maintain historical time-series data on a symbol-level (prices, indicators, metrics)
- Incrementally update tables from pandas pipelines
- Keep per-symbol metadata and snapshots
- Store models or transforms in SQL as versioned pickles
## Installation
- Python: 3.12+
- Installed packages: sqlalchemy, pandas etc.
- Optional: Docker to use the docker compose environment that is set up to work wiht the utils - batteries included
Create/activate your virtualenv, then install your project’s dependencies as usual with pip inside the virtualenv.
Run `docker compose up` in the project root to start the MariaDB container with the default environment variables.
### Environment Variables
- Standard DB credentials (e.g., user, password, host, port, database) loaded from your .env
- A default setup for Docker is included
- Built-in validation for host configuration; safe fallbacks if misconfigured
## Quick Start
The main functions are listed below.
- `upload_df()`: DataFrame uploads
- Auto-creates tables with sensible column types (numeric as DOUBLE, categoricals as TEXT, datetimes as DATETIME) using `create_table()`
- Optional table alteration to add newly appearing DataFrame columns
- Optional enforcement of non-null date/symbol keys with configurable behavior
- When uploading time-series data and using “update latest,” only the newest overlapping row(s) are replaced; older history remains intact
- `upload_dict()`: Dictionary uploads
- Keys become columns; can define a symbol column for idempotent updates
- `upload_object()`: Object uploads
- Serialize Python objects via pickle and store them in LONGBLOB columns
- Retrieval helpers
- `get_symbol_data()` or `get_df_symbols_data()`: Get time-series for a single symbol or multiple symbols
- `get_all_symbol_data()`: Fetch all tables for a symbol in one go
- `get_existing_rows()`: Inspect availability (tables exist, rows > 0), and compute the union of columns across tables
Raw data
{
"_id": null,
"home_page": null,
"name": "SQLplusPython",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.12",
"maintainer_email": null,
"keywords": "sqlalchemy, mysql, pandas, database, orm",
"author": null,
"author_email": "D Hansen <vj3gt0o0l@mozmail.com>",
"download_url": "https://files.pythonhosted.org/packages/80/b5/abe97d9432766b3427c5ebbe8e619bd88804f0239afeb814dc135f5bce86/sqlpluspython-0.2.0.tar.gz",
"platform": null,
"description": "# SQL+Python\nA collection of utils to effortlessly and reliably interact between Python and your SQL database \u2014 purpose\u2011built for time\u2011series workflows.\nThis package streamlines reading, writing, and maintaining SQL tables using familiar Python structures like pandas DataFrames, dictionaries, and other Python objects. It\u2019s user-friendly, strict about data consistency, and tuned for time-series data pipelines.\nIntended for MariaDB/MySQL via SQLAlchemy - pre-configured docker environment included.\n\n### Design Principles\n- Predictable schemas: deterministic dtype mapping and optional auto-alter\n- Strong safeguards: explicit checks, clear assertions, and helpful errors\n- Pandas-friendly: minimal friction between DataFrame types and SQL\n- Maintainability: composable helpers for upload, fetch, and schema operations\n\n## Key Features\n- Focus on time-series data\n - Append-only patterns with optional \u201cupdate latest\u201d logic\n - Automatic handling of date/symbol index columns\n - Fast retrieval by symbol and date with optional indexing\n - Utilities to query by symbol(s), fetch latest dates, and union columns across tables\n\n- Works with your data structures\n - Upload pandas DataFrames with automatic dtype mapping\n - Store/retrieve dictionaries as rows\n - Persist arbitrary Python objects via pickling (e.g., models, configs)\n - Minimal boilerplate for table creation and updates\n - Smart dtype defaults for SQL schema generation\n\n- Safety and consistency checks\n - NaN/Inf coercion and validation for key columns\n - Duplicate/consistency guards when updating recent rows\n - Table introspection: add missing columns automatically (optional)\n - Environment validation and connection checks\n\n### Typical Use Cases\n- Maintain historical time-series data on a symbol-level (prices, indicators, metrics)\n- Incrementally update tables from pandas pipelines\n- Keep per-symbol metadata and snapshots\n- Store models or transforms in SQL as versioned pickles\n\n## Installation\n- Python: 3.12+\n- Installed packages: sqlalchemy, pandas etc.\n- Optional: Docker to use the docker compose environment that is set up to work wiht the utils - batteries included\n\nCreate/activate your virtualenv, then install your project\u2019s dependencies as usual with pip inside the virtualenv.\nRun `docker compose up` in the project root to start the MariaDB container with the default environment variables.\n\n### Environment Variables\n- Standard DB credentials (e.g., user, password, host, port, database) loaded from your .env\n- A default setup for Docker is included\n- Built-in validation for host configuration; safe fallbacks if misconfigured\n\n\n## Quick Start\nThe main functions are listed below.\n\n- `upload_df()`: DataFrame uploads\n - Auto-creates tables with sensible column types (numeric as DOUBLE, categoricals as TEXT, datetimes as DATETIME) using `create_table()`\n - Optional table alteration to add newly appearing DataFrame columns\n - Optional enforcement of non-null date/symbol keys with configurable behavior\n - When uploading time-series data and using \u201cupdate latest,\u201d only the newest overlapping row(s) are replaced; older history remains intact\n\n- `upload_dict()`: Dictionary uploads\n - Keys become columns; can define a symbol column for idempotent updates\n\n- `upload_object()`: Object uploads\n - Serialize Python objects via pickle and store them in LONGBLOB columns\n\n- Retrieval helpers\n - `get_symbol_data()` or `get_df_symbols_data()`: Get time-series for a single symbol or multiple symbols\n - `get_all_symbol_data()`: Fetch all tables for a symbol in one go\n - `get_existing_rows()`: Inspect availability (tables exist, rows > 0), and compute the union of columns across tables\n\n\n\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Utils for SQL+Python - reliably work with SQL in Python apps ",
"version": "0.2.0",
"project_urls": {
"Repository": "https://github.com/dennisdeh/SQLplusPython"
},
"split_keywords": [
"sqlalchemy",
" mysql",
" pandas",
" database",
" orm"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "e25853d39486acc7dc8c1aff48b679484dafb3caafafaa43f2e75a0f32b66967",
"md5": "32f1a09bac78a2af58a7f12c12613379",
"sha256": "a566c307c6eff71ed70c98fa01213a315a01c98dc91a061df80172518bb99b80"
},
"downloads": -1,
"filename": "sqlpluspython-0.2.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "32f1a09bac78a2af58a7f12c12613379",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.12",
"size": 36084,
"upload_time": "2025-10-20T22:09:57",
"upload_time_iso_8601": "2025-10-20T22:09:57.576328Z",
"url": "https://files.pythonhosted.org/packages/e2/58/53d39486acc7dc8c1aff48b679484dafb3caafafaa43f2e75a0f32b66967/sqlpluspython-0.2.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "80b5abe97d9432766b3427c5ebbe8e619bd88804f0239afeb814dc135f5bce86",
"md5": "e38642652873b29a4951f5af29ff81b6",
"sha256": "1e512cca02db4bf1cc21f9967f346f79cd575435c6b1c067a0e01af03fa8950a"
},
"downloads": -1,
"filename": "sqlpluspython-0.2.0.tar.gz",
"has_sig": false,
"md5_digest": "e38642652873b29a4951f5af29ff81b6",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.12",
"size": 33042,
"upload_time": "2025-10-20T22:09:59",
"upload_time_iso_8601": "2025-10-20T22:09:59.217854Z",
"url": "https://files.pythonhosted.org/packages/80/b5/abe97d9432766b3427c5ebbe8e619bd88804f0239afeb814dc135f5bce86/sqlpluspython-0.2.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-10-20 22:09:59",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "dennisdeh",
"github_project": "SQLplusPython",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"requirements": [
{
"name": "dotenv",
"specs": [
[
"==",
"0.9.9"
]
]
},
{
"name": "greenlet",
"specs": [
[
"==",
"3.2.4"
]
]
},
{
"name": "joblib",
"specs": [
[
"==",
"1.5.2"
]
]
},
{
"name": "mysql-connector-python",
"specs": [
[
"==",
"9.4.0"
]
]
},
{
"name": "numpy",
"specs": [
[
"==",
"2.3.4"
]
]
},
{
"name": "pandas",
"specs": [
[
"==",
"2.3.3"
]
]
},
{
"name": "python-dateutil",
"specs": [
[
"==",
"2.9.0.post0"
]
]
},
{
"name": "python-dotenv",
"specs": [
[
"==",
"1.1.1"
]
]
},
{
"name": "pytz",
"specs": [
[
"==",
"2025.2"
]
]
},
{
"name": "scikit-learn",
"specs": [
[
"==",
"1.7.2"
]
]
},
{
"name": "scipy",
"specs": [
[
"==",
"1.16.2"
]
]
},
{
"name": "six",
"specs": [
[
"==",
"1.17.0"
]
]
},
{
"name": "SQLAlchemy",
"specs": [
[
"==",
"2.0.44"
]
]
},
{
"name": "threadpoolctl",
"specs": [
[
"==",
"3.6.0"
]
]
},
{
"name": "typing_extensions",
"specs": [
[
"==",
"4.15.0"
]
]
},
{
"name": "tzdata",
"specs": [
[
"==",
"2025.2"
]
]
}
],
"lcname": "sqlpluspython"
}
JSON
