A python API for manage your MySQL database (select, insert, update, delete) like pymongo by iinitz

Require

python-mysqldb

$ apt-get install python-mysqldb

SQLObject

$ easy_install sqlobject

Install

$ git clone https://github.com/iinitz/pymgsql.git
$ cd pymgsql
$ python setup.py install

Import

from pymgsql import *

Defined your SQL schema with Schema (like SQLObject)

class Model1(Schema):
	class sqlmeta:
		table='model1'
	name=UnicodeCol(length=16,dbName='name',notNone=True)
	lastname=UnicodeCol(length=16,dbName='lastname',notNone=True)
	test=MultipleJoin('Model2',joinColumn='model1_id')
class Model2(Schema):
	class sqlmeta:
		table='model2'
	model1=ForeignKey('Model1',dbName='model1_id')
	type1=UnicodeCol(length=16,dbName='type1',notNone=True)
	type2=UnicodeCol(length=16,dbName='type2',notNone=True)
	type3=IntCol(length=16,dbName='type3',notNone=True)

Create your table

DB=(Model1,Model2)
for table in DB[::-1]: table.dropTable(ifExists=True)
for table in DB: table.createTable(ifNotExists=True)

Create DB object

db=MongoSQL('mysql://DB_USERNAME:DB_PASSWORD@HOSTNAME/DB_NAME?charset=utf8')

Register your schema

db.regis(DB)

Select

Format

db.tableName.find() # select all
db.tableName.find(param) # select filter
db.tableName.findOne(id) # select by id

Example

a=db.model1.find({'name':'a'})
print a
b=db.model1.findOne(1)
print b

Option

db.tableName.find(orderBy=['-id','name'])
db.tableName.find(limit=5,reversed=True)

Operator

• NOT

{'$not':{'columnName':'value'}}

• OR

{'$or':{'columnName1':'value1','columnName2':'value2'}}

• AND

{'$and':{'columnName1':'value1','columnName2':'value2'}}

• IN

{'columnName':{'$in':['value1','value2']}}

• LIKE

{'columnName':{'$like':'%value%'}}

• <

{'columnName':{'$lt':'value'}}

• <=

{'columnName':{'$lte':'value'}}

{'columnName':{'$gt':'value'}}

• =

{'columnName':{'$gte':'value'}}

• !=

{'columnName':{'$ne':'value'}}

Insert

Format

db.tableName.insert(param)

Example

c=db.model1.insert({'name':'a','lastname':'x'})
print c

Update

Format

db.tableName.update(id,param)

Example

d=db.model1.update(1,{'name':'d'})
print d

Delete

Format

db.tableName.remove(id)

Example

e=db.model1.remove(1)
print e # return True if delete success else return False