有关此错误的背景信息，请访问：http://sqlalche.me/e/9h9h

Question

我目前是编程新手；因此，问题是我无法将csv文件导入PostgreSQL数据库，下面是我的代码：

import os
import csv

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

engine = create_engine(os.getenv("DATABASE_URL"))
db = scoped_session(sessionmaker(bind=engine))

def main():
    b = open("books.csv")
    reader = csv.reader(b)
    for isbn, title, author, year in reader:
        db.execute("INSERT INTO books1(isbn, title, author, year) VALUES(:isbn, :title, :author, 
         :year)",
                {"isbn":isbn, "title":title, "author":author, "year":year})

    print(f"{isbn} Added to {title} ")
db.commit()


if __name__ == "__main__":
    main()

错误如下所示

[SQL: INSERT INTO books1(isbn, title, author, year) VALUES(%(isbn)s, %(title)s, %(author)s, % 
(year)s)]
[parameters: {'isbn': 'isbn', 'title': 'title', 'author': 'author', 'year': 'year'}]
(Background on this error at: http://sqlalche.me/e/9h9h)

谁能帮我。。。请

Answer 1

尝试将双引号（“）替换为（`）或可能的（“”）

似乎是我遇到的类似问题：

sql_string = "SELECT a.'ID' From table1 a " (throws below error)
vs
sql_string = "SELECT a.`ID` From table1 a " (executes properly)

ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''ID' From table1 a' at line 1")
[SQL: SELECT a.'ID' From table1 a ]

（此错误的背景信息位于：http://sqlalche.me/e/f405）

Answer 2

我想你不再试图找到解决这个问题的方法了

然而，Ksh1618的回答让我找到了我的解决方案

似乎是我创建了此错误：（此错误的背景信息位于：http://sqlalche.me/e/14/9h9h）

当我将数据输入到要求不同数据类型的列中时

例： 当我在下面输入相应的数据时，下面的这些列产生了错误：

Role =        Column(String(32))
Name =        Column(String(32))
Comment =     Column(String(1024))
Context =     Column(String(1024))
Engine =      Column(String(32))
Tokens =      Column(Integer)
Temp =        Column(Float)
Top_P =       Column(Float)
Stop  =       Column(String(32))
Pres_Pen =    Column(Float)
Freq_Pen =    Column(Float)
Echo =        Column(String(32))
N =           Column(Integer)
Stream =      Column(String(32))
Logprobs =    Column(Integer)
Best_Of =     Column(Integer)
Logit_Bias =  Column(Integer)

"Role": "Test_Role", 
"Name": "Test_Name", 
"Comment": "Test_Comment", 
"Context": "message", 
"Engine": "engine", 
"Tokens": 64, 
"Temp": 0.1, 
"Top_P": 0.1, 
"Stop": "[\n]", 
"Pres_Pen": 0.1, 
"Freq_Pen": 0.1, 
"Echo": "string", 
"N": 1, 
"Stream": "string", 
"Logprobs": 1, 
"Best_Of": 1, 
"Logit_Bias": 1

将我的数据更改为：

Role =        Column(String(32))
Name =        Column(String(32))
Comment =     Column(String(32))
Context =     Column(String(32))
Engine =      Column(String(32))
Tokens =      Column(String(32))
Temp =        Column(String(32))
Top_P =       Column(String(32))
Stop  =       Column(String(32))
Pres_Pen =    Column(String(32))
Freq_Pen =    Column(String(32))
Echo =        Column(String(32))
N =           Column(String(32))
Stream =      Column(String(32))
Logprobs =    Column(String(32))
Best_Of =     Column(String(32))
Logit_Bias =  Column(String(32))

"Role": "Test_Role", 
"Name": "Test_Name", 
"Comment": "Test_Comment", 
"Context": "message", 
"Engine": "engine", 
"Tokens": "64", 
"Temp": "0.1", 
"Top_P": "0.1", 
"Stop": "None", 
"Pres_Pen": "0.1", 
"Freq_Pen": "0.1", 
"Echo": "False", 
"N": "1", 
"Stream": "False", 
"Logprobs": "1", 
"Best_Of": "1", 
"Logit_Bias": "{}"

数据已成功输入数据库

我相信这个错误可能意味着其他事情。 但是，它似乎解决了我的问题