CODEWITHSUNDEEP
pythonsqlite
yak
yak

Reputation: 3930

Insert into SQLite file Python - does not work

I have a simple database application in Python with SQLite. I wrote a simple program to create database and insert into some values. However, database is created, but new values are not inserted, and I don't know why:

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys


def CreateTable():
    try:

        connection = lite.connect(':memory:')

        with connection:

            cursor = connection.cursor()
            sql = 'CREATE TABLE IF NOT EXISTS Authors' + '(ID INT PRIMARY KEY NOT NULL, FIRSTNAME TEXT, LASTNAME TEXT, EMAIL TEXT)'
            cursor.execute(sql)

            data = '\n'.join(connection.iterdump())
            with open('authors.sql', 'w') as f:
                f.write(data)

    except lite.Error, e:
        if connection:
            connection.rollback()
    finally:
        if connection:
            cursor.close()
            connection.close()

def Insert(firstname, lastname, email) :
    try:

        connection = lite.connect('authors.sql')

        with connection:

            cursor = connection.cursor()
            sql = "INSERT INTO Authors VALUES (NULL, %s, %s, %s)" % (firstname, lastname, email)
            cursor.execute(sql)

            data = '\n'.join(connection.iterdump())
            with open('authors.sql', 'w') as f:
                f.write(data)

    except lite.Error, e:
        if connection:
            connection.rollback()
    finally:
        if connection:
            cursor.close()
            connection.close()



CreateTable()
Insert('Tibby', 'Molko', '[email protected]')

Upvotes: 0

Views: 2411

Answers (4)

itzMEonTV
itzMEonTV

Reputation: 20339

You didnt commit it.For writing into database, it should be committed.For read (select) operations,not needed.

try:
    with connection:
        cursor = connection.cursor()
        sql = "INSERT INTO Authors VALUES (NULL, ?, ?, ?)"
        cursor.execute(sql, (firstname, lastname, email))
        connection.commit() # or cursor.commit()


finally:
    connection.close()

Upvotes: 1

Martijn Pieters
Martijn Pieters

Reputation: 1121306

You have misunderstood what connection.iterdump() is for. You are creating SQL text, instructions for SQLite to execute again at a later date. It is not the database itself. If all you wanted was to output SQL statements you can just write your SQL statements directly, there is little point in passing it through SQLite first.

You also cannot 'connect' SQLite to the text file with SQL statements; you'd have to load those statements as text and re-play them all. That's not what I think you wanted however.

You can connect to an existing database to insert additional rows. Each time you want to have add data, just connect:

def CreateTable():
    connection = lite.connect('authors.db')

    try:
        with connection as:
            cursor = connection.cursor()
            sql = '''\
                CREATE TABLE IF NOT EXISTS Authors (
                    ID INT PRIMARY KEY NOT NULL,
                    FIRSTNAME TEXT,
                    LASTNAME TEXT,
                    EMAIL TEXT)
            '''
            cursor.execute(sql)

    finally:
        connection.close()


def Insert(firstname, lastname, email) :
    connection = lite.connect('authors.db')

    try:
        with connection:
            cursor = connection.cursor()
            sql = "INSERT INTO Authors VALUES (NULL, ?, ?, ?)"
            cursor.execute(sql, (firstname, lastname, email))

    finally:
        connection.close()

Note that using the connection as a context manager already ensures that the transaction is either committed or rolled back, depending on there being an exception.

On the whole, you want to be informed of exceptions here; if you cannot connect to the database you'd want to know about it. I simplified the connection handling as such. Closing a connection auto-closes any remaining cursors.

Last but far from least, I switched your insertion to using SQL parameters. Never use string interpolation where parameters can be used instead. Using parameters makes it possible for the database to cache statement parse results and most of all prevents SQL injection attacks.

Upvotes: 1

user3650051
user3650051

Reputation: 51

You are not calling commit on your connection. You should also not write to the database file yourself, the database engine is writing to the file.

Try to go through the first few examples in sqlite documentation, it should be clear then.

Upvotes: 2

Daniel
Daniel

Reputation: 42748

You cannot connect to a text file with sql commands. sqlite3.connect expects or creates a binary file.

Upvotes: 1

Related Questions