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.

**early first draft**

# 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 | utf8 |

# 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

* pip package
* 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-1.tar.gz (3.4 kB view details)

Uploaded Source

File details

Details for the file mariasql-1.tar.gz.

File metadata

  • Download URL: mariasql-1.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-1.tar.gz
Algorithm Hash digest
SHA256 567c208b6f880ea2d17375fab947564da4fa312395bc0823b16cbca8a8186f34
MD5 65633f1d75f61376e5010e466e109f8b
BLAKE2b-256 cdb8c94304c77588a475e4e152afb075f28c36b6953c6303f4ac7e4a6afdaba4

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