SQLAlchemy介绍和基本使用

时间:2022-11-26 14:28:13

pymysqlpymysql是用Python来操作mysql的包,因此通过pip来安装,命令如下:pip3 install pymysql。如果您用的是Python 3,请安装pymysql

豆瓣源  pip install pymysql -i https://pypi.douban.com/simple

SQLAlchemySQLAlchemy是一个数据库的ORM框架,我们在后面会用到。安装命令为:pip3 install SQLAlchemy

豆瓣源  pip install sqlalchemy -i https://pypi.douban.com/simple

#encoding:utf-8
# __author__ = 'donghao'
# __time__ = 2019/1/4 17:07 from sqlalchemy import create_engine
#数据库配置变量
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'firstsqlalchemy'
USERNAME = 'root'
PASSWORD = 'root'
DB_URL = "mysql+pymysql://{}:{}@{}/{}".format(USERNAME,PASSWORD,HOSTNAME,DATABASE)
# 创建引擎
engine = create_engine(DB_URL,encoding='utf8') #创建连接
with engine.connect() as con:
rs = con.execute('SELECT 1')
print rs.fetchone()

首先从sqlalchemy中导入create_engine,用这个函数来创建引擎,然后用engine.connect()来连接数据库。其中一个比较重要的一点是,通过create_engine函数的时候,需要传递一个满足某种格式的字符串,对这个字符串的格式来进行解释:

dialect+driver://username:password@host:port/database?charset=utf8

dialect是数据库的实现,比如MySQLPostgreSQLSQLite,并且转换成小写。driverPython对应的驱动,如果不指定,会选择默认的驱动,比如MySQL的默认驱动是MySQLdbusername是连接数据库的用户名,password是连接数据库的密码,host是连接数据库的域名,port是数据库监听的端口号,database是连接哪个数据库的名字。

如果以上输出了1,说明SQLAlchemy能成功连接到数据库。

用SQLAlchemy执行原生SQL:

我们将上一个例子中的数据库配置选项单独放在一个constants.py的文件中,看以下例子:

from sqlalchemy import create_engine
from constants import DB_URI #连接数据库
engine = create_engine(DB_URI,echo=True) # 使用with语句连接数据库,如果发生异常会被捕获
with engine.connect() as con:
# 先删除users表
con.execute('drop table if exists authors')
# 创建一个users表,有自增长的id和name
con.execute('create table authors(id int primary key auto_increment,'name varchar(25))')
# 插入两条数据到表中
con.execute('insert into persons(name) values("abc")')
con.execute('insert into persons(name) values("xiaotuo")')
# 执行查询操作
results = con.execute('select * from persons')
# 从查找的结果中遍历
for result in results:
print(result)

SQLAlchemy

使用SQLAlchemy:

要使用ORM来操作数据库,首先需要创建一个类来与对应的表进行映射

from sqlalchemy import Column,Integer,String
from constants import DB_URI
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base engine = create_engine(DB_URI,echo=True) # 所有的类都要继承自`declarative_base`这个函数生成的基类
Base = declarative_base(engine)
class User(Base):
# 定义表名为users
__tablename__ = 'users' # 将id设置为主键,并且默认是自增长的
id = Column(Integer,primary_key=True)
# name字段,字符类型,最大的长度是50个字符
name = Column(String(50))
fullname = Column(String(50))
password = Column(String(100)) # 让打印出来的数据更好看,可选的
def __repr__(self):
return "<User(id='%s',name='%s',fullname='%s',password='%s')>" % (self.id,self.name,self.fullname,self.password

SQLAlchemy会自动的设置第一个Integer的主键并且没有被标记为外键的字段添加自增长的属性。因此以上例子中id自动的变成自增长的。以上创建完和表映射的类后,还没有真正的映射到数据库当中,执行以下代码将类映射到数据库中:

Base.metadata.create_all()

增删改查

增:

# 1.增加
def add_func():
p = Person(username='donghao')
session.add(p)
session.commit()

查:

def check():
Persons = session.query(Person).all()
for person in Persons:
print(person.username)

删:

def delete():
Persons = session.query(Person).first()
session.delete(Persons)
session.commit()

改:

def edit():
Persons = session.query(Person).first()
Persons.username = '123

SQLAlchemy的ORM(2)

Column常用参数:

  • default:默认值。
  • nullable:是否可空。
  • primary_key:是否为主键。
  • unique:是否唯一。
  • autoincrement:是否自动增长。
  • onupdate:更新的时候执行的函数。
  • name:该属性在数据库中的字段映射。

sqlalchemy常用数据类型:

  • Integer:整形。
  • Float:浮点类型。
  • Boolean:传递True/False进去。
  • DECIMAL:定点类型。
  • enum:枚举类型。
  • Date:传递datetime.date()进去。
  • DateTime:传递datetime.datetime()进去。
  • Time:传递datetime.time()进去。
  • String:字符类型,使用时需要指定长度,区别于Text类型。
  • Text:文本类型。
  • LONGTEXT:长文本类型。

query可用参数:

  1. 模型对象。指定查找这个模型中所有的对象。
  2. 模型中的属性。可以指定只查找某个模型的其中几个属性。
  3. 聚合函数。
    • func.count:统计行的数量。
    • func.avg:求平均值。
    • func.max:求最大值。
    • func.min:求最小值。
    • func.sum:求和。

过滤条件:

过滤是数据提取的一个很重要的功能,以下对一些常用的过滤条件进行解释,并且这些过滤条件都是只能通过filter方法实现的:

  1. equals

    query.filter(User.name == 'ed')
  2. not equals:

    query.filter(User.name != 'ed')
  3. like

    query.filter(User.name.like('%ed%'))
  4. in

    query.filter(User.name.in_(['ed','wendy','jack']))
    # 同时,in也可以作用于一个Query
    query.filter(User.name.in_(session.query(User.name).filter(User.name.like('%ed%'))))
  5. not in

    query.filter(~User.name.in_(['ed','wendy','jack']))
  6. is null

    query.filter(User.name==None)
    # 或者是
    query.filter(User.name.is_(None))
  7. is not null:

    query.filter(User.name != None)
    # 或者是
    query.filter(User.name.isnot(None))
  8. and

    from sqlalchemy import and_
    query.filter(and_(User.name=='ed',User.fullname=='Ed Jones'))
    # 或者是传递多个参数
    query.filter(User.name=='ed',User.fullname=='Ed Jones')
    # 或者是通过多次filter操作
    query.filter(User.name=='ed').filter(User.fullname=='Ed Jones')
  9. or

    from sqlalchemy import or_  query.filter(or_(User.name=='ed',User.name=='wendy'))

SQLAlchemy的ORM(3)

查找方法:

介绍完过滤条件后,有一些经常用到的查找数据的方法也需要解释一下:

  1. all():返回一个Python列表(list):

    query = session.query(User).filter(User.name.like('%ed%').order_by(User.id)
    # 输出query的类型
    print type(query)
    > <type 'list'>
    # 调用all方法
    query = query.all()
    # 输出query的类型
    print type(query)
    > <class 'sqlalchemy.orm.query.Query'>
  2. first():返回Query中的第一个值:

    user = session.query(User).first()
    print user
    > <User(name='ed', fullname='Ed Jones', password='f8s7ccs')>
  3. one():查找所有行作为一个结果集,如果结果集中只有一条数据,则会把这条数据提取出来,如果这个结果集少于或者多于一条数据,则会抛出异常。总结一句话:有且只有一条数据的时候才会正常的返回,否则抛出异常:

    # 多于一条数据
    user = query.one()
    > Traceback (most recent call last):
    > ...
    > MultipleResultsFound: Multiple rows were found for one()
    # 少于一条数据
    user = query.filter(User.id == 99).one()
    > Traceback (most recent call last):
    > ...
    > NoResultFound: No row was found for one()
    # 只有一条数据
    > query(User).filter_by(name='ed').one()
  4. one_or_none():跟one()方法类似,但是在结果集中没有数据的时候也不会抛出异常。

  5. scalar():底层调用one()方法,并且如果one()方法没有抛出异常,会返回查询到的第一列的数据:

    session.query(User.name,User.fullname).filter_by(name='ed').scalar()

文本SQL:

SQLAlchemy还提供了使用文本SQL的方式来进行查询,这种方式更加的灵活。而文本SQL要装在一个text()方法中,看以下例子:

from sqlalchemy import text
for user in session.query(User).filter(text("id<244")).order_by(text("id")).all():
print user.name

如果过滤条件比如上例中的244存储在变量中,这时候就可以通过传递参数的形式进行构造:

session.query(User).filter(text("id<:value and name=:name")).params(value=224,name='ed').order_by(User.id)

在文本SQL中的变量前面使用了:来区分,然后使用params方法,指定需要传入进去的参数。另外,使用from_statement方法可以把过滤的函数和条件函数都给去掉,使用纯文本的SQL:

sesseion.query(User).from_statement(text("select * from users where name=:name")).params(name='ed').all()

使用from_statement方法一定要注意,from_statement返回的是一个text里面的查询语句,一定要记得调用all()方法来获取所有的值。

计数(Count):

Query对象有一个非常方便的方法来计算里面装了多少数据:

session.query(User).filter(User.name.like('%ed%')).count()

当然,有时候你想明确的计数,比如要统计users表中有多少个不同的姓名,那么简单粗暴的采用以上count是不行的,因为姓名有可能会重复,但是处于两条不同的数据上,如果在原生数据库中,可以使用distinct关键字,那么在SQLAlchemy中,可以通过func.count()方法来实现:

from sqlalchemy import func
session.query(func.count(User.name),User.name).group_by(User.name).all()
# 输出的结果
> [(1, u'ed'), (1, u'fred'), (1, u'mary'), (1, u'wendy')]

另外,如果想实现select count(*) from users,可以通过以下方式来实现:

session.query(func.count(*)).select_from(User).scalar()

当然,如果指定了要查找的表的字段,可以省略select_from()方法:

session.query(func.count(User.id)).scalar()