dblite
======
Simple query interface to SQL databases.
Supports SQLite and Postgres.
Full API documentation available at https://suurjaak.github.io/dblite.
- Installation
- Usage
- Queries
- Name quoting
- Adapters and converters
- Row factories
- Object-relational mapping
- SQLite
- Postgres
- API
- Dependencies
Installation
------------
```bash
pip install dblite
```
Usage
-----
```python
import dblite
dblite.init(":memory:") # Open SQLite in-memory database
dblite.executescript("CREATE TABLE test (id INTEGER PRIMARY KEY, val TEXT)")
dblite.insert("test", val=None)
for i in range(5):
dblite.insert("test", {"val": i})
dblite.fetchone("test", id=1) # Queries return dictionaries
dblite.fetchall("test", order="val", limit=3)
dblite.update("test", {"val": None}, val=2)
dblite.fetchall("test", val=("IN", range(3)))
dblite.delete("test", id=5)
dblite.executescript("DROP TABLE test")
```
Provides a simple context manager for transactions:
```python
# dblite.init("sqlite path" or {..postgres opts..})
with dblite.transaction() as tx:
tx.insert("test", val="will be rolled back")
tx.update("test", {"val": "will be rolled back"}, id=0)
raise dblite.Rollback # Rolls back uncommitted actions and exits block
tx.insert("test", val="this will never be reached")
print("continuing, Rollback does not propagate out of managed context")
with dblite.transaction(commit=False) as tx:
tx.insert("test", val="will be committed")
tx.commit() # Commits uncommitted actions
tx.insert("test", val="will be rolled back")
tx.rollback() # Rolls back uncommitted actions
tx.insert("test", val="will be rolled back automatically by Transaction")
```
Queries directly on the Database object use autocommit mode:
every action query gets committed immediately.
Database instances are usable as context managers:
```python
with dblite.init("my.sqlite") as db: # File will be closed on exiting block
db.executescript("CREATE TABLE test (id INTEGER PRIMARY KEY, val TEXT)")
db.insert("test", id=1, val="value")
```
The first Database instance created for engine is cached per engine,
consecutive `init()` calls with no connection options yield the cached instance.
```python
# Create default database for SQLite
dblite.init(":memory:")
# All module-level queries use the very first created
dblite.fetchall("sqlite_master")
# Create default database for Postgres
dblite.init("postgresql://user@localhost/mydb")
# All module-level queries use the very first database created
dblite.fetchone("sqlite_master")
# Access the second default Database by engine name
dblite.init(engine="postgres").fetchall("information_schema.columns")
# Grab references to either
db1 = dblite.init(engine="sqlite")
db2 = dblite.init(engine="postgres")
```
Queries
-------
Columns to `SELECT` can be a string, or a sequence of strings:
```python
# Result: SELECT *
dblite.fetchone("test")
dblite.fetchone("test", "*")
# Result: SELECT id
dblite.fetchone("test", "id")
dblite.fetchone("test", ["id"])
# Result: SELECT id, val
dblite.fetchone("test", "id, val")
dblite.fetchone("test", ["id", "val"])
# Can be arbitrary SQL expressions, invoking functions and assigning aliases
dblite.fetchone("test", "COUNT(*) AS total")
```
Keyword arguments are added to `WHERE` clause, or to `VALUES` clause for `INSERT`:
```python
myid = dblite.insert("test", val="lorem")
dblite.update("test", {"val": "lorem ipsum"}, id=myid)
dblite.fetchone("test", id=myid)
dblite.delete("test", val="lorem ipsum")
```
`WHERE` clause supports simple equality match, binary operators,
collection lookups (`"IN"`, `"NOT IN"`), raw SQL strings, or
arbitrary SQL expressions. Used SQL needs to be supported by the underlying engine.
```python
dblite.fetchall("test", val="ciao")
dblite.fetchall("test", where={"id": ("<", 10)})
dblite.fetchall("test", id=("IN", list(range(5))))
dblite.fetchall("test", val=("!=", None))
dblite.fetchall("test", val=("IS NOT", None))
dblite.fetchall("test", val=("LIKE", "%a%"))
dblite.fetchall("test", where=[("LENGTH(val)", (">", 4))])
dblite.fetchall("test", where=[("EXPR", ("LENGTH(val) > ?", [4]))])
dblite.fetchall("test", where=[("EXPR", ("val = ? OR id > ? or id < ?", [0, 1, 2]))])
```
`WHERE` arguments are `AND`-ed together, `OR` needs subexpressions:
```python
# Result: WHERE (id < 1 OR id > 2) AND val = 3
dblite.fetchall("test", where=[("id < ? OR id > ?", [1, 2]), ("val", 3)])
```
Argument for key-value parameters, like `WHERE` or `VALUES`,
can be a dict, or a sequence of key-value pairs:
```python
# Result: SET val = 'done' WHERE id = 1
dblite.update("test", values={"val": "done"}, where=[("id", 1)])
```
Argument for sequence parameters, like `GROUP BY`, `ORDER BY`, or `LIMIT`,
can be an iterable sequence like list or tuple, or a single value.
```python
# Result: SELECT * FROM test GROUP BY val
dblite.fetchall("test", group="val")
# Result: SELECT * FROM test GROUP BY id, val
dblite.fetchall("test", group="id, val")
dblite.fetchall("test", group=("id", "val"))
```
```python
# Result: SELECT * FROM test ORDER BY id
dblite.fetchall("test", order="id")
dblite.fetchall("test", order="id ASC")
dblite.fetchall("test", order=("id", True))
# Result: SELECT * FROM test ORDER BY id ASC val DESC
dblite.fetchall("test", order="id, val DESC")
dblite.fetchall("test", order=["id", ("val", False)])
dblite.fetchall("test", order=[("id", True), ("val", False)])
dblite.fetchall("test", order=[("id", "ASC"), ("val", "DESC")])
```
```python
# Result: SELECT * FROM test LIMIT 2 OFFSET 0
dblite.fetchall("test", limit=2)
dblite.fetchall("test", limit=(2, 0))
dblite.fetchall("test", limit=(2, -1))
dblite.fetchall("test", limit=(2, None))
# Result: SELECT * FROM test LIMIT 2 OFFSET 10
dblite.fetchall("test", limit=(2, 10))
# Result: SELECT * FROM test OFFSET 10
dblite.fetchall("test", limit=(-1, 10))
dblite.fetchall("test", limit=(None, 10))
```
### Name quoting
Table and column name strings are not quoted automatically. Names with whitespace
or non-alphanumeric characters or reserved words can be quoted with `Database.quote()`
and `Transaction.quote()`:
```python
with dblite.init("my.sqlite") as db:
db.executescript("CREATE TABLE test (id INTEGER PRIMARY KEY, %s TEXT)" %
db.quote("my column"))
db.insert("test", {"id": 1, db.quote("my column"): "value"})
for row in db.select("test"):
print(row["my column"])
```
Note that in Postgres, quoted identifiers are case-sensitive.
Table and column names that were given as data classes and class members,
*are* quoted automatically if their values need escaping,
see name quoting in objects.
Adapters and converters
-----------------------
Provides options to register custom adapters and converters,
to auto-adapt Python types to database types in query parameters,
and to auto-convert database types to Python types in query results.
```python
dblite.init(":memory:")
dblite.register_adapter(json.dumps, (dict, list, tuple))
dblite.register_converter(json.loads, "JSON")
dblite.executescript("CREATE TABLE test (id INTEGER PRIMARY KEY, data JSON)")
dblite.insert("test", id=1, data={"some": {"nested": ["data", 1, 2]}})
dblite.fetchone("test") # `data` is auto-converted to Python dictionary
```
Row factories
-------------
A custom row factory can be specified, to return results as desired type instead of dictionaries.
```python
def kvfactory(cursor, row): # Returns row as [(colname, value), ].
return list(zip([c[0] for c in cursor.description], row))
dblite.init(":memory:")
dblite.register_row_factory(kvfactory)
dblite.executescript("CREATE TABLE test (id INTEGER PRIMARY KEY, val TEXT)")
for row in dblite.select("sqlite_master"):
print(row) # Prints [("type", "table"), ("name", "test"), ..]
```
Row factory can also be specified per Database:
```python
db = dblite.init(":memory:")
db.row_factory = lambda cursor, row: row
db.executescript("CREATE TABLE test (id INTEGER PRIMARY KEY, val TEXT)")
for row in db.select("sqlite_master"):
print(row) # Prints ("table", "test", ..)
```
Database row factory overrides the globally registered factory, if any.
Object-relational mapping
-------------------------
dblite uses dictionaries as rows by default, but can just as easily operate with
various types of data classes and objects, using classes in place of table names
and objects in place of data dictionaries.
And if data attributes have been declared as properties on the class,
the class properties can be used directly in dblite in place of column names,
e.g. for `ORDER BY` clause.
(Such data descriptor properties are automatically available for
`property` attributes, classes with `__slots__`, and namedtuples).
### Data classes
```python
schema = "CREATE TABLE devices (id INTEGER PRIMARY KEY, name TEXT)"
class Device(object):
def __init__(self, id=None, name=None):
self._id = id
self._name = name
def get_id(self): return self._id
def set_id(self, id): self._id = id
id = property(get_id, set_id)
def get_name(self): return self._name
def set_name(self, name): self._name = name
name = property(get_name, set_name)
Device.__name__ = "devices" # cls.__name__ will be used as table name
dblite.init(":memory:").executescript(schema)
device = Device(name="lidar")
device.id = dblite.insert(Device, device)
device.name = "solid-state lidar"
dblite.update(Device, device, {Device.id: device.id})
device = dblite.fetchone(Device, Device.id, where=device)
print(device.name) # Will be None as we only selected Device.id
for device in dblite.fetchall(Device, order=Device.name):
print(device.id, device.name)
dblite.delete(Device, device)
```
It is also possible to use very simple data classes with no declared properties.
```python
schema = "CREATE TABLE devices (id INTEGER PRIMARY KEY, name TEXT)"
class Device(object):
def __init__(self, id=None, name=None):
self.id = id
self.name = name
Device.__name__ = "devices" # cls.__name__ will be used as table name
dblite.init(":memory:").executescript(schema)
device = Device(name="lidar")
device.id = dblite.insert(Device, device)
device.name = "solid-state lidar"
dblite.update(Device, device, id=device.id)
device = dblite.fetchone(Device, "id", where=device)
print(device.name) # Will be None as we only selected Device.id
for device in dblite.fetchall(Device, order="name"):
print(device.id, device.name)
dblite.delete(Device, device)
```
### Classes with `__slots__`
```python
schema = "CREATE TABLE devices (id INTEGER PRIMARY KEY, name TEXT)"
class Device(object):
__slots__ = ("id", "name")
def __init__(self, id=None, name=None):
self.id = id
self.name = name
Device.__name__ = "devices" # cls.__name__ will be used as table name
dblite.init(":memory:").executescript(schema)
device = Device(name="lidar")
device.id = dblite.insert(Device, device)
device.name = "solid-state lidar"
dblite.update(Device, device, id=device.id)
device = dblite.fetchone(Device, Device.id, where=device)
print(device.name) # Will be None as we only selected Device.id
for device in dblite.fetchall(Device, order=Device.name):
print(device.id, device.name)
dblite.delete(Device, device)
```
### namedtuple
Works with both `collections.namedtuple` and `typing.NamedTuple`.
```python
schema = "CREATE TABLE devices (id INTEGER PRIMARY KEY, name TEXT)"
Device = collections.namedtuple("devices", ("id", "name"))
dblite.init(":memory:").executescript(schema)
device = Device(id=None, name="lidar")
device_id = dblite.insert(Device, device)
device = Device(id=device_id, name="solid-state lidar")
dblite.update(Device, device, {Device.id: device_id})
device = dblite.fetchone(Device, Device.id, where=device)
print(device.name) # Will be None as we only selected Device.id
for device in dblite.fetchall(Device, order=Device.name):
print(device.id, device.name)
dblite.delete(Device, device)
```
### Name quoting in objects
dblite automatically quotes table and column names in queries when using objects as arguments.
```python
schema = 'CREATE TABLE "restaurant bookings" ("group" TEXT, "table" TEXT, "when" TIMESTAMP, "PATRON" BOOLEAN)'
Booking = collections.namedtuple("_", ("group", "table", "when", "patron"))
Booking.__name__ = "restaurant bookings"
dblite.init(":memory:").executescript(schema)
booking1 = Booking("Squirrel Charity", "Table 16", datetime.datetime(2022, 12, 30, 20, 30), False)
booking2 = Booking("The Three Henrys", "Table 23", datetime.datetime(2022, 12, 30, 19, 00), True)
dblite.insert(Booking, booking1)
dblite.insert(Booking, booking2)
for booking in dblite.fetchall(Booking, order=Booking.when):
print(booking.when, booking.group, booking.table, booking.patron)
```
For more thorough examples on using objects, see test/test_orm.py.
In Postgres, schema definition is looked up from the database to ensure properly cased
names in queries, as cased names for Postgres tables and columns must use the declared form.
If there is no exact match for the Python name in database, falls back to lower-case name,
or if name is lower-case, falls back to cased name if database has a single matching cased name.
SQLite
------
SQLite connection parameter needs to be a valid path or a path-like object,
or the special `":memory:"` for transient in-memory database.
Connection flags default to `check_same_thread=False, detect_types=sqlite3.PARSE_DECLTYPES`,
can be overridden on init:
```python
dblite.init("/path/to/my.db", detect_types=False)
```
Note that SQLite connections do not support multiple concurrent isolated transactions,
transaction state is shared per connection. To mitigate this, Transaction contexts
in SQLite default to exclusive access:
```python
dblite.init(":memory:")
with dblite.transaction() as tx:
print("Entering another Transaction with-block will block until this exits.")
```
This can be overridden for `SELECT`-only transactions:
```python
dblite.init(":memory:")
with dblite.transaction(exclusive=False) as tx:
print("Will only be doing SELECT queries, no need for exclusion.")
tx.fetchall("test")
```
Postgres
--------
Postgres connection parameters can be:
- Postgres URI scheme `"postgresql://user:pass@hostname:port/dbname?parameter1=val1&.."`
- Postgres keyword-value format `"user=myuser password=mypass host=myhost port=myport dbname=myname .."`
- dictionary of connection options `{"user": "myuser", "host": "myhost", ..}`
```python
# These are all equivalent:
dblite.init("postgresql://myuser@myhost/mydb")
dblite.init("user=myuser host=myhost dbname=mydb")
dblite.init({"user": "myuser", "host": "myhost", "dbname": "mydb"})
```
Postgres connection parameters can also be specified in OS environment,
via standard Postgres environment variables like `PGUSER` and `PGPASSWORD`.
By default uses a pool of 1..4 connections per Database.
```python
with dblite.init("host=localhost user=postgres dbname=mydb", maxconn=1):
print("Use a pool of only 1 connection.")
with dblite.init("host=localhost user=postgres dbname=mydb", minconn=4, maxconn=8):
print("Use a pool of 4..8 connections.")
```
Postgres transactions can specify database table schema name up front:
```python
dblite.init("host=localhost user=postgres dbname=mydb")
with dblite.transaction(schema="information_schema") as tx:
for row in tx.fetchall("columns", table_schema="public",
order="table_name, ordinal_position"):
print(row["table_name"], row["column_name"], row["data_type"])
```
Postgres transactions support server-side cursors for iterative data access,
fetching and materializing rows in batches:
```python
dblite.init("host=localhost user=postgres dbname=bigdata")
with Transaction(lazy=True) as tx: # Can only run a single query
for i, row in enumerate(tx.select("some really huge table")):
print("Processing row #%s" % i)
# Can also specify size of fetched batches (default is 2000 rows)
with Transaction(lazy=True, itersize=100) as tx:
for i, row in enumerate(tx.select("some really huge table")):
print("Processing row #%s" % i)
```
Note that `executescript()` in Postgres forces an internal reload of schema metadata,
allowing `insert()` to return inserted primary key value for newly created tables,
and query parameters to be auto-cast to expected column types.
API
---
| Name | Description
| --------------------------------------- | -----------------------------------------------------------------------------------------
| `dblite.init()` | returns a opened `dblite.Database` object, the first created if no options given
| `dblite.fetchall()` | runs `SELECT`, returns all rows
| `dblite.fetchone()` | runs `SELECT`, returns a single row, or `None`
| `dblite.insert()` | `INSERT` a single row into table, returns inserted ID
| `dblite.insertmany()` | `INSERT` multiple rows into table, returns a list of inserted IDs
| `dblite.select()` | runs `SELECT`, returns cursor
| `dblite.update()` | `UPDATE` table, returns affected row count
| `dblite.delete()` | `DELETE` from table, returns affected row count
| `dblite.execute()` | executes SQL with arguments, returns cursor
| `dblite.executemany()` | executes SQL against all parameter sequences
| `dblite.executescript()` | executes SQL as a script of one or more SQL statements
| `dblite.close()` | closes the database and all pending transactions, if open
| `dblite.transaction()` | returns `dblite.Transaction` context manager
| `dblite.register_adapter()` | registers function to auto-adapt given Python types to database types in query parameters
| `dblite.register_converter()` | registers function to auto-convert given database types to Python in query results
| `dblite.register_row_factory()` | registers function to produce query results as custom type
| | |
| **dblite.Database** | |
| `Database.fetchall()` | runs `SELECT`, returns all rows
| `Database.fetchone()` | runs `SELECT`, returns a single row, or `None`
| `Database.insert()` | `INSERT` a single row into table, returns inserted ID
| `Database.insertmany()` | `INSERT` multiple rows into table, returns a list of inserted IDs
| `Database.select()` | runs `SELECT`, returns cursor
| `Database.update()` | `UPDATE` table, returns affected row count
| `Database.delete()` | `DELETE` from table, returns affected row count
| `Database.execute()` | executes SQL with arguments, returns cursor
| `Database.executemany()` | executes SQL against all parameter sequences
| `Database.executescript()` | executes SQL as a script of one or more SQL statements
| `Database.transaction()` | returns `dblite.Transaction` context manager
| `Database.open()` | opens database connection if not already open
| `Database.close()` | closes the database and all pending transactions, if open
| `Database.closed` | whether database is not open
| `Database.cursor` | database engine cursor object
| `Database.row_factory` | custom row factory, as `function(cursor, row tuple)`
| `Database.ENGINE` | underlying database engine, `"sqlite"` for SQLite3 and `"postgres"` for PostgreSQL
| | |
| **dblite.Transaction** | |
| `Transaction.fetchall()` | runs `SELECT`, returns all rows
| `Transaction.fetchone()` | runs `SELECT`, returns a single row, or `None`
| `Transaction.insert()` | `INSERT` a single row into table, returns inserted ID
| `Transaction.insertmany()` | `INSERT` multiple rows into table, returns a list of inserted IDs
| `Transaction.select()` | runs `SELECT`, returns cursor
| `Transaction.update()` | `UPDATE` table, returns affected row count
| `Transaction.delete()` | `DELETE` from table, returns affected row count
| `Transaction.execute()` | executes SQL with arguments, returns cursor
| `Transaction.executemany()` | executes SQL against all parameter sequences
| `Transaction.executescript()` | executes SQL as a script of one or more SQL statements
| `Transaction.commit()` | commits pending actions, if any
| `Transaction.rollback()` | rolls back pending actions, if any
| `Transaction.close()` | closes the transaction, performing commit or rollback as specified
| `Transaction.closed` | whether transaction is not open
| `Transaction.cursor` | database engine cursor object
| `Transaction.database` | transaction `Database` instance
| `Database.ENGINE` | underlying database engine, `"sqlite"` for SQLite3 and `"postgres"` for PostgreSQL
Dependencies
------------
- Python 3 or Python 2.7
- six (https://pypi.org/project/six)
If using Postgres:
- psycopg2 (https://pypi.org/project/psycopg2)
Raw data
{
"_id": null,
"home_page": "https://github.com/suurjaak/dblite",
"name": "dblite",
"maintainer": "",
"docs_url": null,
"requires_python": ">=2.7",
"maintainer_email": "",
"keywords": "SQL SQLite Postgres psycopg2",
"author": "Erki Suurjaak",
"author_email": "erki@lap.ee",
"download_url": "",
"platform": "any",
"description": "dblite\r\n======\r\n\r\nSimple query interface to SQL databases.\r\n\r\nSupports SQLite and Postgres.\r\n\r\nFull API documentation available at https://suurjaak.github.io/dblite.\r\n\r\n- Installation\r\n- Usage\r\n- Queries\r\n - Name quoting\r\n- Adapters and converters\r\n- Row factories\r\n- Object-relational mapping\r\n- SQLite\r\n- Postgres\r\n- API\r\n- Dependencies\r\n\r\n\r\nInstallation\r\n------------\r\n\r\n```bash\r\npip install dblite\r\n```\r\n\r\n\r\nUsage\r\n-----\r\n\r\n```python\r\nimport dblite\r\n\r\ndblite.init(\":memory:\") # Open SQLite in-memory database\r\ndblite.executescript(\"CREATE TABLE test (id INTEGER PRIMARY KEY, val TEXT)\")\r\ndblite.insert(\"test\", val=None)\r\nfor i in range(5):\r\n dblite.insert(\"test\", {\"val\": i})\r\ndblite.fetchone(\"test\", id=1) # Queries return dictionaries\r\ndblite.fetchall(\"test\", order=\"val\", limit=3)\r\ndblite.update(\"test\", {\"val\": None}, val=2)\r\ndblite.fetchall(\"test\", val=(\"IN\", range(3)))\r\ndblite.delete(\"test\", id=5)\r\ndblite.executescript(\"DROP TABLE test\")\r\n```\r\n\r\n\r\nProvides a simple context manager for transactions:\r\n\r\n```python\r\n# dblite.init(\"sqlite path\" or {..postgres opts..})\r\nwith dblite.transaction() as tx:\r\n tx.insert(\"test\", val=\"will be rolled back\")\r\n tx.update(\"test\", {\"val\": \"will be rolled back\"}, id=0)\r\n raise dblite.Rollback # Rolls back uncommitted actions and exits block\r\n tx.insert(\"test\", val=\"this will never be reached\")\r\nprint(\"continuing, Rollback does not propagate out of managed context\")\r\n\r\nwith dblite.transaction(commit=False) as tx:\r\n tx.insert(\"test\", val=\"will be committed\")\r\n tx.commit() # Commits uncommitted actions\r\n tx.insert(\"test\", val=\"will be rolled back\")\r\n tx.rollback() # Rolls back uncommitted actions\r\n tx.insert(\"test\", val=\"will be rolled back automatically by Transaction\")\r\n```\r\n\r\nQueries directly on the Database object use autocommit mode:\r\nevery action query gets committed immediately.\r\n\r\nDatabase instances are usable as context managers:\r\n\r\n```python\r\nwith dblite.init(\"my.sqlite\") as db: # File will be closed on exiting block\r\n db.executescript(\"CREATE TABLE test (id INTEGER PRIMARY KEY, val TEXT)\")\r\n db.insert(\"test\", id=1, val=\"value\")\r\n```\r\n\r\nThe first Database instance created for engine is cached per engine,\r\nconsecutive `init()` calls with no connection options yield the cached instance.\r\n\r\n```python\r\n# Create default database for SQLite\r\ndblite.init(\":memory:\")\r\n# All module-level queries use the very first created\r\ndblite.fetchall(\"sqlite_master\")\r\n\r\n# Create default database for Postgres\r\ndblite.init(\"postgresql://user@localhost/mydb\")\r\n# All module-level queries use the very first database created\r\ndblite.fetchone(\"sqlite_master\")\r\n# Access the second default Database by engine name\r\ndblite.init(engine=\"postgres\").fetchall(\"information_schema.columns\")\r\n\r\n# Grab references to either\r\ndb1 = dblite.init(engine=\"sqlite\")\r\ndb2 = dblite.init(engine=\"postgres\")\r\n```\r\n\r\n\r\nQueries\r\n-------\r\n\r\nColumns to `SELECT` can be a string, or a sequence of strings:\r\n\r\n```python\r\n# Result: SELECT *\r\ndblite.fetchone(\"test\")\r\ndblite.fetchone(\"test\", \"*\")\r\n\r\n# Result: SELECT id\r\ndblite.fetchone(\"test\", \"id\")\r\ndblite.fetchone(\"test\", [\"id\"])\r\n\r\n# Result: SELECT id, val\r\ndblite.fetchone(\"test\", \"id, val\")\r\ndblite.fetchone(\"test\", [\"id\", \"val\"])\r\n\r\n# Can be arbitrary SQL expressions, invoking functions and assigning aliases\r\ndblite.fetchone(\"test\", \"COUNT(*) AS total\")\r\n```\r\n\r\n\r\nKeyword arguments are added to `WHERE` clause, or to `VALUES` clause for `INSERT`:\r\n\r\n```python\r\nmyid = dblite.insert(\"test\", val=\"lorem\")\r\ndblite.update(\"test\", {\"val\": \"lorem ipsum\"}, id=myid)\r\ndblite.fetchone(\"test\", id=myid)\r\ndblite.delete(\"test\", val=\"lorem ipsum\")\r\n```\r\n\r\n\r\n`WHERE` clause supports simple equality match, binary operators,\r\ncollection lookups (`\"IN\"`, `\"NOT IN\"`), raw SQL strings, or\r\narbitrary SQL expressions. Used SQL needs to be supported by the underlying engine.\r\n\r\n```python\r\ndblite.fetchall(\"test\", val=\"ciao\")\r\ndblite.fetchall(\"test\", where={\"id\": (\"<\", 10)})\r\ndblite.fetchall(\"test\", id=(\"IN\", list(range(5))))\r\ndblite.fetchall(\"test\", val=(\"!=\", None))\r\ndblite.fetchall(\"test\", val=(\"IS NOT\", None))\r\ndblite.fetchall(\"test\", val=(\"LIKE\", \"%a%\"))\r\ndblite.fetchall(\"test\", where=[(\"LENGTH(val)\", (\">\", 4))])\r\n\r\ndblite.fetchall(\"test\", where=[(\"EXPR\", (\"LENGTH(val) > ?\", [4]))])\r\ndblite.fetchall(\"test\", where=[(\"EXPR\", (\"val = ? OR id > ? or id < ?\", [0, 1, 2]))])\r\n```\r\n\r\n\r\n`WHERE` arguments are `AND`-ed together, `OR` needs subexpressions:\r\n\r\n```python\r\n# Result: WHERE (id < 1 OR id > 2) AND val = 3\r\ndblite.fetchall(\"test\", where=[(\"id < ? OR id > ?\", [1, 2]), (\"val\", 3)])\r\n```\r\n\r\n\r\nArgument for key-value parameters, like `WHERE` or `VALUES`,\r\ncan be a dict, or a sequence of key-value pairs:\r\n\r\n```python\r\n# Result: SET val = 'done' WHERE id = 1\r\ndblite.update(\"test\", values={\"val\": \"done\"}, where=[(\"id\", 1)])\r\n```\r\n\r\n\r\nArgument for sequence parameters, like `GROUP BY`, `ORDER BY`, or `LIMIT`,\r\ncan be an iterable sequence like list or tuple, or a single value.\r\n\r\n```python\r\n# Result: SELECT * FROM test GROUP BY val\r\ndblite.fetchall(\"test\", group=\"val\")\r\n# Result: SELECT * FROM test GROUP BY id, val\r\ndblite.fetchall(\"test\", group=\"id, val\")\r\ndblite.fetchall(\"test\", group=(\"id\", \"val\"))\r\n```\r\n\r\n```python\r\n# Result: SELECT * FROM test ORDER BY id\r\ndblite.fetchall(\"test\", order=\"id\")\r\ndblite.fetchall(\"test\", order=\"id ASC\")\r\ndblite.fetchall(\"test\", order=(\"id\", True))\r\n# Result: SELECT * FROM test ORDER BY id ASC val DESC\r\ndblite.fetchall(\"test\", order=\"id, val DESC\")\r\ndblite.fetchall(\"test\", order=[\"id\", (\"val\", False)])\r\ndblite.fetchall(\"test\", order=[(\"id\", True), (\"val\", False)])\r\ndblite.fetchall(\"test\", order=[(\"id\", \"ASC\"), (\"val\", \"DESC\")])\r\n```\r\n\r\n```python\r\n# Result: SELECT * FROM test LIMIT 2 OFFSET 0\r\ndblite.fetchall(\"test\", limit=2)\r\ndblite.fetchall(\"test\", limit=(2, 0))\r\ndblite.fetchall(\"test\", limit=(2, -1))\r\ndblite.fetchall(\"test\", limit=(2, None))\r\n# Result: SELECT * FROM test LIMIT 2 OFFSET 10\r\ndblite.fetchall(\"test\", limit=(2, 10))\r\n# Result: SELECT * FROM test OFFSET 10\r\ndblite.fetchall(\"test\", limit=(-1, 10))\r\ndblite.fetchall(\"test\", limit=(None, 10))\r\n```\r\n\r\n\r\n### Name quoting\r\n\r\nTable and column name strings are not quoted automatically. Names with whitespace\r\nor non-alphanumeric characters or reserved words can be quoted with `Database.quote()`\r\nand `Transaction.quote()`:\r\n\r\n```python\r\nwith dblite.init(\"my.sqlite\") as db:\r\n db.executescript(\"CREATE TABLE test (id INTEGER PRIMARY KEY, %s TEXT)\" %\r\n db.quote(\"my column\"))\r\n db.insert(\"test\", {\"id\": 1, db.quote(\"my column\"): \"value\"})\r\n for row in db.select(\"test\"):\r\n print(row[\"my column\"])\r\n```\r\n\r\nNote that in Postgres, quoted identifiers are case-sensitive.\r\n\r\nTable and column names that were given as data classes and class members,\r\n*are* quoted automatically if their values need escaping,\r\nsee name quoting in objects.\r\n\r\n\r\nAdapters and converters\r\n-----------------------\r\n\r\nProvides options to register custom adapters and converters,\r\nto auto-adapt Python types to database types in query parameters,\r\nand to auto-convert database types to Python types in query results.\r\n\r\n```python\r\ndblite.init(\":memory:\")\r\ndblite.register_adapter(json.dumps, (dict, list, tuple))\r\ndblite.register_converter(json.loads, \"JSON\")\r\n\r\ndblite.executescript(\"CREATE TABLE test (id INTEGER PRIMARY KEY, data JSON)\")\r\ndblite.insert(\"test\", id=1, data={\"some\": {\"nested\": [\"data\", 1, 2]}})\r\ndblite.fetchone(\"test\") # `data` is auto-converted to Python dictionary\r\n```\r\n\r\n\r\nRow factories\r\n-------------\r\n\r\nA custom row factory can be specified, to return results as desired type instead of dictionaries.\r\n\r\n```python\r\ndef kvfactory(cursor, row): # Returns row as [(colname, value), ].\r\n return list(zip([c[0] for c in cursor.description], row))\r\n\r\ndblite.init(\":memory:\")\r\ndblite.register_row_factory(kvfactory)\r\ndblite.executescript(\"CREATE TABLE test (id INTEGER PRIMARY KEY, val TEXT)\")\r\nfor row in dblite.select(\"sqlite_master\"):\r\n print(row) # Prints [(\"type\", \"table\"), (\"name\", \"test\"), ..]\r\n```\r\n\r\nRow factory can also be specified per Database:\r\n\r\n```python\r\ndb = dblite.init(\":memory:\")\r\ndb.row_factory = lambda cursor, row: row\r\ndb.executescript(\"CREATE TABLE test (id INTEGER PRIMARY KEY, val TEXT)\")\r\nfor row in db.select(\"sqlite_master\"):\r\n print(row) # Prints (\"table\", \"test\", ..)\r\n```\r\n\r\nDatabase row factory overrides the globally registered factory, if any.\r\n\r\n\r\nObject-relational mapping\r\n-------------------------\r\n\r\ndblite uses dictionaries as rows by default, but can just as easily operate with\r\nvarious types of data classes and objects, using classes in place of table names\r\nand objects in place of data dictionaries.\r\n\r\nAnd if data attributes have been declared as properties on the class,\r\nthe class properties can be used directly in dblite in place of column names,\r\ne.g. for `ORDER BY` clause.\r\n\r\n(Such data descriptor properties are automatically available for\r\n`property` attributes, classes with `__slots__`, and namedtuples).\r\n\r\n### Data classes\r\n\r\n```python\r\nschema = \"CREATE TABLE devices (id INTEGER PRIMARY KEY, name TEXT)\"\r\nclass Device(object):\r\n def __init__(self, id=None, name=None):\r\n self._id = id\r\n self._name = name\r\n\r\n def get_id(self): return self._id\r\n def set_id(self, id): self._id = id\r\n id = property(get_id, set_id)\r\n\r\n def get_name(self): return self._name\r\n def set_name(self, name): self._name = name\r\n name = property(get_name, set_name)\r\nDevice.__name__ = \"devices\" # cls.__name__ will be used as table name\r\n\r\ndblite.init(\":memory:\").executescript(schema)\r\n\r\ndevice = Device(name=\"lidar\")\r\ndevice.id = dblite.insert(Device, device)\r\n\r\ndevice.name = \"solid-state lidar\"\r\ndblite.update(Device, device, {Device.id: device.id})\r\n\r\ndevice = dblite.fetchone(Device, Device.id, where=device)\r\nprint(device.name) # Will be None as we only selected Device.id\r\n\r\nfor device in dblite.fetchall(Device, order=Device.name):\r\n print(device.id, device.name)\r\n dblite.delete(Device, device)\r\n```\r\n\r\nIt is also possible to use very simple data classes with no declared properties.\r\n\r\n```python\r\nschema = \"CREATE TABLE devices (id INTEGER PRIMARY KEY, name TEXT)\"\r\nclass Device(object):\r\n def __init__(self, id=None, name=None):\r\n self.id = id\r\n self.name = name\r\nDevice.__name__ = \"devices\" # cls.__name__ will be used as table name\r\n\r\ndblite.init(\":memory:\").executescript(schema)\r\n\r\ndevice = Device(name=\"lidar\")\r\ndevice.id = dblite.insert(Device, device)\r\n\r\ndevice.name = \"solid-state lidar\"\r\ndblite.update(Device, device, id=device.id)\r\n\r\ndevice = dblite.fetchone(Device, \"id\", where=device)\r\nprint(device.name) # Will be None as we only selected Device.id\r\n\r\nfor device in dblite.fetchall(Device, order=\"name\"):\r\n print(device.id, device.name)\r\n dblite.delete(Device, device)\r\n```\r\n\r\n### Classes with `__slots__`\r\n\r\n```python\r\nschema = \"CREATE TABLE devices (id INTEGER PRIMARY KEY, name TEXT)\"\r\nclass Device(object):\r\n __slots__ = (\"id\", \"name\")\r\n def __init__(self, id=None, name=None):\r\n self.id = id\r\n self.name = name\r\nDevice.__name__ = \"devices\" # cls.__name__ will be used as table name\r\n\r\ndblite.init(\":memory:\").executescript(schema)\r\n\r\ndevice = Device(name=\"lidar\")\r\ndevice.id = dblite.insert(Device, device)\r\n\r\ndevice.name = \"solid-state lidar\"\r\ndblite.update(Device, device, id=device.id)\r\n\r\ndevice = dblite.fetchone(Device, Device.id, where=device)\r\nprint(device.name) # Will be None as we only selected Device.id\r\n\r\nfor device in dblite.fetchall(Device, order=Device.name):\r\n print(device.id, device.name)\r\n dblite.delete(Device, device)\r\n```\r\n\r\n### namedtuple\r\n\r\nWorks with both `collections.namedtuple` and `typing.NamedTuple`.\r\n\r\n```python\r\nschema = \"CREATE TABLE devices (id INTEGER PRIMARY KEY, name TEXT)\"\r\nDevice = collections.namedtuple(\"devices\", (\"id\", \"name\"))\r\n\r\ndblite.init(\":memory:\").executescript(schema)\r\n\r\ndevice = Device(id=None, name=\"lidar\")\r\ndevice_id = dblite.insert(Device, device)\r\n\r\ndevice = Device(id=device_id, name=\"solid-state lidar\")\r\ndblite.update(Device, device, {Device.id: device_id})\r\n\r\ndevice = dblite.fetchone(Device, Device.id, where=device)\r\nprint(device.name) # Will be None as we only selected Device.id\r\n\r\nfor device in dblite.fetchall(Device, order=Device.name):\r\n print(device.id, device.name)\r\n dblite.delete(Device, device)\r\n```\r\n\r\n### Name quoting in objects\r\n\r\ndblite automatically quotes table and column names in queries when using objects as arguments.\r\n\r\n```python\r\nschema = 'CREATE TABLE \"restaurant bookings\" (\"group\" TEXT, \"table\" TEXT, \"when\" TIMESTAMP, \"PATRON\" BOOLEAN)'\r\nBooking = collections.namedtuple(\"_\", (\"group\", \"table\", \"when\", \"patron\"))\r\nBooking.__name__ = \"restaurant bookings\"\r\n\r\ndblite.init(\":memory:\").executescript(schema)\r\n\r\nbooking1 = Booking(\"Squirrel Charity\", \"Table 16\", datetime.datetime(2022, 12, 30, 20, 30), False)\r\nbooking2 = Booking(\"The Three Henrys\", \"Table 23\", datetime.datetime(2022, 12, 30, 19, 00), True)\r\ndblite.insert(Booking, booking1)\r\ndblite.insert(Booking, booking2)\r\n\r\nfor booking in dblite.fetchall(Booking, order=Booking.when):\r\n print(booking.when, booking.group, booking.table, booking.patron)\r\n```\r\n\r\nFor more thorough examples on using objects, see test/test_orm.py.\r\n\r\nIn Postgres, schema definition is looked up from the database to ensure properly cased\r\nnames in queries, as cased names for Postgres tables and columns must use the declared form.\r\nIf there is no exact match for the Python name in database, falls back to lower-case name,\r\nor if name is lower-case, falls back to cased name if database has a single matching cased name.\r\n\r\n\r\nSQLite\r\n------\r\n\r\nSQLite connection parameter needs to be a valid path or a path-like object,\r\nor the special `\":memory:\"` for transient in-memory database.\r\n\r\nConnection flags default to `check_same_thread=False, detect_types=sqlite3.PARSE_DECLTYPES`,\r\ncan be overridden on init:\r\n\r\n```python\r\ndblite.init(\"/path/to/my.db\", detect_types=False)\r\n```\r\n\r\nNote that SQLite connections do not support multiple concurrent isolated transactions,\r\ntransaction state is shared per connection. To mitigate this, Transaction contexts\r\nin SQLite default to exclusive access:\r\n\r\n```python\r\ndblite.init(\":memory:\")\r\nwith dblite.transaction() as tx:\r\n print(\"Entering another Transaction with-block will block until this exits.\")\r\n```\r\n\r\nThis can be overridden for `SELECT`-only transactions:\r\n\r\n```python\r\ndblite.init(\":memory:\")\r\nwith dblite.transaction(exclusive=False) as tx:\r\n print(\"Will only be doing SELECT queries, no need for exclusion.\")\r\n tx.fetchall(\"test\")\r\n```\r\n\r\n\r\nPostgres\r\n--------\r\n\r\nPostgres connection parameters can be:\r\n- Postgres URI scheme `\"postgresql://user:pass@hostname:port/dbname?parameter1=val1&..\"`\r\n- Postgres keyword-value format `\"user=myuser password=mypass host=myhost port=myport dbname=myname ..\"`\r\n- dictionary of connection options `{\"user\": \"myuser\", \"host\": \"myhost\", ..}`\r\n\r\n```python\r\n# These are all equivalent:\r\ndblite.init(\"postgresql://myuser@myhost/mydb\")\r\ndblite.init(\"user=myuser host=myhost dbname=mydb\")\r\ndblite.init({\"user\": \"myuser\", \"host\": \"myhost\", \"dbname\": \"mydb\"})\r\n```\r\n\r\nPostgres connection parameters can also be specified in OS environment,\r\nvia standard Postgres environment variables like `PGUSER` and `PGPASSWORD`.\r\n\r\nBy default uses a pool of 1..4 connections per Database.\r\n\r\n```python\r\nwith dblite.init(\"host=localhost user=postgres dbname=mydb\", maxconn=1):\r\n print(\"Use a pool of only 1 connection.\")\r\nwith dblite.init(\"host=localhost user=postgres dbname=mydb\", minconn=4, maxconn=8):\r\n print(\"Use a pool of 4..8 connections.\")\r\n```\r\n\r\nPostgres transactions can specify database table schema name up front:\r\n\r\n```python\r\ndblite.init(\"host=localhost user=postgres dbname=mydb\")\r\nwith dblite.transaction(schema=\"information_schema\") as tx:\r\n for row in tx.fetchall(\"columns\", table_schema=\"public\",\r\n order=\"table_name, ordinal_position\"):\r\n print(row[\"table_name\"], row[\"column_name\"], row[\"data_type\"])\r\n```\r\n\r\nPostgres transactions support server-side cursors for iterative data access,\r\nfetching and materializing rows in batches:\r\n\r\n```python\r\ndblite.init(\"host=localhost user=postgres dbname=bigdata\")\r\nwith Transaction(lazy=True) as tx: # Can only run a single query\r\n for i, row in enumerate(tx.select(\"some really huge table\")):\r\n print(\"Processing row #%s\" % i)\r\n\r\n# Can also specify size of fetched batches (default is 2000 rows)\r\nwith Transaction(lazy=True, itersize=100) as tx:\r\n for i, row in enumerate(tx.select(\"some really huge table\")):\r\n print(\"Processing row #%s\" % i)\r\n```\r\n\r\nNote that `executescript()` in Postgres forces an internal reload of schema metadata,\r\nallowing `insert()` to return inserted primary key value for newly created tables,\r\nand query parameters to be auto-cast to expected column types.\r\n\r\n\r\nAPI\r\n---\r\n\r\n| Name | Description\r\n| --------------------------------------- | -----------------------------------------------------------------------------------------\r\n| `dblite.init()` | returns a opened `dblite.Database` object, the first created if no options given\r\n| `dblite.fetchall()` | runs `SELECT`, returns all rows\r\n| `dblite.fetchone()` | runs `SELECT`, returns a single row, or `None`\r\n| `dblite.insert()` | `INSERT` a single row into table, returns inserted ID\r\n| `dblite.insertmany()` | `INSERT` multiple rows into table, returns a list of inserted IDs\r\n| `dblite.select()` | runs `SELECT`, returns cursor\r\n| `dblite.update()` | `UPDATE` table, returns affected row count\r\n| `dblite.delete()` | `DELETE` from table, returns affected row count\r\n| `dblite.execute()` | executes SQL with arguments, returns cursor\r\n| `dblite.executemany()` | executes SQL against all parameter sequences\r\n| `dblite.executescript()` | executes SQL as a script of one or more SQL statements\r\n| `dblite.close()` | closes the database and all pending transactions, if open\r\n| `dblite.transaction()` | returns `dblite.Transaction` context manager\r\n| `dblite.register_adapter()` | registers function to auto-adapt given Python types to database types in query parameters\r\n| `dblite.register_converter()` | registers function to auto-convert given database types to Python in query results\r\n| `dblite.register_row_factory()` | registers function to produce query results as custom type\r\n| | |\r\n| **dblite.Database** | |\r\n| `Database.fetchall()` | runs `SELECT`, returns all rows\r\n| `Database.fetchone()` | runs `SELECT`, returns a single row, or `None`\r\n| `Database.insert()` | `INSERT` a single row into table, returns inserted ID\r\n| `Database.insertmany()` | `INSERT` multiple rows into table, returns a list of inserted IDs\r\n| `Database.select()` | runs `SELECT`, returns cursor\r\n| `Database.update()` | `UPDATE` table, returns affected row count\r\n| `Database.delete()` | `DELETE` from table, returns affected row count\r\n| `Database.execute()` | executes SQL with arguments, returns cursor\r\n| `Database.executemany()` | executes SQL against all parameter sequences\r\n| `Database.executescript()` | executes SQL as a script of one or more SQL statements\r\n| `Database.transaction()` | returns `dblite.Transaction` context manager\r\n| `Database.open()` | opens database connection if not already open\r\n| `Database.close()` | closes the database and all pending transactions, if open\r\n| `Database.closed` | whether database is not open\r\n| `Database.cursor` | database engine cursor object\r\n| `Database.row_factory` | custom row factory, as `function(cursor, row tuple)`\r\n| `Database.ENGINE` | underlying database engine, `\"sqlite\"` for SQLite3 and `\"postgres\"` for PostgreSQL\r\n| | |\r\n| **dblite.Transaction** | |\r\n| `Transaction.fetchall()` | runs `SELECT`, returns all rows\r\n| `Transaction.fetchone()` | runs `SELECT`, returns a single row, or `None`\r\n| `Transaction.insert()` | `INSERT` a single row into table, returns inserted ID\r\n| `Transaction.insertmany()` | `INSERT` multiple rows into table, returns a list of inserted IDs\r\n| `Transaction.select()` | runs `SELECT`, returns cursor\r\n| `Transaction.update()` | `UPDATE` table, returns affected row count\r\n| `Transaction.delete()` | `DELETE` from table, returns affected row count\r\n| `Transaction.execute()` | executes SQL with arguments, returns cursor\r\n| `Transaction.executemany()` | executes SQL against all parameter sequences\r\n| `Transaction.executescript()` | executes SQL as a script of one or more SQL statements\r\n| `Transaction.commit()` | commits pending actions, if any\r\n| `Transaction.rollback()` | rolls back pending actions, if any\r\n| `Transaction.close()` | closes the transaction, performing commit or rollback as specified\r\n| `Transaction.closed` | whether transaction is not open\r\n| `Transaction.cursor` | database engine cursor object\r\n| `Transaction.database` | transaction `Database` instance\r\n| `Database.ENGINE` | underlying database engine, `\"sqlite\"` for SQLite3 and `\"postgres\"` for PostgreSQL\r\n\r\n\r\nDependencies\r\n------------\r\n\r\n- Python 3 or Python 2.7\r\n- six (https://pypi.org/project/six)\r\n\r\nIf using Postgres:\r\n- psycopg2 (https://pypi.org/project/psycopg2)\r\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Simple query interface for SQL databases",
"version": "1.3.3",
"project_urls": {
"Homepage": "https://github.com/suurjaak/dblite"
},
"split_keywords": [
"sql",
"sqlite",
"postgres",
"psycopg2"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "2371c609cc93ddde7e42768b5c164578940c9dcc7563c0a4c5cb1cecb7ad6593",
"md5": "43cf1c6c81a04839e13c786b5be92590",
"sha256": "25b69b0da7a5c52106006a2568c58d7fd415cdc1d34df5c7c2c75007f9dfa3e8"
},
"downloads": -1,
"filename": "dblite-1.3.3-py2.py3-none-any.whl",
"has_sig": false,
"md5_digest": "43cf1c6c81a04839e13c786b5be92590",
"packagetype": "bdist_wheel",
"python_version": "py2.py3",
"requires_python": ">=2.7",
"size": 36230,
"upload_time": "2023-07-20T12:11:43",
"upload_time_iso_8601": "2023-07-20T12:11:43.602879Z",
"url": "https://files.pythonhosted.org/packages/23/71/c609cc93ddde7e42768b5c164578940c9dcc7563c0a4c5cb1cecb7ad6593/dblite-1.3.3-py2.py3-none-any.whl",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-07-20 12:11:43",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "suurjaak",
"github_project": "dblite",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"requirements": [],
"lcname": "dblite"
}
JSON
