User Tools

Site Tools


python:sqlalchemymysql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

python:sqlalchemymysql [2013/03/16 17:40] (current)
Line 1: Line 1:
 +==== SQL Alchemy + MySQL + SqlSoup ====
 +06.05.2010
  
 +
 +For this I'll be using ipython (Python 2.6.5 (r265:​79063,​ Apr 16 2010, 13:09:56) and SQLAlchemy version 0.6 (with [[ http://​sourceforge.net/​projects/​mysql-python | mysql-python ]]). \\ 
 +We assume that we already have created a '​test'​ database with a table '​users'​ in it (for the table structure see below) \\
 +Because we're dealing with already existing data, we'll be using [[ http://​www.sqlalchemy.org/​docs/​reference/​ext/​sqlsoup.html#​introduction | SqlSoup ]], which is a convenient way to access existing database tables without having to declare table or mapper classes ahead of time. It is built on top of the SQLAlchemy ORM and provides a super-minimalistic interface to an existing database.
 +
 +
 +=== Create the connection ===
 +
 +<code python>
 +from sqlalchemy.ext.sqlsoup import SqlSoup
 +db = SqlSoup('​mysql+mysqldb://​root:​password@localhost/​test?​charset=utf8'​)
 +# or db = SqlSoup(engine) if you have engine previously defined
 +</​code>​
 +
 +
 +=== Load our existing table ===
 +
 +<code python>
 +users_tbl = db.users
 +In [73]: type(users_tbl)
 +Out[73]: <class '​sqlalchemy.ext.sqlsoup.TableClassType'>​
 +</​code>​
 +
 +=== Querying ===
 +
 +
 +First, __get all users__ (we have just two there at this time):
 +
 +<code python>
 +In [17]: [ (user.id,​user.name,​user.age) for user in users_tbl.all() ]
 +Out[17]: [(1L, u'John Doe', 34), (2L, u'Tom Bruise',​ 23)]
 +</​code>​
 +
 +__Get after primary key__ (in this case called //id//)
 +
 +<code python>
 +In [21]: users_tbl.get(2)
 +Out[21]: MappedUsers(id=2L,​name=u'​Tom Bruise',​age=23)
 +</​code>​
 +
 +__Filter after some conditions__:​
 +
 +- after name only (using **filter_by**)
 +
 +<code python>
 +In [41]: users_tbl.filter_by(name='​Tom Bruise'​).one()
 +Out[41]: MappedUsers(id=2L,​name=u'​Tom Bruise',​age=23)
 +</​code>​
 +
 +- after name only (using **filter** and **LIKE**)
 +
 +<code python>
 +In [57]: users_tbl.filter(users_tbl.name.like("​Tom%"​)).one()
 +Out[57]: MappedUsers(id=2L,​name=u'​Tom Bruise',​age=23)
 +</​code>​
 +
 +
 +- after name and age (using **filter**, **or_**, **and_**)
 +
 +<code python>
 +from sqlalchemy import or_, and_, desc
 +whereRule = and_(users_tbl.name=='​Tom Bruise',​ users_tbl.age==23)
 +In [44]: users_tbl.filter(whereRule).all()
 +Out[44]: [MappedUsers(id=2L,​name=u'​Tom Bruise',​age=23)]
 +</​code>​
 +
 +- after name (using **IN**)
 +
 +<code python>
 +In [62]: users_tbl.filter(users_tbl.name.in_(['​Tom Bruise',​ 'Bruce Lee', 'John Doe'​])).all()
 +Out[62]: ​
 +[MappedUsers(id=1L,​name=u'​John Doe',​age=34),​
 + ​MappedUsers(id=2L,​name=u'​Tom Bruise',​age=23)]
 +</​code>​
 +
 +
 +=== Modifying objects ===
 +
 +<code python>
 +eduser = users_tbl.get(2) # grab the user
 +eduser.age = 12 # modify age
 +db.commit() # save
 +</​code>​
 +
 +
 +=== Insert new objects ===
 +
 +
 +<code python>
 +In [70]: users_tbl.insert(name="​Ronnie James Dio", age=54)
 +Out[70]: MappedUsers(id=None,​name='​Ronnie James Dio',​age=54)
 +
 +In [71]: db.commit() ​ # actually commits the data
 +</​code>​
 +
 +
 +=== Delete data ===
 +
 +<code python>
 +deluser = users_tbl.get(2) # grab the user using primary key or any other method (filter, filter_by, etc)
 +db.delete(deluser)
 +db.commit()
 +</​code>​
 +
 +
 +=== References ===
 +
 +http://​www.sqlalchemy.org/​docs/​ | SQLAlchemy documentation for 0.6 \\ 
 +http://​www.sqlalchemy.org/​docs/​ormtutorial.html#​ormtutorial-querying |  SQLAlchemy querying \\ 
 +http://​www.sqlalchemy.org/​docs/​reference/​ext/​sqlsoup.html | SqlSoup \\ 
python/sqlalchemymysql.txt ยท Last modified: 2013/03/16 17:40 (external edit)