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

# For database manipulation to ensure data changes are saved automatically
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()

# To commit database changes without autocommit
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