CODEWITHSUNDEEP
pythonsqlite
Scalextrix
Scalextrix

Reputation: 531

python sqlite create id INTEGER PRIMARY KEY AUTOINCREMENT

Creating a simple database and have the rows with an id so I can select row values later:

conn = sqlite3.connect("APIlan.db")
c = conn.cursor()
c.execute('''CREATE TABLE IF NOT EXISTS ENERGYLOG (id INTEGER PRIMARY KEY AUTOINCREMENT, totalenergy REAL)''')
c.execute("INSERT INTO ENERGYLOG VALUES (?);", (total_energy,))
conn.commit()       
conn.close()

Error sqlite3.OperationalError: table ENERGYLOG has 2 columns but 1 values were supplied

Second try:

conn = sqlite3.connect("APIlan.db")
c = conn.cursor()
c.execute('''CREATE TABLE IF NOT EXISTS ENERGYLOG (id INTEGER PRIMARY KEY AUTOINCREMENT, totalenergy REAL)''')
c.execute("INSERT INTO ENERGYLOG VALUES (?,?);", (NULL,total_energy,))
conn.commit()       
conn.close()

Error NameError: name 'NULL' is not defined

Without supplying the value for id, how do I get it into the table? Thanks.

Upvotes: 2

Views: 9031

Answers (2)

RChanger
RChanger

Reputation: 110

I have two solutions.

1.Your first try, if you only want to insert the columns you choose, you can follow this syntax:

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN);

so, you can write this:

c.execute("INSERT INTO ENERGYLOG (totalenergy) VALUES (?);", (total_energy,))

2.Your second try, if you want to insert all columns, you can replace 'NULL' to 'None':

c.execute("INSERT INTO ENERGYLOG VALUES (?, ?);", (None, total_energy))

Because python don't know 'NULL'.

In SQL we use 'NULL' and in python we use 'None'.

hope it can help you!

Upvotes: 2

alecxe
alecxe

Reputation: 474211

You should explicitly list what columns you want to insert into:

c.execute("INSERT INTO ENERGYLOG (totalenergy) VALUES (?);", (total_energy,))

As far as parameterizing NULL, you should specify None as the parameter value:

c.execute("INSERT INTO ENERGYLOG VALUES (?, ?);", (None, total_energy))

Or, use NULL and a single parameter:

c.execute("INSERT INTO ENERGYLOG VALUES (NULL, ?);", (total_energy,))

Upvotes: 2

Related Questions