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

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!👋