How to insert variable into sqlite database in pyt

Question

My code seems to run fine without any errors but it just creates an empty database file with nothing in it, Cant figure out what i'm doing wrong here.

import sqlite3 as lite
import sys
con = lite.connect('test43.db')

def create_db():
    with con:
        cur = con.cursor()
        cur.execute("DROP TABLE IF EXISTS Contacts")
        cur.execute("CREATE TABLE Contacts (First Name TEXT, Last Name TEXT, Phone TEXT, Email TEXT);")
        cur.execute("INSERT INTO Contacts VALUES (?, ?, ?, ?);", (firstname, lastname, phone, email))
        cur.commit()

#Get user input
print ('Enter a new contact')
print ('')
firstname = input('Enter first name: ')
lastname = input('Enter last name: ')
phone = input('Enter phone number: ')
email = input('Enter Email address: ')

createnewdb = input('Enter 1 to create new db: ')
if createnewdb == 1:
    create_db()
else:
    sys.exit(0)

Answer 1

I found this example for inserting variables to be very helpful.

c.execute("SELECT * FROM {tn} WHERE {idf}={my_id}".\
        format(tn=table_name, cn=column_2, idf=id_column, my_id=some_id))

Here's the link to the tutorial. http://sebastianraschka.com/Articles/2014_sqlite_in_python_tutorial.html

Answer 2

It's not getting to the create_db() method, as the if clause is comparing a string to a number. input() returns a string, so you really should be comparing it to another string..

try the following:

if createnewdb == "1":
    create_db()
else:
    sys.exit(0)

Then, you should call commit() on the connection object, not the cursor.. so change your create_db() method a little here too:

def create_db():
    with con:
        cur = con.cursor()
        cur.execute("DROP TABLE IF EXISTS Contacts")
        cur.execute("CREATE TABLE Contacts (First Name TEXT, Last Name TEXT, Phone TEXT, Email TEXT);")
        cur.execute("INSERT INTO Contacts VALUES (?, ?, ?, ?);", (firstname, lastname, phone, email))

        ## call commit on the connection...
        con.commit()

then it should be working for you!

Answer 3

The reason that your database file is becoming blank isn't because your varbiables aren't inserted into your SQL syntax properly. Its because of what you did here cur.commit(). You're not meant to commit your cursor to save any changes to the database. You're meant to apply this attribute to the variable in which you've connected to your database file. So in your case, it should be con.commit()

Hope this helps :)