python sqlite3 insert command

Question

any idea what I'm doing wrong?

I'm creating a table called General:

    conn = sqlite3.connect(self.dbLocation)
    c = conn.cursor()        
    sql = "create table if not exists General (id integer NOT NULL,current char[20] NOT NULL,PRIMARY KEY (id))"
    c.execute(sql)
    c.close()
    conn.close()

I'm then using max(id) to see if the table is empty. If it is, I create a table called Current1 and insert a row in General (id, 'Current1'). id is autoincrementing integer:

    self.currentDB = "Current1"
    self.currentDBID = "1"
    #create the table
    sql = "create table %s (id integer NOT NULL,key char[90] NOT NULL,value float NOT NULL,PRIMARY KEY (id))" % (str(self.currentDB))
    c.execute(sql)
    c.close()
    conn.close()
    conn = sqlite3.connect(self.dbLocation)
    c = conn.cursor()
    sql = "insert into General(current) values('%s')" % (str(self.currentDB))
    print "sql = %s" % (str(sql)) ---> *sql = insert into General(current) values('Current1')*
    c.execute(sql)
    print "executed insert Current"
    c.execute ("select max(id) from General")
    temp = c.next()[0]
    print "temp = %s" % (str(temp)) ---> *temp = 1*
    c.close()
    conn.close()

The problem is that if I open the database, I do not find any rows in the General table. Current1 table is being created, but the insert statement into General does not seem to be doing anything. What am I doing wrong? Thanks.

Arsh Singh · Accepted Answer · 2013-07-29 14:58:59Z

1

You have to commit the changes before closing the connection:

conn.commit()

check the example in the docs : http://docs.python.org/2/library/sqlite3.html

answered Jul 29, 2013 at 14:58

Arsh Singh

2,14617 silver badges16 bronze badges

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

1 Comment

user2623934 Over a year ago

Just found the answer on another page before I refreshed this one and found your answers. You are right, I also tried committing with variable c but didn't work...it has to be on conn. Thanks a lot :) Will mark yours as the right answer since yours is the first one, even though @Maxime Lorant is right too!

tasos · Accepted Answer · 2013-07-29 15:01:30Z

0

Or you can use the connection as a context manager: http://docs.python.org/2/library/sqlite3.html#using-the-connection-as-a-context-manager

Connection objects can be used as context managers that automatically commit or rollback transactions. In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed

Example:

import sqlite3
conn = sqlite3.connect('current')
with conn:
    conn.execute("create table if not exists General (id integer NOT NULL,current char[20] NOT NULL,PRIMARY KEY (id))")
    conn.execute("insert into General(current) values('{0}')".format("some data"))

with conn:
    q = conn.execute("select max(id) from General")
q.fetchone()[0]

answered Jul 29, 2013 at 15:01

tasos

3923 silver badges9 bronze badges

Comments

Maxime Lorant · Accepted Answer · 2013-07-29 15:02:28Z

0

You need to commit changes in the database with

conn.commit()

This will write on the disk changes you made in your database. If you close your database without doing it, you lost your modification (INSERT/UPDATE/DELETE)

answered Jul 29, 2013 at 15:02

Maxime Lorant

36.4k19 gold badges91 silver badges97 bronze badges

Collectives™ on Stack Overflow

python sqlite3 insert command

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related