从 oracle 数据库获取数据作为 CSV 文件（或任何其他自定义文本格式）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/65447/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting data from an oracle database as a CSV file (or any other custom text format)
提问by dreeves
A sample perl script that connects to an oracle database, does a simple SELECT query, and spits the results to stdout in CSV format would be great. Python or any other language available in a typical unix distribution would be fine too.
一个连接到 oracle 数据库、执行简单 SELECT 查询并将结果以 CSV 格式输出到 stdout 的示例 perl 脚本会很棒。Python 或在典型的 unix 发行版中可用的任何其他语言也可以。
Note that I'm starting from scratch with nothing but a username/password for a remote Oracle database. Is there more to this than just having the right oracle connection library?
请注意，我从头开始，只使用远程 Oracle 数据库的用户名/密码。除了拥有正确的 oracle 连接库之外，还有更多吗？
If there's a way to do this directly in mathematica, that would be ideal (presumably it should be possible with J/Link (mathematica's java integration thingy)).
如果有一种方法可以直接在 mathematica 中执行此操作，那将是理想的（大概 J/Link 应该可以实现（mathematica 的 java 集成））。
采纳答案by Jumpy
In perl you could do something like this, leaving out all the my local variable declarations and ... or die "failmessage" error handling for brevity.
在 perl 中，你可以做这样的事情，省略我所有的局部变量声明和......或者为了简洁而死“失败消息”错误处理。
use DBI;
use DBD::Oracle;
$dbh = DBI->connect( "dbi:Oracle:host=127.0.0.1;sid=XE", "username", "password" );
# some settings that you usually want for oracle 10
$dbh->{LongReadLen} = 65535;
$dbh->{PrintError} = 0;
$sth = $dbh->prepare("SELECT * FROM PEOPLE");
$sth->execute();
# one example for error handling just to show how it's done in principle
if ( $dbh->err() ) { die $dbh->errstr(); }
# you can also do other types of fetchrow, see perldoc DBI
while ( $arrayref = $sth->fetchrow_arrayref ) {
print join ";", @$arrayref;
print "\n";
}
$dbh->disconnect();
Two notes, because people asked in comments:
两个注释，因为人们在评论中问：
- sid=XE is the oracle service id, that is like the name of your database. If you install the free version of oracle, it defaults to "XE", but you can change it.
- Installing DBD::Oracle needs the oracle client libraries on your system. Installing that will also set all the necessary environment variables.
- sid=XE 是 oracle 服务 id，就像你的数据库名称。如果您安装免费版的 oracle，它默认为“XE”，但您可以更改它。
- 安装 DBD::Oracle 需要系统上的 oracle 客户端库。安装它也将设置所有必要的环境变量。
回答by
How about something as simple as creating the file from sqlplus...
像从 sqlplus 创建文件这样简单的事情怎么样？
set echo off heading off feedback off colsep ,;
spool file.csv;
select owner, table_name
from all_tables;
spool off;
回答by Galuvian
Here is an implementation in Python:
这是在 Python 中的一个实现：
import cx_Oracle, csv
orcl = cx_Oracle.connect('ohd/john@ohddb')
curs = orcl.cursor()
csv_file_dest = "C:\test.csv"
output = csv.writer(open(csv_file_dest,'wb'))
sql = "select * from parameter"
curs.execute(sql)
headers_printed = False
for row_data in curs:
if not headers_printed:
cols = []
for col in curs.description:
cols.append(col[0])
output.writerow(cols)
headers_printed = True
output.writerow(row_data)
回答by jfklein
As dreeves says, DatabaseLink makes this trivial. The part I don't know is the details of the JDBC declaration. But here's how things look for MySQL:
正如dreeves 所说，DatabaseLink 使这变得微不足道。我不知道的部分是JDBC声明的细节。但这里是 MySQL 的样子：
Then from within Mathematica:
然后从 Mathematica 中：
Needs["DatabaseLink`"]
conn = OpenSQLConnection[JDBC["mysql","hostname/dbname"], Username->"user", Password->"secret"]
Export["file.csv", SQLSelect[conn, "MyTable"]]
You could of course assign the SQLSelect to a variable first and examine it. It will be a list of lists holding the table data. You can pass conditions to SQLSelect, see the documentation for that (e.g. SQLColumn["Name"]=="joeuser").
您当然可以先将 SQLSelect 分配给一个变量并检查它。它将是一个包含表数据的列表列表。您可以将条件传递给 SQLSelect，请参阅相关文档（例如 SQLColumn["Name"]=="joeuser"）。
The only thing Oracle-specific here is how you make the connection, in the JDBC expression. It is probably something like JDBC["oracle", "hostname/dbname"].
这里唯一特定于 Oracle 的是您如何在 JDBC 表达式中建立连接。它可能类似于 JDBC["oracle", "hostname/dbname"]。
回答by dreeves
Mathematica has a package "DatabaseLink" built in that should make this easy but you need to find a driver for Oracle. Installing the "oracle client libraries" should do that...
Mathematica 有一个内置的包“DatabaseLink”，它应该可以使这变得容易，但您需要为 Oracle 找到一个驱动程序。安装“oracle 客户端库”应该这样做......
回答by Sten Vesterli
Get Oracle Application Express. It's a browser-based tool that comes free with the database. It allows you to quickly click together reports and specify CSV (or Excel) as output format. (You can also use it to build complete applications).
获取 Oracle Application Express。它是一个基于浏览器的工具，随数据库免费提供。它允许您快速单击报告并指定 CSV（或 Excel）作为输出格式。（您也可以使用它来构建完整的应用程序）。
You find tons of documentation, demos etc. here: http://apex.oracle.com
您可以在此处找到大量文档、演示等：http: //apex.oracle.com
You can also download the tool at this URL, or you can register for a free workspace and play around with the tool on an Oracle server.
您还可以从该 URL 下载该工具，或者您可以注册一个免费工作区并在 Oracle 服务器上试用该工具。
回答by Mike McAllister
I'm not a PERL programmer, but here's a little extra feature you might want to investigate. Have a look at the concept of external tables in Oracle. You create a table with a definition of something similar to the following:-
我不是 PERL 程序员，但这里有一些您可能想要研究的额外功能。看看 Oracle 中外部表的概念。您创建一个表，其定义类似于以下内容：-
CREATE TABLE MY_TABLE ( COL1 NUMBER(2), COL2 VARCHAR2(20 BYTE) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY SOME_DIRECTORY_NAME ACCESS PARAMETERS ( FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL ) LOCATION (SOME_DIRECTORY_NAME:'my_file.csv') ) REJECT LIMIT UNLIMITED;
CREATE TABLE MY_TABLE ( COL1 NUMBER(2), COL2 VARCHAR2(20 BYTE) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY SOME_DIRECTORY_NAME ACCESS PARAMETERS ( FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL ) LOCATION (SOME_DIRECTORY_NAME:'my_file.csv') ) REJECT LIMIT UNLIMITED;
Note this DDL statement assumes you have a directory already created called "SOME_DIRECTORY_NAME". You can then issue DML commands to get data into or out of this table, and once the commit has been done, the data is all nice and neat in your file my_file.csv. After that, do your PERL magic to get the file wherever you want it to be.
请注意，此 DDL 语句假定您已经创建了一个名为“SOME_DIRECTORY_NAME”的目录。然后，您可以发出 DML 命令以将数据导入或导出该表，并且一旦提交完成，您的文件 my_file.csv 中的数据就会全部完好无损。之后，用你的 PERL 魔法把文件放到任何你想要的地方。
