使用psycopg2从csv文件创建Postgresql表

Question

嗨，我正在寻找解决方案，让我从csv文件创建表。我在另一个论坛上找到了解决方案。这段代码看起来像这样:

import csv
import psycopg2
import os
import glob

conn = psycopg2.connect("host= localhost port=5433  dbname=testDB user= postgres password= ************")
print("Connecting to Database")
cur = conn.cursor()
csvPath = "W:Maciej.Olechstructure_files"
# Loop through each CSV
for filename in glob.glob(csvPath+"*.csv"):
# Create a table name
tablename = filename.replace("W:Maciej.Olechstructure_files", "").replace(".csv", "")
print(tablename)
# Open file
fileInput = open(filename, "r")
# Extract first line of file
firstLine = fileInput.readline().strip()

# Split columns into an array [...]
columns = firstLine.split(",")

# Build SQL code to drop table if exists and create table
sqlQueryCreate = 'DROP TABLE IF EXISTS '+ tablename + ";n"
sqlQueryCreate += 'CREATE TABLE'+ tablename + "("
#some loop or function according to your requiremennt
# Define columns for table
for column in columns:
sqlQueryCreate += column + " VARCHAR(64),n"
sqlQueryCreate = sqlQueryCreate[:-2]
sqlQueryCreate += ");"
cur.execute(sqlQueryCreate)
conn.commit()
cur.close()

我试着运行这段代码，但我得到这个错误:

C:UsersMACIEJ~1.OLEAppDataLocalTemp/ipykernel_5320/1273240169.py in <module>
40         sqlQueryCreate += ");"
41 
---> 42 cur.execute(sqlQueryCreate)
43 conn.commit()
44 cur.close()
NameError: name 'sqlQueryCreate' is not defined

我不明白为什么我有这个错误，因为sqlQueryCreate被定义。有人知道是怎么回事吗?谢谢你的帮助。

Answer 1

你的代码有几个问题。

1. 在Windows中，路径需要转义。
2. 您的cur.execute(sqlQueryCreate)和conn.commit()缩进错误。sqlQueryCreate = sqlQueryCreate[:-2]也是如此和sqlQueryCreate += ");"
编辑:意识到您的global .glob()参数不正确。您的意图:W:\Jan.Bree\structure_files\*.csv，您实际拥有的W:\Jan.Bree\structure_files*.csv

import csv
import psycopg2
import os
import glob

conn = psycopg2.connect("host= localhost port=5433  dbname=testDB user= postgres password= ************")
print("Connecting to Database")
cur = conn.cursor()
csvPath = "W:\Jan.Bree\structure_files"
# Loop through each CSV
for filename in glob.glob(os.path.join(csvPath,"*.csv")):
# Create a table name
tablename = filename.replace("W:\Jan.Bree\structure_files", "").replace(".csv", "")
print(tablename)
# Open file
fileInput = open(filename, "r")
# Extract first line of file
firstLine = fileInput.readline().strip()
# Split columns into an array [...]
columns = firstLine.split(",")
# Build SQL code to drop table if exists and create table
sqlQueryCreate = 'DROP TABLE IF EXISTS '+ tablename + ";n"
sqlQueryCreate += 'CREATE TABLE'+ tablename + "("
#some loop or function according to your requiremennt
# Define columns for table
for column in columns:
sqlQueryCreate += column + " VARCHAR(64),n"
sqlQueryCreate = sqlQueryCreate[:-2]
sqlQueryCreate += ");"
cur.execute(sqlQueryCreate)
conn.commit()
cur.close()

这应该涵盖了问题;但我没有办法测试代码我不用psycopg2。我假设connect()可以工作。

Answer 2

你做了太多的工作，有炼金术库和事情完成迅速和无痛。

import pandas as pd
from sqlalchemy import create_engine
import psycopg2
df = pd.read_csv('W:Maciej.Olechstructure_files.csv', sep=',')

##> {dialect}+{driver}://{user}:{password}@{host}:{port}/{database}
# database = bazakot22
# user = foka2
# password = #gg3Ewampkl
# host = 127.0.0.1
# port= 5432

engine = create_engine('postgresql://foka2:#gg3Ewampkl@127.0.0.1:5432/bazakot22')
df.to_sql("table_name4", engine)