Pandas直接读取sql脚本的方法
作者:小小明(代码实体) 发布时间:2022-08-16 20:16:34
之前有群友反应同事给了他一个几百MB的sql脚本,导入数据库再从数据库读取数据有点慢,想了解下有没有可以直接读取sql脚本到pandas的方法。
解析sql脚本文本文件替换成csv格式并加载
我考虑了一下sql脚本也就只是一个文本文件而已,而且只有几百MB,现代的机器足以把它一次性全部加载到内存中,使用python来处理也不会太慢。
我简单研究了一下sql脚本的导出格式,并根据格式写出了以下sql脚本的读取方法。
注意:该读取方法只针对SQLyog导出的mysql脚本测试,其他数据库可能代码需要根据实际情况微调。
读取方法:
from io import StringIO
import pandas as pd
import re
def read_sql_script_all(sql_file_path, quotechar="'") -> (str, dict):
insert_check = re.compile(r"insert +into +`?(\w+?)`?\(", re.I | re.A)
with open(sql_file_path, encoding="utf-8") as f:
sql_txt = f.read()
end_pos = -1
df_dict = {}
while True:
match_obj = insert_check.search(sql_txt, end_pos+1)
if not match_obj:
break
table_name = match_obj.group(1)
start_pos = match_obj.span()[1]+1
end_pos = sql_txt.find(";", start_pos)
tmp = re.sub(r"\)( values |,)\(", "\n", sql_txt[start_pos:end_pos])
tmp = re.sub(r"[`()]", "", tmp)
df = pd.read_csv(StringIO(tmp), quotechar=quotechar)
dfs = df_dict.setdefault(table_name, [])
dfs.append(df)
for table_name, dfs in df_dict.items():
df_dict[table_name] = pd.concat(dfs)
return df_dict
参数:
sql_file_path:sql脚本的位置
quotechar:脚本中字符串是单引号还是双引号,默认使用单引号解析
返回:
一个字典,键是表名,值是该表对应的数据所组成的datafream对象
下面我测试读取下面这个sql脚本:
其中的表名是index_test
:
df_dict = read_sql_script_all("D:/tmp/test.sql")
df = df_dict['index_test']
df.head(10)
结果:
可以看到能顺利的直接从sql脚本中读取数据生成datafream。
当然上面写的方法是一次性读取整个sql脚本的所有表,结果为一个字典(键为表名,值为datafream)。但大部分时候我们只需要读取sql脚本的某一张表,我们可以改造一下上面的方法:
def read_sql_script_by_tablename(sql_file_path, table_name, quotechar="'") -> (str, dict):
insert_check = re.compile(r"insert +into +`?(\w+?)`?\(", re.I | re.A)
with open(sql_file_path, encoding="utf-8") as f:
sql_txt = f.read()
end_pos = -1
dfs = []
while True:
match_obj = insert_check.search(sql_txt, end_pos+1)
if not match_obj:
break
start_pos = match_obj.span()[1]+1
end_pos = sql_txt.find(";", start_pos)
if table_name != match_obj.group(1):
continue
tmp = re.sub(r"\)( values |,)\(", "\n", sql_txt[start_pos:end_pos])
tmp = re.sub(r"[`()]", "", tmp)
df = pd.read_csv(StringIO(tmp), quotechar=quotechar)
dfs.append(df)
return pd.concat(dfs)
参数:
sql_file_path:sql脚本的位置
table_name:被读取的表名
quotechar:脚本中字符串是单引号还是双引号,默认使用单引号解析
返回:
该表所对应的datafream对象
读取代码:
df = read_sql_script_by_tablename("D:/tmp/test.sql", "index_test")
df.head()
结果:
将sql脚本转换为sqlite格式并通过本地sql连接读取
在写完上面的方法后,我又想到另一种解决思路,就是将sql脚本转换成sqlite语法的sql语句,然后直接加载。各种类型的数据库的sql语句变化较大,下面的方法仅针对SQLyog导出的mysql脚本测试通过,如果是其他的数据库,可能下面的方法仍然需要微调。最好是先自行将sql脚本转换为sqlite语法的sql语句后,再使用我写的方法加载。
加载sql脚本的方法:
from sqlalchemy import create_engine
import pandas as pd
import re
def load_sql2sqlite_conn(sqltxt_path):
create_rule = re.compile("create +table [^;]+;", re.I)
insert_rule = re.compile("insert +into [^;]+;", re.I)
with open(sqltxt_path, encoding="utf-8") as f:
sqltxt = f.read()
engine = create_engine('sqlite:///:memory:')
pos = -1
while True:
match_obj = create_rule.search(sqltxt, pos+1)
if match_obj:
pos = match_obj.span()[1]
sql = match_obj.group(0).replace("AUTO_INCREMENT", "")
sql = re.sub("\).+;", ");", sql)
engine.execute(sql)
match_obj = insert_rule.search(sqltxt, pos+1)
if match_obj:
pos = match_obj.span()[1]
sql = match_obj.group(0)
engine.execute(sql)
else:
break
tablenames = [t[0] for t in engine.execute(
"SELECT tbl_name FROM sqlite_master WHERE type='table';").fetchall()]
return tablenames, engine.connect()
参数:
sql_file_path:sql脚本的位置
返回:
两个元素的元组,第一个元素是表名列表,第二个元素是sqlite内存虚拟连接
测试读取:
tablenames, conn = load_sql2sqlite_conn("D:/tmp/test.sql")
tablename = tablenames[0]
print(tablename)
df = pd.read_sql(f"select * from {tablename};", conn)
df
结果:
来源:https://blog.csdn.net/as604049322/article/details/112853971
猜你喜欢
- 本文为大家分享了购物商城小程序,供大家参考,具体内容如下软件版本:python3.x功能:实现简单购物商城1.允许用户选择购买多
- 本文实例讲述了PHP截取指定图片大小的方法。分享给大家供大家参考。具体分析如下:imagecopyresampled($newim, $im
- 1、使用+加号+加号是将两个list列表相加,返回一个新的列表对象,会消耗额外的内存。#!/usr/bin/env python# -*-
- 问:我最近升级了一个应用程序,使其可以在 SQL Server 2005 上运行。我利用了允许行长度超出 8,060 个字节这项功能,以便用
- 首次安装、运行MySQL时,你可能会遇到一些错误,使MySQL服务器不能启动。本节的目的是帮助你诊断并纠正这些错误。解决服务器问题时你的第一
- 大家在写按钮(input、button)的时候会发现在 IE 下:随着字数的增多,两边的间距也会越来越大。在 WIN 的XP 风格下,当字数
- 重置oracle序列从指定数字开始 代码如下:declare n number(10); v_startnum number(10):=1
- 代码如下:--程序员们在编写一个雇员报表,他们需要得到每个雇员当前及历史工资状态的信息, --以便生成报表。报表需要显示每个人的晋升日期和工
- Python 操作文件时,我们一般要先判断指定的文件或目录是否存在,不然容易产生异常。1.文件# 是否存在import osos.path.
- 如何提高SQL Server数据库的性能,该从哪里入手呢?笔者认为,该遵循从外到内的顺序,来改善数据库的运行性能。如下图: 第一层
- 八种获取当前日期的js代码,第一步:把如下代码加入<head>区域中:<SCRIPT language=java
- python——pip install xxx报错SyntaxError: invalid syntax在安装好python后,进入pyth
- 要判断自定义对象的类型,用__class__方法,或者用isinstance(object, class-or-type-or-tuple)
- 如何实现优惠打折? 代码及说明见下:<%@ LANG
- UTC时间转换,最终得到的都是UTC时间。简单来说就是:时间戳(timestamp) 转换-> UTC显示时间(datetime),使
- 【原文地址】 Tip/Trick: Supporting Full Screen Mode with Silverlight 【原文发表日期
- 1.partial首先是partial函数,它可以重新绑定函数的可选参数,生成一个callable的partial对象:>>&g
- #coding=utf-8#对话框import sysfrom PyQt4 import QtGui, QtCoreclass Window
- 使用python来登录asp网站和登录其他网站差不多,只是因为asp页面在每次请求的时候都要带上viewstate,因此使用python来登
- #!/usr/bin/env pythonimport sys,timefrom socket import socketdef read_