PGDB problem

[bgk111]

Well, I just had another issue resolved and have come to another one that baffles me, and as I can find little to no documentation on the pgdb module, I thought I'd sound you guys out again.

I am attempting to insert values into a table in postgresql, and while I have working code that will do this is if specify all the values I am sticking into the database, code that dynaimcally grabs the data from a file and then inserts does not, even though no exceptions are thrown. Im not sure whats up, any ideas?

fa = open(datafilename, 'r')

#THIS BLOCK OF CODE UPDATES MY DB WITH THE LINE SHOWN
db=pgdb.connect(database=databasename, host=hostname,user=username,password=passwd)
cursor = db.cursor()
command = "INSERT INTO buildingarea VALUES (001,'ADAMS COUNTY',99344,46.827354,-119.1742,'COM',1592);"
print(command)
cursor.execute(command)
db.commit()
db.close()


#THIS LINE OF CODE OUTPUTS A COMMAND IN THE PRINT STATEMENT THAT IS IDENTICAL TO THE ONE ABOVE AND RUNS WITH NO ERRORS, BUT NOTHING UPDATES.
for line in fa:
    out = line.split(",")
    s = ''.join(out)
    final = []
    for segment in s.split('\x00\x00'):
        temp = ''
        for char in segment:
             if ord(char) > 31:
                  temp +=char
        final.append(temp)

    db=pgdb.connect(database=databasename, host=hostname,user=username,password=passwd)
    cursor = db.cursor()
    command = "INSERT INTO "+tablename+" VALUES ("+final[0]+",'"+final[1]+"',"+final[2]+","+final[3]+","+final[4]+",'"+final[5]+"',"+final[6]+");"
    print(command)
    cursor.execute(command)
    db.commit
    cursor.close()
    db.close()

[bgk111]

I did find a solution, but it is considerably slower and I don't like it, it uses what I have above, but instead using an os.system statement and uses the psql command to individually write each line...Let me know if anyone can think of something better! Thanks!

[jlm699]

print(command)
    cursor.execute(command)
    db.commit
    cursor.close()
    db.close()

Can you give us some example output from the print(command) trace code? It could be as simple as a missing quote or an improper tablename. Also, where is tablename even coming from? I don't see it in your code anywhere.

[bgk111]

here is one sample line:

INSERT INTO buildingarea VALUES (001,'ADAMS ',99344,46.827354,-119.1742,'COM',1592);

O there is code I am not showing, but the idea was that the first few lines of code above DO work, and the second set do not. All of that is declared in a part of the program that I do not think is part of the problem, but let me know if you want the rest, ill just have to edit the passwords and such. But I know I am linking to the right stuff as the first block of code works. Thanks!

[jlm699]

I know I am linking to the right stuff as the first block of code works.

That's very peculiar. I wonder if there is some sort of timing issue happening when you're repeatedly opening and closing the database connection...

Why don't you just try to move the db = , cursor = and db.close statements outside of the for loop and see if that gives you any better results.

As far as I can tell everything looks okay.

[bgk111]

Hmm, ill give this a try. I noticed that sometimes I have to add extra db.commit() statements in some other code I am writing right now as well, it seems that there is a problem somewhere with regard to this. I have found another method on how to do this however, which appears to work all-right. Ill try this on the older code as soon as I have some extra time to go back for posterities sake.

[bgk111]

I dont know that im ever going to have time to figure this out, my recomendation is to do what I did, create a file with all the commands, then go into psql and run the text file. Sorry!