nmi-mysql
A very simple and intuitive mysql client wrapper for sqlalchemy.
Installation
- Install
sqlalchemy - Install
pymysqlas it is the MySQL driver used bynmi_mysql - Run the command to install:
pip install nmi_mysql
Usage
Minimal and straightforward when doing queries
-
Import the nmi-mysql client library
from nmi_mysql import nmi_mysql
-
Initialization: Requires a parameter,
conf-
confis a dictionary containing the configurations needed to connect to the database-
sample
conf:conf = { 'host': 'localhost', 'user': 'root', 'password':'', 'db': 'mydb', 'port': 3306, 'max_pool_size': 20 # optional, default is 10 }
-
db = nmi_mysql.DB(conf) -
-
Query execution: Requires a parameter,
query, and has two optional parameters,paramsandretry_connection-
queryis a string which is the MySQL query to be executed -
paramsis a list containing the parameters needed to bind to the query (default: None or no parameters) -
retry_connectionis an integer which will determine how many times to retry connecting to the database (default: 0 or do not retry)-
Single Query
data = db.query(query, params) -
Multiple Query (delimited by semi-colon)
data = db.multi_query(query, params)
-
-
SELECT and DELETE operations
from nmi_mysql import nmi_mysql
db = nmi_mysql.DB(conf)
result1 = db.query('SELECT * FROM users WHERE name = %s', ['ninz'])
result2 = db.query('SELECT * FROM users WHERE name IN (%s) AND age = %s', [['john', 'doe'], 10])
result3 = db.query('DELETE FROM users WHERE name IN (%s) OR id = %s', [['ninz', 'john'], 1])
print(result1)
print(result2)
print(result3)INSERT operations
from nmi_mysql import nmi_mysql
db = nmi_mysql.DB(conf)
# Throws an error upon failure
try:
result1 = db.query('INSERT INTO users(id, name) VALUES (%s)', [(1, 'ninz')])
result2 = db.query('INSERT INTO users(id, name) VALUES (%s)', [(2, 'jasper'), (3, 'jv')])
result3 = db.query('INSERT INTO users(id, name) VALUES (%s, %s)', [4, 'sherwin'])
result4 = db.query('INSERT INTO users(id, name) VALUES (%s, %s), (%s, %s)', [5, 'asdf', 6, 'qwerty'])
except Exception as err:
print(err)UPDATE operations
from nmi_mysql import nmi_mysql
db = nmi_mysql.DB(conf)
result1 = db.query('UPDATE users SET %s WHERE name = %s', [{'name': 'ninz'}, 'jasper'])
result2 = db.query('UPDATE users SET name = %s WHERE id IN (%s)', ['sherwin', [1, 2]])Multiple statements in a single query
- Note: INSERTs using tuple parameters are not supported by
db.multi_query.
from nmi_mysql import nmi_mysql
db = nmi_mysql.DB(conf)
results = db.multi_query(
'''
SELECT * FROM users WHERE status = %s;
UPDATE users SET status = %s WHERE status = %s;
DELETE FROM users WHERE status = %s;
INSERT INTO users (id, name) VALUES (%s, %s);
''',
['active', 'active', 'inactive', 'active', 1, 'ninz']
)
print(results)
