Oracle to Mysql (dates) Help please

[duikboot]

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_O racle.connect(" bla/bla")
c_oracle=con_or acle.cursor()

import MySQLdb
my=MySQLdb.Conn ect("localhost" , db="bla")
my_mysql=my.cur sor()
for tabel in tabellen:
print tabel
c_oracle.execut e("select * from %s" % tabel)
a_oracle=c_orac le.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.execut e(ins)
#print ins

a_oracle=c_orac le.fetchone()
file.close()

con_oracle.clos e()

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);

[Dennis Lee Bieber]

Re: Oracle to Mysql (dates) Help please

duikboot fed this fish to the penguins on Sunday 11 January 2004 05:31
am:

[color=blue]
> ins="insert into %s values %s;\n" % (tabel, b)
> #file.write(ins )
> my_mysql.execut e(ins)
> #print ins[/color]

Don't do that.

It appears you are trying to build a string with multiple insert
statements for a single execute call.

If you accept the overhead of single inserts, the execute can do the
parsing and theoretically will properly quote needed fields...

my_mysql.execut e("insert into %s values %s", (tabel, b))


If you really want a single execute, look at the specs for executemany
(you'll need to build a tuple of tuples: ( (tabel1, b1), (tabel2, b2),
...., (tabelN, Bn) ) but the rest looks similar)

--[color=blue]
> =============== =============== =============== =============== == <
> wlfraed@ix.netc om.com | Wulfraed Dennis Lee Bieber KD6MOG <
> wulfraed@dm.net | Bestiaria Support Staff <
> =============== =============== =============== =============== == <
> Bestiaria Home Page: http://www.beastie.dm.net/ <
> Home Page: http://www.dm.net/~wulfraed/ <[/color]

[Dennis Lee Bieber]

Re: Oracle to Mysql (dates) Help please

Dennis Lee Bieber fed this fish to the penguins on Sunday 11 January
2004 11:02 am:

[color=blue]
> (you'll need to build a tuple of tuples: ( (tabel1, b1), (tabel2, b2),
> ..., (tabelN, Bn) ) but the rest looks similar)
>[/color]
Whoops, slight mistake there -- I hadn't quite noticed that the first
term was the relation itself, and each Bx contained all the values for
one row.

Someone else has the more correct variation...

--[color=blue]
> =============== =============== =============== =============== == <
> wlfraed@ix.netc om.com | Wulfraed Dennis Lee Bieber KD6MOG <
> wulfraed@dm.net | Bestiaria Support Staff <
> =============== =============== =============== =============== == <
> Bestiaria Home Page: http://www.beastie.dm.net/ <
> Home Page: http://www.dm.net/~wulfraed/ <[/color]

[duikboot]

Re: Oracle to Mysql (dates) Help please

Sorry, I can't find it. Can you quote it for me, please?
"Dennis Lee Bieber" <wlfraed@ix.net com.com> schreef in bericht
news:p7mdd1-g74.ln1@beastie .ix.netcom.com. ..[color=blue]
> duikboot fed this fish to the penguins on Tuesday 13 January 2004 01:13
> am:
>[color=green]
> >
> >
> > Could you please explain that?[/color]
>
> See the reply[/color]
<mailman.282.10 73834719.12720. python-list@python.org >[color=blue]
> (Pieter Claerhout) -- though according to my documents, the method is
> executemany(), not execute_many().
>
>
> --[color=green]
> > =============== =============== =============== =============== == <
> > wlfraed@ix.netc om.com | Wulfraed Dennis Lee Bieber KD6MOG <
> > wulfraed@dm.net | Bestiaria Support Staff <
> > =============== =============== =============== =============== == <
> > Bestiaria Home Page: http://www.beastie.dm.net/ <
> > Home Page: http://www.dm.net/~wulfraed/ <[/color]
>[/color]

[Dennis Lee Bieber]

Re: Oracle to Mysql (dates) Help please

duikboot fed this fish to the penguins on Wednesday 14 January 2004
01:07 am:
[color=blue]
>
>
> Sorry, I can't find it. Can you quote it for me, please?[/color]

Hopefully without offending anyone... Formatting may be a bit off,
since I'm including the basic headers for completeness...



[color=blue]
> RE: Oracle to Mysql (dates) Help please
>
> From:
>
> Pieter Claerhout <Pieter.Claerho ut@Creo.com>
>
> Date:
>
> Sunday 11 January 2004 07:25:09 am
>
> To:
>
> duikboot <ad@ad.nl>, python-list@python.org
>
> Groups:
>
> comp.lang.pytho n
>
>
>
> no references
>
>
>
>
>
> What your seeing in the insert statement is not a string, but is a[/color]
DateTime[color=blue]
> object, which needs to be converted to the correct representation for[/color]
the[color=blue]
> target database.
>
> Prepared statements are the best option here. With prepared[/color]
statements, the[color=blue]
> data conversion happens automagically.
>
> The code will then look as follows:
>
> ## BEGIN CODE
> import cx_Oracle
> import MySQLdb
>
> tabellen = [ 'machine' ]
>
> connO = cx_Oracle.conne ct( '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_m any(
> '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[/color]
process[color=blue]
> quite a lot.
> - Instead of fetching one record at a time, all records are fetched[/color]
at once.[color=blue]
> - The number of "%s" in the insert statement will depend on the[/color]
number of[color=blue]
> columns in the target table. You could look at the first row of the[/color]
results[color=blue]
> variable to know how many columns there are in the table.
> - The type of placeholders in the SQL statement depend on the[/color]
database.[color=blue]
>
> 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.claerhou t@creo.com | www.creo.com
>
> IMAGINE CREATE BELIEVE(tm)
>
>
> -----Original Message-----
> From: duikboot [mailto:ad@ad.nl]
> Sent: 11 January 2004 14:32
> To: python-list@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[/color]
goes[color=blue]
> 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[/color]
generates.[color=blue]
> Could anyone please help me?
>
> Thanks,
>
> Arjen
>
> ####Code####
>
> import cx_Oracle
> tabellen=["machine"]
> con_oracle=cx_O racle.connect(" bla/bla")
> c_oracle=con_or acle.cursor()
>
> import MySQLdb
> my=MySQLdb.Conn ect("localhost" , db="bla")
> my_mysql=my.cur sor()
> for tabel in tabellen:
> print tabel
> c_oracle.execut e("select * from %s" % tabel)
> a_oracle=c_orac le.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.execut e(ins)
> #print ins
>
> a_oracle=c_orac le.fetchone()
> file.close()
>
> con_oracle.clos e()
>
> my.close()
>
>
> ##insert statement###
>
> insert into machine values ('230KM', ' ', '230KM', 1980-01-01[/color]
00:00:00,[color=blue]
> 2035-01-01 00:00:00, 1, 100, 'asap', 'NO', 0, 0, 'corrugator',[/color]
2003-12-04[color=blue]
> 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
>
>[/color]

--[color=blue]
> =============== =============== =============== =============== == <
> wlfraed@ix.netc om.com | Wulfraed Dennis Lee Bieber KD6MOG <
> wulfraed@dm.net | Bestiaria Support Staff <
> =============== =============== =============== =============== == <
> Bestiaria Home Page: http://www.beastie.dm.net/ <
> Home Page: http://www.dm.net/~wulfraed/ <[/color]

[duikboot]

Re: Oracle to Mysql (dates) Help please

Thank you all very much for your help.

I'll think it will work now (don't know yet, I'll work on it later this
week)


Cheers Arjen
(If you're interested, I will post the solution that worked for me)