On Tue, 8 Feb 2005, james middendorff wrote:
> I want to use mysqldb to add people into a database, but when I ask for
> the certain fields like Name, PhoneNumber and such, I cannot get it to
> put them in as a string? I am not sure what I am doing wrong but here is
> my code thanks to anyone who helps:
Hi James,
Ok, I see a few things in the execute statement that can be fixed. Let's
first take a look at the code:
> c.execute ("""
> INSERT INTO people ()
> VALUES
> ('%s','%s','%s','%s','%s');
> """)% (Name, PhoneNumber, Address,
> EmailAddress, BirthDate)
The SQL here has an empty column list:
INSERT into people ()
^^
and this is probably not a good idea: instead, list out the field names
explicitely.
The reason is because SQL tables don't necessarily imply a specific order.
The empty column list approach is also not robust to SQL table changes in
the future: if you add a new column into people, your existing code will
certainly break since the number of values don't match the number of
columns.
More than that, though, is a silly syntax issue that's related to string
interpolation. Let's pretend for the moment that we do fix the SQL column
issue:
###
c.execute ("""
INSERT INTO people (name, phone_number, address,
email_address, birthdate)
VALUES
('%s','%s','%s','%s','%s');
""") % (Name, PhoneNumber, Address, EmailAddress, BirthDate)
###
Brace yourself: you're not going to like this. One of the parenthesis is
misplaced.
You meant to write:
###
c.execute ("""
INSERT INTO people (name, phone_number, address,
email_address, birthdate)
VALUES
('%s','%s','%s','%s','%s');
""" % (Name, PhoneNumber, Address, EmailAddress, BirthDate)
)
###
Don't worry, we all do this sometimes. *grin*
Which brings up the point: at the moment, you're doing explicit string
interpolation, but there are some special cases that the code above isn't
considering. In particular, what happens if one of the names that get
entered looks like:
"D'Artagnan"
Then all of the quotes get unbalanced, and we get a really messed up SQL
statement. *grin*
Most database systems provide a system to automatically do robust
interpolation of values into a statement. Here's your execute(), using
the robust approach:
###
c.execute ("""
INSERT INTO people (name, phone_number, address,
email_address, birthdate)
VALUES
(%s,%s,%s,%s,%s);
""", (Name, PhoneNumber, Address, EmailAddress, BirthDate)
)
###
Not much changes here syntactically, but semanically, this is nicer: the
cursor's execute() statement itself takes the tuple of values, and does
the interpolation itself. Notice that there's no more quotes around the
string values: the execute() will add them in for you.
If you have more questions, please feel free to ask. Good luck to you!
_______________________________________________
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
