Oracle to Mysql (dates) Help please
Pieter Claerhout
Pieter.Claerhout at Creo.com
Sun Jan 11 10:25:09 EST 2004
What your seeing in the insert statement is not a string, but is a DateTime
object, which needs to be converted to the correct representation for the
target database.
Prepared statements are the best option here. With prepared statements, the
data conversion happens automagically.
The code will then look as follows:
## BEGIN CODE
import cx_Oracle
import MySQLdb
tabellen = [ 'machine' ]
connO = cx_Oracle.connect( 'bla/bla' )
cursO = connO.cursor()
connM = MySQLdb.Connect( 'localhost', db='bla' )
cursM = connM.cursor()
for tabel in tabellen:
print tabel
cursO.execute( 'select * from ' + tabel )
results = cursO.fetchall()
cursM.execute_many(
'insert into ' + tabel + ' values ( %s,%s,%s,%s,%s )',
results
)
# END CODE
A few notes:
- This uses the execute_many function which will speed up the insert process
quite a lot.
- Instead of fetching one record at a time, all records are fetched at once.
- The number of "%s" in the insert statement will depend on the number of
columns in the target table. You could look at the first row of the results
variable to know how many columns there are in the table.
- The type of placeholders in the SQL statement depend on the database.
More info on execute_many and other can be found on:
http://www.python.org/peps/pep-0249.html (look for paramstyle and
execute_many).
Cheers,
pieter
Creo
pieter claerhout | product support prinergy | tel: +32 2 352 2511 |
pieter.claerhout at creo.com | www.creo.com
IMAGINE CREATE BELIEVE(tm)
-----Original Message-----
From: duikboot [mailto:ad at ad.nl]
Sent: 11 January 2004 14:32
To: python-list at python.org
Subject: Oracle to Mysql (dates) Help please
Hi all,
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
####Code####
import cx_Oracle
tabellen=["machine"]
con_oracle=cx_Oracle.connect("bla/bla")
c_oracle=con_oracle.cursor()
import MySQLdb
my=MySQLdb.Connect("localhost", db="bla")
my_mysql=my.cursor()
for tabel in tabellen:
print tabel
c_oracle.execute("select * from %s" % tabel)
a_oracle=c_oracle.fetchone()
#file=open("%s.sql" % tabel, 'w')
while a_oracle != None:
b=str(a_oracle)
ins="insert into %s values %s;\n" % (tabel, b)
#file.write(ins)
my_mysql.execute(ins)
#print ins
a_oracle=c_oracle.fetchone()
file.close()
con_oracle.close()
my.close()
##insert statement###
insert into machine values ('230KM', ' ', '230KM', 1980-01-01 00:00:00,
2035-01-01 00:00:00, 1, 100, 'asap', 'NO', 0, 0, 'corrugator', 2003-12-04
06:00:00, 1970-01-01 01:00:00, ' ', 'normal', 0.0, 0.0, 7, ' ', ' ',
'normal', ' ', ' ', 'A', 2003-12-04 09:42:14, 82766);
--
http://mail.python.org/mailman/listinfo/python-list
More information about the Python-list
mailing list