最近学习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相匹配的类
水彩春季少女配饰小物矢量素材适用于手账和日记本、日程规划表、笔记本和学习资料、贺卡和邀请函、旅行日志、食谱和烹饪手册、儿童教育书籍、个人博客和社交媒体等相关设计的AI格式素材。
>>>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，睡觉
