使用Pandas和psycopg2在PostgreSQL创建表时出现语法错误

问题重现 #

执行CREATE TABLE语句时触发语法错误：

SyntaxError                               Traceback (most recent call last)
Cell In[67], line 9
7 sql = "CREATE TABLE linux (Distribution, {})".format(', '.join(column_names))
8 # Execute the statement
----> 9 cur.execute(sql)
11 # Commit the changes
12 engine.commit()
SyntaxError: syntax error at end of input
LINE 1: ...tion_Commitment, Forked_From, Target_Audience, Cost, Status)

用户原代码：

import pandas as pd
import psycopg2
engine = psycopg2.connect(dbname="pandas", user="postgres", password="root", host="localhost")

cur = engine.cursor()
# Define the table
column_names = [
                "Founder", 
                "Maintainer", 
                "Initial_Release_Year", 
                "Current_Stable_Version", 
                "Security_Updates", 
                "Release_Date", 
                "System_Distribution_Commitment", 
                "Forked_From", 
                "Target_Audience", 
                "Cost", 
                "Status"
               ]
sql = "CREATE TABLE linux (Distribution, {})".format(', '.join(column_names))
# Execute the statement
cur.execute(sql)

# Commit the changes
engine.commit()

# Close the cursor and connection
cur.close()
engine.close()

错误原因 #

PostgreSQL的CREATE TABLE语法要求每个列必须明确指定数据类型，原代码只提供了列名，缺少数据类型定义，导致数据库无法解析语句。

修改后的代码 #

将列名列表替换为包含列名和对应数据类型的定义，示例如下（可根据实际数据调整数据类型）：

import pandas as pd
import psycopg2
engine = psycopg2.connect(dbname="pandas", user="postgres", password="root", host="localhost")

cur = engine.cursor()
# 定义列名及对应数据类型
column_definitions = [
    "Distribution VARCHAR(100) PRIMARY KEY",  # 设为主键，可根据需求调整
    "Founder VARCHAR(255)",
    "Maintainer VARCHAR(255)",
    "Initial_Release_Year INT",
    "Current_Stable_Version VARCHAR(50)",
    "Security_Updates VARCHAR(100)",
    "Release_Date DATE",
    "System_Distribution_Commitment TEXT",
    "Forked_From VARCHAR(100)",
    "Target_Audience TEXT",
    "Cost VARCHAR(50)",
    "Status VARCHAR(50)"
]
# 拼接CREATE TABLE语句
sql = "CREATE TABLE linux ({})".format(', '.join(column_definitions))
# 执行语句
cur.execute(sql)

# 提交更改
engine.commit()

# 关闭游标和连接
cur.close()
engine.close()

关键修改点 #

• 为每个列添加了合适的数据类型（如VARCHAR、INT、DATE、TEXT），匹配数据的实际存储需求
• 给Distribution列添加了PRIMARY KEY约束，确保该列值唯一，可根据业务需求调整或移除

内容的提问来源于stack exchange，提问作者Aashish Thapa Magar