sqlalchemy学习日志_MySQL

最近学习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 sqlalch<div>本文来源gaodai.ma#com搞##代!^码7网</div>emy.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_12014-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_12014-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_12014-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 ROLLBACK2014-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_passwordFROM users WHERE users.name = %s  LIMIT %s2014-07-19 01:08:38,627 INFO sqlalchemy.engine.base.Engine ('mary', 1)>>> our_user

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

喜欢 (0)赏

[搞代码]

分享 (0)