470,819 Members | 1,612 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,819 developers. It's quick & easy.

Strange sqlite3 library behavior

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.
Feb 2 '08 #1
2 1596
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-C...nsactions.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
Feb 2 '08 #2
2008/2/2, Roel Schroeven <rs****************@fastmail.fm>:
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.
While these solutions are correct I wouldn't suggest enabling
auto-commit, you will have serious performance issues.
>
For more info, see the sqlite3 docs (especially the section on
controlling transactions:
http://docs.python.org/lib/sqlite3-C...nsactions.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

--
http://mail.python.org/mailman/listinfo/python-list

--
-- Guilherme H. Polo Goncalves
Feb 2 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by lolmcbride | last post: by
2 posts views Thread by Harold Shore | last post: by
66 posts views Thread by mensanator | last post: by
3 posts views Thread by cjl | last post: by
4 posts views Thread by Simon | last post: by
1 post views Thread by jeff_d_harper | last post: by
12 posts views Thread by Gilles Ganault | last post: by
reply views Thread by =?ISO-8859-1?Q?Gerhard_H=E4ring?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.