[DB-SIG] Oracle to Mysql (dates) Help please

Andy Todd andy47 at halfcooked.com
Mon Jan 12 05:01:22 EST 2004

arjen.dijkstra at hccnet.nl wrote:
> Hi all,
> I'm posting to this sig for the first time, i hope I'm doing this right.

You are asking your questions in the right place.

> I'm trying to export a view tables from a Oracle database to a Mysql
> database.  I create insert statements (they look alright), but it all goes
> wrong when I try to execute them in Mysql, because the dates must have
> quotes on each side.
> I just don't know how make the dates right.
> Well I'll just show you the code and some insert statements it generates.
> Could anyone please help me?
> Thanks,
> Arjen
[snip sample code]

The simple answer is because you are converting all of your results to 
strings when selecting them from Oracle you have to convert them to the 
appropriate types before you insert them into MySQL. In particular date 
and time objects need to be handled (and converted) with care.

Its a tricky subject and one not easily covered in a short email. The 
essence is that databases are quite strict on what they accept from 
client programs to store in their tables. If you specified that a column 
is a positive integer with no more than 2 digits then the database will 
reject anything over 99 and below 0. Pythons types and objects, on the 
other hand, are generally a lot more magnanimous.

To aid the developer of programs which access databases the DB-API and 
the people who write the DB modules provide a means for adapting the 
values you have in your Python program to the correct types for 
insertion into the database. For instance, when inserting a date into an 
Oracle database using cx_Oracle you use the function Date, e.g.;

 >>> today=cx_Oracle.Date(2004,1,12)

And then use the result in your insert, e.g.

 >>> c_oracle.execute("INSERT INTO my_table (date_column) VALUES 
(:today)", {'today':today})

To start to get to grips with these issues, your best bet is to have a 
quick read of the DB-API ( http://www.python.org/peps/pep-0249.html ) 
and then the documentation of the various modules you are using.

 From the desk of Andrew J Todd esq - http://www.halfcooked.com/

More information about the DB-SIG mailing list