Skip to main content

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

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)

Uploaded Source

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

Hashes for mariasql-4.tar.gz
Algorithm Hash digest
SHA256 d9f2e0cd8e71f160a44b7a7912c198327f3bee2fa4349772849fce259075b333
MD5 bba68ffb1d0199060dfb123e62344789
BLAKE2b-256 0a99bc7961fa78d7af1cfacbffaa15fc8cf94a8deb269d1eb1009082d7574b38

See more details on using hashes here.

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page