149 lines
4.2 KiB
Markdown
149 lines
4.2 KiB
Markdown
<img width="320px" src="logo.svg"/>
|
|
|
|
**Quicker** is a pythonic 🐍 tool for querying databases.
|
|
|
|
Quicker wraps Python libraries:
|
|
|
|
- `mysqlclient` for MySQL.
|
|
- `psycopg2` for PostgreSQL.
|
|
- `sqlite3` from Python standard library for SQLite.
|
|
|
|
# Why is it needed?
|
|
|
|
At work, I periodically have to make queries to different databases and then somehow process the information received. This may be necessary for one-time use, so I don't want to write a lot of code. You may also want to do all the work right in the interactive Python shell.
|
|
|
|
Quicker interface is as simple as possible, thanks to which lazy system administrators can now effortlessly extract data from the database.
|
|
|
|
Of course, this library **should not be used in production**. This is just a small assistant in routine tasks.
|
|
|
|
# Installation
|
|
|
|
```
|
|
pip install git+https://git.nxhs.cloud/ge/quicker
|
|
```
|
|
|
|
# Usage
|
|
|
|
Quicker uses a context manager. All that is needed for work is to pass connection parameters to object and write the queries themselves. See MySQL example:
|
|
|
|
```python
|
|
from quicker import Connection
|
|
|
|
|
|
with Connection(provider='mysql', read_default_file='~/.my.cnf') as query:
|
|
users = query("SELECT * FROM `users` WHERE admin = 'N'")
|
|
```
|
|
|
|
`Connection` object initialises `Query` callable object for interacting with cursor. You can use `query("sql there..")` or `query.execute("sql...")` syntax. There are the same.
|
|
|
|
`Query` object methods and properties:
|
|
|
|
- `query()`, `execute()`. Execute SQL. There is you can use here this syntax: `query('SELECT * FROM users WHERE id = %s', (15,))`.
|
|
- `commit()`. Write changes into database.
|
|
- `cursor`. Access cursor object directly.
|
|
- `connection`. Access connection object directly.
|
|
|
|
You don't need to commit to the database, Quicker will do it for you, but if you need to, you can commit manually calling `query.commit()`. You can also turn off automatic commit when creating a `Connection` object — pass it the argument `commit=Fasle`.
|
|
|
|
That's not all — Quicker converts the received data into a list of dictionaries. The list will be empty if nothing was found for the query. If the request does not imply a response, `None` will be returned.
|
|
|
|
## MySQL example
|
|
|
|
```python
|
|
from quicker import Connection
|
|
|
|
|
|
config = {
|
|
'provider': 'mysql',
|
|
'host': '127.0.0.1',
|
|
'port': 3306,
|
|
'user': 'myuser',
|
|
'database': 'mydb',
|
|
'password': 'example',
|
|
}
|
|
|
|
with Connection(**config) as query:
|
|
query(
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id int AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
email VARCHAR(255) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
"""
|
|
)
|
|
query(
|
|
"INSERT INTO users VALUES (NULL, %s, %s, current_timestamp)",
|
|
('john', 'john@exmpl.org',)
|
|
)
|
|
query.commit()
|
|
users = query("SELECT * FROM users")
|
|
|
|
print('ID\t NAME\t EMAIL')
|
|
for user in users:
|
|
print(user['id'], '\t', user['name'], '\t', user['email'])
|
|
```
|
|
|
|
## PostgreSQL example
|
|
|
|
```python
|
|
import logging
|
|
|
|
from quicker import Connection
|
|
|
|
|
|
logging.basicConfig(level=logging.DEBUG)
|
|
|
|
config = {
|
|
'provider': 'postgres',
|
|
'host': '127.0.0.1',
|
|
'port': 5432,
|
|
'user': 'myuser',
|
|
'database': 'mydb',
|
|
'password': 'example',
|
|
}
|
|
|
|
with Connection(**config) as query:
|
|
query(
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id serial PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
email VARCHAR(255) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
"""
|
|
)
|
|
query(
|
|
"INSERT INTO users VALUES ((SELECT MAX(id)+1 FROM users), %s, %s, current_timestamp)",
|
|
('phil', 'phil@exmpl.org',)
|
|
)
|
|
```
|
|
|
|
|
|
## Logging
|
|
|
|
For logging add following code in your module:
|
|
|
|
```python
|
|
import logging
|
|
|
|
logging.basicConfig(level=logging.DEBUG)
|
|
```
|
|
|
|
## Direct access to Cursor object
|
|
|
|
```python
|
|
from quicker import Connection, mklist
|
|
|
|
# config declaration here...
|
|
|
|
with Connection(**config) as db:
|
|
db.cursor.execute('SELECT id, name, email FROM users WHERE name = %s', ('John',))
|
|
users = db.cursor.fetchall()
|
|
# Note: "users" is tuple! Convert it to list of dicts!
|
|
colnames = [desc[0] for desc in db.cursor.description]
|
|
users_list = mklist(colnames, users)
|
|
```
|