Python SQLite insert statement executing but not inserting any data

Question

Dear Stackoverflow community,

i have the following problem. I am using Python 3.5 and SQLite3 package and I'm trying to insert 20 cities into the city table of my database. The problem ist that after executing the code there is no error message but the entries won't show up in the database.

My Code looks like this

Database.py: this file encapsulates the database and provides the functionality to easily execute queries.

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sqlite3
from sqlite3 import Error

class Database:
    database_connection = 0;

    def __init__(self, path_to_database):
        self.__try_connect__(path_to_database)

    def __try_connect__ (self, path_to_database):

        """ create a database connection to the SQLite database
        specified by the db_file
        :param db_file: database file
        :return: Connection object or None
        """
        try:
            self.database_connection = sqlite3.connect(path_to_database)
        except Error as e:
            print(e)

    def execute_query(self, query):

        database_cursor = self.database_connection.cursor()
        print(database_cursor.execute(query))

    def insert(self, table_name, assoziative_key_value_array):
        print("insert not yet implemented")

DatabaseCreator.py: if you want to try to run my code you can use this class that creates the database for you

from Database import Database

class DatabaseCreator:
    database = 0
    @staticmethod
    def create(path_to_database):
        database = Database(path_to_database)
        DatabaseCreator.delete_old(database)
        DatabaseCreator.create_new(database)

    @staticmethod
    def delete_old(database):
        print("@DatabaseCreator - delete old")
        database.execute_query("DROP TABLE 'Städte'")
        database.execute_query("DROP TABLE 'Vereine'")
        database.execute_query("DROP TABLE 'Präsidenten'")
        database.execute_query("DROP TABLE 'Spieler'")

    @staticmethod
    def create_new(database):
        print("@DatabaseCreator - create new");
        database.execute_query("CREATE TABLE 'Städte' ('Id' INTEGER PRIMARY KEY, 'Name' Varchar NOT NULL);")
        database.execute_query("CREATE TABLE 'Präsidenten' ('Id' INTEGER PRIMARY KEY, 'Name' Varchar NOT NULL);")
        database.execute_query("CREATE TABLE 'Vereine' ('Id' INTEGER PRIMARY KEY, 'Name' Varchar NOT NULL, 'Tabellenplatz' INTEGER NOT NULL, 'PräsidentId' INTEGER NOT NULL, 'StadtId' INTEGER NOT NULL, FOREIGN KEY('StadtId') REFERENCES 'Städte'('Id'), FOREIGN KEY('PräsidentId') REFERENCES 'Präsidenten'('Id') ,CONSTRAINT exclusive_präsidentschaft UNIQUE ('PräsidentId'));")
        database.execute_query("CREATE TABLE 'Spieler' ('Id' INTEGER PRIMARY KEY, 'Name' Varchar NOT NULL, 'Alter' INTEGER NOT NULL, 'Position' Varchar NOT NULL, 'VereinId' INTEGER NOT NULL, FOREIGN KEY('VereinId') REFERENCES 'Vereine'('Id'));")

DatabaseSeeder.py: this class uses the Database class within its static methods and tries to insert the 20 cities into the database.

#!/usr/bin/env python
# -*- coding: utf-8 -*-
from Database import Database


class DatabaseSeeder:

    database = 0

    @staticmethod
    def seed(database):
        DatabaseSeeder.seed_cities(database)
        DatabaseSeeder.seed_presidents(database)
        DatabaseSeeder.seed_clubs(database)
        DatabaseSeeder.seed_players(database)

    @staticmethod
    def seed_cities(database):
        print("@DatabaseSeeder - seed cities")
        cities = [
            "Berlin",
            "Hamburg",
            "München",
            "Köln",
            "Frankfurt am Main",
            "Stuttgart",
            "Düsseldorf",
            "Dortmund",
            "Essen",
            "Leipzig",
            "Bremen",
            "Dresden",
            "Hannover",
            "Nürnberg",
            "Duisburg",
            "Bochum",
            "Wuppertal",
            "Bielefeld",
            "Bonn",
            "Münster"
        ]

        insert_city_sql_template = "INSERT INTO 'Städte' ('{0}') VALUES ('{1}');"
        for city in cities:
            sql_query = insert_city_sql_template.format("Name", city)
            print(sql_query)
            database.execute_query(sql_query)

My template for createing the final sql statement looks like this

insert_city_sql_template = "INSERT INTO 'Städte' ('{0}') VALUES ('{1}');"

It gets formatted into the final sql statement here

sql_query = insert_city_sql_template.format("Name", city)

When I print the sql statement before it is executed it looks like this

INSERT INTO 'Städte' ('Name') VALUES ('Berlin');

Application.py: in my main class I create a new database connection at first and hand this connection over to the DatabaseSeeder class.

from tkinter import *
import sqlite3
from sqlite3 import Error
from Database import Database
from table import Table
from DatabaseCreator import DatabaseCreator
from DatabaseSeeder import DatabaseSeeder

def main():
    database_path = "./database.db"
    DatabaseCreator.create(database_path)
    database = Database(database_path)
    DatabaseSeeder.seed(database)

When I execute my code I don't get any error message but the entries just won't show up in the database.

When I copy the SQL statement from my code and directly execute it on the sqlite cli it works perfectly fine.

I hope someone of you knows what I am missing. Thanks a lot :)

Please no pics. could we get the actual code formatted here? — Paritosh Singh
– Paritosh Singh, Commented Jan 2, 2019 at 15:58
thanks, replacing the images with code is really a good idea — Jan Kreischer
– Jan Kreischer, Commented Jan 2, 2019 at 16:23
Where is your commit statment? Read sqlite3.Connection.commit — stovfl
– stovfl, Commented Jan 2, 2019 at 18:19
Please try to create a minimal reproducible example. For example, if the problem is with inserting data, we don’t need to see functions for deleting. — Bryan Oakley
– Bryan Oakley, Commented Jan 2, 2019 at 19:00

Jan Kreischer · Accepted Answer · 2019-01-02 18:59:32Z

4

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

answered Jan 2, 2019 at 18:59

Jan Kreischer

7713 gold badges8 silver badges23 bronze badges

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

Collectives™ on Stack Overflow

Python SQLite insert statement executing but not inserting any data

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related