Getting started with mysqldb

[Michal Wallace]

On Sat, 16 Sep 2000, Geoffrey L. Wright wrote:

> So in a burst of enthusiasm born of my new found interest in python,
> I've decided to use it to write a few SQL data-loaders for a project
> I'm working on.  After only a little bit of a struggle I've
> (seemingly) managed to get the MySQLdb module compliled and correctly
> located on a RH6.2 box running MySQL MySQL-3.23.23.  After looking
> though some examples, I've managed to write a simple script that
> displays results from my database.  So far so good.
> 
> But now (just to prove that I can) I want to take the all the records
> from one table and insert them in an identictly structured test table.
> But I'm stuck, because I can't seem to concatenate what I _think_ are
> two strings.
> 
> Here's the the relevent code:
> 
> for row in sel_radgroupreply:
>    currentRow = row
>    q_ins_radgroupreply = "INSERT INTO test_radgroupreply (id
> GroupName, Attribute, Value) VALUES %s" % currentRow
>    print q_ins_radgroupreply
> 
> 
> But I get "TypeError: not all arguments converted".  I'm guessing that
> the value of currentRow is not a string, but I'm _very_ new to both
> python, and I don't see another obvious way to do this.  Any hints?

Well, assuming row/currentRow was a result of a cursor.fetchone()
call, row is not a string, but a tuple (like a list, but it can't be
changed)

For example:

>>> import sqlTest # a module I made to define a db connection (dbc)
>>> cur = sqlTest.dbc.cursor()
>>> cur.execute("select * from test_fish")
1L
>>> row = cur.fetchone()
>>> row
(1, 'squid')
>>> row[0]
1
>>> row[1]
'squid'


now, the % operator works on either a string or a tuple:

>>> "%s" % "banana"
'banana'
>>> "%s %s" % ("banana", "mango")
'banana mango'


but if you try to pass in too many or not enough parameters,
you get an error:

>>> "%s" % ("banana", "mango")
Traceback (innermost last):
  File "<stdin>", line 1, in ?
TypeError: not all arguments converted

>>> "%s %s" % "banana"
Traceback (innermost last):
  File "<stdin>", line 1, in ?
TypeError: not enough arguments for format string



You're getting the problem, because, as you said, currentRow
isn't a string... but you can make it a string by doing this:

>>> str(row)
"(1, 'squid')


Here's how I'd re-write your code:

##############

cur = yourMySQLConnection.cursor()

sql = "SELECT id, GroupName, Attribute, Value from first_table"
cur.execute(sql)

for row in cur.fetchall():
    sql = """
        INSERT INTO test_radgroupreply 
               (id, GroupName, Attribute, Value)
        VALUES %s
        """ % str(row)
    cur.execute(sql)

##############

I tend to use "sql", "dbc", "cur", and "row" almost exclusively when
dealing with databases because it seems to keep the code cleaner.  I
always assign sql on a seperate line (or lines) in case I need to
print it out to make sure I've built it correctly, or in case I need
to change it later. Your mileage may vary.. :)

Cheers,

- Michal
------------------------------------------------------------------------
www.manifestation.com  www.sabren.com  www.linkwatcher.com  www.zike.net
------------------------------------------------------------------------