How to connect to and operate databases and handle SQL queries

How to connect and operate the database and process SQL queries

In the process of developing applications, database connection and operation are a very important part. Database is an important tool for storing and managing data, and SQL (Structured Query Language) is a standard language for querying and operating databases. In this article, we'll learn how to connect to and manipulate a database and show some code examples for handling SQL queries.

1. Connect to the database:

First, we need to connect to the database to operate. In most development environments, we can use a database connection string to connect to the database. The specific connection string may vary for different database systems. Here is an example code to connect to a MySQL database:

import mysql.connector

# 配置数据库连接参数
config = {
  'user': 'root',
  'password': 'password',
  'host': 'localhost',
  'database': 'mydatabase'
}

# 建立数据库连接
conn = mysql.connector.connect(**config)

# 获取数据库的游标
cursor = conn.cursor()

# 在这里执行数据库操作

# 关闭连接
conn.close()

2. Execute SQL query:

After connecting to the database, we can use the cursor object to execute SQL queries. The following is an example showing how to perform query operations and obtain query results:

# 执行查询
query = "SELECT * FROM customers"
cursor.execute(query)

# 获取查询结果
result = cursor.fetchall()

# 输出每一行的数据
for row in result:
  print(row)

3. Parameterized query:

Sometimes, we need to execute based on different input conditions Query operations. At this time, parameterized queries are very useful. Parameterized queries prevent SQL injection attacks and improve query performance. Here is an example showing how to perform a parameterized query:

# 执行参数化查询
query = "SELECT * FROM customers WHERE country = %s"
params = ("China",)
cursor.execute(query, params)

# 获取查询结果
result = cursor.fetchall()

# 输出每一行的数据
for row in result:
  print(row)

4. Update database records:

In addition to query operations, we can also perform operations to update database records. Here is an example showing how to update a record in the database:

# 执行更新操作
query = "UPDATE customers SET address = %s WHERE customer_id = %s"
params = ("New Address", 1)
cursor.execute(query, params)

# 提交事务
conn.commit()

# 输出受影响的行数
print("Updated rows:", cursor.rowcount)

5. Insert a new record:

In addition, we can also perform the operation of inserting a new record. Here is an example showing how to insert a new record into the database:

# 执行插入操作
query = "INSERT INTO customers (name, email) VALUES (%s, %s)"
params = ("John Doe", "johndoe@example.com")
cursor.execute(query, params)

# 提交事务
conn.commit()

# 输出插入记录的主键值
print("Inserted ID:", cursor.lastrowid)

Summary:

By learning the knowledge of connecting and operating databases and processing SQL queries, we can better develop applications program. We can connect to the database, perform query and update operations, and learn about parameterized queries and ways to insert new records. These code examples will help us better understand how to connect and operate databases using Python. Be sure to modify the database connection parameters and query statements in the code according to the actual situation to adapt to your own development environment and needs.

The above is the detailed content of How to connect to and operate databases and handle SQL queries. For more information, please follow other related articles on the PHP Chinese website!