DEV Community

Cover image for How to connect to Oracle, MySql and PostgreSQL databases using Python?
Luca Liu
Luca Liu

Posted on

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


Enter fullscreen mode Exit fullscreen mode

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')



Enter fullscreen mode Exit fullscreen mode

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)


Enter fullscreen mode Exit fullscreen mode

Explore more

Thank you for taking the time to explore data-related insights with me. I appreciate your engagement.

🚀 Connect with me on LinkedIn

🎃 Connect with me on X

Top comments (0)