How do you connect to a database in Python?
Jul 10, 2025 pm 01:44 PMTo connect to a database in Python, use the appropriate library for the database type. 1. For SQLite, use sqlite3 with connect() and manage with cursor and commit. 2. For MySQL, install mysql-connector-python and provide credentials in connect(). 3. For PostgreSQL, install psycopg2 and configure connection parameters including SSL if needed. 4. Optionally, use SQLAlchemy ORM for abstraction across databases by defining models and using create_engine(). Always close connections and avoid SQL injection by using parameterized queries.
Connecting to a database in Python is pretty straightforward once you know which type of database you're working with. Most common databases—like MySQL, PostgreSQL, SQLite, or MongoDB—have corresponding Python libraries that handle the connection process.

Here’s how to do it for a few popular ones.
Using SQLite with sqlite3
SQLite is built into Python, so no extra installation is needed. It's great for small apps or local development.

To connect:
import sqlite3 conn = sqlite3.connect('example.db') cursor = conn.cursor()
This creates a file-based database called example.db
(or opens it if it already exists). You can then run SQL queries using the cursor object.

If you want to create a table:
cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''') conn.commit()
Don’t forget to close the connection when you’re done:
conn.close()
A couple things to watch out for:
- Always call
.commit()
after making changes. - Use parameterized queries instead of string formatting to avoid SQL injection.
Connecting to MySQL with mysql-connector-python
For MySQL, you’ll need to install a package first:
pip install mysql-connector-python
Then connect like this:
import mysql.connector conn = mysql.connector.connect( host="localhost", user="your_username", password="your_password", database="your_database" ) cursor = conn.cursor()
You can also run queries just like with SQLite:
cursor.execute("SELECT * FROM users") results = cursor.fetchall() for row in results: print(row)
Common issues:
- Make sure your MySQL server is running.
- Double-check credentials and permissions.
- Close the cursor and connection properly.
Working with PostgreSQL via psycopg2
PostgreSQL support comes through the psycopg2
library:
Install it first:
pip install psycopg2
Then connect:
import psycopg2 conn = psycopg2.connect( dbname="your_db", user="your_user", password="your_pass", host="localhost" ) cursor = conn.cursor()
You can execute queries the same way:
cursor.execute("SELECT version();") print(cursor.fetchone())
Some tips:
- If you're connecting over SSL, you may need to add
sslmode='require'
or similar. - Like with MySQL, always remember to commit after writes.
Bonus: ORM Option with SQLAlchemy
If you're looking for something more flexible or abstracted, SQLAlchemy is a solid choice. It supports multiple backends and lets you define models instead of writing raw SQL.
Install it:
pip install sqlalchemy
Basic usage:
from sqlalchemy import create_engine engine = create_engine("sqlite:///example.db") connection = engine.connect()
Or for PostgreSQL:
engine = create_engine("postgresql://user:password@localhost/dbname")
It takes a bit more setup but gives you cleaner code and better portability across databases.
That’s basically how you connect to different databases in Python. Each method has its own use case—SQLite for simplicity, MySQL/PostgreSQL for more serious applications, and SQLAlchemy when you want an ORM layer.
The above is the detailed content of How do you connect to a database 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

The key to dealing with API authentication is to understand and use the authentication method correctly. 1. APIKey is the simplest authentication method, usually placed in the request header or URL parameters; 2. BasicAuth uses username and password for Base64 encoding transmission, which is suitable for internal systems; 3. OAuth2 needs to obtain the token first through client_id and client_secret, and then bring the BearerToken in the request header; 4. In order to deal with the token expiration, the token management class can be encapsulated and automatically refreshed the token; in short, selecting the appropriate method according to the document and safely storing the key information is the key.

To test the API, you need to use Python's Requests library. The steps are to install the library, send requests, verify responses, set timeouts and retry. First, install the library through pipinstallrequests; then use requests.get() or requests.post() and other methods to send GET or POST requests; then check response.status_code and response.json() to ensure that the return result is in compliance with expectations; finally, add timeout parameters to set the timeout time, and combine the retrying library to achieve automatic retry to enhance stability.

In Python, variables defined inside a function are local variables and are only valid within the function; externally defined are global variables that can be read anywhere. 1. Local variables are destroyed as the function is executed; 2. The function can access global variables but cannot be modified directly, so the global keyword is required; 3. If you want to modify outer function variables in nested functions, you need to use the nonlocal keyword; 4. Variables with the same name do not affect each other in different scopes; 5. Global must be declared when modifying global variables, otherwise UnboundLocalError error will be raised. Understanding these rules helps avoid bugs and write more reliable functions.

To create modern and efficient APIs using Python, FastAPI is recommended; it is based on standard Python type prompts and can automatically generate documents, with excellent performance. After installing FastAPI and ASGI server uvicorn, you can write interface code. By defining routes, writing processing functions, and returning data, APIs can be quickly built. FastAPI supports a variety of HTTP methods and provides automatically generated SwaggerUI and ReDoc documentation systems. URL parameters can be captured through path definition, while query parameters can be implemented by setting default values ??for function parameters. The rational use of Pydantic models can help improve development efficiency and accuracy.

Add timeout control to Python's for loop. 1. You can record the start time with the time module, and judge whether it is timed out in each iteration and use break to jump out of the loop; 2. For polling class tasks, you can use the while loop to match time judgment, and add sleep to avoid CPU fullness; 3. Advanced methods can consider threading or signal to achieve more precise control, but the complexity is high, and it is not recommended for beginners to choose; summary key points: manual time judgment is the basic solution, while is more suitable for time-limited waiting class tasks, sleep is indispensable, and advanced methods are suitable for specific scenarios.

In Python, the method of traversing tuples with for loops includes directly iterating over elements, getting indexes and elements at the same time, and processing nested tuples. 1. Use the for loop directly to access each element in sequence without managing the index; 2. Use enumerate() to get the index and value at the same time. The default index is 0, and the start parameter can also be specified; 3. Nested tuples can be unpacked in the loop, but it is necessary to ensure that the subtuple structure is consistent, otherwise an unpacking error will be raised; in addition, the tuple is immutable and the content cannot be modified in the loop. Unwanted values can be ignored by \_. It is recommended to check whether the tuple is empty before traversing to avoid errors.

How to efficiently handle large JSON files in Python? 1. Use the ijson library to stream and avoid memory overflow through item-by-item parsing; 2. If it is in JSONLines format, you can read it line by line and process it with json.loads(); 3. Or split the large file into small pieces and then process it separately. These methods effectively solve the memory limitation problem and are suitable for different scenarios.

TosendemailsusingPython,usethesmtplibandemaillibraries.1)SetupSMTPwithserverdetailsandlogincredentials.2)ComposetheemailusingEmailMessagetosetcontent,subject,sender,andrecipient.3)Sendthemessageviaserver.send_message()andclosetheconnectionwithserver.
