如何将解析的数据从Python导出到SQL Developer中的Oracle表? [英] How to export parsed data from Python to an Oracle table in SQL Developer?
问题描述
我已经使用Python来解析txt文件以获取特定的信息(日期,金额,磅等),现在我想将数据导出到我在SQL Developer中创建的Oracle表。
I have used Python to parse a txt file for specific information (dates, $ amounts, lbs, etc) and now I want to export that data to an Oracle table that I made in SQL Developer.
我已经使用cx_Oracle模块成功地将Python与Oracle连接起来,但是我很难从Python导出甚至打印任何数据到我的数据库。
I have successfully connected Python to Oracle with the cx_Oracle module, but I am struggling to export or even print any data to my database from Python.
我不熟练使用SQL,我知道简单的查询,就是这样。我已经探索了Oracle文档,并没有找到直接的导出命令。通过Python将数据导出到Oracle表时,我将使用Python代码或SQL代码?例如,导入CSV文件是否相同?
I am not proficient at using SQL, I know of simple queries and that's about it. I have explored the Oracle docs and haven't found straightforward export commands. When exporting data to an Oracle table via Python is it Python code I am going to be using or SQL code? Is it the same as importing a CSV file, for example?
我想了解如何从Python写Oracle表;我需要解析和导出非常大量的数据,因此这不会是一次导出/导入。我也希望有一种方法来预览我的导入,以确保它与我已经创建的Oracle表正确对齐,或者如果存在足够的简单的撤消操作。
I would like to understand how to write to an Oracle table from Python; I need to parse and export a very large amount of data so this won't be a one time export/import. I would also ideally like to have a way to preview my import to ensure it aligns correctly with my already created Oracle table, or if a simple undo action exists that would suffice.
如果我的问题不清楚,我很乐意澄清。感谢所有帮助。
If my problem is unclear I am more than happy to clarify it. Thanks for all help.
我的代码到目前为止:
import cx_Oracle
dsnStr = cx_Oracle.makedsn("sole.wh.whoi.edu", "1526", "sole")
con = cx_Oracle.connect(user="myusername", password="mypassword", dsn=dsnStr)
print (con.version)
#imp 'Book1.csv' [this didn't work]
cursor = con.cursor()
print (cursor)
con.close()
推荐答案
从使用CX_Oracle& Python 2.7 你可以看到总体规划。
所以如果你已经将数据解析成csv,你可以轻松地做到:
From Import a CSV file into Oracle using CX_Oracle & Python 2.7 you can see overall plan. So if you already parsed data into csv you can easily do it like:
import cx_Oracle
import csv
dsnStr = cx_Oracle.makedsn("sole.wh.whoi.edu", "1526", "sole")
con = cx_Oracle.connect(user="myusername", password="mypassword", dsn=dsnStr)
print (con.version)
#imp 'Book1.csv' [this didn't work]
cursor = con.cursor()
print (cursor)
text_sql = '''
INSERT INTO tablename (firstfield, secondfield) VALUES(:1,:2)
'''
my_file = 'C:\CSVData\Book1.csv'
cr = csv.reader(open(my_file,"rb"))
for row in cr:
print row
cursor.execute(text_sql, row)
print 'Imported'
con.close()
这篇关于如何将解析的数据从Python导出到SQL Developer中的Oracle表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!