DEV Community

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

Posted on

3

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

Quadratic AI

Quadratic AI – The Spreadsheet with AI, Code, and Connections

  • AI-Powered Insights: Ask questions in plain English and get instant visualizations
  • Multi-Language Support: Seamlessly switch between Python, SQL, and JavaScript in one workspace
  • Zero Setup Required: Connect to databases or drag-and-drop files straight from your browser
  • Live Collaboration: Work together in real-time, no matter where your team is located
  • Beyond Formulas: Tackle complex analysis that traditional spreadsheets can't handle

Get started for free.

Watch The Demo πŸ“Šβœ¨

Top comments (0)

Billboard image

Try REST API Generation for Snowflake

DevOps for Private APIs. Automate the building, securing, and documenting of internal/private REST APIs with built-in enterprise security on bare-metal, VMs, or containers.

  • Auto-generated live APIs mapped from Snowflake database schema
  • Interactive Swagger API documentation
  • Scripting engine to customize your API
  • Built-in role-based access control

Learn more

πŸ‘‹ Kindness is contagious

Engage with a wealth of insights in this thoughtful article, valued within the supportive DEV Community. Coders of every background are welcome to join in and add to our collective wisdom.

A sincere "thank you" often brightens someone’s day. Share your gratitude in the comments below!

On DEV, the act of sharing knowledge eases our journey and fortifies our community ties. Found value in this? A quick thank you to the author can make a significant impact.

Okay