如何编写从CSV文件导入数据并填充表的存储过程?
当前回答
在Python中,你可以使用这段代码自动创建带有列名的PostgreSQL表:
import pandas, csv
from io import StringIO
from sqlalchemy import create_engine
def psql_insert_copy(table, conn, keys, data_iter):
dbapi_conn = conn.connection
with dbapi_conn.cursor() as cur:
s_buf = StringIO()
writer = csv.writer(s_buf)
writer.writerows(data_iter)
s_buf.seek(0)
columns = ', '.join('"{}"'.format(k) for k in keys)
if table.schema:
table_name = '{}.{}'.format(table.schema, table.name)
else:
table_name = table.name
sql = 'COPY {} ({}) FROM STDIN WITH CSV'.format(table_name, columns)
cur.copy_expert(sql=sql, file=s_buf)
engine = create_engine('postgresql://user:password@localhost:5432/my_db')
df = pandas.read_csv("my.csv")
df.to_sql('my_table', engine, schema='my_schema', method=psql_insert_copy)
它的速度也相对较快。我可以在大约4分钟内导入330多万行。
其他回答
如何将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;
这里的大多数其他解决方案都要求您提前/手动创建表。这在某些情况下可能不实用(例如,如果目标表中有很多列)。因此,下面的方法可能会派上用场。
提供你的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;
您可以创建一个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)
然后运行这个脚本。
一种快速的方法是使用Python Pandas库(0.15或更高版本最好)。这将为您处理创建列的问题——尽管它为数据类型所做的选择可能不是您想要的。如果它不能完全做到你想要的,你总是可以使用生成为模板的“创建表”代码。
这里有一个简单的例子:
import pandas as pd
df = pd.read_csv('mypath.csv')
df.columns = [c.lower() for c in df.columns] # PostgreSQL doesn't like capitals or spaces
from sqlalchemy import create_engine
engine = create_engine('postgresql://username:password@localhost:5432/dbname')
df.to_sql("my_table_name", engine)
下面是一些代码,告诉你如何设置各种选项:
# Set it so the raw SQL output is logged
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
df.to_sql("my_table_name2",
engine,
if_exists="append", # Options are ‘fail’, ‘replace’, ‘append’, default ‘fail’
index = False, # Do not output the index of the dataframe
dtype = {'col1': sqlalchemy.types.NUMERIC,
'col2': sqlalchemy.types.String}) # Datatypes should be SQLAlchemy types
如果你需要一个简单的机制来导入文本/解析多行CSV内容,你可以使用:
CREATE TABLE t -- OR INSERT INTO tab(col_names)
AS
SELECT
t.f[1] AS col1
,t.f[2]::int AS col2
,t.f[3]::date AS col3
,t.f[4] AS col4
FROM (
SELECT regexp_split_to_array(l, ',') AS f
FROM regexp_split_to_table(
$$a,1,2016-01-01,bbb
c,2,2018-01-01,ddd
e,3,2019-01-01,eee$$, '\n') AS l) t;
DBFiddle演示
推荐文章
- 将一列的多个结果行连接为一列,按另一列分组
- 使用pgadmin连接到heroku数据库
- 在PostgreSQL中快速发现表的行数
- 更改varchar列的大小为较低的长度
- 如何首次配置postgresql ?
- 数据库性能调优有哪些资源?
- 如何在PostgreSQL中自动更新时间戳
- 在Ruby中输出数组到CSV
- 当使用JDBC连接到postgres时,是否可以指定模式?
- 我如何在PHP中输出一个UTF-8 CSV, Excel将正确读取?
- SQL:从时间戳日期减去1天
- PostgreSQL删除所有内容
- 如何加载一个tsv文件到熊猫数据框架?
- 从csv文件创建字典?
- 为什么PostgreSQL要对索引列进行顺序扫描?