knadh / simplemysql Goto Github PK

View Code? Open in Web Editor NEW

77.0 8.0 34.0 40 KB

An ultra simple wrapper for Python MySQLdb with very basic functionality

Python 100.00%

simplemysql's Introduction

#SimpleMysql An ultra simple wrapper for Python MySQLdb with very basic functionality

Kailash Nadh, June 2013
Documentation: http://nadh.in/code/simplemysql
License: GPL v2

Installation

With pip or easy_install

pip install simplemysql or easy_install simplemysql

Or from the source

python setup.py install

Usage

For normal connection

from simplemysql import SimpleMysql

db = SimpleMysql(
	host="127.0.0.1",
	db="mydatabase",
	user="username",
	passwd="password",
	keep_alive=True # try and reconnect timedout mysql connections?
)

For SSL Connection

from simplemysql import SimpleMysql

db = SimpleMysql(
    host="127.0.0.1",
    db="mydatabase",
    user="username",
    passwd="password",
    ssl = {'cert': 'client-cert.pem', 'key': 'client-key.pem'},
    keep_alive=True # try and reconnect timedout mysql connections?
)

# 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.name

Query methods

insert(), update(), delete(), getOne(), getAll(), lastId(), query()

insert(table, record{})

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(table, row{}, condition[])

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])
)

insertBatch(table, rows{})

Insert Multiple values into table.

# insert multiple values in table
db.insertBatch("books", [{"discount": 0},{"discount":1},{"discount":3}])

insertOrUpdate(table, row{}, key)

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"
)

getOne(table, fields[], where[], order[], limit[])

getAll(table, fields[], where[], order[], limit[])

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
)

lastId()

Get the last insert id

# get the last insert ID
db.lastId()

lastQuery()

Get the last query executed

# get the SQL of the last executed query
db.lastQuery()

delete(table, fields[], condition[], order[], limit[])

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]))

query(table)

Run a raw SQL query. The MySQLdb cursor is returned.

# run a raw SQL query
db.query("DELETE FROM books WHERE year > 2005")

commit()

Insert, update, and delete operations on transactional databases such as innoDB need to be committed

# Commit all pending transaction queries
db.commit()

simplemysql's People

Contributors

Stargazers

Watchers

simplemysql's Issues

Disabling and re-enabling autocommit on the fly

Hi,

I want to start a connection with autocommit enabled (autocommit=True, which is fine for most of the operations) and occasionally I want to disable it, run a transaction and then re-enable it.

To do so, I've done as follows:

start the connection with autocommit enabled

db = SimpleMysql(
        host="127.0.0.1",
	db="mydatabase",
	user="username",
	passwd="password",
	keep_alive=True,
        autocommit = True
)

disable autocommit when needed

cursor = db.query("SET autocommit = 0")

run a bunch of queries, for instance

db.update(...)
db.insert(...)
db.delete(...)

call commit

db.commit()

finally, turn autocommit back on

cursor = db.query("SET autocommit = 1")

Is that safe or do you think it may cause issues during the connection?

Thanks

Last inserted id

Provide a method to get the last inserted id. E.g.
db.lastid(): return self.cursor.lastrowid

Or change the return value of db.insert.

SQL Injection

The code is vulnerable to SQL injection.

See, for example, line 153:
This is vulnerable to SQL injection.

Update pypi package

The current package being distributed by pip is out of date. @knadh, can you please push an update?

Maybe we should tag some releases?

Is it possible to enable compression?

Hi,

I would like to know if there's a way to enable the compression protocol, as specified here: https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html

Thanks

Install Error: Missing ConfigParser

Was trying to install simplemysql (using pip3) and got an error saying that I was missing "ConfigParser". It seems that the package was renamed "configparser" (lowercase) to comply with PEP8.

incompatible with python 2.6.6

Python 2.6.6 (r266:84292, Sep 11 2012, 08:34:23)
[GCC 4.4.6 20120305 (Red Hat 4.4.6-4)] on linux2
Type "help", "copyright", "credits" or "license" for more information.

from simplemysql import SimpleMysql
Traceback (most recent call last):
File "", line 1, in
File "/usr/lib/python2.6/site-packages/simplemysql/init.py", line 1, in
from .simplemysql import SimpleMysql
File "/usr/lib/python2.6/site-packages/simplemysql/simplemysql.py", line 162
data = {k: data[k] for k in data if k not in keys}
^
SyntaxError: invalid syntax

But I'n not sure I fallow one part of code. Please correct me if I'm wrong.

Method _select line 229:

def _select(self, table=None, fields=(), where=None, order=None, limit=None):
........
#limit
if limit:
    sql += " LIMIT %s" % limit[0]
    if 1 in LIMIT:
        sql+= ", " + limit[1]

form my understanding this part should have look something like this:

# limit
if limit:
    sql += " LIMIT %s" % limit[0]
    if len(limit) > 1:
        sql = "%s, %s"% (sql, limit[1])

Best Regards