Recently I’ve been working a lot with Oracle Databases and Python. Although Oracle is a big company, I found there’s not such a strong community to help with their package in Python for dealing with databases, cx_Oracle, and I found their documentation not that great.

最近,我一直在与Oracle数据库和Python一起工作。 尽管Oracle是一家大公司,但我发现并没有一个强大的社区来帮助他们使用Python处理数据库cx_Oracle的软件包,而且我发现他们的文档不是很好。

So, I decided to do this post following my philosophy of writing posts that the Thiago (me) of 1 month ago would have thanked for.


1.连接到数据库 (1. Connect to the database)

The first thing to do is to connect to your database. In this case, probably you have an user and a password. Also, you should have the data source name. With all of those in hand you can connect to the database:

首先要做的是连接到数据库。 在这种情况下,您可能有一个用户和一个密码。 另外,您应该具有数据源名称。 有了这些,您可以连接到数据库:

Easy peasy!


2.执行SQL命令 (2. Execute SQL Commands)

With cx_Oracle you can execute any Oracle SQL command, selects, inserts, updates etc. In order to execute the commands, you have to create a cursor. You can use the same cursor for running several commands. I just recommend you to use a connection and a cursor for each function in your application. So, you create a connection and a cursor in the beginning of your function and close them at the end.

使用cx_Oracle,您可以执行任何Oracle SQL命令,选择,插入,更新等。为了执行命令,必须创建一个游标。 您可以使用同一光标运行多个命令。 我只建议您为应用程序中的每个功能使用连接和游标。 因此,您可以在函数的开头创建一个连接和一个游标,然后在结尾处将其关闭。

2.1选择语句 (2.1 Select Statements)

It is important to mention here that the fetchone() and fetchall() methods only work when you’re executing a “select”. It does not make sense to use those methods when executing a insert or update statement. Also, it is important to note that, if your query is really big, fetchall() may be really slow, as it loads the queried data in memory.

重要的是在这里提到fetchone ()fetchall()方法仅在执行“选择”时起作用。 在执行插入或更新语句时使用这些方法没有任何意义。 另外,请务必注意,如果您的查询很大,则fetchall() 可能真的很慢,因为它将查询的数据加载到内存中。

2.2插入语句 (2.2 Insert Statements)

If you want to insert data from your machine into a table in your database, follow a similar process:


Also, if you want to insert several rows, instead of running execute() several times, it is better to use executemany():

另外,如果要插入几行,而不是多次运行execute() ,则最好使用executemany ():

This code requires only one round-trip from the client to the database instead of the five round-trips that would be required for repeated calls to execute(). For very large data sets there may be an external buffer or network limits to how many rows can be processed, so repeated calls to executemany() may be required. The limits are based on both the number of rows being processed as well as the “size” of each row that is being processed. Repeated calls to executemany() are still better than repeated calls to execute().

此代码仅需要从客户端到数据库的一次往返 ,而不是重复调用execute()所需的五次往返。 对于非常大的数据集,可能存在外部缓冲区或网络限制,以限制可以处理多少行,因此可能需要重复调​​用executemany () 。 限制基于要处理的行数以及要处理的每一行的“大小”。 重复调用executemany ()仍然比重复调用execute()好

Now, if you’re inserting the result of a select statement, it is better to make a single statement and execute it with execute() then to execute a select statement, fetchall() and then insert with executemany(). This happens to be true especially if the result from the select statement is big.

现在,如果要插入选择语句的结果,最好制作一条语句并使用execute()执行它 然后执行一条select语句, fetchall() ,然后使用executemany()插入。 特别是在select语句的结果很大的情况下,这确实是正确的。

3.使用参数 (3. Using Parameters)

A very important feature is to be able to use parameters in your SQL commands. cx_Oracle provides a simple way of doing such a thing:

一个非常重要的功能是能够在SQL命令中使用参数。 cx_Oracle提供了一种执行此操作的简单方法:

There are other ways of passing parameters to SQL commands in cx_Oracle but I think this is the best one, so I’ll keep with it.


4.使用cx_Oracle进行多处理 (4. Multiprocessing with cx_Oracle)

When I was working with cx_Oracle, this was certainly the hardest thing to make work. In my problem, I wanted to make several insertions in several tables and I wanted to be as fast as possible. Also, the order of the insertions wasn’t important, so it is suitable to use multiprocessing.

当我使用cx_Oracle时,这无疑是最难的工作。 在我的问题中,我想在多个表中进行几次插入,并且希望尽快。 另外,插入顺序也不重要,因此适合使用多重处理。

The main thing here is to create the connection inside the process. Each of the workers in the process pool needs to have its own connection which it established itself.

这里最主要的是在流程内部创建连接。 在每一个进程池中需要工人有自己的连接,它确立了自己

Keeping that in mind it is pretty straight forward to use multiprocessing with cx_Oracle.


结论 (Conclusion)

I really don’t think I’ve shown complex stuff here. I kind of struggled to make my application work because there are not many questions on Stackoverflow about cx_Oracle and I don’t think cx_Oracle’s documentation is the best one out there.

我真的不认为我在这里展示了复杂的东西。 我很难使我的应用程序正常工作,因为关于StackOverflow的关于cx_Oracle的问题不多,而且我不认为cx_Oracle的文档是那里最好的文档。

I hope this post helps people working with Oracle Databases and Python to get things working faster.


You can check more about what I’m doing by visiting my GitHub and get in touch through LinkedIn.


Thank you for reading!




  • 0
  • 1
    觉得还不错? 一键收藏
  • 0


  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助




当前余额3.43前往充值 >
领取后你会自动成为博主和红包主的粉丝 规则
钱包余额 0