CSV 파일 데이터를 Postgre로 가져오는 방법SQL 테이블
CSV 파일에서 데이터를 가져오고 테이블을 채우는 저장 프로시저를 작성하려면 어떻게 해야 합니까?
이 짧은 기사를 보세요.
솔루션은 다음과 같이 해석됩니다.
테이블 만들기:
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);
사용 권한이 없는 경우COPY서버에서 ), (DB 서작동수있다습니사), (DB 할에버용서다),있▁(▁(▁you수▁work▁use▁on)를 할 수 있습니다.\copy대신(db 클라이언트에서 작동함).Bozhidar Batsov와 동일한 예 사용:
테이블 만들기:
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' DELIMITER ',' CSV
\copy...는 한 줄로 써야 하며 끝에는 a가 없어야 합니다.
읽을 열을 지정할 수도 있습니다.
\copy zip_codes(ZIP,CITY,STATE) FROM '/path/to/csv/ZIP_CODES.txt' DELIMITER ',' CSV
복사에 대한 설명서를 참조하십시오.
복사와 psql 명령 \copy를 혼동하지 마십시오.\copy는 COPY FROM STDIN 또는 COPY TO STDOUT를 호출한 다음 psql 클라이언트가 액세스할 수 있는 파일로 데이터를 가져오거나 저장합니다.따라서 \copy를 사용할 때 파일 접근성과 접근 권한은 서버가 아닌 클라이언트에 따라 달라집니다.
참고:
ID 열의 경우 COPY FROM 명령은 항상 입력 데이터에 제공된 열 값을 INSERT 옵션 OVERRIDING SYSTEM VALUE와 같이 기록합니다.
이를 위한 빠른 방법 중 하나는 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
대부분의 다른 솔루션에서는 테이블을 미리/수동으로 만들어야 합니다.이 방법은 경우에 따라 실용적이지 않을 수 있습니다(예: 대상 표에 열이 많은 경우).따라서 아래의 접근 방식이 유용할 수 있습니다.
파일의 및 열 " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " "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;
가져오기를 수행하는 GUI를 제공하는 pgAdmin을 사용할 수도 있습니다.이 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
)
COPY table_name FROM 'path/to/data.csv' DELIMITER ',' CSV HEADER;
먼저 테이블 만들기
그런 다음 copy 명령을 사용하여 테이블 세부 정보를 복사합니다.
copy table_name (C1,C2,C3....) from 'path to your CSV file' delimiter ',' csv header;
참고:
- 및 는 " 및순는다의지해다니정됩음에"로 지정됩니다.
C1,C2,C3.. - 그
header옵션은 열 이름을 기준으로 하지 않고 입력에서 한 줄만 건너뜁니다.
Paul이 언급했듯이 Import는 pgAdmin에서 작동합니다.
→ 테이블에서 마우스 오른쪽 버튼을 클릭합니다.
로컬 파일, 형식 및 코딩을 선택합니다.
다음은 독일어 pgAdmin GUI 스크린샷입니다.
DbVisualizer에서도 비슷한 작업을 수행할 수 있습니다(라이센스가 있지만 무료 버전에 대해서는 잘 모르겠습니다).
테이블을 마우스 오른쪽 버튼으로 클릭 → 테이블 데이터 가져오기...
다음 SQL 코드 사용:
copy table_name(atribute1,attribute2,attribute3...)
from 'E:\test.csv' delimiter ',' csv header
header 키워드를 사용하면 DBMS는 CSV 파일에 특성이 있는 헤더가 있음을 알 수 있습니다.
자세한 내용은 Postgre로 CSV 파일 가져오기를 참조하십시오.SQL 테이블.
이것은 Postgre에 대한 개인적인 경험입니다.SQL, 그리고 나는 여전히 더 빠른 방법을 기다리고 있습니다.
파일이 로컬로 저장된 경우 먼저 테이블 골격을 작성합니다.
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;\path\xxx.csv 파일이 서버에 있는 경우 PostgreSQL에 서버에 액세스할 수 있는 권한이 없습니다.pgAdmin 내장 기능을 통해 .csv 파일을 가져와야 합니다.
테이블 이름을 마우스 오른쪽 단추로 클릭하고 가져오기를 선택합니다.
여전히 문제가 있는 경우 다음 자습서를 참조하십시오.Postgre로 CSV 파일 가져오기SQL 테이블
CSV 파일 데이터를 Postgre로 가져오는 방법SQL 테이블
단계:
Postgre 연결 필요터미널의 SQL 데이터베이스
psql -U postgres -h localhost데이터베이스를 작성해야 합니다.
create database mydb;사용자를 생성해야 합니다.
create user siva with password 'mypass';데이터베이스와 연결
\c mydb;스키마를 만들어야 합니다.
create schema trip;테이블을 만들어야 합니다.
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 );postgresql로 csv 파일 데이터 가져오기
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;지정된 테이블 데이터 찾기
select * from trip.test;
또한 pgfutter 또는 더 나은 pgcsv를 사용할 수 있습니다.
이러한 도구는 CSV 헤더를 기반으로 사용자로부터 테이블 열을 생성합니다.
pgfutter는 꽤 버그가 많고, 저는 pgcsv를 추천합니다.
다음은 pgcsv로 수행하는 방법입니다.
sudo pip install pgcsv
pgcsv --db 'postgresql://localhost/postgres?user=postgres&password=...' my_table my_file.csv
Python에서는 자동 Postgre에 이 코드를 사용할 수 있습니다.열 이름으로 SQL 테이블 만들기:
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만 개 이상의 행을 가져올 수 있습니다.
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)
그런 다음 이 스크립트를 실행합니다.
DBeaver Community Edition(dbeaver.io )을 사용하면 데이터베이스에 연결한 다음 업로드할 CSV 파일을 Postgre로 가져올 수 있습니다.SQL 데이터베이스.또한 쿼리를 쉽게 실행하고 데이터를 검색하며 결과 세트를 CSV, JSON, SQL 또는 기타 일반적인 데이터 형식으로 다운로드할 수 있습니다.
MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Hive, Presto 등 널리 사용되는 모든 데이터베이스를 지원하는 SQL 프로그래머, DBA 및 분석가를 위한 FOSS 멀티 플랫폼 데이터베이스 툴입니다.Postgres용 Toad, SQL Server용 Toad 또는 Oracle용 Toad와 경쟁할 수 있는 FOSS입니다.
저는 DBeaver와 관계가 없습니다.가격(무료!)과 모든 기능이 마음에 들지만, 애플리케이션 내에서 그래프와 차트를 직접 생성하는 것만으로 사용자에게 199달러의 연간 구독료를 지불하게 하는 대신 이 DBea/Eclipse 애플리케이션을 더 많이 열고 분석 위젯을 DBea/Eclipse에 쉽게 추가할 수 있기를 바랍니다.Java 코딩 기술이 미숙하여 Eclipse 위젯을 만드는 방법을 다시 배우는 데 몇 주가 걸릴 것 같지 않습니다(다만 DBeaver가 DBea Community Edition에 타사 위젯을 추가하는 기능을 비활성화했다는 사실만 알게 되었습니다).
파일이 그리 크지 않으면 Pandas 라이브러리를 사용할 수 있습니다.
Panda 데이터 프레임 위에서 iter를 사용할 때는 주의해야 합니다.저는 가능성을 보여주기 위해 여기서 이것을 하고 있습니다.pd도 고려할 수 있습니다.데이터 프레임에서 SQL 테이블로 복사할 때 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.')
CSV 파일을 Postgre로 가져오는 세 가지 옵션이 있습니다.SQL: 먼저 명령줄을 통해 복사 명령을 사용합니다.
둘째, pgAdmin 도구의 가져오기/내보내기를 사용합니다.
셋째, Skyvia와 같은 클라우드 솔루션을 사용하여 FTP 소스 또는 Google Drive와 같은 클라우드 스토리지와 같은 온라인 위치에서 CSV 파일을 가져옵니다.
당신은 여기에서 이 모든 것을 설명하는 기사를 확인할 수 있습니다.
테이블을 생성하고 CSV 파일에서 테이블을 생성하는 데 사용되는 필수 열을 지정합니다.
포스트그레스를 열고 로드할 대상 테이블을 마우스 오른쪽 버튼으로 클릭합니다.파일 옵션 섹션에서 가져오기 및 업데이트를 선택합니다.
이제 파일에서 파일 이름을 찾습니다.
CSV 형식 선택
ISO_8859_5로 인코딩
이제 기타 옵션으로 이동합니다.헤더를 확인하고 가져오기를 클릭합니다.
텍스트/parse 다중 줄 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;
는 ▁imports를 가져오는 작은 .csv에 합니다.SQL은 매우 쉽습니다.이것은 명령일 뿐이며 테이블을 작성하고 채울 것이지만, 안타깝게도 현재 자동으로 작성된 모든 필드는 TEXT: 유형을 사용합니다.
csv2pg users.csv -d ";" -H 192.168.99.100 -U postgres -B mydatabase
이 도구는 https://github.com/eduardonunesp/csv2pg 에서 확인할 수 있습니다.
이것들은 훌륭한 답변들이지만 저에게는 너무 복잡합니다.postgre에 CSV 파일을 로드하기만 하면 됩니다.테이블을 먼저 만들지 않고 SQL을 수행합니다.
제 방법은 다음과 같습니다.
도서관
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(create+driver://driver:password@host:포트/디버깅)
브레이크 다운
- postgresql+psycopg2 = 사투리+드라이버
- postgres = 사용자 이름
- password = 내 환경 변수의 암호입니다.필요하지만 권장되지 않는 경우 암호를 입력할 수 있습니다.
- localhost = 호스트
- 5432 = 포트
- 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')
언급URL : https://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgresql-table
'programing' 카테고리의 다른 글
| Android에서 '앱이 설치되지 않음' 오류 (0) | 2023.06.10 |
|---|---|
| 판다에서 dtype('O')은 무엇입니까? (0) | 2023.06.10 |
| 각진 2 - 타사 립 추가 (0) | 2023.06.10 |
| 여러 개의 제출 버튼 장고 양식을 작성하려면 어떻게 해야 합니까? (0) | 2023.06.10 |
| 우선 순위에 따라 status_id인 각 직원에 대해 단일 행 선택 (0) | 2023.06.10 |