how to insert null value in sqlite3 with python

Question

I created a table in sqlite3, following is the structure:

cid     name                    type    notnull         pk
0   wheelId                 INTEGER 1       1
1   wheelName           VARCHAR 1       0
2   paraDiam            INTEGER 0       0
3   paraWidth           FLOAT   0       0
4   paraEt                  FLOAT   0       0
5   paraHole            INTEGER 0       0
6   factParaHole            INTEGER 0       0
7   paraCenterhole          FLOAT   0       0
8   wheelColorName          VARCHAR 0       0
9   wheelColorNameShow  TEXT    0       0
10  paraWeight          FLOAT   0       0
11  vpPrice                 INTEGER 0       0
12  ntStock                 INTEGER 0       0
13  wheelURL            VARCHAR 0       0
14  wheelImg            VARCHAR 0       0
15  wheelRank           INTEGER 0       0

and now, I use python to insert a record into the table, and I only want to insert the column value 'wheelName', 'wheelImg' and keep the other columns null, (the first one is wheelId, and I hope it can increment by itself automatically).

I used almost every method I can find on the website:

cursor.execute("""INSERT INTO WHEELS (wheelName,wheelImg) VALUES (%s,%s)""" , (WHEELNAME,WHEELIMG))

Python throws sqlite3.OperationalError: near "%": syntax error

cursor.execute("""INSERT INTO WHEELS(WHEELID,WHEELNAME, PARADIAM, PARAWIDTH, PARAET,PARAHOLE,FACTPARAHOLE,
                        PARACENTERHOLE,WHEELCOLORNAME,WHEELCOLORNAMESHOW,PARAWEIGHT,VPPRICE,NTSTOCK,
                        WHEELURL,WHEELIMG,WHEELRANK)

               VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""" ,
               (WHEELID, WHEELNAME, PARADIAM, PARAWIDTH, PARAET,PARAHOLE,FACTPARAHOLE,
                PARACENTERHOLE,WHEELCOLORNAME,WHEELCOLORNAMESHOW,PARAWEIGHT,VPPRICE,NTSTOCK,
                WHEELURL,WHEELIMG,WHEELRANK))

where I set the other values to None like:

PARADIAM = None
PARAWIDTH = None
PARAET = None
PARAHOLE = None

and python again throws sqlite3.OperationalError: near "%": syntax error.

Martijn Pieters · Accepted Answer · 2013-02-06 03:00:00Z

2

The sqlite3 adapter uses ? for SQL parameters, not %s:

cursor.execute("INSERT INTO WHEELS (wheelName,wheelImg) VALUES (?, ?)", (WHEELNAME,WHEELIMG))

Your schema also restricts the wheelColorNameShow:

"wheelColorNameShow" TEXT check(typeof("wheelColorNameShow") = 'text')

which means it can no longer be NULL. You have to include a value for that column too:

cursor.execute('insert into wheels (wheelName,wheelImg,wheelColorNameShow) VALUES (?, ?, ?)', ('test', 'test', 'test'))

succeeds.

edited Feb 6, 2013 at 3:00

answered Feb 6, 2013 at 2:43

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

10 Comments

staryin Over a year ago

cursor.execute("""INSERT INTO WHEELS VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""" , (WHEELID, WHEELNAME, PARADIAM, PARAWIDTH, PARAET,PARAHOLE,FACTPARAHOLE, PARACENTERHOLE,WHEELCOLORNAME,WHEELCOLORNAMESHOW,PARAWEIGHT,VPPRICE,NTSTOCK, WHEELURL,WHEELIMG,WHEELRANK)) sqlite3.IntegrityError: constraint failed

Mike Corcoran Over a year ago

make sure you surround strings being inserted with single quotes

staryin Over a year ago

sqlite3.IntegrityError: constraint failed

staryin Over a year ago

cursor.execute("INSERT INTO WHEELS (wheelName,wheelImg) VALUES (?, ?)", ("test","test")) sqlite3.IntegrityError: constraint failed

Martijn Pieters Over a year ago

@staryin: SQLLite does not think your primary key column can be omitted, it appears.

|

Collectives™ on Stack Overflow

how to insert null value in sqlite3 with python

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related