sqlalchemy学习日志

最近学习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 "<User('%s','%s','%s')>" % (self.name,self.fullname,self.password)

#__init__ 里的各项属性要和建立的表对应，User这个类就相当于表的模板，类的实例就是表中的一行了

#class User(object)中object不能少，我也不知道为什么反正在我的机子上要是括号内空的就会出错

#__repr__只是用来测试观察

映射

>>> mapper(User,users_table)
<Mapper at 0xb67a086c; User>

#这步就是把表和类联系起来，一一对应了

创建事务并绑定数据库连接

>>> 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
<User('mary','Mary Contrary','xxg527')>

#查询表中name为‘mary’的第一行，返回的数据在our_user里

这些就是基础内容里吧，然后再往下看就so easy了。ok，睡觉