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); 5 3457
duikboot fed this fish to the penguins on Sunday 11 January 2004 05:31
am: ins="insert into %s values %s;\n" % (tabel, b) #file.write(ins) my_mysql.execute(ins) #print ins
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.execute("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)
-- ================================================== ============ < wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG < wu******@dm.net | Bestiaria Support Staff < ================================================== ============ < Bestiaria Home Page: http://www.beastie.dm.net/ < Home Page: http://www.dm.net/~wulfraed/ <
Dennis Lee Bieber fed this fish to the penguins on Sunday 11 January
2004 11:02 am: (you'll need to build a tuple of tuples: ( (tabel1, b1), (tabel2, b2), ..., (tabelN, Bn) ) but the rest looks similar)
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...
-- ================================================== ============ < wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG < wu******@dm.net | Bestiaria Support Staff < ================================================== ============ < Bestiaria Home Page: http://www.beastie.dm.net/ < Home Page: http://www.dm.net/~wulfraed/ <
Sorry, I can't find it. Can you quote it for me, please?
"Dennis Lee Bieber" <wl*****@ix.netcom.com> schreef in bericht
news:p7************@beastie.ix.netcom.com... duikboot fed this fish to the penguins on Tuesday 13 January 2004 01:13 am:
Could you please explain that? See the reply
<ma**************************************@python.o rg> (Pieter Claerhout) -- though according to my documents, the method is executemany(), not execute_many().
-- > ================================================== ============ < > wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG < > wu******@dm.net | Bestiaria Support Staff < > ================================================== ============ < > Bestiaria Home Page: http://www.beastie.dm.net/ < > Home Page: http://www.dm.net/~wulfraed/ <
duikboot fed this fish to the penguins on Wednesday 14 January 2004
01:07 am:
Sorry, I can't find it. Can you quote it for me, please?
Hopefully without offending anyone... Formatting may be a bit off,
since I'm including the basic headers for completeness...
RE: Oracle to Mysql (dates) Help please
From:
Pieter Claerhout <Pi**************@Creo.com>
Date:
Sunday 11 January 2004 07:25:09 am
To:
duikboot <ad@ad.nl>, py*********@python.org
Groups:
comp.lang.python no references
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 | pi**************@creo.com | www.creo.com
IMAGINE CREATE BELIEVE(tm)
-----Original Message----- From: duikboot [mailto:ad@ad.nl] Sent: 11 January 2004 14:32 To: py*********@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
-- ================================================== ============ < wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG < wu******@dm.net | Bestiaria Support Staff < ================================================== ============ < Bestiaria Home Page: http://www.beastie.dm.net/ < Home Page: http://www.dm.net/~wulfraed/ <
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) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Mark Wilson CPU |
last post by:
A colleague has written a prototype program in PHP, using a MySQL
database.
It's a relatively simple app, with a restricted set of mysql commands
used (see below). The MySQL DB is being replaced...
|
by: JWM |
last post by:
I am trying to implement Oracle connection pooling for the following code,
which was written by someone else. Here is my main question -- this java
file creates code that is executed every hour,...
|
by: Nath |
last post by:
Hello,
I use to work with Oracle for several years. Now, I must work with MySql.
I would like to know, if possible, how to "transfer" this command from
oracle to a new command mysql:
...
|
by: jonathan |
last post by:
hey all,
I realize that this question might pop up from time to time, but I
haven't seen it a while and things might of changed, so -
Right now (July 2004) how does mysql stand up in...
|
by: Cern |
last post by:
Is it somebody out there who has made a migration from an Oracle server to an MySQL server??
The scenario is as simply:
I've got a Oracle 8 server with a database with content that I want to...
|
by: Jan Olsen |
last post by:
We're planning an SQL-based solution, possibly mySQL for Windows or
Linux. But some in the organization might favor Oracle.
Are there any particular pitfalls of using mySQL instead of Oracle?
...
|
by: Jaye |
last post by:
Hi. I was wondering if anyone knows how to convert dates in the SAS format into an Oracle date format without the use of third party software. I'd like to be able to run a procedure that would...
|
by: --CELKO-- |
last post by:
I need to convert a bunch of DB2 triggers to Oracle. Is there any
kind of tools for this?
|
by: amitpatel66 |
last post by:
Hi All,
Find below some useful information about Time Zone Conversion in oracle. Hope this would be helpful for many of them since all the real time projects that we work in follow different time...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |