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

 


安装:

pip3 install SQLAlchemy

版本检查:

import sqlalchemy
sqlalchemy.__version__


不同数据库配置:

根据配置文件的不同调用不同的数据库API,从而实现对数据库的操作:

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

MySQL-Python
    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
 
pymysql
    mysql+pymysql://<username>:<password>@<host>[:<port>]/<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...]

 

连接数据库:

from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:a13896321z@127.0.0.1/school",encoding='utf-8',echo=True)

声明映射:

declaractive用来表示类与表的关系。

声明基类,将类映射到数据表,自定义数据表类必须继承这个基类。

from sqlalchemy.ext.declaractive import  declaractive_base
Base = declaractive_base()

创建表结构类:

一个表结构类必须包含一个__tablename__和primary_key的字段

字段的类型:

SmallInteger
Integer
BigInteger
Float
Numeric(precision=None, scale=None, decimal_return_scale=None, asdecimal=True)

Boolean
Enum

Date
DateTime
Time
Interval(native=True, second_precision=None, day_precision=None)

LargeBinary(length=None)
MatchType(create_constraint=True, name=None, _create_events=True)
PickleType(protocol=4, pickler=None, comparator=None)
SchemaType(name=None, schema=None, metadata=None, inherit_schema=False, quote=None, _create_events=True)
String(length=None, collation=None, convert_unicode=False, unicode_error=None, _warn_on_bytestring=False)
Text(length=None, collation=None, convert_unicode=False, unicode_error=None, _warn_on_bytestring=False)
Unicode(length=None, **kwargs)

# 常用的类型:
类型名        python类型            说明
Integer       int                普通整数,32位    
Float         float              浮点数    
String        str                变长字符串    
Text          str                变长字符串,对较长字符串做了优化    
Boolean       bool               布尔值    
PickleType    任何python对象      自动使用Pickle序列化

字段的选项:

Column(
nullable=True,      # 可以为空

autoincrement=True, # 数值自增

default='red'       # 指定默认值

primary_key = True  # 设为主键

ForeignKey = 'table_name.id'    # 指定外键,ForeignKey需要导入

unique = True       # 设定此字段键值唯一,不允许出现重复值。
)

 

建立sqlalchemy连接:

 

from sqlalchemy import create_engine
engine = create_engine()

声明表结构:

class School(Base):
    __tablename__ = "school"                # 这个才是表名
    id = Column(Integer, primary_key = True)
    sch_name = Column(String(32))
    sch_addr = Column(String(255))
    sch_tel = Column(Integer)
    
    def __repr__(self):
        # 查询的时候显示的是值,需不是一个内存地址。
        return "school name:{},tel:{}".format(self.sch_name,self.sch_tel)

 

 

动态添加表字段。

def add_filed(table_name,):
for i in range(3):

    setattr(table_class,'Col'+str(i),(Column('Col'+str(i), String(50),comment='Col'+str(i))))

Base.metadata.create_all(engine)

 

 

创建表:

Base.metadata.create_all(engine)
# 删除表 Base.metadata.drop_all(engine)

增加一条记录:

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)        # 创建一个与数据库的会话,生成的对象是类
session = Session()                        # 实例化这个会话类

class AddRecord(object):
    # 交互添加记录类。
    def __init__(self,*args):
        # self.table_name = table_name
        self.school_feild = {'sch_name':'学校名:','sch_addr':'学校地址:','sch_tel':'学校电话:'}
    
    def school(self):
        f = {}
        for k,v in self.school.items():
            f[k] = input('{}'.format(v)).strip()
        return f            # 返回一个字典
        
add = AddRecord()
school_attr = add.school()    # 获取输入的值
# 下面a1是创建一条记录
a1 = School(sch_name = school_attr[sch_name],sch_addr= school_attr[sch_addr],sch_tel = school_attr[sch_tel])
session.add(a1)                # 把记录添加到数据库
session.commit()               # 提交到数据库,最终对数据库更改。


表的参数__table_args__:

__table_args__:参数需要导入。

from sqlalchemy import UniqueConstraint, PrimaryKeyConstraint, Index


参数名参数作用示例
UniqueConstraint('field1','feild2',..., name='')

field1~n:要联合唯一的字段名

name:设置联合的字段名

把多个字段建立一个联合唯一的限制

__table_args__=(

UniqueConstraint('ip','port',name='ip_port'),

)

Index('field1','feild2',...,)field1~n:字段名把指定字段建立索引

__table_args__=(

Index('ip','port'),

)

PrimeryKeyConstraint('field1','feild2',...,)field1~n:字段名设置多字段主键

__table_args__=(

PrimeryKeyConstraint('ip','port'),

)

...更多参数待补充




 增、查、

这四个操作都需要导入sessionmaker ,用sessionmaker创建一个会话,除了建库外,所有操作都在会话中完成。

from sqlalchemy.orm import sessionmaker
Session = sessionmaker()
session =Session()

 

 增:

 





add(记录)
记录:由表类生成的实例增加一条记录

d1=School(name='DL',addr='GJZ')

session.add(d1)

add_all(记录列表)
记录列表:由表类生成的实例列表同时增加多条记录

d1=School(name='DL',addr='GJZ')

d2=School(name='BJ',addr='HLG')

session.add([d1,d2])






 查:

 这三个操作都需要用到query

query参数作用
query(table)

table:表名。

 

查询表中所有数据

以表结构__repr__中定义的格式显示

session.query(table).all()
query(table.field1,table.field2,……)

table:表名。

field:字段名,可多个。

显示指定字段,不以表结构__repr__格式输出session.query(table.id,table.name).all()
结果显示的方法:


all()
显示所有结果,结果是一个列表session.query(table).all()
first()
只显示第一个结果session.query(table).first()
one()

如果没有获得结果或者返回了多个结果,则会产生一个 error

结果是一个实例类。

a=session.query(table).filter(table.id==6).one()

a.id#就可以查看id的值。

scalar()

干啥用的?

感觉和one一样。。。


count()
计数session.query(table).count()
[m:n]
切片,读取指定的结果和列表切片一样session.query(table)[:3]
offset(2)
从第3条数据开始读session.query(table).offset(2).all()
limit(3)
只显示前三条session.query(table).limit(3).all()
order_by


desc

降序

查询结果降序显示

session.query(t).order_by(t.id.desc()).filter(t.id>3).all()
asc
升序

查询结果升序显示

session.query(t).order_by(t.id.asc()).filter(t.id>3).all()




query给表和字段重命名


lable()

给字段重命名

调用时可以调用lable内的名字。

a=session.query(table.filed.lable('other')).all()

for i in a:

      print (i.other)

aliased
给表重命名,需要导入from sqlalchemy.orm import aliased
table_a = aliased(table)

query使用text执行SQL语句

text需要导入

 


 

from sqlalchemy import text

text('SQL')SQL:SQL语句或SQL表达式或表的字段



在filter()方法中使用

指定过滤条件session.query(t).filter(text('id >2')).all()

在order_by()方法中使用指定排序的字段session.query(t).order_by(text('id desc')).all()

在from_statement()方法中使用运行完整的SQL语句

session.query(t).from_statement(text(

'select * from talbe where id>2'

))

params(变量1=值1,变量2=值2...)

TEXT中的变量前面必须加冒号。

例:text('id = id')

给text中的变量指定值。

session.query(t).filter(text('id>:id')).params(id=2).all()

相当于text('id>2')

query.filter 条件查询

支持所有SQL条件表达式(where部分)




filter(表名.字段 == 值)

表名.字段:固定格式,不能省略表名。

==:条件表达式,==,>=,<=,>,<,!=

根据条件查询数据session.query(table).filter(table.id <2).all()
filter(表名.字段.关键字(值) )

表名.字段:固定格式,不能省略表名。

关键字:in_,like,notin_,notlike,between,contains,is_,notis

值:可以是列表,元组,字符串,数字

       字符串:可以用通配符%,例:"%a%",单字符通配 _

1在列表里

2没在列表里

3匹配字符串

4不匹配字符串

5匹配字符串,不区分大小写

6不匹配字符串,不区分大小写

7在2和6之间,含2和6

8包含字符a

9field是真

10field是不是真

11匹配索引,匹配表报错

1-filter(table.field.in_([1,2,'1']))

2-filter(table.field.notin_([1,2,'1']))

3-filter(table.field.like('%a%'))

4-filter(table.field.notlike('%a%'))

5-filter(table.field.ilike('%a%'))

6-filter(table.field.notilike('%a%'))

7-filter(table.field.between(2,6))

8-filter(table.field.contains('a'))

9-filter(table.field.is_(True))

10-filter(table.field.notis(True))

11-filter(table.field.match('dage'))

fileter多条件查询,or_,and_


filter(逻辑运算符(条件表达式1,条件表达式2,......))
逻辑运算符:and_,or_

需要从sqlalchemy导入

from sqlalchmy import and_,or_

filter(and_(table.id >1,table.id<6))

filter(or_(table.id>1,table.name=='dage'))





func写在query方法里,可以和字段一起显示。


func.参数

avg:求平均值

count:统计数量

sum:求和

max:最大

min:最小

对分组和字段进行简单的数学运算。

如果不符合逻辑会提示错误“Inaggregated query without GROUP BY

session.query(table_b.name

,func.count(table_b.age),

func.min(table_b.age)).group_by(table_b.name).all()

这一句是对name分组,显示name,显示每个name的个数,和每组最小的age

 

改:

有两种方法:


query.fileter(条件).update(值)值:字典格式。{表名.字段:值}更新所有filter筛选的记录的指定字段session.query(table_b).filter(table_b.name == 'erge').update({table_b.score : 60})
实例.字段=值

先创建一个查询结果的实例

再通过实例改字段的值

提交更改

sql=seesion.query(table_b).filter(table_b.id ==3).first()

sql.name = 'new name'

session.commit()

实例.字段=值

批量更新



sql=seesion.query(table_b).filter(table_b.age ==20).all()

for i in range(len(sql)):

    sql(i).score= 100

session.commit()

update(值,synchronize_session=False)
synchronize_session=False立即提交,更新的时候速度更快,同时批量更新时不加此参数报错session.query(table_b).filter(table_b.name == 'erge').update({table_b.score : 60},synchronize_session =False)
user = User(id=1, name='通过主键改内容')
session.merge(user)

merge的作用是合并,查找primary key是否一致,一致则合并,不一致则新建

 

删:





delete()

qurey后面加limit.all,first,等方法时,

不能直接加delete()

需要使用for删除。


sql1 = session.query(table_b).filter(and_(table_b.name == 'dage', table_b.age == 19)).delete()

或者:

sql1 = session.query(table_b).filter(and_(table_b.name == 'dage', table_b.age == 19).first()

sql1.delete()

in_查询的结果,删除会报错


错误提示:

sqlalchemy.exc.InvalidRequestError: Could not evaluate current criteria in Python.  Specify 'fetch' or False for the synchronize_session parameter

解决方法:

在delete中,添加synchronize_session=False,含义是同步删除。

delete(synchronize_session=False)

sql1 = session.query(table_b).filter(table_b.name.in_('dage', 'erge')).delete(synchronize_session=False)

删除指定条数删除d开头的前三条记录

sql1 = session.query(table_b).filter(table_b.name.like('d%')).limit(3).all()

for i in sql1:

       session.delete(i)





 

 

一个基本表的创建与操作:

 

from sqlalchemy import create_engine, ForeignKey, Table, Column, String, Integer, Boolean, Enum
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
mysql_name = 'david'
mysql_pw = 'Yaotiao&shunv666'
mysql_server = '192.168.2.120'
database_name = 'test'


# 1、连接数据库
engine = create_engine("mysql+pymysql://{name}:{pw}@{server}/{database}".
                        format(mysql_name,mysql_pw,mysql_server,database_name),
                        encoding ='utf-8'
                        )

# 2、生成ORM基类
Base = declarative_base()


# 3、继承基类,定义表结构
class Product(Base):
    # 创建orm对象
    __tablename__ = 'products'                # 数据表名
    id = Column(Integer, primary_key = True)    # 字段,设为主键,默认不用赋值,此字段会自增
    name = Column(String(32))
    price = Column(Integer)

    def __repr__(self):
        # 打印查询结果实例显示结果,不加此项显示的是类的内存地址
        return "name:{},price:{}".format(self.name,self.price)
    
# 4、创建数据表
Base.metadata.create_all(engine)                # 在数据库中创建表,已存在则不创建


# 5、添加多条数据
product1 = Product(name = '华为100', price = 999) # 创建两个实例
product2 = Product(name = '华为400', price = 1999)
session.add_all([product1,product2 ])    # add_all把实例列表添加到数据库

# 6、添加单条数据
product = Product(name = '一条', price = 9999)
session.add(product)


# 7、查询数据

query_data = session.query(Product).all()       # 查所有,如果不加__repr__,结果是内存地址列表,使用for查看

for i in query_data:
    # 查看结果
    print (i.id,i.name,i.price)

# 8、修改一项数据
query_data = session.query(Product).filter(Product.name=='一条').first()
query_data.name = 'yitiao'        # 只改name一个数据

# 9、批量更新:把所有华为开头的产品价格改成10000
session.query(Product).filter(Product.name.like('华为%')).update({Product.price:10000},synchronize_session =False)


# 10、通过主键改内容merge:
update_name = Product(id=3,price=3651) 
session.merge(update_name)                # id 和 name不变,只修改了价格price = 3651

# 11、删除一条数据:把华为开头的价格最高的一条数据删掉
del_product = session.query(Product).fileter(Product.price.like('华为%'))).order_by(Product.price.desc()).first()
session.delete(del_product)

# 12、删除所有符合条件的数据:
session.query(Product).filter(Product.name.like('华为%')).delete(synchronize_session = False)


 

 问题集:https://blog.51cto.com/yishi/2335554