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.

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

>>> 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-5.tar.gz (3.5 kB view details)

Uploaded Source

File details

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

File metadata

  • Download URL: mariasql-5.tar.gz
  • Upload date:
  • Size: 3.5 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.1.1 pkginfo/1.5.0.1 requests/2.22.0 setuptools/41.2.0 requests-toolbelt/0.9.1 tqdm/4.40.0 CPython/3.7.5

File hashes

Hashes for mariasql-5.tar.gz
Algorithm Hash digest
SHA256 314cf7b68bd1a3d643855264e5c1d15febd79efbe0576f4f55d6c3f9819c4453
MD5 d765e7e4a74b7611b5ae468a64ce06ef
BLAKE2b-256 ad8e711e9cff1f97a60f5ad24e15542f7ca3e66dfca3a0c0a65d7a5ed53dfa18

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