安裝對(duì)應(yīng)數(shù)據(jù)庫(kù)驅(qū)動(dòng)；2. 使用connect()連接數(shù)據(jù)庫(kù)；3. 創(chuàng)建cursor對(duì)象；4. 用execute()或executemany()執(zhí)行SQL并用參數(shù)化查詢(xún)防注入；5. 用fetchall()等獲取結(jié)果；6. 修改后需commit()；7. 最后關(guān)閉連接或使用上下文管理器自動(dòng)處理；完整流程確保安全且高效執(zhí)行SQL操作。

To execute SQL queries in Python, you typically use a database driver or an ORM (like SQLAlchemy or Django ORM). The most common approach is to connect to a database using a library, then run your SQL statements through that connection. Here's how to do it step by step.

1. Choose a Database and Install the Right Driver

First, decide which database you're using (e.g., SQLite, PostgreSQL, MySQL), and install the corresponding Python package:

• SQLite: Built into Python (sqlite3 module), no installation needed.
• PostgreSQL: Use psycopg2 → pip install psycopg2-binary
• MySQL: Use mysql-connector-python or PyMySQL → pip install mysql-connector-python

2. Connect to the Database

Use the appropriate method to establish a connection.

For SQLite (simplest example):

import sqlite3

conn = sqlite3.connect('example.db')  # Creates file if it doesn't exist
cursor = conn.cursor()

For PostgreSQL:

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="myuser",
    password="mypassword"
)
cursor = conn.cursor()

For MySQL:

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="myuser",
    password="mypassword",
    database="mydb"
)
cursor = conn.cursor()

3. Execute SQL Queries

Once connected, use the cursor to run SQL commands.

# Example: Create a table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER
    )
''')

# Insert a record
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ("Alice", 30))

# Insert multiple records
cursor.executemany("INSERT INTO users (name, age) VALUES (%s, %s)",
                   [("Bob", 25), ("Charlie", 35)])

# Query data
cursor.execute("SELECT * FROM users WHERE age > %s", (25,))
rows = cursor.fetchall()

for row in rows:
    print(row)

? Note: Use parameterized queries (%s, ?) to avoid SQL injection.

4. Commit and Close the Connection

Always commit changes (for inserts/updates) and close the connection.

conn.commit()  # Saves changes
conn.close()   # Closes connection

5. Handle Errors Gracefully (Recommended)

Wrap your code in a try-except block:

import sqlite3

try:
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM nonexistent_table")
except sqlite3.Error as e:
    print(f"Database error: {e}")
finally:
    if conn:
        conn.close()

Bonus: Using Context Managers (Best Practice)

For SQLite, you can use context managers to auto-commit or rollback:

with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("David", 40))
# Automatically commits if no error, rolls back otherwise

Summary of Key Steps:

• ? Install the right database driver
• ? Connect using connect()
• ? Create a cursor
• ? Run SQL with execute() or executemany()
• ? Use parameters to prevent injection
• ? fetchall() / fetchone() for reading results
• ? commit() after modifications
• ? Close connections or use context managers

Basically, it’s straightforward once you have the driver set up — just connect, execute, and clean up.

以上是如何在Python中執(zhí)行SQL查詢(xún)？的詳細(xì)內(nèi)容。更多信息請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！