Can`t add two rows in database during one connection

Coding
#1

Do I need to close connection every time I add/delete something in the database table?

Here is the code, adding a second Project object rise error (probably because second time the cursor.lastrowid returns the same id as first):

import sqlite3

DB = 'C:/temp/projects.db'

class Project:
    def __init__(self, project_name):
        self.id = None
        self.name = project_name


def add_project(project):

    cursor.execute("INSERT INTO projects VALUES (:id, :name)",
                   {'id': cursor.lastrowid,
                    'name': project.name})

    connection.commit()

connection = sqlite3.connect(DB)
cursor = connection.cursor()

cursor.execute('''CREATE TABLE IF NOT EXISTS projects (
                id integer primary key autoincrement,
                name text
                )''')
connection.commit()

cursor = connection.cursor()
project = Project('TESTER_1')
add_project(project)
print '1 created'

# connection.close()
# connection = sqlite3.connect(DB)
# cursor = connection.cursor()

project = Project('TESTER_2')
add_project(project)
print '2 created'

If I close connection and create a new one (uncomment lines) the second item is working fine.

#2

ordinarily you should not have to close and reopen like that. I think sqllite defaults to auto-commit as well, so you should not even have to call connection.commit() either (see https://stackoverflow.com/questions/4699605/sqlite3-saving-changes-without-commit-command-in-python/48391535)

1 Like