Running into unrecognized token error when writing csv file into sqlite3 database

Question

I'm trying to write the contents of a csv file into an sqlite3 database but I'm running into an unrecognized token error while creating the database and defining the schema

# Connect to database
conn = sqlite3.connect('test.db')
# Create cursor
c = conn.cursor()
# Open CSV file
with open('500000 Records.csv', mode='r') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=',')
    line_count = 0
    for row in csv_reader:
        if line_count == 0:
            # Create table
            query = '''CREATE TABLE IF NOT EXISTS Employee({} INT, {} TEXT,
{} TEXT, {} TEXT, {} TEXT, {} TEXT, {} TEXT, {} TEXT, {} TEXT, {} TEXT,
{} TEXT, {} TEXT, {} TEXT, {} TEXT, {} TEXT, {} TEXT, {} TEXT, {} TEXT,
{} TEXT, {} TEXT, {} TEXT, {} TEXT, {} TEXT, {} TEXT, {} TEXT, {} TEXT,
{} TEXT, {} TEXT, {} TEXT)'''.format(*row)
            print(query)
            c.execute(query)

This is the query that is printed when the print(query) line is executed:

CREATE TABLE IF NOT EXISTS Employee(ï»¿Emp ID INT, Name Prefix TEXT,
First Name TEXT, Middle Initial TEXT, Last Name TEXT, Gender TEXT, E Mail TEXT, Father's Name TEXT, Mother's Name TEXT, Mother's Maiden Name TEXT,
Date of Birth TEXT, Time of Birth TEXT, Age in Yrs. TEXT, Weight in Kgs. TEXT, Date of Joining TEXT, Quarter of Joining TEXT, Half of Joining TEXT, Year of Joining TEXT,
Month of Joining TEXT, Month Name of Joining TEXT, Short Month TEXT, Day of Joining TEXT, DOW of Joining TEXT, Short DOW TEXT, Age in Company (Years) TEXT, Salary TEXT,
Last % Hike TEXT, SSN TEXT, Phone No.  TEXT)

This is the error that results from the c.execute(query) line:

Traceback (most recent call last):
  File "C:\Users\User\Google Drive\CSC443\A1\create_database.py", line 21, in <module>
    c.execute(query)
sqlite3.OperationalError: unrecognized token: "'s Maiden Name TEXT,
Date of Birth TEXT, Time of Birth TEXT, Age in Yrs. TEXT, Weight in Kgs. TEXT, Date of Joining TEXT, Quarter of Joining TEXT, Half of Joining TEXT, Year of Joining TEXT,
Month of Joining TEXT, Month Name of Joining TEXT, Short Month TEXT, Day of Joining TEXT, DOW of Joining TEXT, Short DOW TEXT, Age in Company (Years) TEXT, Salary TEXT,
Last % Hike TEXT, SSN TEXT, Phone No.  TEXT)"

sqlite3 is taking issue with the "Mother's Maiden Name" column for some reason and I can't figure it out. It's not the first apostrophe symbol to occur; that would be in the "Father's Name" column.

You have to double quote column names with things like spaces and punctuation in them. But such names are horrible; don't use them. — Shawn, Feb 03 '19 at 00:50

MikeT · Accepted Answer · 2019-02-03T01:15:38.813

Basically you have numerous issues.

First consider removing all the column definitions from the syntax error on e.g. using :-

DROP TABLE IF EXISTS Employee;
CREATE TABLE IF NOT EXISTS Employee(ï»¿Emp ID INT, Name Prefix TEXT,
First Name TEXT, Middle Initial TEXT, Last Name TEXT, Gender TEXT, E Mail TEXT, Father's Name TEXT, Mother'

/*s Name TEXT, Mother's Maiden Name TEXT,
Date of Birth TEXT, Time of Birth TEXT, Age in Yrs. TEXT, Weight in Kgs. TEXT, Date of Joining TEXT, Quarter of Joining TEXT, Half of Joining TEXT, Year of Joining TEXT,
Month of Joining TEXT, Month Name of Joining TEXT, Short Month TEXT, Day of Joining TEXT, DOW of Joining TEXT, Short DOW TEXT, Age in Company (Years) TEXT, Salary TEXT,
Last % Hike TEXT, SSN TEXT, Phone No.  TEXT
*/
)
;

SELECT * FROM Employee;

The resultant table created has the following columns :-

I believe that you would have expected columns such as ï»¿Emp ID, Name Prefix, First name etc.

What is happening is that the text up to the first space is used as the column name the subsequent text is then used for the column definition which due to the flexibility of SQLite can be rather forgiving with the column type.
- see How flexible/restricive are SQLite column types?

Column names (and names in general) cannot have an embedded space unless the name is suitable enclosed.

If you now consider all names enclosed e.g as per :-

DROP TABLE IF EXISTS Employee;
CREATE TABLE IF NOT EXISTS Employee(`ï»¿Emp ID` INT, `Name Prefix` TEXT,
`First Name` TEXT, `Middle Initial` TEXT, `Last Name` TEXT, `Gender` TEXT, `E Mail` TEXT, `Father's Name` TEXT, `Mother's Name` TEXT, 
`Mother's Maiden Name` TEXT,
`Date of Birth` TEXT, `Time of Birth` TEXT, `Age in Yrs.` TEXT, `Weight in Kgs.` TEXT, `Date of Joining` TEXT, `Quarter of Joining` TEXT, `Half of Joining TEXT, Year of Joining TEXT,
Month of Joining` TEXT, `Month Name of Joining` TEXT, `Short Month` TEXT, `Day of Joining` TEXT, `DOW of Joining` TEXT, `Short DOW` TEXT, `Age in Company (Years)` TEXT, `Salary` TEXT,
`Last % Hike TEXT`, `SSN` TEXT, `Phone No.`  TEXT
)
;

SELECT * FROM Employee;

The the result is :-

note only a subset of the columns shown

In short, due to the column names including spaces, you need to enclose the names (identifiers) according to :-

SQL As Understood By SQLite - SQLite Keywords

Of course using such names/identifiers will probably only result in ongoing issues and it is doubtful that many would recommended the use of such conventions.

Running into unrecognized token error when writing csv file into sqlite3 database

1 Answers1