CODEWITHSUNDEEP
pythonsqlite
Mike Chan
Mike Chan

Reputation: 805

sqlite3.OperationalError: near "INTEGER": syntax error

I want to learn how to build sqlite with python, after following the tutorial, I keep find this error, I do search on stackoverflow but after checking few times I still can't figure out what happened...

Here's my code:

import sqlite3
import csv

conn = sqlite3.connect("member_2.db")
cursor = conn.cursor()

# build db table
 sql_command="""
    CREATE TABLE member (
    id PRIMARY KEY INTEGER AUTOINCREMENT,
    name TEXT NOT NULL,
    gender CHAR(1),
    score INTEGER,
    team TEXT
    );

    CREATE TABLE pick_history (
      member_id INTEGER,
      pick_time DATE DEFAULT (datatime('now', 'localtime')),
      FOREIGN KEY(member_id) REFERENCES member(id)
    );
 """

cursor.execute(sql_command)

Please help~Thanks!

Upvotes: 0

Views: 3297

Answers (1)

roganjosh
roganjosh

Reputation: 13185

You need to specify the data type of id prior to setting it as a primary key. For example, the following will work:

sql_command="""
    CREATE TABLE member (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    gender CHAR(1),
    score INTEGER,
    team TEXT
    );"""

Thanks to @kindall for linking to the docs making it clear that the column type is specified before the column constraint.

Also, thanks to @PRMoureu for pointing out that once you fix that error you will then get the warning:

Warning: You can only execute one statement at a time.

So split the query up into two statements and create the tables separately.

Upvotes: 2

Related Questions