0%

Some Notes About SQLAlchemy

近期在收集处理大量的数据,通常是把XML/文本格式的数据转为关系型,结构化的的数据,能够方便导出直接进行分析,尤其是导出能直接被pandas分析的格式(csv/json)。因此需要使用一个在Python下的mysql处理库,既然找了处理库,也干脆一步到位,直接选一个支持ORM的库。于是,就选择了SQLAlchemy。用了一段时间后,写个记录来记下这段时间用到的常用的东西。

SQLAlchemy是Python编程语言下的一款开源软件。提供了SQL工具包及对象关系映射(ORM)工具,主要能满足一下的需求: - 提供对数据库的常用操作:增删改查 - 提供ORM功能,可以让操作的单元变为对象,而不用写sql语句进行硬编码/解码解析数据

数据库连接

SQLalchemy不能支持操作数据库,因而需要安装额外的数据库驱动,对于不同的数据库与驱动,有着不一样的配置URI,总体格式为dbms://user:pwd@host/dbname。这里用的Mysql+mysqlconnector,具体的连接代码如下

1
2
3
4
5
6
7
8
9
10
11
12
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

sql_connection = 'mysql+mysqlconnector://root:pwd@localhost:3306/database'
engine = create_engine(sql_connection)

# 执行sql事务需要使用会话
DBsession = sessionmaker(bind=engine)
session = DBsession()

# 结束前需要关闭session
session.close()

结构定义

在SQLalchemy中,ORM通过定义对象进行数据库model的绑定。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relation, sessionmaker

Base = declarative_base()

class Movie(Base):
__tablename__ = 'movies'

id = Column(Integer, primary_key=True)
title = Column(String(255), nullable=False)
year = Column(Integer)
directed_by = Column(Integer)


def __init__(self, title=None, year=None):
self.title = title
self.year = year

  • ORM的model类通过继承declarative_base进行定义,通过__tablename__绑定到对应的table
  • 每一个Column属性为表中的一列,需要设置类型,类型必须要与数据库中列的类型对应,常用类型有Integer,Float, String(对应varchar),Text
  • 必须设置一个属性/列为primary_key
  • 可以通过Base.metadata.create_all(engine)创建table

数据操作

数据使用ORM进行操作,操作的基本单位为对象(也可以直接使用sql语句,但对于常用操作来说没有必要)。除查询外,对数据库有修改的操作都需要commit事务

插入

1
2
3
m1 = Movie("Star Trek", 2009)
session.add(m1)
session.commit()
  1. 创建待插入的对象,添加相应的数据属性
  2. 添加进session中
  3. 提交session,提交事务

查询

1
2
3
movies = session.query(Movie).all() # 获取所有数据
movie = session.query(Movie).get(movie_id) # 获取单个记录
filter_movie = session.query(Movie).filter(Movie.id == 1).one()
  • 查询中query的参数为查询的table/返回的对象
  • filter等于where语句,可以对查询结果进行进一步筛选,one返回唯一行,all则返回所有行

更新

查询出来的数据,修改属性后重新提交事务即可。

1
2
3
movie = session.query(Movie).get(id)
movie.year = 1999
session.commit()

删除

1
2
3
movie = session.query(Movie).get(id)
session.delete()
session.commit()

some problems

大批量数据插入

在使用的过程中,遇到一次需要插入三十多万条数据到数据库的情况。在此之前,我一直都是插完数据再一起commit的(几千/几百条),因为每次commit需要写入数据库,会比较慢(磁盘IO制约)。 但这次数量过多的话,提交的时候出现错误,提示mysql的连接丢失。估计是写入的时间太多,时间太长超出了其限制,进而断开了连接。于是我选择批量commit,在添加一定数量数据后提交一次(5000/1000/500/100/10),但是没有成功,插入一定量的记录后,提示某一条插入的记录的某一列过长(尽管我那一列是不限长度的Text),显然有问题。 上网搜索批量数据插入的方法,但内容基本都是千篇一律,全部都是转载/翻译StackOverflow上的一篇回答。给出了两种方法 - bulk_save_objects(objects) - add_all()

都没有效果,最终还是采取了逐条添加并提交的方法,虽然慢了点,但是能work。

写这件事只是为了吐槽一下现在用Google搜索一些具体的技术问题是,内容严重同质化的现象,大家都抄来抄去,转来转去。像这一次的搜索,最开始的source是在StackOverflow上的提问,然后有网站翻译成了中文并发布(甚至初创,然后就流传于各个中文网站了,一搜,全是这些内容相同的网页,属实恶心。

佛系求打赏