#SimpleMysql An ultra simple wrapper for Python MySQLdb with very basic functionality
- Kailash Nadh, June 2013. Sergey Nagaytsev, Aug 2018
- Documentation: http://nadh.in/code/simplemysql
- License: GPL v2
With pip or easy_install
pip install simplemysql or easy_install simplemysql
Or from the source
python setup.py install
import MySQLdb
from simplemysql import SimpleMysql
def _connection_factory():
conn = MySQLdb.connect(
host='127.0.0.1',
port=3306,
user='root',
passwd='',
db='test',
)
conn.autocommit(True)
return conn
db = SimpleMysql(_connection_factory)import psycopg2
from simplemysql import DialectPostgres, SimpleMysql
_CONNECT = dict(
host='127.0.0.1',
port=5432,
user='travis',
password='',
dbname='test',
)
db = SimpleMysql(lambda: psycopg2.connect(**_CONNECT), DialectPostgres())# insert a record to the <em>books</em> table
db.insert("books", {"type": "paperback", "name": "Time Machine", "price": 5.55, year: "1997"})
book = db.getOne("books", ["name"], ["year = 1997"])
print "The book's name is " + book.nameinsert(), update(), delete(), getOne(), getAll(), lastId(), query()
Inserts a single record into a table.
db.insert("food", {"type": "fruit", "name": "Apple", "color": "red"})
db.insert("books", {"type": "paperback", "name": "Time Machine", "price": 5.55})Update one more or rows based on a condition (or no condition).
# update all rows
db.update("books", {"discount": 0})
# update rows based on a simple hardcoded condition
db.update("books",
{"discount": 10},
["id=1"]
)
# update rows based on a parametrized condition
db.update("books",
{"discount": 10},
("id=%s AND year=%s", [id, year])
)Insert Multiple values into table.
# insert multiple values in table
db.insertBatch("books", [{"discount": 0},{"discount":1},{"discount":3}])Insert a new row, or update if there is a primary key conflict.
# insert a book with id 123. if it already exists, update values
db.insertOrUpdate("books",
{"id": 123, type": "paperback", "name": "Time Machine", "price": 5.55},
"id"
)Get a single record or multiple records from a table given a condition (or no condition). The resultant rows are returned as namedtuples. getOne() returns a single namedtuple, and getAll() returns a list of namedtuples.
book = db.getOne("books", ["id", "name"])# get a row based on a simple hardcoded condition
book = db.getOne("books", ["name", "year"], ("id=1"))# get multiple rows based on a parametrized condition
books = db.getAll("books",
["id", "name"],
("year > %s and price < %s", [year, 12.99])
)# get multiple rows based on a parametrized condition with an order and limit specified
books = db.getAll("books",
["id", "name", "year"],
("year > %s and price < %s", [year, 12.99]),
["year", "DESC"], # ORDER BY year DESC
[0, 10] # LIMIT 0, 10
)Get the last insert id
# get the last insert ID
db.lastId()Get the last query executed
# get the SQL of the last executed query
db.lastQuery()Delete one or more records based on a condition (or no condition)
# delete all rows
db.delete("books")
# delete rows based on a condition
db.delete("books", ("price > %s AND year < %s", [25, 1999]))Run a raw SQL query. The MySQLdb cursor is returned.
# run a raw SQL query
db.query("DELETE FROM books WHERE year > 2005")Insert, update, and delete operations on transactional databases such as innoDB need to be committed
# Commit all pending transaction queries
db.commit()