使用psycopg2從csv檔案創建Postgresql表

嗨，我正在尋找讓我從 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.Olech\structure_files"

# Loop through each CSV
for filename in glob.glob(csvPath "*.csv"):
# Create a table name
    tablename = filename.replace("W:\Maciej.Olech\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()

我嘗試運行此代碼，但出現此錯誤：

C:\Users\MACIEJ~1.OLE\AppData\Local\Temp/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。有人知道出了什么問題嗎？謝謝你的幫助。

uj5u.com熱心網友回復：

您的代碼存在一些問題。

1. 在 Windows 中，路徑需要\轉義。
2. 你的cur.execute(sqlQueryCreate)和conn.commit()縮進錯誤。sqlQueryCreate = sqlQueryCreate[:-2] 與和同上sqlQueryCreate = ");"
3. 編輯：意識到您的 glob.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() 有效。

標籤：Python PostgreSQL CSV 心理咨询师2

上一篇：Powershell-將列格式化為日期

下一篇：如何在使用閃亮下載處理程式下載的表格頂部插入一行文本？