如何编写从CSV文件导入数据并填充表的存储过程?
当前回答
您可以创建一个Bash文件import.sh(您的CSV格式是一个制表符分隔符):
#!/usr/bin/env bash
USER="test"
DB="postgres"
TBALE_NAME="user"
CSV_DIR="$(pwd)/csv"
FILE_NAME="user.txt"
echo $(psql -d $DB -U $USER -c "\copy $TBALE_NAME from '$CSV_DIR/$FILE_NAME' DELIMITER E'\t' csv" 2>&1 |tee /dev/tty)
然后运行这个脚本。
其他回答
您还可以使用pgfutter,或者更好的pgcsv。
这些工具根据CSV标题为您创建表列。
pgfutter有很多bug,我推荐pgcsv。
下面是如何使用pgcsv:
sudo pip install pgcsv
pgcsv --db 'postgresql://localhost/postgres?user=postgres&password=...' my_table my_file.csv
这里的大多数其他解决方案都要求您提前/手动创建表。这在某些情况下可能不实用(例如,如果目标表中有很多列)。因此,下面的方法可能会派上用场。
提供你的CSV文件的路径和列数,你可以使用下面的函数来加载你的表到一个临时表,它将被命名为target_table:
假设第一行具有列名。
create or replace function data.load_csv_file
(
target_table text,
csv_path text,
col_count integer
)
returns void as $$
declare
iter integer; -- dummy integer to iterate columns with
col text; -- variable to keep the column name at each iteration
col_first text; -- first column name, e.g., top left corner on a csv file or spreadsheet
begin
create table temp_table ();
-- add just enough number of columns
for iter in 1..col_count
loop
execute format('alter table temp_table add column col_%s text;', iter);
end loop;
-- copy the data from csv file
execute format('copy temp_table from %L with delimiter '','' quote ''"'' csv ', csv_path);
iter := 1;
col_first := (select col_1 from temp_table limit 1);
-- update the column names based on the first row which has the column names
for col in execute format('select unnest(string_to_array(trim(temp_table::text, ''()''), '','')) from temp_table where col_1 = %L', col_first)
loop
execute format('alter table temp_table rename column col_%s to %s', iter, col);
iter := iter + 1;
end loop;
-- delete the columns row
execute format('delete from temp_table where %s = %L', col_first, col_first);
-- change the temp table name to the name given as parameter, if not blank
if length(target_table) > 0 then
execute format('alter table temp_table rename to %I', target_table);
end if;
end;
$$ language plpgsql;
如何将CSV文件数据导入PostgreSQL表
步骤:
Need to connect a PostgreSQL database in the terminal psql -U postgres -h localhost Need to create a database create database mydb; Need to create a user create user siva with password 'mypass'; Connect with the database \c mydb; Need to create a schema create schema trip; Need to create a table create table trip.test(VendorID int,passenger_count int,trip_distance decimal,RatecodeID int,store_and_fwd_flag varchar,PULocationID int,DOLocationID int,payment_type decimal,fare_amount decimal,extra decimal,mta_tax decimal,tip_amount decimal,tolls_amount int,improvement_surcharge decimal,total_amount ); Import csv file data to postgresql COPY trip.test(VendorID int,passenger_count int,trip_distance decimal,RatecodeID int,store_and_fwd_flag varchar,PULocationID int,DOLocationID int,payment_type decimal,fare_amount decimal,extra decimal,mta_tax decimal,tip_amount decimal,tolls_amount int,improvement_surcharge decimal,total_amount) FROM '/home/Documents/trip.csv' DELIMITER ',' CSV HEADER; Find the given table data select * from trip.test;
如果文件不是很大,可以使用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.')
这些都是很好的答案,但对我来说太复杂了。我只需要在postgreSQL中加载一个CSV文件,而不需要先创建一个表。
这是我的方法:
库
import pandas as pd
import os
import psycopg2 as pg
from sqlalchemy import create_engine
使用环境变量获取密码
password = os.environ.get('PSW')
创建引擎
engine = create_engine(f"postgresql+psycopg2://postgres:{password}@localhost:5432/postgres")
发动机需求分解:
Engine = create_engine(dialect+驱动程序://用户名:password@host:端口/数据库)
分解
Postgresql +psycopg2 =方言+驱动程序 Postgres =用户名 Password =来自环境变量的密码。如果需要,可以输入密码,但不建议输入 Localhost = host 5432 = port Postgres =数据库
获取您的CSV文件路径,我不得不使用编码方面。原因可以在这里找到
data = pd.read_csv(r"path, encoding= 'unicode_escape')
发送数据到Postgress SQL:
data.to_sql('test', engine, if_exists='replace')
分解
Test =你想要的表名 引擎=上面创建的引擎。也就是我们的联系 if_exists =将替换旧表。请谨慎使用。
在一起:
import pandas as pd
import os
import psycopg2 as pg
from sqlalchemy import create_engine
password = os.environ.get('PSW')
engine = create_engine(f"postgresql+psycopg2://postgres:{password}@localhost:5432/postgres")
data = pd.read_csv(r"path, encoding= 'unicode_escape')
data.to_sql('test', engine, if_exists='replace')
推荐文章
- 将varchar字段的类型更改为整数:"不能自动转换为整数类型"
- PostgreSQL可以索引数组列吗?
- PostgreSQL:角色不允许登录
- 用csv模块从csv文件中读取特定的列?
- 如何查找Postgres / PostgreSQL表及其索引的磁盘大小
- 是使用各有一个模式的多个数据库更好,还是使用一个数据库有多个模式更好?
- 如果字符串包含
- 对于PostgreSQL表来说,多大才算太大?
- 将一列的多个结果行连接为一列,按另一列分组
- 使用pgadmin连接到heroku数据库
- 在PostgreSQL中快速发现表的行数
- 更改varchar列的大小为较低的长度
- 如何首次配置postgresql ?
- 数据库性能调优有哪些资源?
- 如何在PostgreSQL中自动更新时间戳