Strange sqlite3 library behavior

[Victor Lin]

Now I am now developing a program that base on sqlite3 in python.
But there is a strange problem.
That is, all data I insert into sqlite database do not goes into file
in disk.
It is really strange....
Why do these data just keep in memory and discarded?

All things that really store in file is the table.
If I create a table, it would appears in the sqlite file.

This is the example :

import sqlite3

createSyntax = """CREATE TABLE IF NOT EXISTS category(
id INTEGER NOT NULL PRIMARY KEY
)"""

createSyntax2 = """CREATE TABLE IF NOT EXISTS category2(
id INTEGER NOT NULL PRIMARY KEY
)"""

insertSyntax = """INSERT INTO category VALUES (1234)"""

db = sqlite3.connect('test.db')
cur = db.cursor()
cur.execute(createSyntax)
cur.execute(insertSyntax)
# only by doing so can let inserted data store in file
cur.execute(createSyntax2)
cur.execute('select * from category')
print cur.fetchall()

db.close()

raw_input()


After I tried some way. I found that create table force data goes into
file. Otherwise all data would just keep in memory and discarded with
the program's ending.
Why sqlite3 just keep inserted data in memory? And how to force
inserted data into file?

Thanks.

Victor Lin.

 

[Roel Schroeven]

Victor Lin schreef:

Now I am now developing a program that base on sqlite3 in python.
But there is a strange problem.
That is, all data I insert into sqlite database do not goes into file
in disk.
It is really strange....
Why do these data just keep in memory and discarded?

sqlite3 works according to PEP 249, which means among other things that
transactions are by default not auto-committed.

There are two possible solutions:
- manually commit the transaction when it's finished with db.commit()
(perhaps the cursor object also has a commit() method; I'm not sure
about that)
- enable auto-commit.

For more info, see the sqlite3 docs (especially the section on
controlling transactions:
http://docs.python.org/lib/sqlite3-Controlling-Transactions.html) and
PEP 249 (http://www.python.org/dev/peps/pep-0249/)

--
The saddest aspect of life right now is that science gathers knowledge
faster than society gathers wisdom.
-- Isaac Asimov

Roel Schroeven

 

[Guilherme Polo]

Victor Lin schreef:



sqlite3 works according to PEP 249, which means among other things that
transactions are by default not auto-committed.

There are two possible solutions:
- manually commit the transaction when it's finished with db.commit()
(perhaps the cursor object also has a commit() method; I'm not sure
about that)
- enable auto-commit.

While these solutions are correct I wouldn't suggest enabling
auto-commit, you will have serious performance issues.