Python Form/Prompt for SQLite input

Question

In an effort to learn SQL I've been using it at work. I keep a list of every account I work on and the information I gather while working on that particular account. My database has two tables that I update. One is the customer's information and it always gets updated with each new account and the other is an institution table that gets updated sometimes but not every time. These two tables are relational.

As I said, I am new to SQL and have been using the command line to update these tables and it's getting annoying. What I thought would be an easy solution is to run a series of Python prompts that queries the user for each column and then executes the INSERT command at the end of the prompt. It would then ask if I wanted to create an entry for the second table (since I don't always add to this one).

These are my tables.

> create table stu_info (
> id Integer,
> name text,
> CEEB integer,
> degree text,
> terms integer,
> comm text
> );
> create table coll_info (
> CEEB integer,
> name text,
> Accred text,
> Hours text,
> comm text
> );

In Python I figure it'd be easy to just use raw_input() and add an int() around it when required. Then use a loop so that after adding each new row to the database it starts over until I'm done for the day. My problem is I cannot figure out how to execute sqlite commands through Python and I can't figure out how to access the database.

Whenever I run

import sqlite3

conn = sqlite3.connect(stu.db)
c = conn.cursor()
    enter code here

I get a NameError: name 'stu' is not defined

Any help? I imagine this is easy and my Google-fu is bad and inexperience has led even good search results to being useless...

Jamie Bull · Accepted Answer · 2016-05-03 14:01:20Z

2

It looks like it's as simple as needing quotes.

conn = sqlite3.connect("stu.db")

The error you're getting is because there is no variable stu in scope. So long as "stu.db" is in the working directory then the snippet above should work.

answered May 3, 2016 at 14:01

Jamie Bull

13.6k18 gold badges80 silver badges123 bronze badges

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

2 Comments

Ian Pringle Over a year ago

Thanks, that did it! I can connect to it now.

Jamie Bull Over a year ago

Glad to hear it. Please do accept/upvote the answer if you're happy with it. It will be much appreciated. And welcome to SO!

Ian Pringle · Accepted Answer · 2016-05-03 15:04:49Z

Once I found out, thanks to Jamie, that the filename needs to be in quotes I was able to connect to the database. I then was able to work my way through the problem. Here is my solution:

import sqlite3

conn = sqlite3.connect('stu.db')
c = conn.cursor()

def add_coll():
    cceeb = int(raw_input("CEEB:> "))
    cname = raw_input("Name:> ")
    ccred = raw_input("Accred:> ")
    ccal = raw_input("Calendar:> ")
    ccomm = raw_input("Comments:> ")

    c.execute("INSERT INTO coll VALUES (%d, '%s', '%s', '%s', '%s')" %
                (cceeb, cname, ccred, ccal, ccomm))


var = 1 #This creates an infinite loop
while var == 1:

    input_stu = raw_input("Add Student?:> ")

    if input_stu == 'no' or input_stu == 'n':
        add_coll()

    else:
        id = int(raw_input("ID:> "))
        name = raw_input("Name:> ")
        ceeb = int(raw_input("CEEB:> "))
        deg = raw_input("Degree:> ")
        terms = int(raw_input("Terms:> "))
        comm = raw_input("Comments:> ")

        c.execute("INSERT INTO stu VALUES (%d, '%s', %d, '%s', %d, '%s')" %
                (id, name, ceeb, deg, terms, comm))

        input_coll = raw_input("Add College?:> ")

        if input_coll == 'yes' or input_coll == 'y':
            #cceeb = int(raw_input("CEEB:> "))
            #cname = raw_input("Name:> ")
            #ccred = raw_input("Accred:> ")
            #ccal = raw_input("Calendar:> ")
            #
            #c.execute("INSERT INTO coll VALUES (%d, '%s', '%s', '%s')" %
            #           (cceeb, cname, ccred, ccal))
            add_coll()

    conn.commit()

Collectives™ on Stack Overflow

Python Form/Prompt for SQLite input

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related