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
very slowly.

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.

++ kevin

Code follows:
#!/usr/bin/env python3
"""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
import qcreate
from qerror import *
import myparser

if __name__ == '__main__':
    parser = argparse.ArgumentParser(description="""A program to read
positions and count how many are
            in the database""",)
    parser.add_argument("--dbname", default=None,
            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")
            if os.path.exists(f):
                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
qubic.ini files")

    present = missing = lines = 0
    with sqlite3.connect(args.dbname) as conn:
        for line in infile:
            fields = line.split()
            pos = fields[0]
            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("""
                        SELECT pnum
                        FROM pos
                        WHERE ppos=?
                pnum = row[0]
            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

Reply via email to