sqlite3.OperationalError: near "INTEGER": syntax error

Question

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!

roganjosh · Accepted Answer · 2017-09-19 17:57:18Z

2

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.

edited Sep 19, 2017 at 17:57

answered Sep 19, 2017 at 17:54

roganjosh

13.3k4 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kindall Over a year ago

Yep, column type comes before column constraints. sqlite.org/syntax/column-def.html

roganjosh Over a year ago

@PRMoureu Thanks, was about to add but then switched to edit in the link from kindall :) Joint effort!

Collectives™ on Stack Overflow

sqlite3.OperationalError: near "INTEGER": syntax error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related