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