sqlalchemy是python操作數(shù)據(jù)庫的一個庫。能夠進行orm映射，sqlalchemy“采用簡單的python語言，為高效和高性能的數(shù)據(jù)庫訪問設(shè)計，實現(xiàn)了完整的企業(yè)級持久模型”
sqlalchemy的理念是，sql數(shù)據(jù)庫的量級和性能重要于對象集合；而對象集合的抽象又重要于表和行。
一安裝sqlalchemy
代碼如下:
pipinstallsqlalchemy
導(dǎo)入如果沒有報錯則安裝成功
代碼如下:>>>importsqlalchemy
>>>sqlalchemy.__version__
'0.9.1'
>>>
二使用sqlalchemy對數(shù)據(jù)庫操作
1.定義元信息，綁定到引擎
代碼如下:
(env)ghost@ghost-h61m-s2v-b3:~/project/flask/fsql$python
python2.7.3(default,apr102013,05:13:16)
[gcc4.7.2]onlinux2
typehelp,copyright,creditsorlicenseformoreinformation.
>>>fromsqlalchemyimport*
>>>fromsqlalchemy.ormimport*
>>>engine=create_engine('sqlite:///./sqlalchemy.db',echo=true)#定義引擎
>>>metadata=metadata(engine)#綁定元信息
>>>
2.創(chuàng)建表格，初始化數(shù)據(jù)庫
代碼如下:
>>>users_table=table('users',metadata,
...column('id',integer,primary_key=true),
...column('name',string(40)),
...column('email',string(120)))
>>>
>>>users_table.create()
2014-01-0910:03:32,436infosqlalchemy.engine.base.engine
createtableusers(
idintegernotnull,
namevarchar(40),
emailvarchar(120),
primarykey(id)
)
2014-01-0910:03:32,436infosqlalchemy.engine.base.engine()
2014-01-0910:03:32,575infosqlalchemy.engine.base.enginecommit
>>>
執(zhí)行上述代碼，我們就創(chuàng)建一個users表，有id，name，email三個字段
代碼如下:
(env)ghost@ghost-h61m-s2v-b3:~/project/flask/fsql$sqlite3sqlalchemy.db
sqliteversion3.7.132012-06-1102:05:22
enter.helpforinstructions
entersqlstatementsterminatedwitha;
sqlite>.tables
users
sqlite>
3.基本操作，插入
如果已經(jīng)table表已經(jīng)存在，第二次運行就不許要create了，使用autoload設(shè)置
代碼如下:
>>>fromsqlalchemyimport*
>>>fromsqlalchemy.ormimport*
>>>engine=create_engine('sqlite:///./sqlalchemy.db',echo=true)
>>>metadata=metadata(engine)
>>>users_table=table('users',metadata,autoload=true)
2014-01-0910:20:01,580infosqlalchemy.engine.base.enginepragmatable_info(users)
2014-01-0910:20:01,581infosqlalchemy.engine.base.engine()
2014-01-0910:20:01,582infosqlalchemy.engine.base.enginepragmaforeign_key_list(users)
2014-01-0910:20:01,583infosqlalchemy.engine.base.engine()
2014-01-0910:20:01,583infosqlalchemy.engine.base.enginepragmaindex_list(users)
2014-01-0910:20:01,583infosqlalchemy.engine.base.engine()
>>>users_table
table('users',metadata(bind=engine(sqlite:///./sqlalchemy.db)),column('id',integer(),table=<users>,primary_key=true,nullable=false),column('name',varchar(length=40),table=<users>),column('email',varchar(length=120),table=<users>),schema=none)
>>>
實例化一個插入句柄
代碼如下:
>>>i=users_table.insert()
>>>i
<sqlalchemy.sql.dml.insertobjectat0x31bc850>
>>>printi
insertintousers(id,name,email)values(?,?,?)
>>>i.execute(name='rsj217',email='rsj21@gmail.com')
2014-01-0910:24:02,250infosqlalchemy.engine.base.engineinsertintousers(name,email)values(?,?)
2014-01-0910:24:02,250infosqlalchemy.engine.base.engine('rsj217','rsj21@gmail.com')
2014-01-0910:24:02,251infosqlalchemy.engine.base.enginecommit
<sqlalchemy.engine.result.resultproxyobjectat0x31bce10>
>>>i.execute({'name':'ghost'},{'name':'test'})
2014-01-0910:24:57,537infosqlalchemy.engine.base.engineinsertintousers(name)values(?)
2014-01-0910:24:57,537infosqlalchemy.engine.base.engine(('ghost',),('test',))
2014-01-0910:24:57,537infosqlalchemy.engine.base.enginecommit
<sqlalchemy.engine.result.resultproxyobjectat0x31bcd50>
>>>
數(shù)據(jù)庫內(nèi)容為
代碼如下:
sqlite>select*fromusers;
1|rsj217|rsj21@gmail.com
2|ghost|
3|test|
sqlite>
查詢刪除和插入類似都需要先實例一個sqlalchemy.sql.dml對象
三使用orm
使用orm就是將pythonclass與數(shù)據(jù)庫的table映射，免去直接寫sql語句
創(chuàng)建映射
代碼如下:
>>>classuser(object):
...def__repr__(self):
...return'%s(%r,%r)'%(self.__class__.__name__,self.name,self.email)
...
>>>mapper(user,users_table)#創(chuàng)建映射
<mapperat0x31bcfd0;user>
>>>ul=user()
>>>ul.name
>>>printul
user(none,none)
>>>printul.name
none
>>>
建立會話
查詢
代碼如下:
>>>session=create_session()
>>>session
<sqlalchemy.orm.session.sessionobjectat0x31bef10>
>>>query=session.query(user)
>>>query
<sqlalchemy.orm.query.queryobjectat0x31bee50>
>>>u=query.filter_by(name='rsj217').first()
2014-01-0910:44:23,809infosqlalchemy.engine.base.engineselectusers.idasusers_id,users.nameasusers_name,users.emailasusers_email
fromusers
whereusers.name=?
limit?offset?
2014-01-0910:44:23,809infosqlalchemy.engine.base.engine('rsj217',1,0)
>>>u.name
u'rsj217'
>>>
插入
代碼如下:
>>>fromsqlalchemyimport*
>>>fromsqlalchemy.ormimport*
>>>engine=create_engine('sqlite:///./sqlalchemy.db')
>>>metadata=metadata(engine)
>>>users_table=table('users',metadata,autoload=true)
>>>classuser(object):pass
...
>>>mapper(user,users_table)
<mapperat0x18185d0;user>
>>>session=sessionmaker(bind=engine)
>>>session=session()
>>>u=user()
>>>u.name='new'
>>>session.add(u)
>>>session.flush()
>>>session.commit()
>>>
注意建立會話的方式，sqlalchemy的版本不同sessionmaker的方式更好
剩下刪除關(guān)系事物等高級操作就參考官方文檔了。