populate database with python script

Question

I am trying to populate my database with the follow script which does not run the query at all, instead it goes straight to the except and print the error message. Can anyone identify the problem with the function called insertGuest():

for i in range (100):
    addGuest = "INSERT INTO hotel_guests VALUES (\"%d\", \"%s\"%s    \"%s\")", (int(i), "Name"+str(i),"gender"+str(i))
    try:
        mycursor.execute(addGuest)
        conn.commit()
        print ("10000 Guests Successfully Inserted")
    except:
        conn.rollback()
        print ("An error occurred")

I would also like the data, especially the name to be meaningful, but if i can on get random strings then that will just have to do.

Thanks Summer

You can try to add traceback to your except so you know why it's giving you the error. Other than that this is a "Why isn't my code working" question which is off-topic to SO. — Leb
– Leb, Commented Oct 18, 2015 at 3:40

Will Hogan · Accepted Answer · 2015-10-18 17:06:34Z

2

Character values in SQL are single-quoted, numbers have no quoting. Python string formatting as you are attempting requires a "%" between the string and arguments tuple. So you would have something like this instead:

addGuest = "INSERT INTO hotel_guests VALUES (%d, '%s', '%s')" % (int(i), "Name"+str(i),"gender"+str(i))

That said, you may be better off with str.format() and named arguments, for clarity. See https://docs.python.org/2/library/string.html#format-string-syntax

You may also want to improve the error reporting from your except. You could replace it with:

    except mdb.Error as e:
        conn.rollback()
        print "Error %d: %s" % (e.args[0],e.args[1])

(This assumes you imported MySQLdb with import MySQLdb as mdb, adjust the mdb name accordingly otherwise.)

edited Oct 18, 2015 at 17:06

answered Oct 18, 2015 at 3:56

Will Hogan

9195 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

summerdaiz Over a year ago

Thanks. I tried this but it still doesn't take the query instead it goes runs the error statement.

Will Hogan Over a year ago

Ahh, I see. It should produce valid SQL now at least so there may be other factors such as the state of the cursor/connection or something about the table. At least, as @leb suggests, you should get some more info from your except, a traceback or at least an error description.

summerdaiz Over a year ago

I ended up just using a for loop and took out the try/except. I'm new to programming so i will take tiny working steps. Additionally apparently the MySQLdb library was not available for my version.

Adriano_Pinaffo · Accepted Answer · 2020-08-03 01:39:47Z

0

Depending on the case,you would be better off concatenating the strings with the + sign like below:

addGuest = "INSERT INTO hotel_guests VALUES (" + int(i) + ", 'Name" + str(i) + "', 'gender" + str(i) + "')"

I took that approach in my project (populate databases with bogus/fake, but valid, data) because the queries were long and complex. You might want to take a look.

answered Aug 3, 2020 at 1:39

Adriano_Pinaffo

1,7495 gold badges31 silver badges52 bronze badges

Collectives™ on Stack Overflow

populate database with python script

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related