How to insert variable in sqlite3 table?

This is the place for queries that don't fit in any of the other categories.

How to insert variable in sqlite3 table?

Postby skilo47 » Sun Nov 03, 2013 11:45 pm

This has really been bugging me, My code seems to run fine with no errors but the program just creates and empty .db file with nothing in it.

Code: Select all
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 (?, ?, ?, ?);", (firstname, lastname, phone, email))
       
       
       
#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)
Last edited by stranac on Sun Nov 03, 2013 11:50 pm, edited 1 time in total.
Reason: First post lock.
skilo47
 
Posts: 3
Joined: Sun Nov 03, 2013 11:41 pm

Re: How to insert variable in sqlite3 table?

Postby rrashkin » Mon Nov 04, 2013 2:10 pm

2 things come to mind.
First, I always use a list in the insert, not a tuple as you have.
Second, the insert is happening inside a function where the variables have not been defined. Python always surprises me with how it knows about global variables even if they're not declared but that could still be your problem.
___________________
Bob Rashkin
User avatar
rrashkin
 
Posts: 35
Joined: Tue Feb 12, 2013 4:47 pm

Re: How to insert variable in sqlite3 table?

Postby skilo47 » Mon Nov 04, 2013 4:52 pm

rrashkin wrote:2 things come to mind.
First, I always use a list in the insert, not a tuple as you have.
Second, the insert is happening inside a function where the variables have not been defined. Python always surprises me with how it knows about global variables even if they're not declared but that could still be your problem.


So you think i can't access those variables outside of the function?

I guess i could put all of the code into the function and just call it.
skilo47
 
Posts: 3
Joined: Sun Nov 03, 2013 11:41 pm

Re: How to insert variable in sqlite3 table?

Postby rrashkin » Tue Nov 05, 2013 2:51 pm

...or pass the variables to the function or make them global explicitly.
___________________
Bob Rashkin
User avatar
rrashkin
 
Posts: 35
Joined: Tue Feb 12, 2013 4:47 pm

Re: How to insert variable in sqlite3 table?

Postby Yoriz » Tue Nov 05, 2013 5:40 pm

... and commit the changes to the database.
New Users, Read This
Join the #python-forum IRC channel on irc.freenode.net!
Spam topic disapproval technician
Windows7, Python 2.7.4., WxPython 2.9.5.0., some Python 3.3
User avatar
Yoriz
 
Posts: 781
Joined: Fri Feb 08, 2013 1:35 am
Location: UK

Re: How to insert variable in sqlite3 table?

Postby ochichinyezaboombwa » Wed Nov 06, 2013 4:23 am

1) Your SQL (INSERT) statement is totally incorrect syntactically;
2) You use " " in column names: good luck with that.
3) Finally, with Python, you don't need ";"s at the end of your SQL statements.

The correct code would look like this:
Code: Select all
       ....
    cur.execute("CREATE TABLE Contacts (First_Name TEXT, Last_Name TEXT, Phone TEXT, Email TEXT)")
    cur.execute("INSERT INTO Contacts (First_Name, Last_Name, Phone, Email) VALUES (?, ?, ?, ?)",
                    (firstname, lastname, phone, email))
ochichinyezaboombwa
 
Posts: 200
Joined: Tue Jun 04, 2013 7:53 pm


Return to General Coding Help

Who is online

Users browsing this forum: No registered users and 2 guests