- 1 year ago
Connecting Python 3.x to Oracle DB
Comment
1 / 1
To connect Python 3.x to an Oracle database, you can use the cx_Oracle
module, which is a popular Oracle Database interface for Python. Here's a step-by-step guide to help you get started:
- Install cx_Oracle:
You need to install thecx_Oracle
package if you haven't already. You can install it using pip:
bashpip install cx_Oracle
- Import the Module:
Import thecx_Oracle
module in your Python script:
pythonimport cx_Oracle
- Establish a Connection:
Create a connection to your Oracle database by providing the connection details like username, password, host, port, and service name or SID:
python# Connection details
username = 'your_username'
password = 'your_password'
host = 'your_host'
port = 'your_port'
service_name = 'your_service_name'
# Establish the connection
connection = cx_Oracle.connect(username, password, f"{host}:{port}/{service_name}")
Replace the placeholders (your_username
, your_password
, your_host
, your_port
, your_service_name
) with your actual Oracle database connection details.
- Create a Cursor:
A cursor is used to execute SQL statements and fetch results from the database. Create a cursor from the connection:
pythoncursor = connection.cursor()
- Execute SQL Statements:
You can execute SQL statements using the cursor'sexecute()
method:
pythonsql_query = "SELECT * FROM your_table"
cursor.execute(sql_query)
- Fetch Data:
After executing a query, you can fetch the data using methods likefetchone()
,fetchall()
, orfetchmany()
:
python# Fetch all rows
rows = cursor.fetchall()
for row in rows:
print(row)
- Close Cursor and Connection:
Once you're done working with the cursor and fetching data, don't forget to close both the cursor and the connection:
pythoncursor.close()
connection.close()
Here's the complete example:
pythonimport cx_Oracle
# Connection details
username = 'your_username'
password = 'your_password'
host = 'your_host'
port = 'your_port'
service_name = 'your_service_name'
# Establish the connection
connection = cx_Oracle.connect(username, password, f"{host}:{port}/{service_name}")
# Create a cursor
cursor = connection.cursor()
# Execute a query
sql_query = "SELECT * FROM your_table"
cursor.execute(sql_query)
# Fetch and print data
rows = cursor.fetchall()
for row in rows:
print(row)
# Close cursor and connection
cursor.close()
connection.close()
Make sure to replace the placeholders with your actual Oracle database connection details and query. This example demonstrates the basic steps to connect to an Oracle database and fetch data using Python.