Python MysqlCommand
Python MysqlCommand is a MySql query builder that enables using keyword parameters. Inspired by the MysqlCommand class by Mysql for ASP.net
Installation
Use the package manager pip to install Python MysqlCommand
pip install mysqlcommand
Usage
Select Rows
import mysql.connector
from mysqlcommand import MysqlCommand
dbcon = mysql.connector.connect(
host='{host}',
user='{username}',
passwd='{password}'
)
dbcom = MysqlCommand('select * from {table}', dbcon)
dbcom.execute_reader()
while dbcom.read():
dbcom.data['{columnName}']
Select Rows using Parameter
dbcom.commandstr = 'select * from {table} where {columnName} = ?columnValue'
dbcom.parameters.add('?columnValue', '{value}')
dbcom.execute_reader()
while dbcom.read():
dbcom.data['{columnName}']
Select Rows using Parameter with list value
dbcom.commandstr = 'select * from {table} where {columnName} in ?columnValue'
dbcom.parameters.add('?columnValue', ['{value}', '{value2}', '{value3}'])
dbcom.execute_reader()
while dbcom.read():
dbcom.data['{columnName}']
Retrieve single value
Useful when just needing a boolean or count
dbcom.commandstr = 'select count(*) from {table}'
table_count = dbcom.execute_scalar()
Database Manipulation - Autocommit
dbcom.set_autocommit(True)
Update/Delete using Parameters
dbcom.commandstr = 'update {table} set '{columnName}' = ?columnNewValue where '{columnName}' = ?columnOldValue';
dbcom.parameters.add('?columnOldValue', 3)
dbcom.parameters.add('?columnNewValue', 5)
dbcom.execute_non_query()
dbcom.commit()
Insert Row
new_row = {
'{columnName}': 1
}
insert_id = dbcom.insert('{table}', new_row)
Insert Rows
new_rows = [{
'{columnName}': 2
},
{
'{columnName}': 3
}]
recordsInserted = dbcom.insert('{table}', new_rows)
Other Parameter options
Parameter update current parameter valuue
dbcom.parameters.set('?columnOldValue', 5)
Parameter upsert parameter value - update if existing, create if doesn't
dbcom.parameters.setadd('?columnOldValue', 5)
Remove all parameters
dbcom.parameters.clear()
Contributing
Pull requests are welcome. For major changes, please open an issue first to discuss what you would like to change.
License
MIT