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.Claerhout@Creo.com>
>
> Date:
>
> Sunday 11 January 2004 07:25:09 am
>
> To:
>
> duikboot <ad@ad.nl>,
python-list@python.org
>
> Groups:
>
> comp.lang.python
>
>
>
> 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.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[/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.claerhout@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_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[/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.netcom.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]
