SoFunction
Updated on 2024-11-07

How to use pycharm connection to read tables from orcl databases

Background: Job requirements
Requirement: use pycharm to access a table in a remote oracle type database that contains lob fields (this is also a pitfall!)

hemp, engaged in a week, finally succeeded, it can be said that every step has a pit, read the article also has a small hundred articles, I want to summarize their own stepping on the pit road in a timely manner, I hope to be useful to you yo~

I. Download the cx_Oracle package in Pycharm

If loading is unsuccessful, use the Anaconda Prompt to download it in your own environment
I am executing the following code in the Pycharm terminal

pip install -i /simple cx_Oracle

Then ran happily, thinking it would end successfully here

import cx_Oracle as oracle
#
def main():
    # cx_Oracle.connect("Username / Password @ Oracle Server IP / Oracle's SERVICE_NAME")
    db = ('XXX/XXX@IP address:port/sid_name')# Change to the library you want to connect to remotely
    c = ()  # Get cursor
    x = ("select * from XXX WHERE XXX")  # Use cursor for various query operations
    rows = ()
    for row in rows:
        for v in row:
            print(v)
    ()  # Close cursor
    ()  # Close the connection
if __name__ == '__main__':
    main()

However, it came up with an ERROR!
Cannot locate a 64-bit Oracle Client library: “The specified module could not be found”.
The long and clueless searching and reading and trying began! The useless ones are skimmed, the useful two steps are below:

Download orcl instantclient

Given that I have previously downloaded and unzipped instantclient_21_12, if you haven't, please go toOracle Client Website
Since I have the latest version of pycharm installed and my computer is up to date, no brainer picking the latest on~

Third, copy the three dll suffix files to the location of the pychrm environment installation package

Take mine as an example, I'm using the vene environment, you can click on file-settings-python interpreter to see what environment you're currently using

The easiest way to do this is to copy these three files from instantclient_21_12.

Directly in pycharm click on vene/Lib/site_packages and paste it in

Once the paste is done, run it again and it works, restart pycharm if it doesn't.

ps: If it doesn't work, go set the environment variables.

Mine was OK up to step 3, depending on your situation
Win11 desktop right click, randomly select a display settings/personalization, enter the settings pop-up page, enter environment to search for

Create a new variable named instantclient in the user variables, with the value of the variable being something in the path where instantclient_21_12 is located

Try restarting pycharm again when you're done.

IV. Query table and output data frame form

# Running successfully
import cx_Oracle as cx
import pandas as pd
def visitOracle(sql):
    # cx_Oracle.connect("Username / Password @ Oracle Server IP / Oracle's SERVICE_NAME")
    conn = ('XXX/XXX@IP address:port/sid_name')# Change to the library you want to connect to remotely
    cursor = ()
    (sql)  # Use cursor for various operations
    # Read field names
    index = 
    row = list()
    for i in range(len(index)):
        (index[i][0])
    # Get the return information
    data = ()
    result = (list(data), columns=row)
    # Close links and release resources
    ()
    ()
    return result
sql = "select * from XXX WHERE XXX'"
df = visitOracle(sql)
print(df)

If it's a normal table, it's running successfully, given that one of the fields in my table is of the long text CLOB type, it's reporting an error
cx_Oracle.DatabaseError: DPI-1040: LOB was already closed
Step 5 is the solution I tried successfully

V. Query tables containing lob type fields

5.1 Downloading the sqlalchemy package in Pycharm

  • Installation fails with pip install sqlalchemy and setup there.
  • SQLAlchemy - PyPI installation of specified version also fails
  • Installation using the mirror source was successful!
#pip3 install flask-sqlalchemy -i /simple
pip3 install sqlalchemy -i /simple

5.2 Querying databases and accessing tables

#--------- ran successfully
import pandas as pd
import cx_Oracle
from sqlalchemy import create_engine
# Database connection information
dsn_tns = cx_Oracle.makedsn('YOUR_HOST', 'YOUR_PORT', service_name='YOUR_SERVICE_NAME')
conn_string = f"oracle+cx_oracle://{YOUR_USERNAME}:{YOUR_PASSWORD}@{dsn_tns}"
# Create database engine
engine = create_engine(conn_string)
# Construct SQL query statements
query = f"select {number_column}, {clob_column} FROM {table_name} where rownum<=5"
# Use the read_sql_query method of pandas to execute a query and get the results
df = pd.read_sql_query(query, con=engine)
print(df)
# Close the database connection
()

Or the following code, which customizes the form of a function to achieve multiple calls.

##---- ran successfully
import pandas as pd
import cx_Oracle as cx
from sqlalchemy import create_engine
def visitOracle(sql):
    # Database connection information
    dsn_tns = (('YOUR_HOST', 'YOUR_PORT', service_name='YOUR_SERVICE_NAME')
    conn = f"oracle+cx_oracle://{YOUR_USERNAME}:{YOUR_PASSWORD}@{dsn_tns}"
    # Create database engine
    engine = create_engine(conn)
    # Use the read_sql_query method of pandas to execute a query and get the results
    df = pd.read_sql_query(sql, con=engine)
    # Close the database connection
    ()
    return df
## Clinical records
sql = "select {number_column}, {clob_column} FROM {table_name} where rownum<=5""
df = visitOracle(sql)
print(df)

Both of the above codes can be executed successfully!

to this article on the use of pycharm connection to read orcl database table is introduced to this article, more related pycharm orcl database table content please search for my previous articles or continue to browse the following related articles I hope you will support me more in the future!