How to Connect to Oracle, MySql and PostgreSQL Databases Using Python

cover
29 Apr 2024

Step 1: Install the necessary libraries

To connect to a database and query data, you need to install the following two libraries:

  1. pandas: A Python library for data analysis, including functions for reading and writing data.
  2. sqlalchemy: A Python library for interacting with relational databases, allowing you to use Python with various databases.

You can install these libraries using the following command in the command prompt or terminal:

pip install pandas
pip install sqlalchemy

Step 2: Connect to the database

Connecting to a database requires the following information:

  1. Database Type: The type of database you want to connect to, such as MySQL, PostgreSQL, etc.
  2. Host Name: The host name or IP address where the database is located.
  3. Port Number: The port number of the database, usually the default port number.
  4. Username: The username required to connect to the database.
  5. Password: The password required to connect to the database.
  6. Database Name: The name of the database you want to connect to.

You can use the following Python code to connect to the database:

from sqlalchemy import create_engine

# Connect to a MySQL database
engine = create_engine('mysql://username:password@hostname:port/databasename')

# Connect to a PostgreSQL database
engine = create_engine('postgresql://username:password@hostname:port/databasename')

# Connect to an Oracle database, requires cx_Oracle library to be installed
engine = create_engine('oracle+cx_oracle://username:password@hostname:port/databasename')

Step 3: Query data using the read_sql function

Use the read_sql function from pandas to query data from the database. The read_sql function requires two parameters:

  1. SQL Query: The SQL query you want to execute.
  2. Database Connection: The database connection you created earlier.

Here is an example of querying data:

import pandas as pd

# Execute SQL query and store the result in a DataFrame
df = pd.read_sql('SELECT * FROM mytable', engine)

# Print the DataFrame
print(df)

Thank you for taking the time to explore data-related insights with me. I appreciate your engagement. If you find this information helpful, I invite you to follow me or connect with me on LinkedIn or X(@Luca_DataTeam). Happy exploring!👋