使用python读取csv文件快速插入数据库的实例_Python

使用python读取csv文件快速插入数据库的实例

2021-03-07 00:45chenKFKevin Python

今天小编就为大家分享一篇使用python读取csv文件快速插入数据库的实例，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧

如下所示：

									# -*- coding:utf-8 -*-

									# auth:ckf

									# date:20170703

									import pandas as pd

									import cStringIO

									import warnings

									from sqlalchemy import create_engine

									import sys

									reload(sys)

									sys.setdefaultencoding('utf8')

									warnings.filterwarnings('ignore')

									engine = create_engine(

									 'postgresql+psycopg2://'数据库连接)

									filename = sys.argv[1]

									tablename = sys.argv[2]

									print '=== csvname is',filename ,'tablename is',tablename,'==='

									print 'read', filename, '...'

									df = pd.read_csv(filename, sep=';')

									print 'read', filename, 'done!'

									print 'lets insert ...'

									output = cStringIO.StringIO()

									# ignore the index

									df.to_csv(output, sep='\t',index = False, header = False)

									output.getvalue()

									# jump to start of stream

									output.seek(0)

									connection = engine.raw_connection()

									cursor = connection.cursor()

									# null value become ''

									cursor.copy_from(output,tablename,null='')

									connection.commit()

									cursor.close()

									print 'done!'