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()

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!

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.

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!

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.

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.

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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.