Need Help Running .execute method in python (SQLite3 library)-CodePudding

I am attempting to take an Excel spreadsheet, turn it into a dataframe, and from there create a database table in SQLite3. Here is my code:

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)
       );
       """
       )

I keep getting this error:

Traceback (most recent call last):

  File "C:\Users\torou\.spyder-py3\temp.py", line 19, in <module>
    c.execute(

OperationalError: near "/": syntax error

Does anyone see why? For the life of me I cannot.

-Tim

CodePudding user response：

SQLite syntax doesn't allow slashes in the names of collums. So you have to remove them.

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)
       );
       """
       )

CodePudding user response：

The / character is a reserved character to SQLite. If you want to use it in a column name, you'll need to escape it:

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

Note that you'll need to not just escape it on creation of the table, but every time you use it as well.