如何使用python自动将基于CSV的表创建到postgres中

Question

我是一名新的 Python 程序员，正在尝试使用 Python 脚本将示例 CSV 文件导入我的 Postgres 数据库。

我有一个名为 abstable1 的 CSV 文件，它有 3 个标题：

absid, name, number 我在一个文件夹中有很多这样的文件，我想在 PostgreSQL 中创建一个与所有 CSV 文件同名的表。

这是我试图为一个文件创建一个表以进行测试的代码：

import psycopg2

import csv

import os

#filePath = 'c:\\Python27\\Scripts\\abstable1.csv'

conn = psycopg2.connect("host= hostnamexx dbname=dbnamexx user= usernamexx password= pwdxx")

print("Connecting to Database")

cur = conn.cursor()

#Uncomment to execute the code below to create a table

cur.execute("""CREATE TABLE abs.abstable1(

absid varchar(10) PRIMARY KEY,

name integer,

number integer 

)

 """)

#to copy the csv data into created table

with open('abstable1.csv', 'r') as f:

    next(f)

    cur.copy_from(f, 'abs.abstable1', sep=',')

conn.commit()

conn.close()

这是我得到的错误：

File "c:\Python27\Scripts\testabs.py", line 26, in <module>

    cur.copy_from(f, 'abs.abstable1', sep=',')

psycopg2.errors.QueryCanceled: COPY from stdin failed: error in .read() call: exceptions.ValueError Mixing iteration and read methods would lose data

CONTEXT:  COPY abstable1, line 1

非常感谢任何解决此问题的建议或替代解决方案。

Answer 1

以下是对我有用的方法：import glob此代码自动读取文件夹中的所有 CSV 文件并创建一个与文件同名的表。 尽管我仍在尝试弄清楚如何根据 CSV 中的数据提取特定的数据类型。但就表格创建而言，这对一个文件夹中的所有 CSV 文件来说就像一个魅力。import csvimport psycopg2import osimport globconn = psycopg2.connect("host= hostnamexx dbname=dbnamexx user= usernamexx password=&nbsp;pwdxx")print("Connecting to Database")csvPath = "./TestDataLGA/"# Loop through each CSVfor filename in glob.glob(csvPath+"*.csv"):# Create a table nametablename = filename.replace("./TestDataLGA\\", "").replace(".csv", "")print tablename# Open filefileInput = open(filename, "r")# Extract first line of filefirstLine = fileInput.readline().strip()# Split columns into an array [...]columns = firstLine.split(",")&nbsp; &nbsp; &nbsp;# Build SQL code to drop table if exists and create tablesqlQueryCreate = 'DROP TABLE IF EXISTS '+ tablename + ";\n"sqlQueryCreate += 'CREATE TABLE'+ tablename + "("#some loop or function according to your requiremennt# Define columns for tablefor column in columns:&nbsp; &nbsp; sqlQueryCreate += column + " VARCHAR(64),\n"sqlQueryCreate = sqlQueryCreate[:-2]sqlQueryCreate += ");"cur = conn.cursor()cur.execute(sqlQueryCreate)conn.commit()cur.close()

Answer 2

我使用了 sqlalchemy，一个 JSON 文件作为配置和 glob。import jsonimport globfrom sqlalchemy import create_engine, textdef create_tables_from_files(files_folder, engine, config):&nbsp; &nbsp; try:&nbsp; &nbsp; &nbsp; &nbsp; for filename in glob.glob(files_folder+"\*csv"):&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tablename = filename.replace(files_folder, "").replace('\\', "").replace(".csv", "")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; input_file = open(filename, "r")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; columns = input_file.readline().strip().split(",")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; create_query = 'DROP TABLE IF EXISTS ' + config["staging_schema"] + "." + tablename + "; \n"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; create_query +='CREATE TABLE ' + config["staging_schema"] + "." + tablename + " ( "&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for column in columns:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; create_query += column + " VARCHAR, \n "&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; create_query = create_query[:-4]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; create_query += ");"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; engine.execute(text(create_query).execution_options(autocommit=True))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; print(tablename + " table created")&nbsp; &nbsp; except:&nbsp; &nbsp; &nbsp; &nbsp; print("Error at uploading tables")

Answer 3

我试过你的代码并且工作正常import psycopg2conn = psycopg2.connect("host= 127.0.0.1 dbname=testdb user=postgres password=postgres")print("Connecting to Database")cur = conn.cursor()'''cur.execute("""CREATE TABLE abstable1(absid varchar(10) PRIMARY KEY,name integer,number integer&nbsp;)""")'''with open('lolo.csv', 'r') as f:&nbsp; &nbsp; next(f)&nbsp; &nbsp; cur.copy_from(f, 'abstable1', sep=',', columns=('absid', 'name', 'number'))conn.commit()conn.close()尽管我必须对其进行一些更改才能正常工作：我必须将表命名为abstable1因为使用abs.abstable1 postgres 假定我正在使用模式abs，如果不检查它，也许您在数据库上创建了该模式，也我正在使用 python 3.7 我注意到你正在使用 python 2.7（我认为它不再受支持），这可能会导致问题，因为你说你正在学习我建议你使用 python 3，因为它现在被更多地使用并且你很可能会遇到写在上面的代码，你必须调整你的代码以适应你的 python 2.7