SoFunction
Updated on 2025-04-05

Summary of methods for connecting different databases in Python

In modern data-driven application development, Python has become an ideal programming language for connecting various databases with its rich libraries and a powerful ecosystem. This article will explore in-depth methods, common libraries and key precautions for Python to connect to different types of databases.

1. Connect to MySQL database

MySQL is a widely used relational database. In Python,mysql - connector - pythonlibrary to implement connection.

Install the library:passpip install mysql - connector - pythonPerform installation.

Connection example：

import 

# Create a connectionmydb = (
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

# Create a cursormycursor = ()

# Execute SQL query("SELECT * FROM your_table")

# Get resultsresults = ()
for row in results:
    print(row)

# Close the connection()
()

In this example, first use()Method establishes a connection to the MySQL database and passes in parameters such as host, user name, password and database name. Then create a cursor, execute SQL query through the cursor, and usefetchall()Methods obtain all query results, and finally close the cursor and database connection to free up resources.

2. Connect to PostgreSQL database

PostgreSQL is known for its powerful features and scalability. Used in Pythonpsycopg2Library connection PostgreSQL.

Install the library：pip install psycopg2

Connection example：

import psycopg2

# Create a connectionconn = (
    host="localhost",
    database="your_database",
    user="your_username",
    password="your_password",
    port="5432"
)

# Create a cursorcur = ()

# Execute SQL query("SELECT * FROM your_table")

# Get resultsresults = ()
for row in results:
    print(row)

# Close cursor and connection()
()

Similar to MySQL connection, use()To establish a connection, note that you need to specify the port number here (default 5432). After creating the cursor, execute the SQL query and get the results, and finally close the connection.

3. Connect to SQLite database

SQLite is a lightweight embedded database, built-in Pythonsqlite3library, no additional installation required.

Connection example：

import sqlite3

# Create a connectionconn = ('your_database.db')

# Create a cursorcur = ()

# Execute SQL query("SELECT * FROM your_table")

# Get resultsresults = ()
for row in results:
    print(row)

# Close cursor and connection()
()

use()Methods connect to SQLite database, just pass in the database file name (created if the file does not exist). The subsequent operations are similar to other databases.

4. Connect to MongoDB database

MongoDB is a popular non-relational (documentary) database. Python usagepymongoLibrary connection to MongoDB.

Install the library：pip install pymongo

Connection example：

from pymongo import MongoClient

# Create a connectionclient = MongoClient("mongodb://localhost:27017/")

# Select a databasedb = client["your_database"]

# Select a collection (equivalent to a table)collection = db["your_collection"]

# Query Documentresults = ()
for doc in results:
    print(doc)

# Close the connection()

useMongoClientEstablish a connection to MongoDB and pass in the address and port of MongoDB (default 27017). passclientObject selection database and collection, usefind()Method query document.

5. Connect to Redis database

Redis is a memory data store, commonly used for cache and message queues. Python usageredis - pyLibrary connection Redis.

Install the library：pip install redis

Connection example：

import redis

# Create a connectionr = (host='localhost', port=6379, db=0)

# Set key-value pairs('key', 'value')

# Get the valuevalue = ('key')
print(value)

# Close the connection (in some cases it can not be closed explicitly)()

use()Method establishes a connection, passing in to the host, port (default 6379) and database number. passset()andget()Method to set and obtain data.

6. Summary and precautions

  • Connection parameters: The parameters for different database connections are slightly different, such as MySQL needs to specify the user name, password, host, and database name; MongoDB needs to specify the address and port, etc. Ensure that the parameters are accurate, otherwise the connection will fail.
  • Exception handling: When connecting and operating the database, you may encounter various exceptions, such as network problems, insufficient permissions, etc. Should be usedtry - exceptBlocks perform exception handling to improve program stability and robustness.
  • Resource Management: After connecting to the database, close the cursor and connection in time to release resources. Especially in highly concurrency or long-running programs, inappropriate resource management can lead to resource exhaustion.
  • Security: Avoid hard-code database passwords in code, and use environment variables or configuration files to store sensitive information. At the same time, user input is strictly filtered and verified to prevent security vulnerabilities such as SQL injection.

Although Python's methods of connecting to different databases vary, the basic steps are similar. By mastering these connection methods and precautions, developers can flexibly select the appropriate database according to project needs and efficiently interact with data.

This is the end of this article about how to connect to different databases in Python. For more related content on Python database connection, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!