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

Does the program this code is part of exit? When do you try to access the table? — user1907906
– user1907906, Commented Apr 3, 2014 at 12:37
Works for me. Show the code that tries to update or access the table. — CL.
– CL., Commented Apr 3, 2014 at 12:45
I used this method and checked and the table did exist. I closed the connection after doing conn.commit(). I then reopened it and the data was there and closed it. The third time I tried to access it I received an error that the table doesn't exist- do I have to end with committing and closing every time? — user2906979
– user2906979, Commented Apr 3, 2014 at 13:10

ChrisProsser · Accepted Answer · 2014-04-03 13:24:25Z

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.

Newbie101 · Accepted Answer · 2019-05-28 19:26:29Z

3

import sqlite3;
import pandas as pd;

con=None

def getConnection():
    databaseFile="./test.db"
    global con
    if con == None:
        con=sqlite3.connect(databaseFile)
    return con

def createTable(con):
    try:
        c = con.cursor()
        c.execute("""CREATE TABLE IF NOT EXISTS Movie
                 (start, end, score)""")
    except Exception as e:
        pass

def insert(con):
    c = con.cursor()
c.execute("""INSERT INTO Movie (start, end, score)
          values(1, 99, 123)""")

def queryExec():
    con=getConnection()
    createTable(con)
    insert(con)
    # r = con.execute("""SELECT * FROM Movie""")
    result=pd.read_sql_query("select * from Movie;",con)
    return result


r = queryExec()
print(r)

edited May 28, 2019 at 19:26

Newbie101

5013 gold badges6 silver badges17 bronze badges

answered Apr 14, 2019 at 11:46

Wang Liang

4,4527 gold badges26 silver badges50 bronze badges

Collectives™ on Stack Overflow

creating a table in sqlite3 python

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related