GVKun编程网logo

使用Pandas导入多个SQL表（pandas 导入数据库）

14

在本文中，我们将为您详细介绍使用Pandas导入多个SQL表的相关知识，并且为您解答关于pandas导入数据库的疑问，此外，我们还会提供一些关于9-pandas导入导出数据、mysqlsource导入

在本文中，我们将为您详细介绍使用Pandas导入多个SQL表的相关知识，并且为您解答关于pandas 导入数据库的疑问，此外，我们还会提供一些关于9-pandas导入导出数据、mysql source导入多个sql文件和较大文件及在线互转、MySQL高效导入多个.sql文件方法详解、PandaSQL：一个让你能够通过SQL语句进行pandas的操作的python包的有用信息。

本文目录一览：

使用Pandas导入多个SQL表（pandas 导入数据库）

使用Pandas导入多个SQL表（pandas 导入数据库）

我有一个包含多个表的数据库，并且我试图将每个表作为pandas数据框导入。我可以对单个表执行以下操作：

import pandas as pd
import pandas.io.sql as psql
import pypyodbc

conn = pypyodbc.connect("DRIVER={SQL Server};\
                        SERVER=serveraddress;\
                        UID=uid;\
                        PWD=pwd;\
                        DATABASE=db")

df1 = psql.read_frame('SELECT * FROM dbo.table1',conn)

数据库中表的数量将发生变化，并且我希望能够随时将每个表导入其自己的数据框中。如何将所有这些桌子放入大熊猫中？

9-pandas导入导出数据

9-pandas导入导出数据

#encoding:utf8



''''''
数据导入导出处理：
    索引和列名
    缺失值处理
    逐块读取数据
    保存数据到磁盘
    二进制数据
    其他格式:HDF5,Excel,JSON,SQL,NoSQL
''''''

import numpy as np
import pandas as pd

#读取文件
''''''
a,b,c,d,message
1,2,3,4,hello
5,6,7,8,world
9,10,11,12,foo
没有行索引
''''''
print(pd.read_csv(''data/ex1.csv''))
''''''
   a   b   c   d message
0  1   2   3   4   hello
1  5   6   7   8   world
2  9  10  11  12     foo
''''''
#读取数据，分隔符逗号，支持正则
print(pd.read_table(''data/ex1.csv'',sep='',''))
''''''
   a   b   c   d message
0  1   2   3   4   hello
1  5   6   7   8   world
2  9  10  11  12     foo
''''''

#读取数据没有行，列名称
''''''
1,2,3,4,hello
5,6,7,8,world
9,10,11,12,foo
''''''
print(pd.read_csv(''data/ex2.csv''))
#说明没有列名称，并且指定列名称
print(pd.read_csv(''data/ex2.csv'',header=None,names=[''a'',''b'',''c'',''d'',''msg'']))

''''''
   1   2   3   4  hello
0  5   6   7   8  world
1  9  10  11  12    foo
默认把第一行作为列名称

   a   b   c   d    msg
0  1   2   3   4  hello
1  5   6   7   8  world
2  9  10  11  12    foo
''''''
#指定某列名为行索引
print(pd.read_csv(''data/ex2.csv'',header=None,names=[''a'',''b'',''c'',''d'',''msg''],index_col=''msg''))
''''''
       a   b   c   d
msg
hello  1   2   3   4
world  5   6   7   8
foo    9  10  11  12
''''''

#分隔符不规则的
''''''
            A         B         C
aaa -0.264438 -1.026059 -0.619500
bbb  0.927272  0.302904 -0.032399
ccc -0.264273 -0.386314 -0.217601
ddd -0.871858 -0.348382  1.100491
''''''
print(pd.read_csv(''data/ex3.csv''))
''''''
               A         B         C
0  aaa -0.264438 -1.026059 -0.619500
1  bbb  0.927272  0.302904 -0.032399
2  ccc -0.264273 -0.386314 -0.217601
3  ddd -0.871858 -0.348382  1.100491
实际上它把后面的整体都作为数据了
''''''
print(pd.read_table(''data/ex3.csv'',sep=''\s+''))
''''''
            A         B         C
aaa -0.264438 -1.026059 -0.619500
bbb  0.927272  0.302904 -0.032399
ccc -0.264273 -0.386314 -0.217601
ddd -0.871858 -0.348382  1.100491
如果行列不匹配会自动的把相应第一列作为索引
''''''

#缺失数据
''''''
something,a,b,c,d,message
one,1,2,3,4,NA
two,5,6,,8,world
three,9,10,11,12,foo
''''''
print(pd.read_csv(''data/ex5.csv''))
print(pd.read_csv(''data/ex5.csv'',na_values=[''NA'',''NULL'',''foo'']))
''''''
  something  a   b     c   d message
0       one  1   2   3.0   4     NaN
1       two  5   6   NaN   8   world
2     three  9  10  11.0  12     foo
会把Na和空值赋值为Nan进行表示
  something  a   b     c   d message
0       one  1   2   3.0   4     NaN
1       two  5   6   NaN   8   world
2     three  9  10  11.0  12     NaN
指定了缺失值
''''''
#给不同的列取不同的缺失值
print(pd.read_csv(''data/ex5.csv'',na_values={''message'':[''NA'',''NULL'',''foo''],''something'':[''one'',''two'']}))
''''''
  something  a   b     c   d message
0       NaN  1   2   3.0   4     NaN
1       NaN  5   6   NaN   8   world
2     three  9  10  11.0  12     NaN
''''''


#处理比较多的数据方式
''''''
one,two,three,four,key
0.467976300189,-0.0386485396255,-0.295344251987,-1.82472622729,L
-0.358893469543,1.40445260007,0.704964644926,-0.200638304015,B
10000行左右
''''''
#统计key列出现的次数并且输出前10的
#读取10行
print(pd.read_csv(''data/ex6.csv'',nrows=10))
''''''
        one       two     three      four key
0  0.467976 -0.038649 -0.295344 -1.824726   L
1 -0.358893  1.404453  0.704965 -0.200638   B
2 -0.501840  0.659254 -0.421691 -0.057688   G
3  0.204886  1.074134  1.388361 -0.982404   R
4  0.354628 -0.133116  0.283763 -0.837063   Q
5  1.817480  0.742273  0.419395 -2.251035   Q
6 -0.776764  0.935518 -0.332872 -1.875641   U
7 -0.913135  1.530624 -0.572657  0.477252   K
8  0.358480 -0.497572 -0.367016  0.507702   S
9 -1.740877 -1.160417 -1.637830  2.172201   G
''''''
#分块
#每次读取1000行
tr = pd.read_csv(''data/ex6.csv'',chunksize=1000)
print(type(tr))
''''''
<class ''pandas.io.parsers.TextFileReader''>
''''''
#可以同for循环来进行
result = pd.Series()
for chunk in tr:
    result = result.add(chunk[''key''].value_counts(),fill_value=0)
print(result)
''''''
0    151.0
1    146.0
2    152.0
3    162.0
4    171.0
5    157.0
6    166.0
7    164.0
8    162.0
9    150.0
A    320.0
B    302.0
C    286.0
D    320.0
E    368.0
F    335.0
G    308.0
H    330.0
I    327.0
J    337.0
K    334.0
L    346.0
M    338.0
N    306.0
O    343.0
P    324.0
Q    340.0
R    318.0
S    308.0
T    304.0
U    326.0
V    328.0
W    305.0
X    364.0
Y    314.0
Z    288.0
''''''
print(result.sort_values(ascending=False)[:10])
''''''
E    368.0
X    364.0
L    346.0
O    343.0
Q    340.0
M    338.0
J    337.0
F    335.0
K    334.0
H    330.0
这样就得到前10的数据
''''''

#保存

df = pd.read_csv(''data/ex5.csv'')
print(df)
''''''
  something  a   b     c   d message
0       one  1   2   3.0   4     NaN
1       two  5   6   NaN   8   world
2     three  9  10  11.0  12     foo
''''''
df.to_csv(''data/ex5_out.csv'')
''''''
,something,a,b,c,d,message
0,one,1,2,3.0,4,
1,two,5,6,,8,world
2,three,9,10,11.0,12,foo
可以看到它把原来的索引也当成数据写入到文件中了
''''''
df.to_csv(''data/ex5_out.csv'',index=False)
''''''
something,a,b,c,d,message
one,1,2,3.0,4,
two,5,6,,8,world
three,9,10,11.0,12,foo
''''''
#取消列
df.to_csv(''data/ex5_out.csv'',index=False,header=None)

#保存那几列
df.to_csv(''data/ex5_out.csv'',index=False,header=None,columns=[''b'',''c'',''message''])

 

mysql source导入多个sql文件和较大文件及在线互转

mysql source导入多个sql文件和较大文件及在线互转

mysqluse dbtest; mysqlset names utf8; mysqlsource D:/mysql/all.sql; 通过source命令导入多个文件，可以新建一个all.sql文件，里面存放下面的命令 例如： source d:/a1.sql; source d:/a2.sql; 当你运行 mysqlsource D:/mysql/all.sql; 这样就可以在一个s

mysql>use dbtest;
mysql>set names utf8;

mysql>source d:/mysql/all.sql;

通过source命令导入多个文件，可以新建一个all.sql文件，里面存放下面的命令
例如：
source d:/a1.sql;
source d:/a2.sql;
当你运行
mysql>source d:/mysql/all.sql;
这样就可以在一个source命令里面导入多个sql文件了。

我看到还有人尝试用 source *.sql 来执行，这样是不行的（哥也尝试了一下）。
值得注意一点的是，all.sql加载进来的sql文件最好写绝对路径，否则会找不到文件，除非你是在.sql的同级目录底下启用的mysql


这样就可以在一个source命令里面导入多个sql文件了。

但这样会有一个问题，如果有一100个这样的文件我们一个个去写命令肯定很麻烦，下面我百度搜索到一个解决办法

<pre name="code">
登录后复制
<span>新建一个all.sql
vim all.sql
在里面写入：
source 1.sql
source 2.sql
......
source 53.sql
source 54.sql
然后只要
mysql&gt; source all.sql</span>
登录后复制


登录后复制


登录后复制
另一种大文件导入解决办法，这个是用服务器管理权限的用户了
在mysql的安装目录下 找到 my.ini文件 加入以下代码：
interactive_timeout = 120
wait_timeout = 120
max_allowed_packet = 32M
登录后复制
导入sql的命令行如下：





source "路径名"+/mytest_emp_dept.sql
登录后复制



如果两个在线的网站互转数据，解决就更简单了：
mysqldump -uuser -ppwd database | mysql -hip -ppwd database
*user为数据库用户名；pwd为数据库密码；ip为数据库ip；database为数据库名字；另外后面为目标库*






MySQL高效导入多个.sql文件方法详解
MySQL高效导入多个.sql文件方法详解
MySQL有多种方法导入多个.sql文件（里面是sql语句），常用的有两个命令：mysql和source。


但是这两个命令的导入效率差别很大，具体请看最后的比较。


（还有sqlimport和LOAD DATA INFILE等导入方法，不过它们主要用于导入.csv或.xml文件数据，不是.sql文件）


假设我们有一个 users.sql 大文件，为方便我们将其拆分成：user1.sql、user2.sql、user3.sql 三个独立的小sql文件。


1、mysql命令导入




mysql命令导入多个sql文件方法：




$ for SQL in *.sql; do mysql -uroot -p"123456" mydb < $SQL; done




2、source命令导入




source命令需要首先进入MySQL命令行：




$ mysql -uroot -p"123456"




导入多个sql文件需要先创建一个额外的文件，名字随意，这里我们取：all.sql，内容：


source user1.sql

source user2.sql

source user3.sql




注意，这里每行一条，必须以source命令开头。


然后用source命令执行该文件：




mysql > use mydb;
mysql > source /home/gary/all.sql




3、如何提高导入速度？




对于百M级以上文件，如果光这样导入，速度是极其缓慢的，


根据MySQL官方建议，我们有几个措施可以极大提高导入的速度，如下：


对于MyISAM，调整系统参数：bulk_insert_buffer_size（至少单个文件大小的2倍以上）




对于InnoDB，调整系统参数：innodb_log_buffer_size（至少单个文件大小的2倍以上，导入完成后可以改回默认的8M，注意不是innodb_buffer_pool_size。）




除主键外，删除其他索引，导入完成后重建索引。




关闭自动提交：autocommit=0。（请勿用set global autocommit=1;命令来关闭，否则整个MySQL系统都会停止自动commit，innodb log buffer很快就会爆满，5和6项也请仅在会话中有效，正确做法请往下看）




关闭唯一索引检查：unique_checks=0。（关闭了这一项会影响on duplicate key update的效果）




关闭外键检查：foreign_key_checks=0。




insert值写在一条语句内，如：INSERT INTO yourtable VALUES (1,2), (5,5), ...;




有自增列的，设置：innodb_autoinc_lock_mode的值为2,




其中，第1-2、8条在修改my.cnf文件，然后重启MySQL：




bulk_insert_buffer_size=2G;
innodb_log_buffer_size=2G;
innodb_autoinc_lock_mode=2;




第3条用到的命令：




#删除索引
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

＃　添加索引
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)




第４-6条写在.sql中，批量bash脚本如下：




for SQL in *.sql;
do
  echo $SQL;
  sed -i ''1i\SET autocommit=0;\nSET unique_checks=0;\nSET foreign_key_checks=0;'' $SQL
  sed -i ''$a\COMMIT;\nSET autocommit=1;\nSET unique_checks=1;\nSET foreign_key_checks=1;'' $SQL
done




按照以上几个步骤调整后，插入速度必会有大幅度的提高。


4、mysql和source效率比较




在sql文件较小的情况下，source速度比mysql高，


在实际测试导入时，5个合计25M的sql文件，mysql命令的速度比source要快2秒（我自己的测试，不代表普遍的结果），


可以粗略得出，在导入大的sql文件时，建议使用mysql命令。


以上既是MySQL高效导入多个.sql文件的方法，希望能帮助到大家

                            
                            

                                您可能感兴趣的文章:
  • MySQL导入csv、excel或者sql文件的小技巧
  • MYSQL导入导出sql文件简析
  • mysql导入sql文件报错 ERROR 2013 2006 2002
  • MySQL导入导出.sql文件及常用命令小结
  • mysql导入sql文件命令和mysql远程登陆使用详解
  • 在linux中导入sql文件的方法分享(使用命令行转移mysql数据库)
  • mysql source 命令导入大的sql文件的方法
  • mysql5的sql文件导入到mysql4的方法
  • mysql导入sql文件出错的解决方法

                            
PandaSQL：一个让你能够通过SQL语句进行pandas的操作的python包
PandaSQL：一个让你能够通过SQL语句进行pandas的操作的python包
 
 

 
 
Pandas是近年来最好的数据操作库之一。它允许切片、分组、连接和执行任意数据转换。如果你熟练的使用SQL,那么这篇文章将介绍一种更直接、简单的使用Pandas处理大多数数据操作案例。
 
 
 
 
假设你对SQL非常的熟悉，或者你想有更可读的代码。或者您只是想在dataframe上运行一个特殊的SQL查询。或者，也许你来自R，想要一个sqldf的替代品。
 
 
这篇文章将介绍一种在pandas的dataframe中使用SQL的python包，并且使用一个不等链接的查询操作来介绍PandasSQL的使用方法。
 
  
 
不等连接(Non-equi join)
 
 
假设你必须连接两个dataframe。其中一个显示了我们对某些商品进行促销的时间段。第二个是事务Dataframe。我想知道促销活动推动的销售情况，也就是促销期间的销售情况。
 
 
我们可以通过联接项目列以及联接条件(TransactionDt≥StartDt和TransactionDt≤EndDt)来实现这一点。因为现在我们的连接条件也有大于号和小于号，这样的连接称为不等连接。在继续之前，一定要考虑如何在pandas中做这样的事情。
 
 
 
  
 
pandas的解决方案
 
 
那么在pandas身上该怎么做呢?pandas肯定可以解决这个问题，尽管我认为它的可读性不够。
 
 
让我们从生成一些要处理的随机数据开始。
 
 
import pandas as pd
import random
import datetime

def random_dt_bw(start_date,end_date):
    days_between = (end_date - start_date).days
    random_num_days = random.randrange(days_between)
    random_dt = start_date + datetime.timedelta(days=random_num_days)
    return random_dt

def generate_data(n=1000):
    items = [f"i_{x}" for x in range(n)]
    start_dates = [random_dt_bw(datetime.date(2020,1,1),datetime.date(2020,9,1)) for x in range(n)]
    end_dates = [x + datetime.timedelta(days=random.randint(1,10)) for x in start_dates]
    
    offerDf = pd.DataFrame({"Item":items,
                            "StartDt":start_dates,
                            "EndDt":end_dates})
    
    transaction_items = [f"i_{random.randint(0,n)}" for x in range(5*n)]
    transaction_dt = [random_dt_bw(datetime.date(2020,1,1),datetime.date(2020,9,1)) for x in range(5*n)]
    sales_amt = [random.randint(0,1000) for x in range(5*n)]
    
    transactionDf = pd.DataFrame({"Item":transaction_items,"TransactionDt":transaction_dt,"Sales":sales_amt})

    return offerDf,transactionDf
 
 
您不需要担心上面的随机数据生成代码。只要知道我们的随机数据是什么样子就可以了:
 
 
offerDf,transactionDf = generate_data(n=100000)
 
 
 
 
 
 
 
 

 
 
一旦我们有了数据，我们就可以通过合并列项上的数据来进行不等连接，然后根据所需条件进行过滤。
 
 
merged_df = pd.merge(offerDf,transactionDf,on=''Item'')pandas_solution = merged_df[(merged_df[''TransactionDt'']>=merged_df[''StartDt'']) &
          (merged_df[''TransactionDt'']<=merged_df[''EndDt''])]
 
 
结果如下，正如我们所希望的:
 
 
 
  
 
PandaSQL解决方案
 
 
Pandas解决方案很好，可以做我们想做的事情，但是我们也可以使用PandaSQL以一种可读性更强的方式完成同样的事情。
 
  
 
PandaSQL是什么?
 
 
PandaSQL为我们提供了在panda数据数据库上编写SQL的方法。因此，如果您已经编写了一些SQL查询，那么使用pandaSQL可能比将它们转换为panda语法更有意义。为了开始使用PandaSQL，我们简单地安装它:
 
 
pip install -U pandasql
 
 
安装了pandaSQL之后，我们可以通过创建pysqldf函数来使用它，该函数接受一个查询作为输入，并运行该查询来返回一个Pandas DF。不用担心语法，因为跟使用pandas差不多。
 
 
from pandasql import sqldf
pysqldf = lambda q: sqldf(q, globals())
 
 
现在，我们可以使用这个函数在我们的pandas dataframe上运行任何SQL查询。下面是不等连接，我们希望使用可读性更强的SQL格式。
 
 
q = """
    SELECT A.*,B.TransactionDt,B.Sales
        FROM
            offerDf A
        INNER JOIN
            transactionDf B
        ON
            A.Item = B.Item AND
            A.StartDt <= B.TransactionDt AND
            A.EndDt >= B.TransactionDt;
    """
pandaSQL_solution = pysqldf(q)
 
 
结果是一个我们所期望的panda Dataframe。索引已经自动为我们重置了，不像以前那样需要手动操作。
 
 
 
  
 
警告
 
 
虽然PandaSQL函数允许我们在我们的panda数据框架上运行SQL查询，并且在某些情况下是一个非常好的工具，但是它的性能不如纯panda语法。
 
 
 
 
 
 

 
 
当我们用可读性更强的PandaSQL为pandas计时时，我们发现PandaSQL花费的时间大约是原生pandas的10倍。
 
  
 
结论
 
 
虽然PandaSQL库的性能不如本地的panda，但当我们想进行特别分析时，它是对我们的数据分析工具箱的一个很好的补充，而且对于那些更习惯使用SQL查询的人来说。
 
 
想要更深入地了解这篇文章的代码，请访问我的GitHub知识库，在那里你可以找到这篇文章和我所有的文章的代码。
 
 
https://github.com/MLWhiz/data_science_blogs/tree/master/pandasql
 
 

 
 
译者注：我一直在寻找能够使用sql处理pandas的dataframe的解决方案，pandasSQL在这这方面起到了很好的开端，虽然他的性能还不足以在生产环境中使用，但是我们再进行EDA和数据分析等一次性的操作的时候完全可以使用sql替代复杂的pandas的查询语法。所以如果你跟我一样，对SQL非常熟悉，并且厌倦了pandas的复杂语法，pandasSQL是一个很好的解决方案
 
 

 
 
作者：Rahul Agarwal
 
 
deephub翻译组
 
 

 
 

 

 

本文分享自微信公众号 - DeepHub IMBA（deephub-imba）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。
关于使用Pandas导入多个SQL表和pandas 导入数据库的介绍现已完结，谢谢您的耐心阅读，如果想了解更多关于9-pandas导入导出数据、mysql source导入多个sql文件和较大文件及在线互转、MySQL高效导入多个.sql文件方法详解、PandaSQL：一个让你能够通过SQL语句进行pandas的操作的python包的相关知识，请在本站寻找。

		

	

本文标签：
 

		





      

    
    

      
 上一篇MySQL内距离查询（mysql计算距离）   

      
 下一篇替换asp.net中的撇号以防止SQL错误（.net 替换字符串）   

    


    

      
相关文章