a newbie question about gadfly

[Steve Holden]

<jm7potter at hotmail.com> wrote in message
news:pn7fitoa7n6u47atng7tv0n9aroc1716ic at 4ax.com...
> On 13 Jun 2001 14:30:51 GMT, gbreed at cix.compulink.co.uk wrote:
>
> >In article <vmoeit4n499fujuj5ine300ial3l94p81l at 4ax.com>,
> >jm7potter at hotmail.com () wrote:
> >
> >> On 12 Jun 2001 15:34:16 GMT, gbreed at cix.compulink.co.uk wrote:
> >>
> >> >In article <hrbcit86g5gbmc63em9gc6tsji7nv78dqr at 4ax.com>,
> >> >jm7potter at hotmail.com () wrote:
> >> >
> >> >> Why? is namex not a direct replacement for "donna" ????
> >> >
> >> >Um, no, not the way you're doing it.  Try replacing that
> >magic
> >> >line with
> >> >
> >> >
> >> >Or even
> >> >
> >> >cursor.execute("insert into students (name, grade) values
> >> >(%(namex)s, %(gradex)s)" % vars())
> >> >
> >> >(that's sure to wordwrap!)
> >> >
> >> >
> >> >                 Graham
> >>
> >>
> >> Thanks for the help Graham,
> >>
> >> However, your code did not work either. The little program
> >chokes every
> >> time I try to
> >> do anything that is not "hard-wired" into the script.
> >
> >Oops!  There is an error:
> >
> >cursor.execute(
> >  "insert into students (name, grade) values ('%s', '%s')"
> >  % (namex, gradex))
> >
> >might be better.  I forgot to quote the arguments, and at least
> >one of them is a string.
> >
>
> It Works?!?!! It is alive!!
>
> Now, if you have the time and inclination --- could you explain why your
first code
> failed and the second worked? (I hate magic)
>
> And further, could you explain to a newbie why *my* code failed?
>
While Graham relaxes after his efforts, allow me to dive and and offer the
explanations you seek. [c.l.py is like this...]. Taking them in reverse
order:

1. Your original problem was why the following didn't work:

namex = 'donna'
gradex = 'grade 2'
cursor.execute("insert into students (name, grade) values (namex, gradex)")

If you had put a print statement in there, you would have seen that the
statement you were trying to execute was, quite literally

    insert into students (name, grade) values (namex, gradex)

In other words, simply using a variable name inside a string isn't enough to
get its contents into the string. Perhaps you are a Perl user who is used to
their language replacing variable names (easily spotted becuase they begin
with a dollar sign) automatically substituted in strings. The question then
becomes "How do I build a valid SQL statement with the contents of the
variables in them?"

There are several ways. You could have tried:

cursor.execute("insert into students (name, grade) values ('" + namex + "',
'" + gradex+ "')"

This expression concatenates several strings, some of which are literals and
some of which are variables bound to string values. Note that you needed to
literally include the single quotes which delimit string values in SQL.

Another way would have used the % string substitution operator, where you
could have written

cursor.execute("insert into students (name, grade) values ('%s', '%s')" %
(namex, gradex))

In this case Python would have used the string to the left of the % operator
as a format string, and eaech time it met a "%s" in the format it would
substitute the next value from the tuple to the right of the %. There are
many other format substitutions, and people are usually pointed at the C
language's printf() function for a reference. I'm sure you can find them.

2. Graham's original suggestion (with proper continuation markup) was

cursor.execute("insert into students (name, grade) values" \
"(%(namex)s, %(gradex)s)" % vars())

Note that when Python sees two string literals in juxtaposition this way it
concatenates them automatically. This is a slightly different attempt at
formatting: this time there is a dictionary (the dictionary of current
variables) on the right of the %, and the format codes give the name of the
required dictionary entry between the parentheses. Given the variable values
you had, this would try to execute the SQL statement

    insert into students (name, grade) values(donna, grade 2)

The problme with this version was Graham forgetting that SQL requires single
quotes around the string values. So his final version put those in, giving
you the statement

    insert into students (name, grade) values('donna', 'grade 2')

Note also that it is really simpler and more efficient to have the SQL
cursor do the value substitution, which I think Graham may have suggested as
an alternative (or was it another poster?). In this case you mark the points
of substitution in your SQL statement with question marks (this is the
simplest style: the DB API allows five different kinds) and then give a
tuple of values as a second argument to execute(). So you could have written

    cursor.execute("insert into students (name, grade) values(?, ?)",
(namex, gradex))

and the cursor would have performed the substitution - including adding the
required quotes around the string values, and excaping any single quotes
that might have crept into the values to ensure that the result SQL
statement was well-formed.

Anyway, be that all as it may, congratulations on succeeding in writing your
first Python database program. I am sure it won't be your last.

regards
 Steve

http://www.holdenweb.com/