CODEWITHSUNDEEP
pythonsqlite
sayth
sayth

Reputation: 7048

Python - Sqlite insert tuple without the autoincrement primary key value

I create a table with primary key and autoincrement.

with open('RAND.xml', "rb") as f, sqlite3.connect("race.db") as connection:
    c = connection.cursor()
    c.execute(
        """CREATE TABLE IF NOT EXISTS race(RaceID INTEGER PRIMARY KEY AUTOINCREMENT,R_Number    INT, R_KEY INT,\
        R_NAME TEXT, R_AGE INT, R_DIST TEXT, R_CLASS, M_ID INT)""")

I want to then insert a tuple which of course has 1 less number than the total columns because the first is autoincrement.

 sql_data = tuple(b)
    c.executemany('insert into race values(?,?,?,?,?,?,?)', b)

How do I stop this error.

sqlite3.OperationalError: table race has 8 columns but 7 values were supplied

Upvotes: 1

Views: 2565

Answers (2)

holdenweb
holdenweb

Reputation: 37033

It's extremely bad practice to assume a specific ordering on the columns. Some DBA might come along and modify the table, breaking your SQL statements. Secondly, an autoincrement value will only be used if you don't specify a value for the field in your INSERT statement - if you give a value, that value will be stored in the new row.

If you amend the code to read

c.executemany('''insert into
            race(R_number, R_KEY, R_NAME, R_AGE, R_DIST, R_CLASS, M_ID)
            values(?,?,?,?,?,?,?)''',
            sql_data)

you should find that everything works as expected.

Upvotes: 5

glibdud
glibdud

Reputation: 7840

From the SQLite documentation:

If the column-name list after table-name is omitted then the number of values inserted into each row must be the same as the number of columns in the table.

RaceID is a column in the table, so it is expected to be present when you're doing an INSERT without explicitly naming the columns. You can get the desired behavior (assign RaceID the next autoincrement value) by passing an SQLite NULL value in that column, which in Python is None:

sql_data = tuple((None,) + a for a in b)
c.executemany('insert into race values(?,?,?,?,?,?,?,?)', sql_data)

The above assumes b is a sequence of sequences of parameters for your executemany statement and attempts to prepend None to each sub-sequence. Modify as necessary for your code.

Upvotes: 1

Related Questions