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

Question

我是一名新的 Python 程序员，正在尝试使用 Python 脚本将示例 CSV 文件导入我的 Postgres 数据库。我有一个名为 abstable1 的 CSV 文件，它有 3 个标题：absid, name, number 我在一个文件夹中有很多这样的文件，我想在 PostgreSQL 中创建一个与所有 CSV 文件同名的表。这是我试图为一个文件创建一个表以进行测试的代码：import psycopg2import csvimport 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 tablecur.execute("""CREATE TABLE abs.abstable1(absid varchar(10) PRIMARY KEY,name integer,number integer ) """)#to copy the csv data into created tablewith 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 dataCONTEXT:  COPY abstable1, line 1非常感谢任何解决此问题的建议或替代解决方案。

Answer 1

以下是对我有用的方法：import glob

此代码自动读取文件夹中的所有 CSV 文件并创建一个与文件同名的表。 尽管我仍在尝试弄清楚如何根据 CSV 中的数据提取特定的数据类型。但就表格创建而言，这对一个文件夹中的所有 CSV 文件来说就像一个魅力。

import csv

import psycopg2

import os

import glob

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

pwdxx")

print("Connecting to Database")

csvPath = "./TestDataLGA/"

# Loop through each CSV

for filename in glob.glob(csvPath+"*.csv"):

# Create a table name

tablename = filename.replace("./TestDataLGA\\", "").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 = conn.cursor()

cur.execute(sqlQueryCreate)

conn.commit()

cur.close()

Answer 2

我使用了 sqlalchemy，一个 JSON 文件作为配置和 glob。

import json

import glob

from sqlalchemy import create_engine, text

def create_tables_from_files(files_folder, engine, config):

    try:

        for filename in glob.glob(files_folder+"\*csv"):

            tablename = filename.replace(files_folder, "").replace('\\', "").replace(".csv", "")

            input_file = open(filename, "r")

            columns = input_file.readline().strip().split(",")

            create_query = 'DROP TABLE IF EXISTS ' + config["staging_schema"] + "." + tablename + "; \n"

            create_query +='CREATE TABLE ' + config["staging_schema"] + "." + tablename + " ( "

            for column in columns:

                create_query += column + " VARCHAR, \n "

            create_query = create_query[:-4]

            create_query += ");"

            engine.execute(text(create_query).execution_options(autocommit=True))

            print(tablename + " table created")

    except:

        print("Error at uploading tables")

Answer 3

我试过你的代码并且工作正常

import psycopg2

conn = 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 

)

""")'''

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

    next(f)

    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