python-postgresql建表導入csv

衆（小衆）所周知，excel隻能存一百萬條資料，csv檔案隻能顯示一百萬條資料。。。無可避免的需要使用資料庫，而我所知的開源資料庫中，postgresql有個很大的特點，就是對地理資料支援度較高。無可避免的又要用python去操作，那。。。

'''

pm2.5-資料庫

'''

import psycopg2

conn=psycopg2.connect(database="postgres",user="postgres",password="1234",host="127.0.0.1",port="5432")

cur = conn.cursor()

cur.execute("CREATE TABLE mxndata1(data timestamp,point varchar,long double precision,lat double precision,pm25 double precision,\

pm10 double precision,so2 double precision,no2 double precision,co double precision,\

o3 double precision,qy double precision,wd double precision,xdsd double precision,fs double precision,fx double precision);")

conn.commit()

cur.close()

conn.close()

'''

postgres=# create table mxndata1

postgres-# (data timestamp,point varchar,long double precision,lat double precision,pm25 double precision,pm10 double precision,so2 double precision,no2 double precision,co double precision,o3 double precision,qy double precision,wd double precision,xdsd double precision,fs double precision,fx double precision);

'''

import psycopg2

from sqlalchemy import create_engine

import pandas as pd

from io import StringIO

data=pd.read_csv(r'D:/minxinan/wrw/2018/2018.csv',header=None,encoding='gbk')

data1 = pd.DataFrame(data)

output = StringIO()

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

output1 = output.getvalue()

conn=psycopg2.connect(database="postgres",user="postgres",password="1234",host="127.0.0.1",port="5432")

cur = conn.cursor()

#cur.execute("CREATE TABLE mxndata1();")

cur.copy_from(StringIO(output1), 'mxndata1',columns=('data','point','long','lat','pm25','pm10','so2','no2','co','o3','qy','wd','xdsd','fs','fx'))

#cur.copy_expert("""COPY mxndata1 FROM 'D:/minxinan/wrw/2018/2018.csv' WITH (FORMAT CSV)""", StringIO(output1))

conn.commit()

cur.close()

conn.close()

print('done')

打開複制了10多秒，也算很快了