# Db Common Lib
Mainly for myself, switching between different database providers all the time, I wanted a consistent interface.
Thats what this is for.
More consistent (Think ADO.NET) db connectivity
## Overview
Links psgsql, mysql and sqlite in a common interface
Unit tests provided to show usage
## Usage
Connection string format is: `<provider>://<username>:<password>@<host>/<database>`
The supported providers are:
`sqlite, mysql, pgsql`
```
with SessionFactory.connect(<connection string>) as session:
id = session.execute_lastrowid("insert into test(name) values (:name);", {"name": "bob"})
session.execute("update test set name = :name where id = :id", {"id": id, "name": "bob1"})
```
The session closes and commits automatically when it goes out of scope.
For more control, manually call commit and rollback.
The queries are db specific, but the mechanisms are the same for all.
Major differences are the different field types, the parameter specification and in the use of identity columns across the different db's.
Remember to add `returning <field>` to the insert statement for pgsql or you won't get the identity back.
### Differences
#### sqlite
```
CREATE TABLE test(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name TEXT
);
```
`INSERT INTO test(name) VALUES (:name)`
#### mysql
```
CREATE TABLE test(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NULL
);
```
`INSERT INTO test(name) VALUES (%(name)s);`
#### pgsql
```
CREATE TABLE test(
id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
name VARCHAR(50) NULL
);
```
`INSERT INTO test(name) VALUES (%(name)s) RETURNING id;`
## Building
`python -m build `
## Deploying
`python -m twine upload --repository testpypi dist/*`
Raw data
{
"_id": null,
"home_page": "",
"name": "sb-db-common",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.7",
"maintainer_email": "",
"keywords": "",
"author": "",
"author_email": "Stephen Booth <stephen.booth.za@gmail.com>",
"download_url": "https://files.pythonhosted.org/packages/f0/e6/64979c66fa9125ba2bc99f1df0493274e6a504631a4419d40acf6c5d5109/sb_db_common-0.0.2.tar.gz",
"platform": null,
"description": "# Db Common Lib \r\nMainly for myself, switching between different database providers all the time, I wanted a consistent interface. \r\nThats what this is for. \r\nMore consistent (Think ADO.NET) db connectivity \r\n\r\n## Overview\r\nLinks psgsql, mysql and sqlite in a common interface \r\nUnit tests provided to show usage \r\n\r\n## Usage\r\nConnection string format is: `<provider>://<username>:<password>@<host>/<database>` \r\nThe supported providers are:\r\n`sqlite, mysql, pgsql`\r\n\r\n```\r\nwith SessionFactory.connect(<connection string>) as session:\r\n id = session.execute_lastrowid(\"insert into test(name) values (:name);\", {\"name\": \"bob\"})\r\n session.execute(\"update test set name = :name where id = :id\", {\"id\": id, \"name\": \"bob1\"})\r\n```\r\n\r\nThe session closes and commits automatically when it goes out of scope. \r\nFor more control, manually call commit and rollback.\r\n\r\nThe queries are db specific, but the mechanisms are the same for all. \r\nMajor differences are the different field types, the parameter specification and in the use of identity columns across the different db's. \r\nRemember to add `returning <field>` to the insert statement for pgsql or you won't get the identity back. \r\n\r\n### Differences \r\n#### sqlite\r\n```\r\nCREATE TABLE test(\r\n id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, \r\n name TEXT\r\n);\r\n```\r\n`INSERT INTO test(name) VALUES (:name)`\r\n#### mysql \r\n```\r\nCREATE TABLE test(\r\n id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, \r\n name VARCHAR(50) NULL\r\n);\r\n```\r\n`INSERT INTO test(name) VALUES (%(name)s);`\r\n#### pgsql\r\n```\r\nCREATE TABLE test(\r\n id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY, \r\n name VARCHAR(50) NULL\r\n );\r\n ```\r\n`INSERT INTO test(name) VALUES (%(name)s) RETURNING id;`\r\n\r\n\r\n\r\n## Building\r\n`python -m build `\r\n\r\n## Deploying\r\n`python -m twine upload --repository testpypi dist/*`\r\n",
"bugtrack_url": null,
"license": "",
"summary": "A set of db wrappers",
"version": "0.0.2",
"project_urls": {
"homepage": "https://github.com/sboothza/dbcommon"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "76d5287a7b0359c79454aad8ded210b3bcdfdbd27b49ad1a662ff6482cb93260",
"md5": "1aed902188fea3733a472d26019ab727",
"sha256": "6cd84fab571623e1f2f399e6d44c5ed06a35fa1deb63db580131ed3105a7cc59"
},
"downloads": -1,
"filename": "sb_db_common-0.0.2-py3-none-any.whl",
"has_sig": false,
"md5_digest": "1aed902188fea3733a472d26019ab727",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.7",
"size": 8714,
"upload_time": "2023-10-27T11:21:52",
"upload_time_iso_8601": "2023-10-27T11:21:52.779817Z",
"url": "https://files.pythonhosted.org/packages/76/d5/287a7b0359c79454aad8ded210b3bcdfdbd27b49ad1a662ff6482cb93260/sb_db_common-0.0.2-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "f0e664979c66fa9125ba2bc99f1df0493274e6a504631a4419d40acf6c5d5109",
"md5": "f4614a91d0d9deca379aa23c7c2813cf",
"sha256": "f4c106830dd477c499d233a686f4929483be0e53b0e372979fc5b775f45ebdef"
},
"downloads": -1,
"filename": "sb_db_common-0.0.2.tar.gz",
"has_sig": false,
"md5_digest": "f4614a91d0d9deca379aa23c7c2813cf",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.7",
"size": 6647,
"upload_time": "2023-10-27T11:21:54",
"upload_time_iso_8601": "2023-10-27T11:21:54.488176Z",
"url": "https://files.pythonhosted.org/packages/f0/e6/64979c66fa9125ba2bc99f1df0493274e6a504631a4419d40acf6c5d5109/sb_db_common-0.0.2.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-10-27 11:21:54",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "sboothza",
"github_project": "dbcommon",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"requirements": [
{
"name": "mysql-connector-python",
"specs": [
[
"~=",
"8.1.0"
]
]
},
{
"name": "psycopg2",
"specs": [
[
"~=",
"2.9.9"
]
]
},
{
"name": "protobuf",
"specs": [
[
">=",
"4.21.1"
]
]
}
],
"lcname": "sb-db-common"
}
JSON
