使用python读取csv文件快速插入postgres数据库

使用python读取csv文件快速插入postgres数据库 下面为完整代码

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!'