How to handle database operations in Python

How to handle database operations in Python

As a high-level programming language, Python is very suitable for handling database operations. It has simple and easy-to-use syntax and rich third-party libraries, allowing developers to easily connect, query and modify databases. In this article, we will introduce how to use Python for database operations and provide specific code examples.

Before we begin, we need to install the Python database driver. Common database drivers include psycopg2, MySQL Connector/Python and PyMongo, which are used to connect to PostgreSQL, MySQL and MongoDB databases respectively. We can use the pip command to install, for example:

pip install psycopg2    # 连接 PostgreSQL
pip install mysql-connector-python    # 连接 MySQL
pip install pymongo    # 连接 MongoDB

After installing the database driver, we can start database operations. Below are some examples of common database operations.

1. Connect to the database

Before performing database operations, we first need to connect to the database. Each database driver has different functions to achieve connection. The following is the sample code to connect PostgreSQL, MySQL and MongoDB:

import psycopg2    # PostgreSQL
conn1 = psycopg2.connect(database="mydb", user="myuser", password="mypassword", host="localhost", port="5432")

import mysql.connector    # MySQL
conn2 = mysql.connector.connect(user='myuser', password='mypassword', host='localhost', database='mydb')

import pymongo    # MongoDB
client = pymongo.MongoClient("mongodb://localhost:27017/")
db = client["mydb"]

2. Query data

Query data is Common requirements in database operations. The following are some sample codes that demonstrate how to query data in PostgreSQL, MySQL and MongoDB:

# PostgreSQL
cur1 = conn1.cursor()
cur1.execute("SELECT * from mytable")
rows1 = cur1.fetchall()
for row in rows1:
    print(row)

# MySQL
cur2 = conn2.cursor()
cur2.execute("SELECT * from mytable")
rows2 = cur2.fetchall()
for row in rows2:
    print(row)

# MongoDB
col = db["mycollection"]
docs = col.find()
for doc in docs:
    print(doc)

3. Insert data

In addition to querying data, we often need to Insert new data into the database. The following is some sample code that demonstrates how to insert data into PostgreSQL, MySQL and MongoDB:

# PostgreSQL
cur1 = conn1.cursor()
cur1.execute("INSERT INTO mytable (column1, column2) VALUES (%s, %s)", ("value1", "value2"))
conn1.commit()

# MySQL
cur2 = conn2.cursor()
cur2.execute("INSERT INTO mytable (column1, column2) VALUES (%s, %s)", ("value1", "value2"))
conn2.commit()

# MongoDB
col = db["mycollection"]
doc = { "column1": "value1", "column2": "value2" }
col.insert_one(doc)

4. Update and delete data

Finally, we also need to understand how Update and delete data in the database. The following are some sample codes that demonstrate how to update and delete data in PostgreSQL, MySQL and MongoDB:

# PostgreSQL
cur1 = conn1.cursor()
cur1.execute("UPDATE mytable SET column1 = %s WHERE column2 = %s", ("newvalue1", "value2"))
conn1.commit()

# MySQL
cur2 = conn2.cursor()
cur2.execute("UPDATE mytable SET column1 = %s WHERE column2 = %s", ("newvalue1", "value2"))
conn2.commit()

# MongoDB
col = db["mycollection"]
col.update_one({ "column2": "value2" }, { "$set": { "column1": "newvalue1" } })

# 删除数据
# PostgreSQL
cur1 = conn1.cursor()
cur1.execute("DELETE FROM mytable WHERE column2 = %s", ("value2",))
conn1.commit()

# MySQL
cur2 = conn2.cursor()
cur2.execute("DELETE FROM mytable WHERE column2 = %s", ("value2",))
conn2.commit()

# MongoDB
col = db["mycollection"]
col.delete_one({ "column2": "value2" })

The above are some basic database operation examples, I hope it can help everyone deal with database operation problems in Python Helps. Python's database operation functions are very powerful and can meet most development needs. Choose the appropriate database driver according to the actual situation and follow the above examples. I believe you will handle database operations easily.

The above is the detailed content of How to handle database operations in Python. For more information, please follow other related articles on the PHP Chinese website!