[](https://codecov.io/gh/ThibTrip/pangres)
[](https://img.shields.io/pypi/v/pangres)
[](https://github.com/ThibTrip/pangres/wiki)
[](https://jupyter.org/try)
# pangres
_Thanks to [freesvg.org](https://freesvg.org/) for the logo assets_
Upsert with pandas DataFrames (<code>ON CONFLICT DO NOTHING</code> or <code>ON CONFLICT DO UPDATE</code>) for PostgreSQL, MySQL, SQlite and potentially other databases behaving like SQlite (untested) with some additional optional features (see features). Upserting can be done with **primary keys** or **unique keys**.
Pangres also handles the creation of non-existing SQL tables and schemas.
# Features
1. <i>(optional)</i> Automatical column creation (when a column exists in the DataFrame but not in the SQL table)
2. <i>(optional)</i> Automatical column type alteration for columns that are empty in the SQL table (except for SQlite where alteration is limited)
3. <i>(optional)</i> Creates the table if it is missing
4. <i>(optional)</i> Creates missing schemas in Postgres (and potentially other databases that have a schema system)
5. JSON is supported (with pd.to_sql it does not work) with some exceptions (see [Gotchas and caveats](#Gotchas-and-caveats))
6. Fast (except for SQlite where some help is needed)
7. Will work even if not all columns defined in the SQL table are there
8. SQL injection safe (schema, table and column names are escaped and values are given as parameters)
9. _New in version 4.1_: **asynchronous support**. Tested using `aiosqlite` for SQlite, `asyncpg` for PostgreSQL and `aiomysql` for MySQL
# Requirements
* SQlite >= 3.24.4
* Python >= 3.6.4
* See also ./pangres/requirements.txt
## Requirements for sqlalchemy>=2.0
For using `pangres` together with **`sqlalchemy>=2.0`** (sqlalchemy is one of pangres dependencies
listed in requirements.txt) - you will need the following base requirements:
* `alembic>=1.7.2`
* `pandas>=1.4.0`
* Python >= 3.8 (`pandas>=1.4.0` only supports Python >=3.8)
## Requirements for asynchronous engines
For using asynchronous engines (such as `aiosqlite`, `asyncpg` or `aiomysql`) you will need **Python >= 3.8**.
# Gotchas and caveats
## All flavors
1. We can't create JSON columns automatically, but we can insert JSON like objects (list, dict) in existing JSON columns.
## Postgres
1. "%", ")" and "(" in column names will most likely cause errors with PostgreSQL (this is due to psycopg2 and also affect pd.to_sql). Use the function pangres.fix_psycopg2_bad_cols to "clean" the columns in the DataFrame. You'll also have to rename columns in the SQL table accordingly (if the table already exists).
2. Even though we only do data type alteration on empty columns, since we don't want to lose column information (e.g. constraints) we use true column alteration (instead of drop+create) so the old data type must be castable to the new data type. Postgres seems a bit restrictive in this regard even when the columns are empty (e.g. BOOLEAN to TIMESTAMP is impossible).
## SQlite
1. **SQlite must be version 3.24.4 or higher**! UPSERT syntax did not exist before.
2. Column type alteration is not possible for SQlite.
3. SQlite inserts can be at worst 5 times slower than pd.to_sql for some reasons. If you can help please contact me!
4. Inserts with 1000 columns (or 32767 columns for SQlite >= 3.32.0) or more are not supported because one could not even insert one row without exceeding the max number of parameters per queries. One way to fix this would inserting the columns progressively but this seems quite tricky. If you know a better way please contact me.
## MySQL
1. MySQL will often change the order of the primary keys in the SQL table when using INSERT... ON CONFLICT.. DO NOTHING/UPDATE. This seems to be the expected behavior so nothing we can do about it but please mind that!
2. You may need to provide SQL dtypes e.g. if you have a primary key with text you will need to provide a character length (e.g. VARCHAR(50)) because MySQL does not support indices/primary keys with flexible text length. pd.to_sql has the same issue.
# Notes
This is a library I was using in production in private with very good results and decided to publish.
Ideally such features will be integrated into pandas since there is already a [PR on the way](https://github.com/pandas-dev/pandas/pull/29636) and I would like to give the option to add columns via another PR.
There is also [pandabase](https://github.com/notsambeck/pandabase) which does almost the same thing (plus lots of extra features) but my implementation is different.
Btw big thanks to pandabase and the sql part of pandas which helped a lot.
# Installation
```
pip install pangres
```
Additionally depending on which database you want to work with you will need to install the corresponding library (note that SQlite is included in the standard library):
* Postgres
```
pip install psycopg2
```
* MySQL
```
pip install pymysql
```
* Postgres (asynchronous)
```
pip install asyncpg
```
* MySQL (asynchronous)
```
pip install aiomysql
```
* SQLite (asynchronous)
```
pip install aiosqlite
```
# Usage
Head over to [pangres' wiki](https://github.com/ThibTrip/pangres/wiki)! Note that the wiki is also available
locally under the [wiki folder](https://github.com/ThibTrip/pangres/tree/master/wiki).
Note:
The wiki is generated with a command which uses my library [npdoc_to_md](https://github.com/ThibTrip/npdoc_to_md).
It must be installed with `pip install npdoc_to_md` and you will also need the extra dependency `fire` which you
can install with `pip install fire`. Replace `$DESTINATION_FOLDER` with the folder of you choice in the command below:
```bash
npdoc-to-md render-folder ./wiki_templates $DESTINATION_FOLDER
```
# Contributing
Pull requests/issues are welcome.
# Development
I develop the library inside of **Jupyter Lab** using the [**jupytext**](https://github.com/mwouts/jupytext) extension.
I recommend using this extension for the best experience.
It will split code blocks within modules in notebook cells and will allow **interactive development**.
If you wish you can also use the provided **conda environment** (see `environment.yml` file) inside of Jupyter Lab/Notebook
thanks to [**nb_conda_kernels**](https://github.com/Anaconda-Platform/nb_conda_kernels).
# Testing
## Pytest
You can test one or multiple of the following SQL flavors (you will of course need a live database for this): PostgreSQL, SQlite or MySQL.
NOTE: in one of the tests of `pangres` we will try to drop and then create a PostgreSQL schema called `pangres_create_schema_test`. If the schema existed and was not empty an error will be raised.
Clone pangres then set your curent working directory to the root of the cloned repository folder. Then use the commands below. You will have to replace the following variables in those commands:
* `SQLITE_CONNECTION_STRING`: replace with a SQlite sqlalchemy connection string (e.g. "sqlite:///test.db")
* `ASYNC_SQLITE_CONNECTION_STRING`: replace with an asynchronous SQlite sqlalchemy connection string (e.g. "sqlite+aiosqlite:///test.db")
* `POSTGRES_CONNECTION_STRING`: replace with a Postgres sqlalchemy connection string (e.g. "postgres:///user:password@localhost:5432/database"). Specifying schema is optional for postgres (will default to public)
* `ASYNC_POSTGRES_CONNECTION_STRING`: replace with an asynchronous Postgres sqlalchemy connection string (e.g. "postgres+asyncpg:///user:password@localhost:5432/database"). Specifying schema is optional for postgres (will default to public)
* `MYSQL_CONNECTION_STRING`: replace with a MySQL sqlalchemy connection string (e.g. "mysql+pymysql:///user:password@localhost:3306/database")
* `ASYNC_MYSQL_CONNECTION_STRING`: replace with an asynchronous MySQL sqlalchemy connection string (e.g. "mysql+aiomysql:///user:password@localhost:3306/database")
* `PG_SCHEMA` (optional): schema for postgres (defaults to public)
```bash
# 1. Create and activate the build environment
conda env create -f environment.yml
conda activate pangres-dev
# 2. Install pangres in editable mode (changes are reflected upon reimporting)
pip install -e .
# 3. Run pytest
# -s prints stdout
# -v prints test parameters
# --cov=./pangres shows coverage only for pangres
# --doctest-modules tests with doctest in all modules
# --benchmark-XXX : these are options for benchmarks tests (see https://pytest-benchmark.readthedocs.io/en/latest/usage.html)
pytest -s -v pangres --cov=pangres --doctest-modules --async_sqlite_conn=$ASYNC_SQLITE_CONNECTION_STRING --sqlite_conn=$SQLITE_CONNECTION_STRING --async_pg_conn=$ASYNC_POSTGRES_CONNECTION_STRING --pg_conn=$POSTGRES_CONNECTION_STRING --async_mysql_conn=$ASYNC_MYSQL_CONNECTION_STRING --mysql_conn=$MYSQL_CONNECTION_STRING --pg_schema=tests --benchmark-group-by=func,param:engine,param:nb_rows --benchmark-columns=min,max,mean,rounds --benchmark-sort=name --benchmark-name=short
```
Additionally, the following flags could be of interest for you:
* `-x` for stopping at the first failure
* `--benchmark-only` for only testing benchmarks
* `--benchmark-skip` for skipping benchmarks
## flake8
flake8 must run without errors for pipelines to succeed.
If you are not using the conda environment, you can install flake8 with: `pip install flake8`.
To test flake8 locally you can simply execute this command:
```
flake8 .
```
Raw data
{
"_id": null,
"home_page": "https://github.com/ThibTrip/pangres",
"name": "pangres",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "pandas,postgres,mysql,sqlite",
"author": "Thibault B\u00e9tr\u00e9mieux",
"author_email": "thibault.betremieux@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/66/17/fe482e5d2e103e4fa22ed3705ce2b0b8145739eb36f07d8529d93228ea68/pangres-4.2.1.tar.gz",
"platform": null,
"description": "\n\n[](https://codecov.io/gh/ThibTrip/pangres)\n[](https://img.shields.io/pypi/v/pangres)\n[](https://github.com/ThibTrip/pangres/wiki)\n[](https://jupyter.org/try)\n\n# pangres\n\n\n_Thanks to [freesvg.org](https://freesvg.org/) for the logo assets_\n\nUpsert with pandas DataFrames (<code>ON CONFLICT DO NOTHING</code> or <code>ON CONFLICT DO UPDATE</code>) for PostgreSQL, MySQL, SQlite and potentially other databases behaving like SQlite (untested) with some additional optional features (see features). Upserting can be done with **primary keys** or **unique keys**.\nPangres also handles the creation of non-existing SQL tables and schemas.\n\n\n# Features\n\n1. <i>(optional)</i> Automatical column creation (when a column exists in the DataFrame but not in the SQL table)\n2. <i>(optional)</i> Automatical column type alteration for columns that are empty in the SQL table (except for SQlite where alteration is limited)\n3. <i>(optional)</i> Creates the table if it is missing\n4. <i>(optional)</i> Creates missing schemas in Postgres (and potentially other databases that have a schema system)\n5. JSON is supported (with pd.to_sql it does not work) with some exceptions (see [Gotchas and caveats](#Gotchas-and-caveats))\n6. Fast (except for SQlite where some help is needed)\n7. Will work even if not all columns defined in the SQL table are there\n8. SQL injection safe (schema, table and column names are escaped and values are given as parameters)\n9. _New in version 4.1_: **asynchronous support**. Tested using `aiosqlite` for SQlite, `asyncpg` for PostgreSQL and `aiomysql` for MySQL\n\n# Requirements\n\n* SQlite >= 3.24.4\n* Python >= 3.6.4\n* See also ./pangres/requirements.txt\n\n## Requirements for sqlalchemy>=2.0\n\nFor using `pangres` together with **`sqlalchemy>=2.0`** (sqlalchemy is one of pangres dependencies\nlisted in requirements.txt) - you will need the following base requirements:\n* `alembic>=1.7.2`\n* `pandas>=1.4.0`\n* Python >= 3.8 (`pandas>=1.4.0` only supports Python >=3.8)\n\n## Requirements for asynchronous engines\n\nFor using asynchronous engines (such as `aiosqlite`, `asyncpg` or `aiomysql`) you will need **Python >= 3.8**.\n\n# Gotchas and caveats\n\n## All flavors\n1. We can't create JSON columns automatically, but we can insert JSON like objects (list, dict) in existing JSON columns.\n\n## Postgres\n\n1. \"%\", \")\" and \"(\" in column names will most likely cause errors with PostgreSQL (this is due to psycopg2 and also affect pd.to_sql). Use the function pangres.fix_psycopg2_bad_cols to \"clean\" the columns in the DataFrame. You'll also have to rename columns in the SQL table accordingly (if the table already exists).\n2. Even though we only do data type alteration on empty columns, since we don't want to lose column information (e.g. constraints) we use true column alteration (instead of drop+create) so the old data type must be castable to the new data type. Postgres seems a bit restrictive in this regard even when the columns are empty (e.g. BOOLEAN to TIMESTAMP is impossible).\n\n## SQlite\n1. **SQlite must be version 3.24.4 or higher**! UPSERT syntax did not exist before. \n2. Column type alteration is not possible for SQlite.\n3. SQlite inserts can be at worst 5 times slower than pd.to_sql for some reasons. If you can help please contact me!\n4. Inserts with 1000 columns (or 32767 columns for SQlite >= 3.32.0) or more are not supported because one could not even insert one row without exceeding the max number of parameters per queries. One way to fix this would inserting the columns progressively but this seems quite tricky. If you know a better way please contact me.\n\n## MySQL\n\n1. MySQL will often change the order of the primary keys in the SQL table when using INSERT... ON CONFLICT.. DO NOTHING/UPDATE. This seems to be the expected behavior so nothing we can do about it but please mind that!\n2. You may need to provide SQL dtypes e.g. if you have a primary key with text you will need to provide a character length (e.g. VARCHAR(50)) because MySQL does not support indices/primary keys with flexible text length. pd.to_sql has the same issue.\n\n\n# Notes\n\nThis is a library I was using in production in private with very good results and decided to publish.\n\nIdeally such features will be integrated into pandas since there is already a [PR on the way](https://github.com/pandas-dev/pandas/pull/29636) and I would like to give the option to add columns via another PR.\n\nThere is also [pandabase](https://github.com/notsambeck/pandabase) which does almost the same thing (plus lots of extra features) but my implementation is different.\nBtw big thanks to pandabase and the sql part of pandas which helped a lot.\n\n# Installation\n```\npip install pangres\n```\nAdditionally depending on which database you want to work with you will need to install the corresponding library (note that SQlite is included in the standard library):\n\n* Postgres\n```\npip install psycopg2\n```\n\n* MySQL\n```\npip install pymysql\n```\n\n* Postgres (asynchronous)\n```\npip install asyncpg\n```\n\n* MySQL (asynchronous)\n```\npip install aiomysql\n```\n\n* SQLite (asynchronous)\n```\npip install aiosqlite\n```\n\n# Usage\n\nHead over to [pangres' wiki](https://github.com/ThibTrip/pangres/wiki)! Note that the wiki is also available\nlocally under the [wiki folder](https://github.com/ThibTrip/pangres/tree/master/wiki).\n\nNote:\n\nThe wiki is generated with a command which uses my library [npdoc_to_md](https://github.com/ThibTrip/npdoc_to_md).\nIt must be installed with `pip install npdoc_to_md` and you will also need the extra dependency `fire` which you\ncan install with `pip install fire`. Replace `$DESTINATION_FOLDER` with the folder of you choice in the command below:\n\n```bash\nnpdoc-to-md render-folder ./wiki_templates $DESTINATION_FOLDER\n```\n\n# Contributing\n\nPull requests/issues are welcome.\n\n# Development\n\nI develop the library inside of **Jupyter Lab** using the [**jupytext**](https://github.com/mwouts/jupytext) extension.\n\nI recommend using this extension for the best experience.\nIt will split code blocks within modules in notebook cells and will allow **interactive development**.\n\nIf you wish you can also use the provided **conda environment** (see `environment.yml` file) inside of Jupyter Lab/Notebook\nthanks to [**nb_conda_kernels**](https://github.com/Anaconda-Platform/nb_conda_kernels).\n\n# Testing\n\n## Pytest\n\nYou can test one or multiple of the following SQL flavors (you will of course need a live database for this): PostgreSQL, SQlite or MySQL.\n\nNOTE: in one of the tests of `pangres` we will try to drop and then create a PostgreSQL schema called `pangres_create_schema_test`. If the schema existed and was not empty an error will be raised.\n\nClone pangres then set your curent working directory to the root of the cloned repository folder. Then use the commands below. You will have to replace the following variables in those commands:\n* `SQLITE_CONNECTION_STRING`: replace with a SQlite sqlalchemy connection string (e.g. \"sqlite:///test.db\")\n* `ASYNC_SQLITE_CONNECTION_STRING`: replace with an asynchronous SQlite sqlalchemy connection string (e.g. \"sqlite+aiosqlite:///test.db\")\n* `POSTGRES_CONNECTION_STRING`: replace with a Postgres sqlalchemy connection string (e.g. \"postgres:///user:password@localhost:5432/database\"). Specifying schema is optional for postgres (will default to public)\n* `ASYNC_POSTGRES_CONNECTION_STRING`: replace with an asynchronous Postgres sqlalchemy connection string (e.g. \"postgres+asyncpg:///user:password@localhost:5432/database\"). Specifying schema is optional for postgres (will default to public)\n* `MYSQL_CONNECTION_STRING`: replace with a MySQL sqlalchemy connection string (e.g. \"mysql+pymysql:///user:password@localhost:3306/database\")\n* `ASYNC_MYSQL_CONNECTION_STRING`: replace with an asynchronous MySQL sqlalchemy connection string (e.g. \"mysql+aiomysql:///user:password@localhost:3306/database\")\n* `PG_SCHEMA` (optional): schema for postgres (defaults to public)\n\n```bash\n# 1. Create and activate the build environment\nconda env create -f environment.yml\nconda activate pangres-dev\n# 2. Install pangres in editable mode (changes are reflected upon reimporting)\npip install -e .\n# 3. Run pytest\n# -s prints stdout\n# -v prints test parameters\n# --cov=./pangres shows coverage only for pangres\n# --doctest-modules tests with doctest in all modules\n# --benchmark-XXX : these are options for benchmarks tests (see https://pytest-benchmark.readthedocs.io/en/latest/usage.html)\npytest -s -v pangres --cov=pangres --doctest-modules --async_sqlite_conn=$ASYNC_SQLITE_CONNECTION_STRING --sqlite_conn=$SQLITE_CONNECTION_STRING --async_pg_conn=$ASYNC_POSTGRES_CONNECTION_STRING --pg_conn=$POSTGRES_CONNECTION_STRING --async_mysql_conn=$ASYNC_MYSQL_CONNECTION_STRING --mysql_conn=$MYSQL_CONNECTION_STRING --pg_schema=tests --benchmark-group-by=func,param:engine,param:nb_rows --benchmark-columns=min,max,mean,rounds --benchmark-sort=name --benchmark-name=short\n```\n\nAdditionally, the following flags could be of interest for you:\n* `-x` for stopping at the first failure\n* `--benchmark-only` for only testing benchmarks\n* `--benchmark-skip` for skipping benchmarks\n\n## flake8\n\nflake8 must run without errors for pipelines to succeed.\nIf you are not using the conda environment, you can install flake8 with: `pip install flake8`.\n\nTo test flake8 locally you can simply execute this command:\n\n```\nflake8 .\n```\n\n",
"bugtrack_url": null,
"license": "The Unlicense",
"summary": "Postgres insert update with pandas DataFrames.",
"version": "4.2.1",
"project_urls": {
"Download": "https://github.com/ThibTrip/pangres/archive/v4.2.1.tar.gz",
"Homepage": "https://github.com/ThibTrip/pangres"
},
"split_keywords": [
"pandas",
"postgres",
"mysql",
"sqlite"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "6617fe482e5d2e103e4fa22ed3705ce2b0b8145739eb36f07d8529d93228ea68",
"md5": "86f07d7c4c91788a3fb3b920b41874cc",
"sha256": "5e42f9e6702dc4979f3380c2de8d986bafa5561b317a15f97d37b8a011d645b6"
},
"downloads": -1,
"filename": "pangres-4.2.1.tar.gz",
"has_sig": false,
"md5_digest": "86f07d7c4c91788a3fb3b920b41874cc",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 57982,
"upload_time": "2023-11-05T20:49:47",
"upload_time_iso_8601": "2023-11-05T20:49:47.242309Z",
"url": "https://files.pythonhosted.org/packages/66/17/fe482e5d2e103e4fa22ed3705ce2b0b8145739eb36f07d8529d93228ea68/pangres-4.2.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-11-05 20:49:47",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "ThibTrip",
"github_project": "pangres",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"requirements": [
{
"name": "pandas",
"specs": [
[
">=",
"0.25.3"
]
]
},
{
"name": "sqlalchemy",
"specs": [
[
">=",
"1.3.12"
]
]
},
{
"name": "alembic",
"specs": [
[
">=",
"1.3.1"
]
]
},
{
"name": "packaging",
"specs": [
[
">=",
"21.0"
]
]
}
],
"lcname": "pangres"
}
JSON
