User input MySQLdb and Python

[rev_ollie]

Hi,

I have been looking at a few tasks I want to do using a MySQL DB and Python. Therefor I'm spending some time looking at the MySQLdb module for Python and how I can use it.

I've worked out a few bits but I want to be able to use user input to enter data instead of having to edit the python script values each time

I found a few alternatives on here and have a single very simple version working:

import MySQLdb

db = MySQLdb.connect("localhost","root","reverend","TESTDB" )
cursor = db.cursor()

staff_first = raw_input("What is your first name? ")
#staff_last = raw_input("What is your second name? ")

sql="""INSERT INTO STAFF(FIRST_NAME) VALUES('%s')""" % staff_first

cursor.execute(sql)
db.commit()
db.close()

This is working so far as it is getting the first name in - but I can't seem to be able to edit the script to allow each input to work (I have commented out staff_last for now - but I would like that information to be requested at some point).

Can someone give me an example of how I can do this - altering the script to allow more that 1 input per line (even if it's just a hint to get me thinking)?
I tried a few different versions altering the values and the field names etc but I keep getting errors when run.

I'm still a bit of a beginner when it comes to programming - and so it's taking a while to sift through and understand some of the errors I am getting.

Thanks!

[TrustyTony]

Even more difficult for us when we do not even know what those error messages are.

[rev_ollie]

Sorry - missed my detail...

I though initially I could just replicate the current code and just insert the extra values:

sql="""INSERT INTO STAFF(FIRST_NAME) VALUES('%s','%s')""" % staff_first, staff_last

But that gives me - not enough arguments for format string - tried without the speech marks on the values as apparently that can cause an issue but no luck

I found another example as well which I have been looking at - but thats giving me not all arguments converted during string formatting...

However I have just this second sorted it - I had slightly incorrect formating (marjorly incorect really)

This is the final version I manage to get working:

import MySQLdb

db = MySQLdb.connect("localhost","root","testing","TESTDB" )
cursor = db.cursor()

staff_first = raw_input("What is your first name? ")
staff_last = raw_input("What is your second name? ")

cursor.execute("""
    INSERT INTO STAFF
    (FIRST_NAME, LAST_NAME) VALUES
    (%s, %s)""" , (staff_first, staff_last))

db.commit()

db.close()

Sorry about shooting the gun - not something I normally give up on so quickly!

While I have the topic open - what would you suggest is the best way of gaining a better insight into the errors i'm getting back from python to allow me to I guess troubleshoot in a cleaner more organised way? Looking at this I was guessing and not really thinking about what was going on...as you could probably tell by the second example

I think I need to look at learning some more of the definitions and language used - going back to basics - unless doing small projects like this can help build the experience?

Thanks

[ryantroop]

because of this very awkward (to me) behavior, I much prefer .format()

you could easily have done:

cursor.execute("""INSERT INTO staff (FIRST_NAME, LAST_NAME) VALUES (r{}, r{})""".format(first_name, second_name)

You can even format by place holder, if you have a list, tuple, or parsed dictionary:

names = ['firstname', 'lastname']

cursor.execute("""INSERT INTO staff (FIRST_NAME, LAST_NAME) VALUES (r{0}, r{1}""".format(*names)

the r should keep quotation marks in place when doing sql inserts. (it makes it a "raw" string)

[rev_ollie]

Thanks for the changes - I will admit it looks like a better looking solution if anything else. Is there a particular advantage of using the second method as apposed to the one I got working?

My other issue however I that I can't seem to get the second solution to actually work - is the following correct or I have I got that variables in the wrong place?

import MySQLdb

db = MySQLdb.connect("localhost","root","test","TESTDB" )

staff_first = raw_input("What is your first name? ")
staff_last = raw_input("What is your second name? ")

names = ['first_name', 'last_name']

cursor.execute("""INSERT INTO staff (FIRST_NAME, LAST_NAME) VALUES (r{0}, r{1}""".format(*names)





Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\Oliver>cd desktop

C:\Users\Oliver\Desktop>python server2.py
  File "server2.py", line 11


                    ^
SyntaxError: invalid syntax

C:\Users\Oliver\Desktop>

Thats the error I get back...which doesn't seem to make much sense. There is nothing on line 11 - and the invalid Syntax is pointing to a blank line and no particular string of text...
Thats Python 2.7 (32) on Windows 7 (64).

[ryantroop]

heh, sorry I wasnt more clear.

by doing:

names = ['first_name', 'last_name']

You are making names be a list with 2 strings, "first_name" and "last_name".

In your particular case, you can either populate the list by manually:

staff_first = raw_input("First Name: ")
staff_last = raw_input("Last Name: ")

names = [staff_first, staff_last]

or, you can simply use the object staff_first and staff_last, which I will show you at the end.

Now, on to your problem - you forgot to close the whole query with a closing parentheses.

cursor.execute("""INSERT INTO staff (FIRST_NAME, LAST_NAME) VALUES (r{0}, r{1})""".format(*names))

Notice the 2 extra parentheses added after r{1} and (*names)

If you don't want to use the unpacking method, you can simply call the items by name -

 cursor.execute("""INSERT INTO staff (FIRST_NAME, LAST_NAME) VALUES (r{0}, r{1})""".format(staff_first, staff_last))

Hope that helps.

Ryan

[rev_ollie]

Right I see now. I've obviously confused myself along the way but that's much clearer - thanks!