ODBC & DbiData problem

[Waldemar Osuch]

Wedrowiec <anonymous at remailer.net> wrote in message news:<5oonivsud5jrt522bldbevo6nvdf8ktqdp at 4ax.com>...
> I have a problem for experienced programmers.

I do know if I am experienced enough but I have a suggestion.

> The following code
> should copy data between two different databases:
> 
> import dbi,odbc
> conn1=odbc.odbc('mysql'); conn2=odbc.odbc('sqlite')
> c1 = conn1.cursor(); c2 = conn2.cursor()
> 
> c1.execute("SELECT field1, field2  FROM table1")
> rows = c1.fetchall()
> c2.executemany('INSERT INTO table1 (field1=%s,field2=%s)', rows)

Try using sql statements with ODBC parameter placeholders.
For example:
c2.executemany('INSERT INTO table1 (field1,field2) values (?,?)',
rows)
should do the trick.

> c1.close();c1=None; c2.close();c1=None;
> conn1.close();conn1=None; conn2.close();conn2=None

Also you do not have to set cursor and connections to None when you
are finished.  Time to forget the VB way of doing things.
Closing them is enough.


> The problem is: field 'field2' is DATE type and after fetchmany()
> method I got DbiDate object instead of string 'YYYY-MM-DD 
> HH:MM:SS'. So my  code breaks because Python cannot 
> insert such object to another database.
> 
> I know how to convert this object to the correct string
> (http://www.python.org/windows/OdbcHints.html) but it must be done for
> every field in every row. :( So my question is: Is it possible to make
> odbc module to *automatically convert* this field from DbiDate object
> to string? Maybe is there any special parameter to set up? I do not
> want to write special code to convert all such fields. It suppose, it
> can be done more automatically by odbc module, bu I do not know
> how....

Even if the above suggestion did not work and you had to convert rows
it is
not too much work.  One of the ways to do it could be:

conv = lambda x: (x[0],
time.strftime('%Y-%m-%d',time.localtime(x[1])))
rows = map(conv, rows)

One last thing. I believe that odbc module is not maintained actively
anymore.  You probably be better off using native dirvers for mysql
and sqlite.

Waldemar Osuch