SQLAlchemy

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

安装：

 pip install SQLAlchemy 

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

MySQL-Python
    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
   
pymysql
    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
   
MySQL-Connector
    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
   
cx_Oracle
    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
   
更多详见：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.表操作

Session = sessionmaker(bind=engine)
    session = Session()

#增
    # objs=([UserType(caption='公关'),
    #        UserType(caption='市场'),])
    # session.add_all(objs)

    # obj = User(name='alex1',extra='sb',type_id=1)
    # session.add(obj)
    # session.add_all(
    #     [User(name='alex4',extra='sb',type_id=2),
    #     User(name='alex5',extra='sb',type_id=1),]
    # )

#删
    # session.query(User).filter(User.id > 3).delete()

#改
    # session.query(User).filter(User.id>2).update({'name':'alex4'})
    # session.query(User).filter(User.id>2).update({User.name:User.name+'sb'},synchronize_session=False)
    # session.query(User).filter(User.id > 2).update({User.type_id: User.type_id + 1}, synchronize_session="evaluate")

#查
    # ret = session.query(User).all()
    # for row in ret:
    #     print(row.id,row.name)
    #>>>>>>>>>>>>>>>>>>>>>>
# 1 alex1
# 2 alex2
# 3 alex3sb

    # ret = session.query(User.name,User.extra).all()
    # for row in ret:
    #     print(row)
    # >>>>>>>>>>>>>>>>>>>>>>
#('alex1', 'sb')
# ('alex2', 'sb')
# ('alex3sb', 'sb')

    # ret = session.query(User).filter_by(name='alex1').first()
    # ret1 = session.query(User).filter_by(name='alex2',extra='sb').first()
    # print(ret.name)
    # print(ret1.name)
    # >>>>>>>>>>>>>>>>>>>>>>
#alex1
#alex2


    session.commit()
    session.close()