需要帮助运行.execute方法在python (SQLite3库)

Question

我试图采取一个Excel电子表格，把它变成一个数据框架，并从那里创建一个数据库表在SQLite3。下面是我的代码:

---

import numpy as np
import pandas as pd
import sqlite3 as sqlite3

qb = pd.read_excel('d:/2021_College_QB_Week_3.xlsx', sheet_name = '2021_College_QB_Week_3', 
header = 0)
print(qb.head)
db_conn = sqlite3.connect("d:/2021_College_Stats.db")
c = db_conn.cursor()
c.execute(
"""
CREATE TABLE qb(
AP RANK INTEGER,
NAME TEXT NOT NULL,
GAME INTEGER,
CMP INTEGER,
ATT INTEGER,
PCT FLOAT, 
YDS INTEGER,
Y/A FLOAT,
AY/A FLOAT,
TD INTEGER,
INT INTEGER,
RATE FLOAT,
SCHOOL TEXT NON NULL,
YEAR INTEGER,
PRIMARY KEY(SCHOOL),
FOREIGN KEY(NAME)REFERENCES qb(NAME)
);
"""
)

---

我一直得到这个错误:

---

Traceback (most recent call last):
File "C:Userstorou.spyder-py3temp.py", line 19, in <module>
c.execute(
OperationalError: near "/": syntax error

---

有人知道为什么吗?我无论如何也不能。

蒂姆

Answer 1

/是SQLite的保留字符。如果要在列名中使用它，则需要转义:

c.execute("""
CREATE TABLE qb(
...
'Y/A' FLOAT,
'AY/A' FLOAT,
...
);
""")

请注意，您不仅需要在创建表时转义它，而且每次使用它时都需要转义。

Answer 2

SQLite语法不允许在列名中使用斜杠。所以你得把它们去掉

import numpy as np
import pandas as pd
import sqlite3 as sqlite3

qb = pd.read_excel('d:/2021_College_QB_Week_3.xlsx', sheet_name = '2021_College_QB_Week_3', 
header = 0)
print(qb.head)
db_conn = sqlite3.connect("d:/2021_College_Stats.db")
c = db_conn.cursor()
c.execute(
"""
CREATE TABLE qb(
AP RANK INTEGER,
NAME TEXT NOT NULL,
GAME INTEGER,
CMP INTEGER,
ATT INTEGER,
PCT FLOAT, 
YDS INTEGER,
YA FLOAT,
AYA FLOAT,
TD INTEGER,
INT INTEGER,
RATE FLOAT,
SCHOOL TEXT NON NULL,
YEAR INTEGER,
PRIMARY KEY(SCHOOL),
FOREIGN KEY(NAME)REFERENCES qb(NAME)
);
"""
)