# easy_db
easy_db is a high-level Python library designed to simplify working with databases. The "DataBase" class handles connecting to various
types of databases while providing simple methods for common tasks. The underlying database connection and cursor can be used when more
precise control is desired.
Currently Supported Databases:
- SQLite
- Access
- ... (Feel free to submit a PR setting up another database type!)
# Goals
- Make common database tasks simple and easy
- Intelligently handle different database types
- Provide intuitive, consistent, Pythonic methods database interaction
- Provide good performance without requiring polished query code
- Expose database connection and cursor to users wanting fine-grained control
- Just get the data into Python so we can use it!
# Why use easy_db?
Before easy_db:
```sh
import pyodbc
import os
conn = pyodbc.connect(
r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};' +
r'DBQ=' + os.path.abspath('MyDatabase.accdb') + ';')
cursor = conn.cursor()
cursor.execute('SELECT * FROM test_table;')
data = cursor.fetchall()
columns = [col[0] for col in cursor.description]
table_data = [dict(zip(columns, row)) for row in data]
# table_data -> [{'column1': value1, 'column2': value2}, {...}, ...]
```
Using easy_db:
```sh
import easy_db
db = easy_db.DataBase('MyDatabase.accdb')
table_data = db.pull('test_table')
# table_data -> [{'column1': value1, 'column2': value2}, {...}, ...]
```
# Quick Start
Let's first connect to a SQLite database.
```sh
import easy_db
db = easy_db.DataBase('test_sqlite3_db.db')
```
Now let's see what tables are available in this database.
```sh
tables = db.table_names()
```
Table columns and types are simple to investigate.
```sh
print(db.columns_and_types('example_table'))
```
Let's pull all of the data from a table. We could start with something like "SELECT * ...", but this is way more fun:
```sh
data = db.pull('example_table')
```
Note that the table/query data is returned as a list of dictionaries with column names as dictionary keys.
- Pro Tip: If desired, a Pandas dataframe of the same form as the database table can be easily created from this data structure using:
```sh
import pandas
df = pandas.DataFrame(data)
```
Now perhaps we have an Access database and would like to pull in a table from our SQLite database. easy_db makes this simple and gracefully handles the nuances of dealing with the different databases.
```sh
db = easy_db.DataBase('test_sqlite3_db.db')
db_2 = easy_db.DataBase('test_access_db.accdb')
db_2.copy_table(db, 'example_table')
```
The DataBase object can be used as a context manager for running custom SQL. The cursor is provided and the connection runs .commit() and .close() implicitly after the "while" block.
```
with db as cursor:
cursor.execute('DELETE * FROM example_table;')
```
# easy_db.DataBase Methods
- Connect to the database...
```
db = easy_db.DataBase(...)
```
## Pulling Data
```
db.pull('tablename')
db.pull_where('tablename', 'sql_condition')
db.pull_where_id_in_list('tablename', 'id_column', match_values_list)
```
## Updating Data
```
db.append('tablename', new_table_rows) # new_table_rows is a list of dicts
db.update('tablename', 'match_column', 'match_value', 'update_column', 'update_value')
db.delete_duplicates('tablename')
```
## Database Info
```
db.table_names()
db.query_names() # for Access
db.columns_and_types('tablename')
db.key_columns('tablename')
db.size # property with size of database in GB
db.compact_db # compact & repair Access db or vacuum SQLite db
```
## Table Manipulation
```
db.create_table('tablename', columns_and_types)
db.drop_table('tablename')
db.copy_table(other_db_with_tablename, 'tablename')
db.add_column('tablename', 'column')
db.drop_column('tablename', 'column')
db.create_index('tablename', 'column')
```
## Custom Control
- Context manager handles opening, commiting, and closing connection
```
with db as cursor:
cursor.execute('SELECT * FROM tablename;') # execute any SQL statement
```
- Can also run .execute() on the database itself (shortcut for the above)
```
db.execute('SELECT * FROM tablename;')
```
# Thanks for checking out easy_db!
License
----
MIT
Raw data
{
"_id": null,
"home_page": "https://github.com/zachbateman/easy_db.git",
"name": "easy-db",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "DATABASE,SIMPLE,EASY",
"author": "Zach Bateman",
"author_email": "",
"download_url": "https://files.pythonhosted.org/packages/9e/8f/6e0649a3bacc3df95f2410718e405f0974f584ec46b7799b3dd91eb0ba2e/easy_db-0.10.0.tar.gz",
"platform": null,
"description": "# easy_db\r\n\r\neasy_db is a high-level Python library designed to simplify working with databases. The \"DataBase\" class handles connecting to various\r\ntypes of databases while providing simple methods for common tasks. The underlying database connection and cursor can be used when more\r\nprecise control is desired.\r\n\r\nCurrently Supported Databases:\r\n - SQLite\r\n - Access\r\n - ... (Feel free to submit a PR setting up another database type!)\r\n\r\n# Goals\r\n\r\n - Make common database tasks simple and easy\r\n - Intelligently handle different database types\r\n - Provide intuitive, consistent, Pythonic methods database interaction\r\n - Provide good performance without requiring polished query code\r\n - Expose database connection and cursor to users wanting fine-grained control\r\n - Just get the data into Python so we can use it!\r\n\r\n\r\n# Why use easy_db?\r\n\r\nBefore easy_db:\r\n```sh\r\nimport pyodbc\r\nimport os\r\n\r\nconn = pyodbc.connect(\r\n r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};' +\r\n r'DBQ=' + os.path.abspath('MyDatabase.accdb') + ';')\r\ncursor = conn.cursor()\r\ncursor.execute('SELECT * FROM test_table;')\r\ndata = cursor.fetchall()\r\ncolumns = [col[0] for col in cursor.description]\r\ntable_data = [dict(zip(columns, row)) for row in data]\r\n\r\n# table_data -> [{'column1': value1, 'column2': value2}, {...}, ...]\r\n```\r\n\r\nUsing easy_db:\r\n```sh\r\nimport easy_db\r\n\r\ndb = easy_db.DataBase('MyDatabase.accdb')\r\ntable_data = db.pull('test_table')\r\n\r\n# table_data -> [{'column1': value1, 'column2': value2}, {...}, ...]\r\n```\r\n\r\n\r\n# Quick Start\r\n\r\nLet's first connect to a SQLite database.\r\n```sh\r\nimport easy_db\r\ndb = easy_db.DataBase('test_sqlite3_db.db')\r\n```\r\n\r\nNow let's see what tables are available in this database.\r\n```sh\r\ntables = db.table_names()\r\n```\r\n\r\nTable columns and types are simple to investigate.\r\n```sh\r\nprint(db.columns_and_types('example_table'))\r\n```\r\n\r\nLet's pull all of the data from a table. We could start with something like \"SELECT * ...\", but this is way more fun:\r\n```sh\r\ndata = db.pull('example_table')\r\n```\r\n\r\nNote that the table/query data is returned as a list of dictionaries with column names as dictionary keys.\r\n\r\n - Pro Tip: If desired, a Pandas dataframe of the same form as the database table can be easily created from this data structure using:\r\n```sh\r\nimport pandas\r\ndf = pandas.DataFrame(data)\r\n```\r\n\r\nNow perhaps we have an Access database and would like to pull in a table from our SQLite database. easy_db makes this simple and gracefully handles the nuances of dealing with the different databases.\r\n```sh\r\ndb = easy_db.DataBase('test_sqlite3_db.db')\r\ndb_2 = easy_db.DataBase('test_access_db.accdb')\r\n\r\ndb_2.copy_table(db, 'example_table')\r\n```\r\n\r\nThe DataBase object can be used as a context manager for running custom SQL. The cursor is provided and the connection runs .commit() and .close() implicitly after the \"while\" block.\r\n```\r\nwith db as cursor:\r\n cursor.execute('DELETE * FROM example_table;')\r\n```\r\n\r\n\r\n\r\n# easy_db.DataBase Methods\r\n - Connect to the database...\r\n```\r\ndb = easy_db.DataBase(...)\r\n```\r\n\r\n## Pulling Data\r\n```\r\ndb.pull('tablename')\r\ndb.pull_where('tablename', 'sql_condition')\r\ndb.pull_where_id_in_list('tablename', 'id_column', match_values_list)\r\n```\r\n\r\n## Updating Data\r\n```\r\ndb.append('tablename', new_table_rows) # new_table_rows is a list of dicts\r\ndb.update('tablename', 'match_column', 'match_value', 'update_column', 'update_value')\r\ndb.delete_duplicates('tablename')\r\n```\r\n\r\n## Database Info\r\n```\r\ndb.table_names()\r\ndb.query_names() # for Access\r\ndb.columns_and_types('tablename')\r\ndb.key_columns('tablename')\r\ndb.size # property with size of database in GB\r\ndb.compact_db # compact & repair Access db or vacuum SQLite db\r\n```\r\n\r\n## Table Manipulation\r\n```\r\ndb.create_table('tablename', columns_and_types)\r\ndb.drop_table('tablename')\r\ndb.copy_table(other_db_with_tablename, 'tablename')\r\ndb.add_column('tablename', 'column')\r\ndb.drop_column('tablename', 'column')\r\ndb.create_index('tablename', 'column')\r\n```\r\n\r\n## Custom Control\r\n - Context manager handles opening, commiting, and closing connection\r\n```\r\nwith db as cursor:\r\n cursor.execute('SELECT * FROM tablename;') # execute any SQL statement\r\n```\r\n - Can also run .execute() on the database itself (shortcut for the above)\r\n ```\r\n db.execute('SELECT * FROM tablename;')\r\n ```\r\n\r\n\r\n# Thanks for checking out easy_db!\r\n\r\nLicense\r\n----\r\nMIT\r\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Easy Python database interaction",
"version": "0.10.0",
"project_urls": {
"Download": "https://github.com/zachbateman/easy_db/archive/v_0.10.0.tar.gz",
"Homepage": "https://github.com/zachbateman/easy_db.git"
},
"split_keywords": [
"database",
"simple",
"easy"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "e953b4fed522f93585d2baff23b0183045fb0d6794e215d26d14cd7e28681db6",
"md5": "c8bd38f0df76f7ae597660e2cf482332",
"sha256": "12d824db10cae5181fd4d674f568b77a64fcb5b3c07a77e3bb64c71efca97b05"
},
"downloads": -1,
"filename": "easy_db-0.10.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "c8bd38f0df76f7ae597660e2cf482332",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 18279,
"upload_time": "2023-10-12T15:13:11",
"upload_time_iso_8601": "2023-10-12T15:13:11.785191Z",
"url": "https://files.pythonhosted.org/packages/e9/53/b4fed522f93585d2baff23b0183045fb0d6794e215d26d14cd7e28681db6/easy_db-0.10.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "9e8f6e0649a3bacc3df95f2410718e405f0974f584ec46b7799b3dd91eb0ba2e",
"md5": "968fcb2dbb25571a5a6070d825c00585",
"sha256": "712ce96c6fc6a96c16b24c79a2b024941d5b2240c97e13204c515c4f617c16e1"
},
"downloads": -1,
"filename": "easy_db-0.10.0.tar.gz",
"has_sig": false,
"md5_digest": "968fcb2dbb25571a5a6070d825c00585",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 19095,
"upload_time": "2023-10-12T15:13:13",
"upload_time_iso_8601": "2023-10-12T15:13:13.318327Z",
"url": "https://files.pythonhosted.org/packages/9e/8f/6e0649a3bacc3df95f2410718e405f0974f584ec46b7799b3dd91eb0ba2e/easy_db-0.10.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-10-12 15:13:13",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "zachbateman",
"github_project": "easy_db",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"requirements": [
{
"name": "pyodbc",
"specs": [
[
">=",
"5.0.0"
]
]
},
{
"name": "tqdm",
"specs": [
[
">=",
"4.31.1"
]
]
}
],
"lcname": "easy-db"
}
JSON
