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**

# 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 | 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-2.tar.gz (3.4 kB view details)

Uploaded Source

File details

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

File metadata

  • Download URL: mariasql-2.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-2.tar.gz
Algorithm Hash digest
SHA256 c540a924bd3ddbc98bc553b4079706125e50442942ada1ae687171da30102885
MD5 3b3d9f912604e32cb9de0275d45d87c6
BLAKE2b-256 736de64082cb996670f970d4e94cd411099148b49a22442510c5c9ec14fe688e

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