🐍 How to Connect Python with SQL Databases

Python can connect to several types of SQL databases like MySQL, PostgreSQL, SQLite, and Microsoft SQL Server using dedicated libraries. Below are general steps and examples for each.

🔗 1. Connect to SQLite (No Server Needed)

SQLite is a lightweight, serverless database built into Python.

✅ Step-by-Step:

python

Copy

Edit

import sqlite3

# Connect to a database (or create one)

conn = sqlite3.connect('mydatabase.db')

# Create a cursor

cursor = conn.cursor()

# Create a table

cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)''')

# Insert data

cursor.execute("INSERT INTO users (name) VALUES (?)", ('Alice',))

# Commit and close

conn.commit()

conn.close()

🐬 2. Connect to MySQL

Install the library:

bash

Copy

Edit

pip install mysql-connector-python

✅ Example:

python

Copy

Edit

import mysql.connector

conn = mysql.connector.connect(

    host="localhost",

    user="your_username",

    password="your_password",

    database="your_database"

)

cursor = conn.cursor()

cursor.execute("SELECT * FROM users")

for row in cursor.fetchall():

    print(row)

conn.close()

🐘 3. Connect to PostgreSQL

Install the library:

bash

Copy

Edit

pip install psycopg2-binary

✅ Example:

python

Copy

Edit

import psycopg2

conn = psycopg2.connect(

    host="localhost",

    database="your_database",

    user="your_user",

    password="your_password"

)

cursor = conn.cursor()

cursor.execute("SELECT * FROM users")

for row in cursor.fetchall():

    print(row)

conn.close()

🪟 4. Connect to Microsoft SQL Server

Install the library:

bash

Copy

Edit

pip install pyodbc

✅ Example:

python

Copy

Edit

import pyodbc

conn = pyodbc.connect(

    'DRIVER={ODBC Driver 17 for SQL Server};'

    'SERVER=your_server_name;'

    'DATABASE=your_database;'

    'UID=your_username;'

    'PWD=your_password'

)

cursor = conn.cursor()

cursor.execute("SELECT * FROM users")

for row in cursor.fetchall():

    print(row)

conn.close()

🔒 Best Practices

Tip Why

✅ Use parameterized queries Prevent SQL injection

🔄 Use connection pooling (e.g., sqlalchemy) Improve performance

🔐 Never hard-code credentials Use environment variables

🧪 Close connections properly Avoid memory leaks

⚙️ Optional: Use SQLAlchemy (ORM)

SQLAlchemy allows you to work with databases using Python classes instead of raw SQL.

bash

Copy

Edit

pip install sqlalchemy

Would you like an example using SQLAlchemy too?

Learn Full Stack Python Course in Hyderabad

Read More

Setting Up PostgreSQL for Full Stack Python Projects

SQL vs NoSQL: What’s Best for Full Stack Python Development?

Introduction to Databases for Full Stack Python Development

Databases and Data Storage

Visit Our Quality Thought Training in Hyderabad

Get Directions