SQLAlchemy基础知识

官方参考资料：1、https://docs.sqlalchemy.org/en/latest/dialects/mssql.html#module-sqlalchemy.dialects.mssql.pyodbc

2、https://docs.sqlalchemy.org/en/rel_0_9/core/index.html

3、https://docs.sqlalchemy.org/en/latest/

参考资料：1、https://blog.youkuaiyun.com/Lotfee/article/details/57406450

2、https://www.2cto.com/database/201708/664698.html

1、微软SQL服务器连接

SQL Server方言使用pyodbc作为默认DBAPI。pymssql也可用：

# pyodbc engine = create_engine('mssql+pyodbc://scott:tiger@mydsn') # pymssql engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')

2、ORM 快速入门

2.1 连接数据库与建表

# coding:utf-8

from sqlalchemy import Column,String,create_engine,Table,MetaData

from sqlalchemy.orm import sessionmaker

from sqlalchemy.ext.declarative import declarative_base

 

# 链接数据库('数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名')

engine = create_engine('mysql+mysqldb://root:mysql@localhost:3306/test'，echo=True)

Base = declarative_base()# 生成SQLORM基类

 

class User(Base):

    __tablename__ = 'userinfo'# 表名

 

    id = Column(String(20),primary_key=True) # 字段

    name = Column(String(20))                # 字段

 

# 创建userinfo表（所有表结构）

Base.metadata.create_all(engine)

DBSession = sessionmaker(bind=engine)# 创建与数据库的会话，返回的是一个类

 

create_engine()用来初始化数据库连接。SQLAlchemy用一个字符串表示连接信息：

格式：’数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名’

根据不同的引擎进行配置

MySQL-python #引擎配置

     mysql+mysqldb：//<user>:<password>@<host>:<port>/<dbname>

pymysql #引擎配置

     mysql+pymysql://<user>:<password>@<host>:<port>/<dbname>

MySQL-Connector #引擎配置

     mysql+mysqlconnector://<user>:<password>@<host>:<port>/<dbname>

2.2 添加一条数据

# 创建session对象:

session = DBSession()# 生成链接数据库的实例

# 创建新User对象:

new_user = User(id='1',name='Bob')

# 添加到session:

session.add(new_user)

# 提交即保存到数据库:

session.commit()

# 关闭session:

session.close()

2.3 添加多条

new_user1 = User(id ='2',name='a')

new_user2 = User(id='3',name='b' )

session.add_all([new_user1,new_user2])

session.commit()

session.close()

2.4 查询

query()的参数是创建映射的类，使用all()返回的是列表.

#获取所有数据

obj = session.query(User).all()

# 获取指定数据

session.query(User).filter(User.id==1).one()

# 获取返回数据的第一行

session.query(User).first()

# 查询ID>1的所有名字

session.query(User.name).filter(User.id>1).all()

# 通过索引取出数据

q1 = session.query(User.name).all()[1:2]

# 根据id降序排列

q1 = session.query(User).order_by(-User.id).all()

# 查询user表中id是1,3的姓名

q1 = session.query(User.name).filter(User.id.in_([1,3])).all()

# 模糊查询,%匹配多个字符,_匹配单个字符

q1 = session.query(User.name).filter(User.name.like('%h__')).all()

# 计算个数,返回查询的数量

q1 = session.query(User).count()

# 逻辑与查询

from sqlalchemy import and_

obj = session.query(User).filter(and_(User.id==1,User.name =='a')).all()

# 提交结果

session.commit()

session.close()

# 逻辑或查询

from sqlalchemy import or_

obj = session.query(User).filter(or_(User.id==1,User.name =='c')).all()

# 提交结果

session.commit()

session.close()

# 排序查询(按照id倒序显示name)

obj = session.query(User.name).order_by(-User.id).all()

# # 提交结果

session.commit()

session.close()

2.5 修改

# 按照条件修改,返回修改的条数

q1 = session.query(User).filter(User.id>2).update({'name':'boy'})

session.commit()

session.close()

2.6 删除

# 查处需要删除的对象

obj = session.query(User).filter(User.id==2).one()

# 删除对象

session.delete(obj)

# 提交结果

session.commit()

session.close()

2.7 外键一对多关联

class Parent(Base):

    __tablename__ = 'parent_table'

    id = Column(Integer,primary_key=True,autoincrement=True)

    name = Column(String(20))

    # 可以通过parent_table表查child_table,数据库中不会创建children字段,只是建立里联系

    # 参数backref相当于给类Child添加了一个属性，在查询的时候可以通过Child.parents属性获取child_table表关联的所有parent_table表

    children = relationship('Child'，backref = 'parents')

 

 

 

class Child(Base):

    __tablename__ = 'child_table'

    id = Column(Integer,primary_key=True,autoincrement=True)

    age = Column(Integer)

    parent_id = Column(Integer,ForeignKey('parent_table.id'))

 

查询例子：

#查询出符合条件的一个对象

q4 = session.query(Parent).filter(Parent.id==1).one()

print q4.children

# 通过对象属性children，查出这个对象的所有关联age字段值

for i in q4.children:

    print i.age

 

#查询出符合条件的一个对象

q5 = session.query(Child).filter(Child.id==2).one()

#打印对象中的关联

print q5.parents.name

 

# 内关联查询出Parent.id == Child.parent_id的结果

q6 = session.query(Parent).join(Child).filter(Parent.id == Child.parent_id).all()