Skip to main content

A library to handle your databases: cassandra, mariadb, (mysql), sqlite3

Project description

MrlDB by Rémi "Mr e-RL" LANGDORPH

Copyright (c) 2019 Rémi LANGDORPH - mrerl@warlegend.net (under MIT license)

mrldb is a powerfull database handler for python

Links:

pypi project

github repo

Install with pypi:

pip install mrldb

This package supports the followings database systems: mariadb, mysql, cassandra, sqlite3

DOCUMENTATION

  • MrlDBCluster(): a cluster of databases, the last created MrlDBCluster can be accessible via mdbcl
    • MrlDBCluster.get(name): return the MrlDB from the alias or the name

    • MrlDBCluster.add(name, db, aliases=[]): add a MrlDB object with the name (overwrite if an object has the same name), and link the aliases

    • MrlDBCluster.addalias(name, aliases): link aliases to the name

    • MrlDBCluster.get_cluster_infos(): return a dict with all the config of the connections {"name": {config...}}

    • MrlDBCluster [name]: return the db from the alias or the name

    • for name, connection in MrlDBCluster: iterate over a list of tuple (dbname, connection)

    • MrlDBCluster.dbs: a dict with all the connections {"conn1": <MrlDB>, ...}

    • MrlDBCluster.aliases: a dict with all the aliases and the realname {"alias": "realname", ...}

from mrldb import MrlDBCluster, mdbcl
mycluster=MrlDBCluster()
  • mdbcl: return the last created MrlDBCluster
print(mdbcl)
  • mdbstr: an object to generate sql commands(str), (the same as the one included in the MrlDB classes)
    • mdbstr.insert(table, data): return str command to insert a new record
      • table is a str of the table where the insert must be executed
      • data is dict with the columns and the values to insert {"col1": "value1", "col2", 3.14}
    • mdbstr.update(table, data, conds=None): return str command to update an existing(s) record(s)
      • table is a str of the table where the update must be executed
      • data is dict with the columns and the values to update {"col1": "value1", "col2", 3.14}
      • conds are a str object as "col1='test' and col2=5" or a NoneType object if you want to update all the records of your table
    • mdbstr.select(table, columns, conds=None): return str command to get record(s) value(s)
      • table is a str of the table where the select is executed
      • data is list with the columns to get or a "*" to get all the columns
      • conds are a str object as "col1='test' and col2=5" or a NoneType object if you want to select all the records of your table

MrlDB classes

  • MrlDB base class: (Base of MrlDBCassandra, MrlDBMsql, MrlDBSqlite)

    • MrlDB.insert(table, data): insert a new record
      • table is a str of the table where the insert must be executed
      • data is dict with the columns and the values to insert {"col1": "value1", "col2", 3.14}
    • MrlDB.update(table, data, conds=None): update an existing(s) record(s)
      • table is a str of the table where the update must be executed
      • data is dict with the columns and the values to update {"col1": "value1", "col2", 3.14}
      • conds are a str object as "col1='test' and col2=5" or a NoneType object if you want to update all the records of your table
    • MrlDB.select(table, columns, conds=None): get record(s) value(s)
      • table is a str of the table where the select is executed
      • data is list with the columns to get or a "*" to get all the columns
      • conds are a str object as "col1='test' and col2=5" or a NoneType object if you want to select all the records of your table
    • MrlDB.init(): create (or ignoreif they already exists) the tables from the db structure
    • MrlDB.cursor: a connection, you can use MrlDB.cursor.execute(command)
    • MrlDB.structure: a dict of the db structure or a NoneType object if not specified
    • MrlDB._config: a dict of the connexion config
    • MrlDB._getinfos(): return MrlDB._config
  • MrlDBCassandra(cluster, db=None, structure=None, username=None, password=None): a cassandra cluster handler, require library cassandra-driver

    • you can use the database you want or don't use it
    • username and passsword are only used with PlainTextAuthProvider, if you've configured users and password for your db, else, we're connecting as anonymous
  • MrlDBMsql(host, database=None, structure=None, user=None, password=None): a cassandra cluster handler, require library mysql

    • host is the ip adress of the host or a dns-resolvable name of the host
    • you can use the database you want
    • username and passsword are only used with PlainTextAuthProvider, if you've configured users and password for your db, else, we're connecting as anonymous
  • MrlDBSqlite(file, structure=None, autocommit=0): a sqlite file handler, require base library sqlite3 (not recommanded)

    • the file is sqlite3 db file
    • autocommit is the time in seconds (can be a float) between each autocommit, disabled if set 0 (by default)

STRUCTURE argument

with structure, you can get the column names with the results in a dict for each records

structure is an argument for all the DB classes, it must be a None oject or a dictionnary:

MrlDBCassandra(... ,structure={"table0": {"col1": "integer unique", "col2": "text"}, "table2": {"name": "text"}}, ...)

Tutorial script:

from mrldb import MrlDBCassandra, MrlDBCluster, mdbcl, mdbstr
  • we add a simple cassandra cluster as connection to the mrldb cluster, we set cc0 as an alias to the db
mdbcl.add("cassandracluster0", MrlDBCassandra("127.0.0.2"), aliases=["cc0"])
  • we can add other aliases to this connection:
mdbcl.addalias("cassandracluster0", ["cc0_", "cassandra0", "testcluster"])
  • you can connect to your host with password and username
mdbcl.add("cassandracluster1", MrlDBCassandra("127.0.0.3", username="admin", password="something"))
  • you can specify the database too
mdbcl.add("cassandracluster2", MrlDBCassandra("127.0.0.4", database="mydbtest", aliases=["cc3"]))
  • and the best, you can provide database structure to have advanced features
mdbcl.add("cassandracluster3", MrlDBCassandra("127.0.0.5", database="mydbtest",
structure={"table0": {"col1": "integer unique", "col2": "text"}, "table2": {"name": "text"}}), aliases=["cc3"])

the following examples are working for all the differents database systems

SELECT

  • with the correct structure, the following command will give you for each records a dict with the col name and the value, (you can replace the columns by a "*"
mdbcl.get("cc3").select(table="table0", columns=["col1"], conds=None)

result= [{"col1": 0}, {"col1": 1}...]

  • you can use this without structure, it just return the results without dictionnarys
mdbcl.get("cc2").select(table="table0", columns=["col1"], conds=None)

result= [(0, ), (1, )...]

  • you can use conditions
mdbcl.get("cc3").select(table="table0", columns="*", conds="col2='test'")

result= [{"col1": 0, "col2": "test"}, ...]

  • in sql (not in cql !), you can do sql subrequests, we are formatting them with mdbstr class
mdbcl.get("cc3").select(table="table0", columns="*", conds=f"""(col2='test') or (col1 not in ({mdbstr.select(table='table2', columns='*', conds="name='john' ")}))""")

will execute this command: "SELECT * FROM table0 WHERE (col2='test') or (in (SELECT * FROM table2 WHERE name='john' ))"

result= [{"col1": 0, "col2": "test"}, ...]

INSERT

  • data is a dict with all the values to insert
mdbcl.get("cc3").insert(table="table0", data={"col1": 5, "col2": "ok"})

UPDATE

  • use data as the insert command, you can specify conditions with conds
mdbcl.get("cc3").update(table="table0", data={"col1": 5, "col2": "ok"}, conds="col2='test'")

DB init

  • will create the table (or ignore if exists) as the structure
mdbcl.get("cc3").init()

with structure={"table0": {"col1": "integer unique", "col2": "text"}, "table2": {"name": "text"}}

will execute the following commands:

['CREATE TABLE IF NOT EXISTS table0(col1 integer unique, col2 text)',
 'CREATE TABLE IF NOT EXISTS table2(name text)']

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Files for mrldb, version 0.0.29
Filename, size File type Python version Upload date Hashes
Filename, size mrldb-0.0.29.tar.gz (8.3 kB) File type Source Python version None Upload date Hashes View hashes

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN DigiCert DigiCert EV certificate StatusPage StatusPage Status page