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.
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:
首先要做的是连接到数据库。 在这种情况下，您可能有一个用户和一个密码。 另外，您应该具有数据源名称。 有了这些，您可以连接到数据库：
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.
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:
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().
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.
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:
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.
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.
I hope this post helps people working with Oracle Databases and Python to get things working faster.
Thank you for reading!