Sqlite Insert Query Not Working with Python

Sqlite insert query not working with python?

You do have to commit after inserting:

cursor.execute("Insert into links (link,id) values (?,?)",(a,b))
conn.commit()

or use the connection as a context manager:

with conn:
    cursor.execute("Insert into links (link,id) values (?,?)", (a, b))

or set autocommit correctly by setting the isolation_level keyword parameter to the connect() method to None:

conn = db.connect('insertlinks.db', isolation_level=None)

See Controlling Transactions.

Sqlite insert not working with python

It's hard to be sure without full details, but I think I can guess the problem.

If you use the INSERT statement without column names, the values must exactly match the columns as given in the schema. You can't skip over any of them.*

The right way to fix this is to just use the column names in your INSERT statement. Something like:

current.execute("INSERT INTO tblExtensionHistory (HostID, ExtensionID) VALUES (?,?)",
                [Hid, Eid])

Now you can skip any columns you want (as long as they're autoincrement, nullable, or otherwise skippable, of course), or provide them in any order you want.

---

For your second problem, you're trying to pass in rows as if they were single values. You can't do that. From your code:

Eid = current.fetchone()

This will return something like:

[3]

And then you try to bind that to the ExtensionID column, which gives you an error.

---

In the future, you may want to try to write and debug the SQL statements in the sqlite3 command-line tool and/or your favorite GUI database manager (there's a simple extension that runs in for Firefox if you don't want anything fancy) and get them right, before you try getting the Python right.

---

* This is not true with all databases. For example, in MSJET/Access, you must skip over autoincrement columns. See the SQLite documentation for how SQLite interprets INSERT with no column names, or similar documentation for other databases.

Python SQLite insert statement executing but not inserting any data

Okay I found a solution thanks to @stovfl. I really was missing the commit statement. Now my execute_query method looks like this with a commit at the end.

def execute_query(self, query):
    try:
        database_cursor = self.database_connection.cursor()
        database_cursor.execute(query)
    except sqlite3.IntegrityError as e:
        print('sqlite error: ', e.args[0])  # column name is not unique
    self.database_connection.commit()

Thanks a lot for your help and always remember to commit your changes :D

Greetings

SQLite insert query does not work properly

Just use the insert with specified fields

insert into customers 
(firstName, lastName, mobile, address, mail, sex, nationalNumber, more) 
values
('first','last','mobile','address','mail','sex','national','more')

---

Related Topics