[Tutor] MySQLdb INSERT with while or for loop

[Danny Yoo]

On Wed, 11 Jun 2003, Decibels wrote:

> Getting closer. I have changed it from a loop to just put the symbol
> string in. But I want it to be a variable. Can't seem to get mysql to
> take a variable. There must be a 'symbol' or something like @ or % that
> is want, otherwise it will just put the item in as is. Example:
>
>
> value=(stock[0])
> print "Value is %s" % value
> cursor.execute("""
> 	INSERT INTO stockinfo (symbol)
> 	VALUES('@value')
> 	""")
>
> Just puts '@value' in the table column. Tried other ways, must be
> missing something. When I print the value in the second line is says
> what stock[0] is, just can't get it into the column.


Hi Decibels,


Very close: prepared statement syntax in Python looks a little different.
Try:

###
cursor.execute("""INSERT INTO stockinfo (symbol)
                  VALUES(%s)""", (stock[0]))
###

For more information, you can look at:

    http://www.amk.ca/python/writing/DB-API.html


(Note that, in AMK's example, he's using '?' to specify parameter values.
However, I'm not sure if all Python API 2.0 database connectors support
'?'; in particular, MySQLdb uses the 'format' paramstyle.  It's a point of
incompatibility that's very annoying; I hope the DB Special Interest Group
unifies this soon!)




> > > Does anyone have an idea how to use a FOR or WHILE loop to INSERT data
> > > into a Table? If it needs more explaination, then pointing me to a doc
> > > would be great. I am checking the MySQL site docs and nothing so far.
> > >
> > > cursor.execute ("""
> > >            INSERT INTO animal (name, category)
> > >            VALUES
> > >                ('snake', 'reptile'),
> > >                ('frog', 'amphibian'),
> > >                ('tuna', 'fish'),
> > >                ('racoon', 'mammal')
> > >        """)



Hmmmm... Good question!  In the case of batch updating, we probably want
to use the executemany() method.


So instead of:

###
animal_categories = [['snake', 'reptile'],
                     ['frog', 'amphibian'],
                     ['tuna', 'fish'],
                     ['racoon', 'mammal']]
for name, category in animal_categories:
    cursor.execute('''insert into animal (name, category)
                      values (%s, %s)''',
                   (name, category))
cursor.commit()
###



We can be more direct and say:

###
animal_categories = [['snake', 'reptile'],
                     ['frog', 'amphibian'],
                     ['tuna', 'fish'],
                     ['racoon', 'mammal']]
cursor.executemany('''insert into animal (name, category)
                      values (%s, %s)''', animal_categories)
###



It might be slightly amusing to compare the way Python supports this with
another popular language. Java's JDBC 2.0 API supports batches of inserts
with its 'addBatch()' method.

   http://java.sun.com/docs/books/tutorial/jdbc/jdbc2dot0/batchupdates.html

But, subjectively speaking, I don't think it looks that much better than
if we had coded an explicit loop.  *grin*



The Python DB API spec is a little terse, but it's still very useful to go
through its methods to get its overview.  Here's a link to the API:

    http://python.org/peps/pep-0249.html


Good luck to you!