I am trying to fill a sqlite3 database with records, and to this end I
have written a class that creates the db, the table and adds rows to
the table.
The problem is that the updating process is *extremely* slow, and
occasionally I get the message "database locked".
I tried removing "self.con.commi t()" in the add_record method, but
then nothing is saved in the db. I don't know whether this has
anything to do with it, but I have found no option to enable
autocommit.
This is the class that I am using:
class sqliteDB(object ):
"Wrapper for SQLite methods"
def __init__(self, db_file="sqlite 3.db"):
'Intialize SQLite database, sqlite_db_init( "db_file_name.d b")'
print 'SQLite db init: ', db_file
self.con = sqlite3.connect (db_file)
self.cur = self.con.cursor ()
def create_table(se lf, table):
"create table (table_name)"
query ='CREATE TABLE %s (hword VARCHAR(256) PRIMARY KEY,
definition TEXT)' % table
try:
self.cur.execut e(query)
self.con.commit ()
except Exception, e:
print e
def add_record (self, table, headWord, definition):
try:
self.cur.execut e('INSERT INTO ' + table + '(hword,
definition) VALUES(?, ?)', (headWord, definition))
self.con.commit ()
except Exception, e:
print e
And this is the actual code that I use to write to the db file:
db = sqliteDB()
db.create_table ("table_name ")
for k, v in myData:
db.add_record(t able, k,v)
This works extremely slow (~10KB of data per second) and takes ages to
complete even with small files. Where did I go wrong?
Would it be faster (or possible) to import a text file to sqlite using
something like the mysql's command
LOAD DATA INFILE "myfile.csv"... ? 5 10683
coldpizza schreef:
I am trying to fill a sqlite3 database with records, and to this end I
have written a class that creates the db, the table and adds rows to
the table.
The problem is that the updating process is *extremely* slow, and
occasionally I get the message "database locked".
I tried removing "self.con.commi t()" in the add_record method, but
then nothing is saved in the db. I don't know whether this has
anything to do with it, but I have found no option to enable
autocommit.
Remove self.con.commit () from add_record(), and do it once after all
records are added.
The reason that the process is slow with a commit after every INSERT is
that sqlite syncs the inserted data to disk before it continues.
--
If I have been able to see further, it was only because I stood
on the shoulders of giants. -- Isaac Newton
Roel Schroeven
Thanks a lot, Roel, adding a single commit() at the end did solve the
speed problem.
Another question is do I have to explicitly close the DB connection,
or is it automatically garbage collected? Is it Ok to no have any
cleanup code?
Another question would be how to define the encoding for newly added
records?
And how do set the encoding for the retrieved records? Is it always
utf-8 by default?
On Jul 16, 11:21 pm, Roel Schroeven <rschroev_nospa m...@fastmail.f m>
wrote:
coldpizza schreef:
I am trying to fill a sqlite3 database with records, and to this end I
have written a class that creates the db, the table and adds rows to
the table.
The problem is that the updating process is *extremely* slow, and
occasionally I get the message "database locked".
I tried removing "self.con.commi t()" in the add_record method, but
then nothing is saved in the db. I don't know whether this has
anything to do with it, but I have found no option to enable
autocommit.
Remove self.con.commit () from add_record(), and do it once after all
records are added.
The reason that the process is slow with a commit after every INSERT is
that sqlite syncs the inserted data to disk before it continues.
--
If I have been able to see further, it was only because I stood
on the shoulders of giants. -- Isaac Newton
Roel Schroeven
coldpizza wrote:
Thanks a lot, Roel, adding a single commit() at the end did solve the
speed problem.
Another question is do I have to explicitly close the DB connection,
or is it automatically garbage collected? Is it Ok to no have any
cleanup code?
It's generally OK, but you can register a function with atexit() if you
are paranoid about cleanup. Here's a sample with an ugly global variable.
from atexit import register
def close():
global conn
if conn:
conn.close()
print "Database closed"
conn = None
#
# We try to ensure the database is always closed by registering
# the nodule's close() function to be called on program exit
#
register(close)
import psycopg2 as db
conn = db.connect(data base="billing", user="steve", password="tadaa !")
curs = conn.cursor()
print "Database opened"
Another question would be how to define the encoding for newly added
records?
And how do set the encoding for the retrieved records? Is it always
utf-8 by default?
Generally speaking each database instance will have an associated
encoding. Trying to establish some other encoding would then be pissing
into the wind.
regards
Steve
--
Steve Holden +1 571 484 6266 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://del.icio.us/steve.holden
--------------- Asciimercial ------------------
Get on the web: Blog, lens and tag the Internet
Many services currently offer free registration
----------- Thank You for Reading -------------
Steve Holden <st***@holdenwe b.comwrites:
# We try to ensure the database is always closed by registering
# the nodule's close() function to be called on program exit
Ooh! Where do I find more about writing Python nodules? Is it related
to cluster programming?
--
\ "If you go parachuting, and your parachute doesn't open, and |
`\ you friends are all watching you fall, I think a funny gag |
_o__) would be to pretend you were swimming." -- Jack Handey |
Ben Finney
coldpizza wrote:
Thanks a lot, Roel, adding a single commit() at the end did solve the
speed problem.
Another question is do I have to explicitly close the DB connection,
or is it automatically garbage collected? Is it Ok to no have any
cleanup code?
Another question would be how to define the encoding for newly added
records?
And how do set the encoding for the retrieved records? Is it always
utf-8 by default?
SQLite databases store text in UTF-8 encoding. If you use pysqlite, and
always use unicode strings, you will never have any problems with that.
pysqlite does not rap on your knuckles if you store arbitrary encodings in
the database, but you will feel sorry once you try to fetch the data:
>>from pysqlite2 import dbapi2 as sqlite con = sqlite.connect( ":memory:") binary_rubbis h = chr(130) + chr(200) con.execute(" create table foo(bar)")
<pysqlite2.dbap i2.Cursor object at 0xb7dc20b0>
>>con.execute(" insert into foo(bar) values (?)", (binary_rubbish ,))
<pysqlite2.dbap i2.Cursor object at 0xb7dc22f0>
>># so far, so good ...
.... # watch now
....
>>con.execute(" select bar from foo")
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
pysqlite2.dbapi 2.OperationalEr ror: Could not decode to UTF-8 column 'bar'
with text '��'
>>>
HTH
-- Gerhard This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: smartin |
last post by:
python IIS cgi loading extremely slow
I recently uninstalled python 1.5.2 and installed python 2.3 on a
Windows 2000 server running IIS 5.0. The issue is that when submitting
a cgi request to a python script, it takes about 1 minute to process
the python (2.3 code) and load the page on this particular server.
Running a simple "print hi" script takes at least a minute. I have
tested the uninstall and reinstall on a test Windows 2000...
|
by: garydevstore |
last post by:
Hi,
I have a table defined as
CREATE TABLE (
IDENTITY (1, 1) NOT NULL ,
NULL ,
NULL ,
NOT NULL ,
NULL ,
(255) COLLATE SQL_Latin1_General_CP1_CS_AS
|
by: D. Dante Lorenso |
last post by:
Trying to use the 'search' in the docs section of PostgreSQL.org
is extremely SLOW. Considering this is a website for a database
and databases are supposed to be good for indexing content, I'd
expect a much faster performance.
I submitted my search over two minutes ago. I just finished this
email to the list. The results have still not come back. I only
searched for:
SECURITY INVOKER
|
by: Chris |
last post by:
I'm using a DataTable in my application. I am able to load rows into the DataTable quickly. What's puzzling me, however, is that when I update a set of cells in the DataTable, the update is really slow
my update code amounts to
dataTable.BeginLoadData()
dataSet.EnforceConstraints = false
tr
for(iRowHandle = 0; iRowHandle < iLimit; iRowHandle++
|
by: gcmf8888 |
last post by:
I use socket client to connect to a server written in C++. I found out
that the C# socket connection(I didn't use TCPClient instead i used
socket) is extremely slow(comparing to Java and C++ one I wrote
before). I found that since my firewall will popup the alert window.
Any suggestion?
| |
by: tormod |
last post by:
I'm using Visual Studio 2005 professional edition and try to build a
web site built in c#.
The solution consists of about 20 class library projects and one web
site. The web site references the class libraries in the project to
project fashion.
My problem starts when I try to build the solution. The compilation of
the projects completes fast, but after that visual studio goes into an
idle state for about 30 seconds before it compiles...
|
by: DCC-700 |
last post by:
I am running VB for ASP.Net in VS.Net 2003 and am experiencing extremely slow
response in the ide at times and the debugger. Below is additional detail on
the problem. Any thoughts are much appreciated.
VS.Net IDE Extremely Slow
Symptoms:
· When you type in IDE editor you have to wait several seconds before the
text appears
Observations:
· This occurs for a page with many controls and a lot of code when editing
|
by: Carlo Marchesoni |
last post by:
I have an application that has a main.aspx page (something like a todo list).
Clicking one of the items open an new window (javascript window.open) with a
new aspx page.
this is extremely slow.
if I type the same (slow) aspx page in the address bar of the browser it
appears immediately. This makes me believe that window.open together with
aspx is very slow - but I don't know why and don't see the reason.
Opening a html page with...
|
by: CSmith |
last post by:
Design view moving extremely slow ...HELP!!!
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |