Install the corresponding database driver; 2. Use connect() to connect to the database; 3. Create a cursor object; 4. Use execute() or executemany() to execute SQL and use parameterized query to prevent injection; 5. Use fetchall(), etc. to obtain results; 6. Commit() is required after modification; 7. Finally, close the connection or use a context manager to automatically handle it; the complete process ensures that SQL operations are safe and efficient.
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 (eg, SQLite, PostgreSQL, MySQL), and install the corresponding Python package:
- SQLite : Built into Python (
sqlite3module), no installation needed. - PostgreSQL : Use
psycopg2→pip install psycopg2-binary - MySQL : Use
mysql-connector-pythonorPyMySQL→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 otherwiseSummary of Key Steps:
- ? Install the right database driver
- ? Connect using
connect() - ? Create a cursor
- ? Run SQL with
execute()orexecutemany() - ? 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.
The above is the detailed content of How to execute SQL queries in Python?. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How to use PHP combined with AI to achieve text error correction PHP syntax detection and optimization
Jul 25, 2025 pm 08:57 PM
To realize text error correction and syntax optimization with AI, you need to follow the following steps: 1. Select a suitable AI model or API, such as Baidu, Tencent API or open source NLP library; 2. Call the API through PHP's curl or Guzzle and process the return results; 3. Display error correction information in the application and allow users to choose whether to adopt it; 4. Use php-l and PHP_CodeSniffer for syntax detection and code optimization; 5. Continuously collect feedback and update the model or rules to improve the effect. When choosing AIAPI, focus on evaluating accuracy, response speed, price and support for PHP. Code optimization should follow PSR specifications, use cache reasonably, avoid circular queries, review code regularly, and use X
PHP calls AI intelligent voice assistant PHP voice interaction system construction
Jul 25, 2025 pm 08:45 PM
User voice input is captured and sent to the PHP backend through the MediaRecorder API of the front-end JavaScript; 2. PHP saves the audio as a temporary file and calls STTAPI (such as Google or Baidu voice recognition) to convert it into text; 3. PHP sends the text to an AI service (such as OpenAIGPT) to obtain intelligent reply; 4. PHP then calls TTSAPI (such as Baidu or Google voice synthesis) to convert the reply to a voice file; 5. PHP streams the voice file back to the front-end to play, completing interaction. The entire process is dominated by PHP to ensure seamless connection between all links.
python seaborn jointplot example
Jul 26, 2025 am 08:11 AM
Use Seaborn's jointplot to quickly visualize the relationship and distribution between two variables; 2. The basic scatter plot is implemented by sns.jointplot(data=tips,x="total_bill",y="tip",kind="scatter"), the center is a scatter plot, and the histogram is displayed on the upper and lower and right sides; 3. Add regression lines and density information to a kind="reg", and combine marginal_kws to set the edge plot style; 4. When the data volume is large, it is recommended to use "hex"
How to use PHP combined with AI to analyze video content PHP intelligent video tag generation
Jul 25, 2025 pm 06:15 PM
The core idea of PHP combining AI for video content analysis is to let PHP serve as the backend "glue", first upload video to cloud storage, and then call AI services (such as Google CloudVideoAI, etc.) for asynchronous analysis; 2. PHP parses the JSON results, extract people, objects, scenes, voice and other information to generate intelligent tags and store them in the database; 3. The advantage is to use PHP's mature web ecosystem to quickly integrate AI capabilities, which is suitable for projects with existing PHP systems to efficiently implement; 4. Common challenges include large file processing (directly transmitted to cloud storage with pre-signed URLs), asynchronous tasks (introducing message queues), cost control (on-demand analysis, budget monitoring) and result optimization (label standardization); 5. Smart tags significantly improve visual
How to develop AI-based text summary with PHP Quick Refining Technology
Jul 25, 2025 pm 05:57 PM
The core of PHP's development of AI text summary is to call external AI service APIs (such as OpenAI, HuggingFace) as a coordinator to realize text preprocessing, API requests, response analysis and result display; 2. The limitation is that the computing performance is weak and the AI ecosystem is weak. The response strategy is to leverage APIs, service decoupling and asynchronous processing; 3. Model selection needs to weigh summary quality, cost, delay, concurrency, data privacy, and abstract models such as GPT or BART/T5 are recommended; 4. Performance optimization includes cache, asynchronous queues, batch processing and nearby area selection. Error processing needs to cover current limit retry, network timeout, key security, input verification and logging to ensure the stable and efficient operation of the system.
PHP integrated AI emotional computing technology PHP user feedback intelligent analysis
Jul 25, 2025 pm 06:54 PM
To integrate AI sentiment computing technology into PHP applications, the core is to use cloud services AIAPI (such as Google, AWS, and Azure) for sentiment analysis, send text through HTTP requests and parse returned JSON results, and store emotional data into the database, thereby realizing automated processing and data insights of user feedback. The specific steps include: 1. Select a suitable AI sentiment analysis API, considering accuracy, cost, language support and integration complexity; 2. Use Guzzle or curl to send requests, store sentiment scores, labels, and intensity information; 3. Build a visual dashboard to support priority sorting, trend analysis, product iteration direction and user segmentation; 4. Respond to technical challenges, such as API call restrictions and numbers
python list to string conversion example
Jul 26, 2025 am 08:00 AM
String lists can be merged with join() method, such as ''.join(words) to get "HelloworldfromPython"; 2. Number lists must be converted to strings with map(str, numbers) or [str(x)forxinnumbers] before joining; 3. Any type list can be directly converted to strings with brackets and quotes, suitable for debugging; 4. Custom formats can be implemented by generator expressions combined with join(), such as '|'.join(f"[{item}]"foriteminitems) output"[a]|[
python pandas melt example
Jul 27, 2025 am 02:48 AM
pandas.melt() is used to convert wide format data into long format. The answer is to define new column names by specifying id_vars retain the identification column, value_vars select the column to be melted, var_name and value_name, 1.id_vars='Name' means that the Name column remains unchanged, 2.value_vars=['Math','English','Science'] specifies the column to be melted, 3.var_name='Subject' sets the new column name of the original column name, 4.value_name='Score' sets the new column name of the original value, and finally generates three columns including Name, Subject and Score.
