Pandas to oracle

by

Pandas to Oracle

When working with data in Python using the pandas library, it is common to want to store that data in a database such as Oracle. The Pandas library provides a convenient way to connect and write data to an Oracle database.

Setup

First, make sure you have the necessary dependencies installed. You will need the ‘pandas’ library and the ‘cx_Oracle’ library. You can install them using pip:


    pip install pandas
    pip install cx_Oracle

Connecting to Oracle

To connect to an Oracle database, you will need the connection details such as the hostname, port, service name, username, and password. Use the following code to establish a connection:


    import cx_Oracle
    
    # Connection details
    host = "your_hostname"
    port = 1521
    service_name = "your_service_name"
    username = "your_username"
    password = "your_password"
    
    # Establish connection
    conn = cx_Oracle.connect("{}/{}@{}:{}/{}".format(username, password, host, port, service_name))

Writing Data to Oracle

Once you have established a connection, you can use the pandas library to write data to your Oracle database. The pandas library provides the ‘to_sql’ function, which can be used to write a DataFrame to a database table.

Here is an example:


    import pandas as pd
    
    # Assume you have a DataFrame called 'df' containing your data
    
    table_name = "your_table_name"
    
    # Write DataFrame to Oracle
    df.to_sql(table_name, conn, if_exists='replace', index=False)

The ‘to_sql’ function takes the following parameters:

  • name: The name of the table to write the DataFrame to.
  • con: The database connection object.
  • if_exists: Specifies what to do if the table already exists. Use ‘replace’ to replace the table or ‘append’ to append the data to the existing table.
  • index: Specifies whether to write the DataFrame’s index as a column in the table.

Example

Let’s say you have a DataFrame called ‘sales_data’ with columns ‘product’, ‘quantity’, and ‘price’. You want to write this data to a table called ‘sales’ in your Oracle database.


    import pandas as pd
    
    # Connection details
    host = "your_hostname"
    port = 1521
    service_name = "your_service_name"
    username = "your_username"
    password = "your_password"
    
    # Establish connection
    conn = cx_Oracle.connect("{}/{}@{}:{}/{}".format(username, password, host, port, service_name))
    
    # DataFrame
    data = {'product': ['A', 'B', 'C'],
            'quantity': [10, 20, 30],
            'price': [100, 200, 300]}
    sales_data = pd.DataFrame(data)
    
    # Write DataFrame to Oracle
    sales_data.to_sql('sales', conn, if_exists='replace', index=False)

This code will create a new table called ‘sales’ in your Oracle database (or replace an existing table) and populate it with the data from the ‘sales_data’ DataFrame.

Leave a comment