Python / Pandas / SQL table issues

Question

我希望有人能帮助我，我有一个任务要做我的大学课程使用Python和SQlite。

我有两个excel表格，我想做成两个表格和一个左连接的一些财务信息。我已经开始通过导入两张表到熊猫和什么我认为是创建两个表，但如果我是诚实的，我不知道我在哪里出错。电子表格包含的列比我想在SQL数据库中使用的多得多。这有问题吗?

我最终出现错误代码"OperationalError:表Lockedlist没有名为Site refquot;

import pandas as pd
Lockedlist_panda = pd.read_excel (r'Location of Lockedlist_panda')
PO1report_panda = pd.read_excel (r'Location of PO1report_panda')
import sqlite3
conn = sqlite3.connect("data_superstore1.db")
cursor = conn.cursor()
sql='''CREATE TABLE Lockedlist (
NR TEXT,
Programme TEXT NOT NULL,
Sub_Region TEXT NOT NULL,
Site_Type TEXT NOT NULL,
MS13_Actual TEXT NOT NULL,
PRIMARY KEY(NR)
)'''
cursor.execute(sql)
print("Table created successfully........")
#Commit your changes in the database
conn.commit()
#Closing the connection
conn.close()
conn = sqlite3.connect("data_superstore1.db")
cursor = conn.cursor()
sql='''CREATE TABLE PoReportTable (
Tracking_Field TEXT,
VENDOR TEXT NOT NULL,
Short_Text TEXT NOT NULL,
COST INT NOT NULL,
PRIMARY KEY(Tracking_Field)
)'''
cursor.execute(sql)
print("Table created successfully........")
#Commit your changes in the database
conn.commit()
#Closing the connection
conn.close()
conn = sqlite3.connect("data_superstore1.db")
Lockedlist_panda.to_sql('Lockedlist', conn, if_exists='append', index=False)
PO1report_panda.to_sql('PoReportTable', conn, if_exists='append', index=False)
#Commit your changes in the database
conn.commit()
#Closing the connection
conn.close()

Answer 1

实际上pandas会尝试将数据框中的所有列插入到数据库中，因此如果它的列比您在数据库中定义的列多，它会报错缺少列，

尝试将您的数据框限制为您首先想要的列:

Lockedlist_panda = Lockedlist_panda[["NR","Programme","Sub_Region","Site_Type","MS13_Actual" ]]