I'm pretty new at SQLite, so this may seem obvious to you. Be kind.
I'm using Python on Ubuntu Linux 16.04 LTS, and the sqlite that is built
into Python. The database
is using WAL.
I've got a database of some 100 million records, and a file of just over
300 thousand that I want represented in it. I wanted to check how much
difference it was going to make, so I wrote a super
simple program to the read the file and count how many records are already
there. I got impatient waiting for it so I killed the process and added an
output of one dot (".") per 1000 records. It went very fast for what I
estimate was around 200 dots and hit a wall. It made progress, but very
So I killed it again and added a commit() call every time it output a dot.
It didn't hit a wall, just some rough road (that is, it slowed down at
about the same spot but not nearly so drastically).
The code makes to changes to the database at all. Why does commit() make a
difference? What else should I learn from this?
The field being used for the lookup has an index.
"""Count the number of records that represent rows in the database 'pos'
The database is not modified.
Last Modified: Sat Nov 25 18:56:49 PST 2017
import os.path # https://docs.python.org/3.5/library/os.path.html
import sys # https://docs.python.org/3.5/library/sys.html
import argparse # https://docs.python.org/3.5/library/argparse.html
import sqlite3 # https://docs.python.org/3.5/library/sqlite3.html
import re # https://docs.python.org/3.5/library/re.html
# from /usr/local/lib/python3.5/dist-packages
from qerror import *
if __name__ == '__main__':
parser = argparse.ArgumentParser(description="""A program to read
positions and count how many are
in the database""",)
help="name of the database to work on (overrides qubic.ini
parser.add_argument("file", nargs='?', type=argparse.FileType('r'),
help="file containing the qsearch results (default stdin)")
infile = args.file
if args.dbname is None:
for confdir in
f = os.path.join(confdir, "qubic.ini")
args.dbname = myparser.parse(f, here, "dbname")
if args.dbname is not None:
if args.dbname is None:
print(" *** ERROR: no database name provided and none found in
present = missing = lines = 0
with sqlite3.connect(args.dbname) as conn:
for line in infile:
fields = line.split()
pos = fields
if len(pos) != 64: # Important test to catch grep without
raise InputError(" ERROR: input line has wrong-sized
position: " + line)
pnum = None
for row in conn.execute("""
pnum = row
if pnum is None:
missing += 1
present += 1
lines += 1
if lines % 1000 == 0:
print("there were",present,"records on file and",missing," were
print("out of a total of", lines, "records.")
word of the year: *kakistocracy*
sqlite-users mailing list