python中postgreSQL方法copy_from

系统 237 0
原文链接: https://blog.csdn.net/skye1208/article/details/90264431

1.查询数据库中数据表是否存在,不存在则创建

            
              import psycopg2

conn = psycopg2.connect(host=***, port=***, database=***, user=***, password=***)
cur = conn.cursor()
try:
     cur.execute("select to_regclass(" + "\'" +  table_name + "\'" + ") is not null")
     rows = cur.fetchall()
except Exception as e:
     rows = []
     conn.close()
if rows:
     data = rows
     flag = data[0][0]
     print(flag)   

            
          

flag的值即为查询出来的表是否存在的状态,存在则为True,不存在则为False
利用python在数据库创建表的例子网上很多,在此就不进行赘述了。

  1. executemany()方法批量输入数据到数据库
    data是需要插入的数据,为list类型,3列,数据条数有70多万条
            
              import psycopg2
    
conn = psycopg2.connect(host=***, port=***, database=***, user=***, password=***)
cur = conn.cursor()
sql =  "insert into " + table_name + " values(%s, %s, %s)"
cur.executemany(sql, data)
conn.commit()
conn.close()

            
          

该方法下,70多万条数据插入到数据库需要3.88分钟

  1. datafame的to_sql()插入数据到数据库
            
              from sqlalchemy import create_engine

result = pd.DataFrame(data)
engine = create_engine('postgresql://user:password@host:port/database')
pd.io.sql.to_sql(result, table_name, engine, index = False, if_exists='append')

            
          

该方法下,70多万条数据插入到数据库需要4.42分钟

  1. 强大的copy_from(),是postgresSQ的内置函数
            
              import psycopg2
from sqlalchemy import create_engine
import pandas as pd
import StringIO
from io import BytesIO

data1 = pd.DataFrame(data)
# dataframe类型转换为IO缓冲区中的str类型
output = BytesIO()
data1.to_csv(output, sep='\t', index=False, header=False)
output1 = output.getvalue()
   
conn = psycopg2.connect(host=***, user=***, password=***, database=***)
cur = conn.cursor()
cur.copy_from(StringIO.StringIO(output1), table_name1, null='',columns=columns)
conn.commit()
cur.close()
conn.close()
print('done')

            
          

这儿需要特别记录下,copy_from是默认将\N作为NULL值得,但是to_csv会将None值变为“”字符串,因此需要在copy_from中说明null='',即空字符串就是代表的NULL,找了好久才找到快速解决null值得问题,是中文网站上找到的,确实英文提问英文回答的,应该是两个老外一问一答

在实际使用中又遇到一个问题,查询数据库时,如果字段为int类型,存在空值,python查询出数据后,int类型空值会变成NaN,to_csv时,int类型字段会被修改为float类型的,我的解决方法是在查询sql时将int型字段转换为字符串类型的。

用copy_from()方法,70多万条数据插入到数据库仅仅需要0.06分钟,相比于前两种方法执行效率高太多啦
尝试了多张数据表循环批量插入,之前用executemany()需要15个小时才能插入完成的数据,用copy_from()只需要90分钟左右。相比来说已经很优秀了!

原文地址:https://blog.csdn.net/skye1208/article/details/90264431


更多文章、技术交流、商务合作、联系博主

微信扫码或搜索:z360901061

微信扫一扫加我为好友

QQ号联系: 360901061

您的支持是博主写作最大的动力,如果您喜欢我的文章,感觉我的文章对您有帮助,请请扫描上面二维码支持博主1元、2元、5元等您想捐的金额吧,狠狠点击下面给点支持吧

发表我的评论
最新评论 总共0条评论