如何编写从CSV文件导入数据并填充表的存储过程?
当前回答
我创建了一个小工具,可以超级简单地将csv文件导入PostgreSQL。它只是一个命令,它将创建和填充表,但不幸的是,目前自动创建的所有字段都使用TEXT类型:
csv2pg users.csv -d ";" -H 192.168.99.100 -U postgres -B mydatabase
该工具可以在https://github.com/eduardonunesp/csv2pg上找到
其他回答
这是我个人使用PostgreSQL的经验,我还在等待更快的方法。
Create a table skeleton first if the file is stored locally: drop table if exists ur_table; CREATE TABLE ur_table ( id serial NOT NULL, log_id numeric, proc_code numeric, date timestamp, qty int, name varchar, price money ); COPY ur_table(id, log_id, proc_code, date, qty, name, price) FROM '\path\xxx.csv' DELIMITER ',' CSV HEADER; When the \path\xxx.csv file is on the server, PostgreSQL doesn't have the permission to access the server. You will have to import the .csv file through the pgAdmin built in functionality. Right click the table name and choose import.
如果您仍然有问题,请参考本教程:导入CSV文件到PostgreSQL表
如果文件不是很大,可以使用Pandas库。
在Pandas数据框架上使用iter时要小心。我这样做是为了证明这种可能性。当从数据帧复制到SQL表时,也可以考虑使用pd.Dataframe.to_sql()函数。
假设你已经创建了你想要的表,你可以:
import psycopg2
import pandas as pd
data=pd.read_csv(r'path\to\file.csv', delimiter=' ')
#prepare your data and keep only relevant columns
data.drop(['col2', 'col4','col5'], axis=1, inplace=True)
data.dropna(inplace=True)
print(data.iloc[:3])
conn=psycopg2.connect("dbname=db user=postgres password=password")
cur=conn.cursor()
for index,row in data.iterrows():
cur.execute('''insert into table (col1,col3,col6)
VALUES (%s,%s,%s)''', (row['col1'], row['col3'], row['col6'])
cur.close()
conn.commit()
conn.close()
print('\n db connection closed.')
看看这篇短文吧。
解决方案如下:
创建你的表:
CREATE TABLE zip_codes
(ZIP char(5), LATITUDE double precision, LONGITUDE double precision,
CITY varchar, STATE char(2), COUNTY varchar, ZIP_CLASS varchar);
将数据从CSV文件复制到表中:
COPY zip_codes FROM '/path/to/csv/ZIP_CODES.txt' WITH (FORMAT csv);
您还可以使用pgAdmin,它提供了一个GUI来执行导入。这在这个SO线程中显示。使用pgAdmin的优点是它也适用于远程数据库。
不过,与前面的解决方案非常相似,您需要在数据库中已经有表。每个人都有自己的解决方案,但我通常在Excel中打开CSV文件,复制标题,在不同的工作表上粘贴特殊的换位,在下一列上放置相应的数据类型,然后将其复制并粘贴到文本编辑器中,并使用适当的SQL表创建查询,如下所示:
CREATE TABLE my_table (
/* Paste data from Excel here for example ... */
col_1 bigint,
col_2 bigint,
/* ... */
col_n bigint
)
您还可以使用pgfutter,或者更好的pgcsv。
这些工具根据CSV标题为您创建表列。
pgfutter有很多bug,我推荐pgcsv。
下面是如何使用pgcsv:
sudo pip install pgcsv
pgcsv --db 'postgresql://localhost/postgres?user=postgres&password=...' my_table my_file.csv
推荐文章
- 将一列的多个结果行连接为一列,按另一列分组
- 使用pgadmin连接到heroku数据库
- 在PostgreSQL中快速发现表的行数
- 更改varchar列的大小为较低的长度
- 如何首次配置postgresql ?
- 数据库性能调优有哪些资源?
- 如何在PostgreSQL中自动更新时间戳
- 在Ruby中输出数组到CSV
- 当使用JDBC连接到postgres时,是否可以指定模式?
- 我如何在PHP中输出一个UTF-8 CSV, Excel将正确读取?
- SQL:从时间戳日期减去1天
- PostgreSQL删除所有内容
- 如何加载一个tsv文件到熊猫数据框架?
- 从csv文件创建字典?
- 为什么PostgreSQL要对索引列进行顺序扫描?