How to insert variable into sqlite database in python?

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)

Yea i actually corrected that part but it still doesn't work. — Skilo Skilo
– Skilo Skilo, Commented Nov 4, 2013 at 14:20

EnergyGeek · Accepted Answer · 2014-03-11 18:15:42Z

3

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

answered Mar 11, 2014 at 18:15

EnergyGeek

2711 gold badge4 silver badges14 bronze badges

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

Comments

msturdy · Accepted Answer · 2013-11-04 20:31:16Z

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!

edited Nov 4, 2013 at 20:31

answered Nov 4, 2013 at 20:23

msturdy

10.9k11 gold badges44 silver badges53 bronze badges

2 Comments

Skilo Skilo Over a year ago

Works now, Thanks a lot. It's amazing how 2 very tiny mistakes caused the whole program not to work lol.

msturdy Over a year ago

haha, such is the way with programming. One piece of advice is to put debugging lines that print things in your code. For example just inside the function definition, put a line saying print("create_db() function called") and that way you know where the program is getting to when it writes its output :)

Mashrur Khondokar · Accepted Answer · 2017-08-07 15:26:24Z

0

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 :)

answered Aug 7, 2017 at 15:26

Mashrur Khondokar

13 bronze badges

Collectives™ on Stack Overflow

How to insert variable into sqlite database in python?

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related