5
\$\begingroup\$

I'm changing code that writes data to a DB, so I have a dump (a text file) of an earlier run to compare against, to ensure that my changes don't screw things up. Here goes:

def dbcheck(cursor):
    dbresult = list()
    cursor.execute("SELECT COLUMN FROM TABLE")
    for item in cursor.fetchall():
        line = item[0] + "\n"
        dbresult.append(line)
    with open(dbdump) as f:
        for n, line in enumerate(f):
            if line != dbresult[n]:
                print("DB content does not match original data!")

This code runs fine, but I'm worried that dbresult can grow really large, so am looking for a less risky way of doing this. I'm also curious of what else can be improved.

[sidenote] I left out exception handling for the sake of simplicity/clarity.

\$\endgroup\$
5
\$\begingroup\$

This should do it:

def dbcheck(cursor):
    cursor.execute("SELECT COLUMN FROM TABLE")
    with open(dbdump) as f:
        for item in cursor:
            if f.readline() != item + '\n'
                print("DB content does not match original data!")

No need to read either the whole column nor the whole file before iterating.

\$\endgroup\$
4
\$\begingroup\$

Use zip to iterate over both iterators at the same time. It'll only use the memory needed to hold a single entry from each at a time.

def dbcheck(cursor):
    cursor.execute("SELECT COLUMN FROM TABLE")
    with open(dbdump) as f:
        for item, line in zip(cursor, f):
            if line != item[0] + '\n':
                print("DB content does not match original data!")

If using python 2.x use itertools.izip instead of zip. zip puts everything in a huge list which won't be very efficient.

\$\endgroup\$
  • \$\begingroup\$ Looks more elegant, however wasn't there talk about stuff like zip and lambda not being very Pythonic? \$\endgroup\$ – tshepang Apr 5 '11 at 23:34
  • \$\begingroup\$ Aren't you missing cursor.fetchall()? \$\endgroup\$ – tshepang Apr 5 '11 at 23:36
  • \$\begingroup\$ @Tshepange, fetchall() returns the entire result of the query as a list. But you can also iterate over the cursor directly to get the results. Since you want to iterate over all the rows in the table, this is what you want. \$\endgroup\$ – Winston Ewert Apr 6 '11 at 0:31
  • \$\begingroup\$ I've never heard zip accused of not being pythonic, lambda and reduce, yes. But zip belongs with enumerate, itertools and all that perfectly pythonic stuff. \$\endgroup\$ – Winston Ewert Apr 6 '11 at 0:37
  • 1
    \$\begingroup\$ And to be clear, it is not as though certain features of python are unpythonic and should be avoided. The goal of pythonicity is readable code. Some features tend to produce unreadable code and thus get a bad rap. \$\endgroup\$ – Winston Ewert Apr 6 '11 at 0:42
1
\$\begingroup\$

You can use itertools.izip to avoid reading all of both sets of data before iterating. Also, this version breaks immediately on finding a problem:

import itertools as it
def dbcheck(cursor):
    with open(dbdump) as f:
        cursor.execute("SELECT COLUMN FROM TABLE")
        for fline, dbrec in it.izip(f, cursor):
            if fline.strip() != dbrec[0]:
                print("DB content does not match original data!")
                break

Here is a version that reports the line number of the file, along with the mismatched data, and continues for all lines in the file. Also note that it's not calling print as a function, but rather using parenthesis to group the expression creating the string to print (in Python 3.x it is calling print as a function):

import itertools as it
def dbcheck(cursor):
    with open(dbdump) as f:
        cursor.execute("SELECT COLUMN FROM TABLE")
        for lineno, fline, dbrec in it.izip(it.count(), f, cursor):
            if fline.strip() != dbrec[0]:
                print("Line %d: File: %s, DB: %s" % 
                      (lineno, 
                       fline.strip(), 
                       dbrec[0].strip()))

Also, in Python 3, the "zip" function is the same as itertools.zip, I think.

\$\endgroup\$
  • 1
    \$\begingroup\$ 2 issues: the very last line, why strip brec, and why call it brec :) \$\endgroup\$ – tshepang Apr 7 '11 at 0:01
  • 1
    \$\begingroup\$ Why introduce a variable, res? Can't we just play with cursor? \$\endgroup\$ – tshepang Apr 7 '11 at 0:06
  • 1
    \$\begingroup\$ @Tshepang, I'm stripping both the line entry and the database value assuming: 1. whitespace on the ends isn't important, and 2. there will be additional whitespace on both the file line and the database record. The file line will have the end-of-line characters, and the database record may have padding, since some drivers pad the values to the full column width. \$\endgroup\$ – The UNIX Man Apr 7 '11 at 0:44
  • 1
    \$\begingroup\$ @TheUNIXMan: I don't understand what you say, but can't you do it.izip(it.count(), f, cursor) instead of introducing res? \$\endgroup\$ – tshepang Apr 7 '11 at 9:57
  • 1
    \$\begingroup\$ Which DB driver allows you to run execute using a Connection object? \$\endgroup\$ – tshepang Apr 12 '11 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.