使用SQLAlchemy操作数据库

使用SQLAlchemy操作数据库

​ 今年寒假写了一个CTFd的动态靶机插件,CTFd操作数据库用到了SQLAlchemy,这个库实现python通过面向对象的方式操作数据库,一直觉得不错,最近又要写一些需要操作数据库的小程序,还是很想用SQLAlchemy,这玩意有瘾,太好用了还是

0x01 ORM 对象关系映射

在学习SQLAlchemy之前,首先要了解什么是ORM:

对象关系映射(英语:Object Relational Mapping,简称ORM,或O/RM,或O/R mapping),是一种程序设计技术,用于实现面向对象编程语言里不同类型系统数据之间的转换。从效果上说,它其实是创建了一个可在编程语言里使用的“虚拟对象数据库”。也可以说,ORM将一个具有结构的数据表看做一个类来操作,而储存在数据表中的记录值就是类的对象实例。

​ SQLAlchemy就是python语言实现的ORM工具

0x02 SQLAlchemy的安装与导入

安装:

$ pip install sqlalchemy

SQLAlchemy常用类导入

from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

0x03 创建数据库连接

from sqlalchemy import create_engine
# 初始化数据库连接:
engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')#mysql

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

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

连接sqlite:

以相对路径连接

# sqlite://<nohostname>/<path>
# where <path> is relative:
engine = create_engine('sqlite:///foo.db')
#建议以以下命令连接数据库
engine = create_engine('sqlite:///foo.db?check_same_thread=False', echo=True)
#echo=Ture----echo默认为False,表示不打印执行的SQL语句等较详细的执行信息,改为Ture表示让其打印。
#check_same_thread=False----sqlite默认建立的对象只能让建立该对象的线程使用,而sqlalchemy是多线程的所以我们需要指定check_same_thread=False来让建立的对象任意线程都可使用。否则不时就会报错

以绝对路径形式创建数据库,格式如下:

#Unix/Mac - 4 initial slashes in total
engine = create_engine('sqlite:////absolute/path/to/foo.db')
#Windows
engine = create_engine('sqlite:///C:\\path\\to\\foo.db')
#Windows alternative using raw string
engine = create_engine(r'sqlite:///C:\path\to\foo.db')

sqlite可以创建内存数据库(其他数据库不可以),格式如下:

# format 1
engine = create_engine('sqlite://')
# format 2
engine = create_engine('sqlite:///:memory:', echo=True)

Oracle:

engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')

engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname')

MSSQL:

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

0x04 定义映射基本类

先定义基本的ORM的base类,之后的所有ORM类都要继承该类

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

以一个User表为例:

from sqlalchemy import Column, Integer, String

# 定义映射类User,其继承上一步创建的Base
class User(Base):
    # 指定本类映射到users表
    __tablename__ = 'users'
    # 如果有多个类指向同一张表,那么在后边的类需要把extend_existing设为True,表示在已有列基础上进行扩展
    # 或者换句话说,sqlalchemy允许类是表的子集
    # __table_args__ = {'extend_existing': True}
    # 如果表在同一个数据库服务(datebase)的不同数据库中(schema),可使用schema参数进一步指定数据库
    # __table_args__ = {'schema': 'test_database'}
    
    # 各变量名一定要与表的各字段名一样,因为相同的名字是他们之间的唯一关联关系
    # 从语法上说,各变量类型和表的类型可以不完全一致,如表字段是String(64),但我就定义成String(32)
    # 但为了避免造成不必要的错误,变量的类型和其对应的表的字段的类型还是要相一致
    # sqlalchemy强制要求必须要有主键字段不然会报错,如果要映射一张已存在且没有主键的表,那么可行的做法是将所有字段都设为primary_key=True
    # 不要看随便将一个非主键字段设为primary_key,然后似乎就没报错就能使用了,sqlalchemy在接收到查询结果后还会自己根据主键进行一次去重
    # 指定id映射到id字段; id字段为整型,为主键,自动增长(其实整型主键默认就自动增长)
    id = Column(Integer, primary_key=True, autoincrement=True)
    # 指定name映射到name字段; name字段为字符串类形,
    name = Column(String(20))
    fullname = Column(String(32))
    password = Column(String(32))

    # __repr__方法用于输出该类的对象被print()时输出的字符串,如果不想写可以不写
    def __repr__(self):
        return "<User(name='%s', fullname='%s', password='%s')>" % (
                   self.name, self.fullname, self.password)

0x05创建数据表

# 查看映射对应的表
User.__table__

# 创建数据表。一方面通过engine来连接数据库,另一方面根据哪些类继承了Base来决定创建哪些表
# checkfirst=True,表示创建表前先检查该表是否存在,如同名表已存在则不再创建。其实默认就是True
Base.metadata.create_all(engine, checkfirst=True)

# 上边的写法会在engine对应的数据库中创建所有继承Base的类对应的表,但很多时候很多只是用来则试的或是其他库的
# 此时可以通过tables参数指定方式,指示仅创建哪些表
# Base.metadata.create_all(engine,tables=[Base.metadata.tables['users']],checkfirst=True)
# 在项目中由于model经常在别的文件定义,没主动加载时上边的写法可能写导致报错,可使用下边这种更明确的写法
# User.__table__.create(engine, checkfirst=True)

# 另外我们说这一步的作用是创建表,当我们已经确定表已经在数据库中存在时,我完可以跳过这一步
# 针对已存放有关键数据的表,或大家共用的表,直接不写这创建代码更让人心里踏实

以下例子通过model代码创建数据表:

# 导入:
from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 创建对象的基类:
Base = declarative_base()

# 定义User对象的model代码:
class User(Base):
    # 表的名字:
    __tablename__ = 'user'

    # 表的结构:
    id = Column(String(20), primary_key=True)
    name = Column(String(20))

# 初始化数据库连接:
engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')
# 创建数据表:
Base.metadata.create_all(engine, checkfirst=True)

运行Base.metadata.create_all(engine, checkfirst=True)后sqlalchemy将创建所有继承了Base类的model类所定义的数据表,如果数据表已经存在,则不会进行其他操作

使用sqlacodegen 描述已存在的表

​ 利用sqlacodegen可以将数据库中已存在的表以sqlalchemy语法的形式进行提取model代码

​ 也就是说,我们既可以通过model代码创建数据表,也可以通过已存在的数据表提取model代码来进行数据库操作

安装:

pip install sqlacodegen
# linux应该被安装在/usr/local/bin/sqlacodegen
# mysql+pymysql示例
# 可使用--tables指定要生成model的表,不指定时为所有表都生成model
# 可使用--outfile指定代码输出到的文件,不指定时输出到stdout
# 注意只有当表有主键时sqlacodegen才生成如下的class,不然会使用旧的生成Table()类实例的形式
# 更多说明可使用-h参看
sqlacodegen mysql+pymysql://user:password@localhost/dbname [--tables table_name1,table_name2] [--outfile model.py]

这里不多赘述

0x06 增删查改

我们仍以上面的表为例

from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 创建对象的基类:
Base = declarative_base()

# 定义User对象的model代码:
class User(Base):
    # 表的名字:
    __tablename__ = 'user'

    # 表的结构:
    id = Column(String(20), primary_key=True)
    name = Column(String(20))

# 初始化数据库连接:
engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')

在学习增删查改之前,我们首先要明白一个原理:在ORM 模型中 ,所有数据的是model类的实例,增删查改每个操作针对的都是一个个对象的操作,而不涉及具体数据的集合层面

首先需要创建一个session

from sqlalchemy.orm import sessionmaker

# engine是2.2中创建的连接
Session = sessionmaker(bind=engine)

# 创建Session类实例
session = Session()

#创建User实例
new_user=User(name="Bob")
#将该实例插入到User表
session.add(new_User)
#一次插入多个数据
session.add_all(
    [User(name='wendy'),
    User(name='mary'),
    User(name='fred')]
)
#必须经过commit之后数据库中才会发生更改
session.commit()

不得不说,在sqlalchemy中查的方式与往常使用sql语句相比还是有很大差别的

在sql语句中,使用where column_name=来过滤数据,在sqlalchemy中使用filter_by方法进行查询

our_user = session.query(User).filter_by(name='Bob').first()

print(our_user.name) # >>>Bob

# 只获取指定字段
# 但要注意如果只获取部分字段,那么返回的就是元组而不是对象了
session.query(User.name).filter_by(name='Bob').all()
# like查询
session.query(User).filter(User.name.like("Bob%")).all()
# 正则查询
session.query(User).filter(User.name.op("regexp")("^Bob")).all()
# 统计数量
 session.query(User).filter(User.name.like("Bob%")).count()
# 调用数据库内置函数
# 以count()为例,都是直接func.func_name()这种格式,func_name与数据库内的写法保持一致
from sqlalchemy import func
 session.query(func.count(User3.name)).one()
# 字段名为字符串形式
# column_name = "name"
session.query(User).filter(User3.__table__.columns[column_name].like("ed%")).all()
# 获取执行的sql语句
# 获取记录数的方法有all()/one()/first()等几个方法,如果没加这些方法,得到的只是一个将要执行的sql对象,并没真正提交执行
 from sqlalchemy.dialects import mysql
sql_obj = session.query(User).filter_by(name='ed')
 sql_command = sql_obj.statement.compile(dialect=mysql.dialect(), compile_kwargs={"literal_binds": True})
sql_result = sql_obj.all()

#先查出来
our_user = session.query(User).filter_by(name='Bob').first()
#修改
our_user.name="John"
#提交
session.commit()

# 要删除需要先将记录查出来
del_user = session.query(User).filter_by(name='ed').first()

# 打印一下,确认未删除前记录存在
del_user

# 将ed用户记录删除
session.delete(del_user)

# 确认删除
session.commit()

# 遍历查看,已无ed用户记录
for user in session.query(User):
    print(user)

# 但上边的写法,先查询再删除,相当于给mysql服务端发了两条语句,和我们印象中的sql语句的delete方式不太一样
# 可直接使用下边的写法,传给服务端的就是传统的通过条件查找的删除方式
session.query(User).filter_by(name='ed').first().delete()

直接执行sql语句

sql="SELECT * FROM users;"
session.execute(sql)

最后别忘了关闭会话

session.close()

节省你的资源吧 记得关掉session 这是一个好的习惯

0x07 总之

数据库技术发展到现在 ,还在使用SQL语句来操作数据库是一种很低级的事情了

在写一些小程序小工具的时候,还是很推荐使用ORM来操作数据库的

0x08 附 数据类型表

Object NameDescription
BigInteger一种更大的 int 整数。
Booleanbool数据类型。
Date一种类型 datetime.date() 实例。
DateTime一种类型 datetime.datetime() 实例。
Enum通用枚举类型。
Float表示浮点类型的类型,例如 FLOATREAL .
Integer一种类型 int 整数。
Interval一种类型 datetime.timedelta() 实例。
LargeBinary用于大二进制字节数据的类型。
MatchType引用match运算符的返回类型。
Numeric用于固定精度数字的类型,例如 NUMERICDECIMAL .
PickleType保存使用pickle序列化的python对象。
SchemaType将类型标记为可能需要架构级DDL才能使用。
SmallInteger较小的一种 int 整数。
String所有字符串和字符类型的基。
Text大小可变的字符串类型。
Time一种类型 datetime.time() 实例。
Unicode长度可变的Unicode字符串类型。
UnicodeText无限长的Unicode字符串类型。

添加新评论

我们会加密处理您的邮箱保证您的隐私. 标有星号的为必填信息 *