Pandas直接读取sql脚本的方法
之前有群友反应同事给了他一个几百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
结果:
到此这篇关于Pandas直接读取sql脚本的文章就介绍到这了,更多相关Pandas读取sql脚本内容请搜索猪先飞以前的文章或继续浏览下面的相关文章希望大家以后多多支持猪先飞!
相关文章
- 本文给大家分享C#连接SQL数据库和查询数据功能的操作技巧,本文通过图文并茂的形式给大家介绍的非常详细,需要的朋友参考下吧...2021-05-17
- 这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
- 这篇文章主要介绍了PostgreSQL判断字符串是否包含目标字符串的多种方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-02-23
- 这篇文章主要介绍了PostgreSQL TIMESTAMP类型 时间戳操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-26
- 新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
pandas pd.read_csv()函数中parse_dates()参数的用法说明
这篇文章主要介绍了pandas pd.read_csv()函数中parse_dates()参数的用法说明,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-03-05- 本文为那些对学习 Windows PowerShell 命令行和脚本编写环境感兴趣的系统管理员提供了资源。也请告诉我们本网站如何才能对您更有用处。...2020-06-30
- 这篇文章主要介绍了postgresql 实现多表关联删除操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-02
- 这篇文章主要介绍了Postgresl 如何选择正确的关闭模式,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-18
- 这篇文章主要介绍了node.js如何操作MySQL数据库,帮助大家更好的进行web开发,感兴趣的朋友可以了解下...2020-10-29
深入研究mysql中的varchar和limit(容易被忽略的知识)
为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15- 这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
- 这篇文章主要介绍了postgresql数据添加两个字段联合唯一的操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-04
- 一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
- 这篇文章主要介绍了Shell脚本中让进程休眠的方法,本文讲解的就是sleep的用法,可以实现睡觉若干秒、若干分钟、若干小时,需要的朋友可以参考下...2020-07-11
- 这篇文章主要介绍了PostgreSQL 字符串处理与日期处理操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-01
- 这篇文章主要介绍了Vscode上使用SQL的方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-01-26
- 我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
- 今天小编就为大家分享一篇Pandas实现DataFrame按行求百分数(比例数),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-05-09
- 这几天在centos下装mysql,这里记录一下安装的过程,方便以后查阅Mysql5.5.37安装需要cmake,5.6版本开始都需要cmake来编译,5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15