Python SQLAlchemy 程式執行個體

標籤：blog   io   ar   使用   for   資料   on   2014   log   

Sample1

# coding=gbkfrom sqlalchemy import *from sqlalchemy.orm import *engine = create_engine('sqlite:///./sqlalchemy.db', echo=True)metadata = MetaData(engine)'''create table'''users_table = Table('users',                     metadata,                    Column('id', Integer, primary_key=True),                    Column('name', String(40)),                    Column('email', String(120)))if not users_table.exists():    users_table.create()'''load table'''users_table = Table('users', metadata, autoload=True)    '''insert'''users_table.insert().execute(name="sss", email="[email protected]")users_table.insert().execute(name="ttt", email="[email protected]")'''update'''users_table.update(users_table.c.name=="ttt").execute(name="ddd")'''select'''result=users_table.select(and_(users_table.c.name == "ddd", users_table.c.email == "[email protected]")).execute()for item in result.fetchall():    print item'''delete'''users_table.delete(users_table.c.name=="ddd").execute()'''ORM -- bind'''class User(object):     pass mapper(User, users_table) Session = sessionmaker() #建立了一個自訂了的 Session類Session.configure(bind=engine)  #將建立的資料庫連接關聯到這個sessionsession = Session()u = User()u.name='asdf'u.email='[email protected]'session.add(u)  #在session中新增內容session.flush() #儲存資料session.commit() #資料庫事務的提交,sisson自動到期而不需要關閉

Sample2

# coding=gbkfrom sqlalchemy.orm import mapper, sessionmaker #sessionmaker() 函數是最常使用的建立最頂層可用於整個應用 Session 的方法,Session 管理著所有與資料庫之間的會話from datetime import datetimefrom sqlalchemy import Table, MetaData, Column, ForeignKey, Integer, String, Unicode, DateTime #會SQL的人能理解這些函數吧?from sqlalchemy import *from sqlalchemy.orm import *engine = create_engine("sqlite:///tutorial.db", echo=True) #建立到資料庫的串連,echo=True 表示用logging輸出調試結果metadata = MetaData() #跟蹤表屬性user_table = Table( #建立一個表所需的資訊:欄位,表名等                    'tf_user', metadata,                    Column('id', Integer, primary_key=True),                    Column('user_name', Unicode(16), unique=True, nullable=False),                    Column('email_address', Unicode(255), unique=True, nullable=False),                    Column('password', Unicode(40), nullable=False),                    Column('first_name', Unicode(255), default=''),                    Column('last_name', Unicode(255), default=''),                    Column('created', DateTime, default=datetime.now))metadata.create_all(engine)  #在資料庫中產生表class User(object): pass #建立一個映射類mapper(User, user_table) #把表映射到類Session = sessionmaker() #建立了一個自訂了的 Session類Session.configure(bind=engine)  #將建立的資料庫連接關聯到這個sessionsession = Session()u = User()u.user_name='dongwm'u.email_address='[email protected]'u.password='testpass'  #給映射類添加以下必要的屬性,因為上面建立表指定這幾個欄位不可為空session.add(u)  #在session中新增內容session.flush() #儲存資料session.commit() #資料庫事務的提交,sisson自動到期而不需要關閉query = session.query(User) #query() 簡單的理解就是select() 的支援 ORM 的替代方法,可以接受任意組合的 class/column 運算式print list(query) #列出所有userprint query.get(1) #根據主鍵顯示print query.filter_by(user_name='dongwm').first()  #類似於SQL的where,列印其中的第一個u = query.filter_by(user_name='dongwm').first()u.password = 'newpass' #修改其密碼欄位session.commit() #提交事務print query.get(1).password #列印會出現新密碼for instance in session.query(User).order_by(User.id):  #根據id欄位排序,列印其中的使用者名稱和郵箱地址    print instance.user_name, instance.email_address

Sample3:

# coding=gbkfrom sqlalchemy import *from sqlalchemy.orm import *from datetime import datetimefrom sqlalchemy import Table, MetaData, Column, ForeignKey, Integer, String, Unicode, DateTimeengine = create_engine("sqlite:///tutorial2.db", echo=True) #建立到資料庫的串連,echo=True 表示用logging輸出調試結果metadata = MetaData() #跟蹤表屬性class User(object):    passclass Group(object):    passclass Permission(object):    passuser_table = Table('tf_user',                    metadata,                    Column('id', Integer, primary_key=True),                   Column('user_name', Unicode(16), unique=True, nullable = False),                   Column('password', Unicode(16), nullable = False))group_table = Table("tf_group",                    metadata,                    Column('id', Integer, primary_key=True),                    Column('group_name', Unicode(16), unique=True, nullable = False))permission_table = Table("tf_permission",                    metadata,                    Column('id', Integer, primary_key=True),                    Column('permission_name', Unicode(16), unique=True, nullable = False))user_group = Table("user_group",                    metadata,                   Column('user_id', None, ForeignKey('tf_user.id'), primary_key=True),                   Column('group_id', None, ForeignKey('tf_group.id'), primary_key=True))group_permission = Table("group_permission",                    metadata,                   Column('permission_id', None, ForeignKey('tf_permission.id'), primary_key=True),                   Column('group_id', None, ForeignKey('tf_group.id'), primary_key=True))mapper(User, user_table, properties=dict(_groups=relation(Group, secondary=user_group, backref='_users')))mapper(Group, group_table, properties=dict(_permissions=relation(Permission, secondary=group_permission, backref='_groups')))mapper(Permission, permission_table)metadata.create_all(engine)

Python SQLAlchemy 程式執行個體