如下所示:
# -*- 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=" ",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!"
这个脚本可以直接运行,将csv文件放在同级目录即可。
csv第一列需要有列名,如果csv里没有列名,需要在代码中添加列名。
代码运行示例:python insert.py csvname tablename
以上这篇使用python读取csv文件快速插入数据库的实例就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持脚本之家。