最近学习sqlalchemy框架,可我没能发现一篇通俗易懂的博文,毕竟本人新手小白还不能理解大神的世界,注释就草草几行全无程序员节操。经过一整天的Traceback和各种error我总算是摸索到一点门路,慌忙整理下以免明早起来又忘了。
导入
>>> from sqlalchemy import * >>> from sqlalchemy.orm import *
建立数据库引擎
>>> engine = create_engine('mysql://root:4QSJQCRC@localhost/testdb',echo=True) >>> metadata = MetaData()
#create_engine()里的格式为:create_engine('数据库://数据库用户名:密码@主机名/要用的数据库名',echo=True) 这里的echo我没做研究,反正让他 =True
#主机名后面也可以加端口号:@localhost:XXXX/testdb,也可省略
#网上看到的文章尽是以sqlite数据库为范例的,我都怀疑那些大拿是不是ctrl c,ctrl d的。自带的文档也讲的不清楚,这步就卡了我很久
#metadata这条我也不太懂,反正是绑定到数据库引擎,调用其中的一些命令可以对数据库作出相应操作
定义表
>>> users_table = Table('users',metadata, ... Column('id',Integer,primary_key=True), ... Column('name',String(40)), ... Column('fullname',String(40)), ... Column('password',String(40)) ... )
#这里只是定义,而并不是真的就在数据库建立了一个表
#记住Table的格式就行,String就是sql里的varchar,网上的文章都可以直接写String而不用加字符长度,可我机子上不行,直接写String会出现如下错误:
# sqlalchemy.exc.CompileError: (in table 'users', column 'name'): VARCHAR requires a length on dialect mysql
#所以我都加了个40的长度
创建表
>>> metadata.create_all(engine) 2014-07-18 23:57:28,023 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode' 2014-07-18 23:57:28,023 INFO sqlalchemy.engine.base.Engine () 2014-07-18 23:57:28,027 INFO sqlalchemy.engine.base.Engine SELECT DATABASE() 2014-07-18 23:57:28,032 INFO sqlalchemy.engine.base.Engine () 2014-07-18 23:57:28,034 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin' 2014-07-18 23:57:28,034 INFO sqlalchemy.engine.base.Engine () 2014-07-18 23:57:28,041 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1 2014-07-18 23:57:28,042 INFO sqlalchemy.engine.base.Engine () 2014-07-18 23:57:28,051 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1 2014-07-18 23:57:28,051 INFO sqlalchemy.engine.base.Engine () 2014-07-18 23:57:28,052 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1 2014-07-18 23:57:28,052 INFO sqlalchemy.engine.base.Engine () 2014-07-18 23:57:28,054 INFO sqlalchemy.engine.base.Engine DESCRIBE `users` 2014-07-18 23:57:28,056 INFO sqlalchemy.engine.base.Engine () 2014-07-18 23:57:28,059 INFO sqlalchemy.engine.base.Engine ROLLBACK 2014-07-18 23:57:28,061 INFO sqlalchemy.engine.base.Engine CREATE TABLE users ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(40), fullname VARCHAR(40), password VARCHAR(40), PRIMARY KEY (id) )
2014-07-18 23:57:28,062 INFO sqlalchemy.engine.base.Engine ()
2014-07-18 23:57:28,074 INFO sqlalchemy.engine.base.Engine COMMIT
#这步才是真正在数据库中建立了表‘users’。注意:是在数据库testdb中,一开始建立引擎时就指定里数据库testdb
定义一个和表users相匹配的类
>>>class User(object): ... def __init__(self,name,fullname,password): ... self.name = name ... self.fullname = fullname ... self.password = password ... def __repr__(self): ... return "
#__init__ 里的各项属性要和建立的表对应,User这个类就相当于表的模板,类的实例就是表中的一行了
#class User(object)中object不能少,我也不知道为什么反正在我的机子上要是括号内空的就会出错
#__repr__只是用来测试观察
映射
>>> mapper(User,users_table)
#这步就是把表和类联系起来,一一对应了
创建事务并绑定数据库连接
>>> Session = sessionmaker(bind=engine) >>> session = Session()
#session就是一个事务,它在提交和关闭前,维护着一个数据库链接
添加类的实例到事务
>>> session.add_all([ ... User('wendy','Wendy Williams','foobar'), ... User('mary','Mary Contrary','xxg527'), ... User('fred','Fred Flinstone','blah')])
#分别创建了三个类User的实例作为表users的行添加到事务中,此时事务还没提交,数据库中还没在表中建立这些行
提交事务
>>> session.commit() 2014-07-19 01:04:46,359 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2014-07-19 01:04:46,362 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (%s, %s, %s) 2014-07-19 01:04:46,362 INFO sqlalchemy.engine.base.Engine ('wendy', 'Wendy Williams', 'foobar') 2014-07-19 01:04:46,365 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (%s, %s, %s) 2014-07-19 01:04:46,366 INFO sqlalchemy.engine.base.Engine ('mary', 'Mary Contrary', 'xxg527') 2014-07-19 01:04:46,367 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (%s, %s, %s) 2014-07-19 01:04:46,367 INFO sqlalchemy.engine.base.Engine ('fred', 'Fred Flinstone', 'blah') 2014-07-19 01:04:46,368 INFO sqlalchemy.engine.base.Engine COMMIT
#由此数据库中的users表中有了这三行
查询
>>> our_user = session.query(User).filter_by(name='mary').first() 2014-07-19 01:08:38,624 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2014-07-19 01:08:38,626 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password FROM users WHERE users.name = %s LIMIT %s 2014-07-19 01:08:38,627 INFO sqlalchemy.engine.base.Engine ('mary', 1) >>> our_user
这些就是基础内容里吧,然后再往下看就so easy了。ok,睡觉