Reading changes with dbf library in python
Question: I am trying to make a program that takes changes in a `dbf` file then uploads
them. I have got it to read the `dbf` file and upload them to a `mysql`
database but its a 50 minuite upload. I have tried to get it to only upload
fields that have been changed. The problem I have is that it seems i need to
close and re-open the `dbf` file. If someone makes a change whilst its doing
this, it doesnt notice theres been a change.

Is there a better/right way of doing this:

    
    
    import time
    import dbf
    import MySQLdb
    import os
    
    source_path = r"\\path\to\file"
    file_name = "\\test.Dbf"
    
    print "Found Source DBF"
    
    source = dbf.Table(source_path + file_name)
    source.open()
    
    print "Opened DBF"
    
    updated = list(source)
    print "Copied Source"
    
    db = MySQLdb.connect(host = "myHost.com", port=3306, user = "user", passwd = "pass", db = "database")
    cur = db.cursor()
    print "Connected to database"
    
    try:
            cur.execute("DROP TABLE IF EXISTS dbftomysql")
    except:
            db.rollback()
    
    print "Dropped old table"
    
    sql = """CREATE TABLE table(
            col1 VARCHAR(200) NOT NULL,
            col2 VARCHAR(200),
            col3 VARCHAR(200),
            col4 NUMERIC(15,2),
            col5 VARCHAR(200) )"""
    
    cur.execute(sql)
    
    print "Created new table"
    
    for i, s in zip(source, updated):
            query = """INSERT table SET col1 = %s, col2 = %s, col3 = %s, col4 = %s, col5 = %s"""
            values = (i["col1"], i ["col2 "], i["col3"], i["col4"], i["col5"])
            cur.execute(query, values)
            db.commit()
            print i["col1"], i ["col2 "], i["col3"], i["col4"], i["col5"]
    print "First Upload Completed"
    
    while True:
            for i, s in zip(source, updated):
                    if i["col1"] != s["col1"]:
                            print i["col1"] + " col1Updated"
                            query = """UPDATE table SET col1= %s WHERE col1= %s"""
                            values = (i["col1"], s["col1"])
                            try:
                                    cur.execute(query, values)
                                    db.commit()
                            except:
                                    db.rollback()
                                    print "No connection to database"
    
                    if i["col2"] != s["col2"]:
                            print i["col2"] + " col2 Updated for " + i["col1"]
                            query = """UPDATE table SET col2 = %s WHERE col1= %s OR col1= %s"""
                            values = (i["col2"], i["col1"], s["col1"])
                            try:
                                    cur.execute(query, values)
                                    db.commit()
                            except:
                                    db.rollback()
                                    print "No connection to database"
                            #ect
    
            updated = list(source)
            source.close()
            source.open()                     
            time.sleep(0.2)
    


Answer: The `dbf` library will only fetch the record from the dbf file if it doesn't
already exist in memory; when you do

    
    
    updated = list(source)
    

you are effectively freezing all the rows because `updated` is a list of
records (not a list of `list`s or a list of `tuple`s; this means that when you
later try to compare `source` and `updated` you are comparing the same data.

In order to make `updated` be a separate entity from `source` try

    
    
    updated = [tuple(row) for row in source]
    

which will give you a list of tuples, or

    
    
    updated = [scatter(row, dict) for row in source]
    

which will give you a list of dicts, which is what you need for your field
comparison code further down.

