专栏首页凹凸玩数据Pandas直接读取sql脚本

Pandas直接读取sql脚本

小小明:「凹凸数据」专栏作者,Pandas数据处理高手,致力于帮助无数数据从业者解决数据处理难题。

之前有群友反应同事给了他一个几百MB的sql脚本,导入数据库再从数据库读取数据有点慢,想了解下有没有可以直接读取sql脚本到pandas的方法。

01

解析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()

结果:

02

将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

结果:

欢迎大家在下方留言,分享你的学习心得和体会。

本文分享自微信公众号 - 凹凸数据(alltodata),作者:小小明

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-01-27

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 原来Python自带了数据库,用起来真方便!

    Python作为数据科学主流语言,被广泛用于数据读存、处理、分析、建模,可以说是无所不能。

    朱小五
  • 干货 | 利用Python操作mysql数据库

    本文主要讲解如何利用python中的pymysql库来对mysql数据库进行操作。

    朱小五
  • 分享十张表的数据!大家一起玩!

    最近有一些朋友留言表示对python不太熟悉,也不太会使用爬虫,但是对文中的数据很感兴趣。问小五能不能分享一下这些数据。

    朱小五
  • pl sql 查看历史执行过的sql记录

    现在越来越多人用plsql 查询和执行sql,因为该工具很方便,不仅可以执行sql、以及命令窗口,但是呢,有时候我们执行完sql,可能忘记保存或者当时觉得可能不...

    小小鱼儿小小林
  • Oracle AWR 阙值影响历史执行计划

          最近有网友提到为什么在dba_hist_sql_plan中无法查看到sql语句的历史执行计划,对于这个问题是由于缺省情况下,Oracle 设定的阙值...

    Leshami
  • Mysql学习笔记(三) - Sql中的安全问题

    很多时候开发人员只关系系统的功能的实现,很多系统开发人员甚至sql的安全全然不知,那么在开发中的sql具体有哪些注意点?这里我们就跟随笔者一起看看sql注入和相...

    写一点笔记
  • python实现一次性封装多条sql语句(begin end)

    因为业务需求,优化模型运行时间。考虑到sql语句每一次执行都要建立连接,查询,获取数据耗时过多。就想到将sql一起提交上去运行,能够节省很多时间。原本1.6-2...

    砸漏
  • Python sql注入 过滤字符串的非法字符实例

    以上这篇Python sql注入 过滤字符串的非法字符实例就是小编分享给大家的全部内容了,希望能给大家一个参考。

    砸漏
  • 从python读取sql的实例方法

    以上就是从python读取sql的实例方法的详细内容,更多关于如何从python读取sql的资料请关注ZaLou.Cn其它相关文章!

    砸漏
  • 达梦数据库:第三章:报错有歧义的列名[PRIVILEGE_NAME]

    java_wxid

扫码关注云+社区

领取腾讯云代金券

,,