SQLAlchemy

SQLAlchemy是Python编程语言下的一款ORM框架,该框架建立在数据库API之上,使用关系对象映射进行数据库操作,简言之便是:将对象转换成SQL,然后使用数据API执行SQL并获取执行结果。

安装:

 1 pip install SQLAlchemy 

SQLAlchemy本身无法操作数据库,其必须以来pymysql等第三方插件,所以在使用前先确保安装好pymysql.

 1 MySQL-Python
 2     mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
 3    
 4 pymysql
 5     mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
 6    
 7 MySQL-Connector
 8     mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
 9    
10 cx_Oracle
11     oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
12    
13 更多详见:http://docs.sqlalchemy.org/en/latest/dialects/index.html

 

1.创建表:

#导入需要用到的模块
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index,CHAR,VARCHAR
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

#建立连接
engine = create_engine("mysql+pymysql://root:password@127.0.0.1:3306/mydb?charset=utf8",max_overflow=5)

Base = declarative_base()

#类形式创建表单
class User(Base):
    __tablename__='my_user'
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    extra = Column(String(16))
    type_id = Column(Integer,ForeignKey("my_usertype.tid"))

#按需求设置索引
    # __table__args__=(
    #     UniqueConstraint('id','name',name=uix_id_name),
    #     Index('ix_id_name','name','extra'),
    # )

class UserType(Base):
    __tablename__='my_usertype'
    tid = Column(Integer,primary_key=True)
    caption = Column(String(30),unique=True)

#执行建表函数
def init_db():
    Base.metadata.create_all(engine)

#删表函数
def drop_db():
    Base.metadata.drop_all(engine)

if __name__ == '__main__':
    init_db()

 

2.表操作

 1 Session = sessionmaker(bind=engine)
 2     session = Session()
 3 
 4 #
 5     # objs=([UserType(caption='公关'),
 6     #        UserType(caption='市场'),])
 7     # session.add_all(objs)
 8 
 9     # obj = User(name='alex1',extra='sb',type_id=1)
10     # session.add(obj)
11     # session.add_all(
12     #     [User(name='alex4',extra='sb',type_id=2),
13     #     User(name='alex5',extra='sb',type_id=1),]
14     # )
15 
16 #
17     # session.query(User).filter(User.id > 3).delete()
18 
19 #
20     # session.query(User).filter(User.id>2).update({'name':'alex4'})
21     # session.query(User).filter(User.id>2).update({User.name:User.name+'sb'},synchronize_session=False)
22     # session.query(User).filter(User.id > 2).update({User.type_id: User.type_id + 1}, synchronize_session="evaluate")
23 
24 #
25     # ret = session.query(User).all()
26     # for row in ret:
27     #     print(row.id,row.name)
28     #>>>>>>>>>>>>>>>>>>>>>>
29 # 1 alex1
30 # 2 alex2
31 # 3 alex3sb
32 
33     # ret = session.query(User.name,User.extra).all()
34     # for row in ret:
35     #     print(row)
36     # >>>>>>>>>>>>>>>>>>>>>>
37 #('alex1', 'sb')
38 # ('alex2', 'sb')
39 # ('alex3sb', 'sb')
40 
41     # ret = session.query(User).filter_by(name='alex1').first()
42     # ret1 = session.query(User).filter_by(name='alex2',extra='sb').first()
43     # print(ret.name)
44     # print(ret1.name)
45     # >>>>>>>>>>>>>>>>>>>>>>
46 #alex1
47 #alex2
48 
49 
50     session.commit()
51     session.close()
View Code

 

posted @ 2017-06-13 20:31  Mitsuis  阅读(150)  评论(0)    收藏  举报