MariaDB/MySQL query builder primary for inserting and updating Python dictionaries into tables.
Project description
# MariaSQL
Nowadays selecting data is easy, but inserting and updating data is annoying.
* https://git.osuv.de/m/MariaSQL
* Mirror, Issues & PRs: https://gitlab.com/markuman/MariaSQL
# install
with `pip3 install mariasql --user`
# Example / Usage
start a temporary MariaDB
`docker run -d -p 3307:3306 --rm --name mariadb -e MYSQL_ROOT_PASSWORD=password mariadb`
start python3 and go
```python
>>> import MariaSQL
>>> db = MariaSQL.MariaSQL(host='127.0.0.1', port=3307)
>>> db.query('create database mariasql;')
()
>>> db.use('mariasql')
>>> # we can create tables based on dict definitions
>>> mytable = dict()
>>> mytable['id'] = int
>>> mytable['name'] = str
>>> mytable['some shitty column name'] = float
>>> db.create_table('test_table', mytable)
()
>>> db.show_tables()
[{'Tables_in_mariasql': 'test_table'}]
>>> # now we inserting a dict into the create tables
>>> data = dict()
>>> data['id'] = 12
>>> data['name'] = 'Alf'
>>> data['some shitty column name'] = 3.1415
>>> db.insert('test_table', data)
()
# let's read from create table
>>> dataset = db.query('select * from test_table')
>>> dataset
[{'id': 12, 'name': 'Alf', 'some shitty column name': 3.1415}]
>>> db.query('show create table test_table')
[{'Table': 'test_table', 'Create Table': 'CREATE TABLE `test_table` (\n `id` int(11) DEFAULT NULL,\n `name` varchar(255) DEFAULT NULL,\n `some shitty column name` double DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8'}]
```
# Properties
| variabele | default value |
| --- | --- |
| host | localhost |
| port | 3306 |
| user | root |
| password | password |
| db | mysql |
| charset | utf8mb4 |
# Methods
* `db.use(dbname)`
* to change the database
* `db.show_tables()`
* get list of current tables in selected database
* `db.query(sql)`
* execute raw sql commands
* `db.create_db(dbname)`
* create a new database if not exists
* `db.create_table(name, tabledef = None)`
* When `tabledef` is not given, `name` must be a raw sql string which will be executed
* When `tabledef` is a dict() and its `keys` are just datatype definitions, a table based on this dict will be created
* datatye limitations when using dict() definitions
* `str` -> `VARCHAR(255)`
* `int` -> `INT`
* `float` -> `DOUBLE`
* `dict` -> `JSON`
* `db.insert(table, data, on_duplicate = False)`
* `data` must be a `dict()` with `keys` which exists as `COLUMN_NAME` in the sql table. But it must not include all column names.
* when `on_duplicate` is set to `True`, it will perform an update when the PKs already exists in the table.
* `db.insert_on_duplicate(table, data)`
* just a wrapper for `db.insert()` with `on_duplicate = True`.
# todo
* some tests and setup ci/cd
* update method
* insert/update list of dicts
Nowadays selecting data is easy, but inserting and updating data is annoying.
* https://git.osuv.de/m/MariaSQL
* Mirror, Issues & PRs: https://gitlab.com/markuman/MariaSQL
# install
with `pip3 install mariasql --user`
# Example / Usage
start a temporary MariaDB
`docker run -d -p 3307:3306 --rm --name mariadb -e MYSQL_ROOT_PASSWORD=password mariadb`
start python3 and go
```python
>>> import MariaSQL
>>> db = MariaSQL.MariaSQL(host='127.0.0.1', port=3307)
>>> db.query('create database mariasql;')
()
>>> db.use('mariasql')
>>> # we can create tables based on dict definitions
>>> mytable = dict()
>>> mytable['id'] = int
>>> mytable['name'] = str
>>> mytable['some shitty column name'] = float
>>> db.create_table('test_table', mytable)
()
>>> db.show_tables()
[{'Tables_in_mariasql': 'test_table'}]
>>> # now we inserting a dict into the create tables
>>> data = dict()
>>> data['id'] = 12
>>> data['name'] = 'Alf'
>>> data['some shitty column name'] = 3.1415
>>> db.insert('test_table', data)
()
# let's read from create table
>>> dataset = db.query('select * from test_table')
>>> dataset
[{'id': 12, 'name': 'Alf', 'some shitty column name': 3.1415}]
>>> db.query('show create table test_table')
[{'Table': 'test_table', 'Create Table': 'CREATE TABLE `test_table` (\n `id` int(11) DEFAULT NULL,\n `name` varchar(255) DEFAULT NULL,\n `some shitty column name` double DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8'}]
```
# Properties
| variabele | default value |
| --- | --- |
| host | localhost |
| port | 3306 |
| user | root |
| password | password |
| db | mysql |
| charset | utf8mb4 |
# Methods
* `db.use(dbname)`
* to change the database
* `db.show_tables()`
* get list of current tables in selected database
* `db.query(sql)`
* execute raw sql commands
* `db.create_db(dbname)`
* create a new database if not exists
* `db.create_table(name, tabledef = None)`
* When `tabledef` is not given, `name` must be a raw sql string which will be executed
* When `tabledef` is a dict() and its `keys` are just datatype definitions, a table based on this dict will be created
* datatye limitations when using dict() definitions
* `str` -> `VARCHAR(255)`
* `int` -> `INT`
* `float` -> `DOUBLE`
* `dict` -> `JSON`
* `db.insert(table, data, on_duplicate = False)`
* `data` must be a `dict()` with `keys` which exists as `COLUMN_NAME` in the sql table. But it must not include all column names.
* when `on_duplicate` is set to `True`, it will perform an update when the PKs already exists in the table.
* `db.insert_on_duplicate(table, data)`
* just a wrapper for `db.insert()` with `on_duplicate = True`.
# todo
* some tests and setup ci/cd
* update method
* insert/update list of dicts
Project details
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
mariasql-4.tar.gz
(3.4 kB
view details)
File details
Details for the file mariasql-4.tar.gz
.
File metadata
- Download URL: mariasql-4.tar.gz
- Upload date:
- Size: 3.4 kB
- Tags: Source
- Uploaded using Trusted Publishing? No
- Uploaded via: Python-urllib/3.7
File hashes
Algorithm | Hash digest | |
---|---|---|
SHA256 | d9f2e0cd8e71f160a44b7a7912c198327f3bee2fa4349772849fce259075b333 |
|
MD5 | bba68ffb1d0199060dfb123e62344789 |
|
BLAKE2b-256 | 0a99bc7961fa78d7af1cfacbffaa15fc8cf94a8deb269d1eb1009082d7574b38 |