creating a table in sqlite3 python
Question: I apologize in advance for asking such a basic question but I am new to
SQlite3 and having trouble starting. I am trying to build a database with one
table. I used the following code to build a table.

    
    
    import sqlite3
    conn = sqlite3.connect('example.db')
    c = conn.cursor()
    c.execute('''CREATE TABLE mytable
             (start, end, score)''')
    

but whenever I try to update or access the table it seems that it doesnt exist
or maybe it exists in a different database. I also tried creating a table
called example.mytable but I got the error: sqlite3.OperationalError: unknown
database example

What am I missing? Thanks


Answer: I think that a commit is needed after inserts (schema changes such as new
tables should automatically commit). I would suggest adding the full path to
your database as well to make sure you are accessing the same location next
time round.

Here is an extension on your code:

    
    
    import sqlite3
    
    def create():
        try:
            c.execute("""CREATE TABLE mytable
                     (start, end, score)""")
        except:
            pass
    
    def insert():
        c.execute("""INSERT INTO mytable (start, end, score)
                  values(1, 99, 123)""")
    
    def select(verbose=True):
        sql = "SELECT * FROM mytable"
        recs = c.execute(sql)
        if verbose:
            for row in recs:
                print row
    
    db_path = r'C:\Users\Prosserc\Documents\Geocoding\test.db'
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    create()
    insert()
    conn.commit() #commit needed
    select()
    c.close()
    

Output:

    
    
    (1, 99, 123)
    

After closing the program if I log onto the SQLite database the data is still
there.

