A complete guide to Python database automation

1. Detailed explanation of MySQL operations

1. Commonly used libraries

mysql-connector-python(Official Driver)
Install:pip install mysql-connector-python
PyMySQL(Pure Python implementation)
Install:pip install pymysql

2. Basic operations

Connect to the database

import 

config = {
    'user': 'root',
    'password': '123456',
    'host': 'localhost',
    'database': 'test_db'
}
conn = (**config)
cursor = ()

Execute SQL

# Create a table("""
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
)
""")

# Insert data("INSERT INTO users (name, email) VALUES (%s, %s)", ('Alice', 'alice@'))
()

3. Advanced features

Transaction Management

try:
    ("INSERT INTO users (name) VALUES ('Bob')")
    ("UPDATE users SET email='error' WHERE id=999")  # Simulation error    ()
except  as e:
    ()

2. Oracle operation details

1. Commonly used libraries

cx_Oracle(Official recommendation)
Install:pip install cx_Oracle
rely: Need to be installedOracle Instant Client

2. Basic operations

Connect to the database

import cx_Oracle

dsn = cx_Oracle.makedsn("localhost", 1521, service_name="ORCLCDB")
conn = cx_Oracle.connect(user="scott", password="tiger", dsn=dsn)
cursor = ()

Execute SQL

# Insert data using sequences("CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1")
("""
INSERT INTO users (id, name) 
VALUES (user_seq.NEXTVAL, :name)""", name="Charlie")
()

3. Advanced features

Call stored procedures

p_name = (str)
("get_user", [1, p_name])
print(p_name.getvalue())  # Output result

3. Detailed explanation of Microsoft SQL Server operation

1. Commonly used libraries

pyodbc(recommend)
Install:pip install pyodbc
rely: Need to be installedODBC Driver
pymssql(Lightweight)
Install:pip install pymssql

2. Basic operations

Connect to the database

import pyodbc

conn = (
    Driver='{ODBC Driver 17 for SQL Server}',
    Server='localhost',
    Database='test_db',
    UID='sa',
    PWD='password'
)
cursor = ()

Execute SQL

#Pagination query (OFFSET FETCH)("""
SELECT * FROM users
ORDER BY id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY
""")
print(())

3. Advanced features

Batch Insert

data = [('David', 'david@'), ('Eva', 'eva@')]
("INSERT INTO users (name, email) VALUES (?, ?)", data)
()

4. General precautions

1. Safety and performance

Parameterized query: Always use placeholders (%s、?、:name) Avoid SQL injection.
Connection Management:usewithContext manager or encapsulation class ensures that the connection is closed.

# Example: MySQL context managerwith (**config) as conn:
    with () as cursor:
        ("SELECT 1")

2. Exception handling

try:
    conn = cx_Oracle.connect("invalid_connection")
except cx_Oracle.DatabaseError as e:
    error = [0]
    print(f"Code: {}, Message: {}")
finally:
    if conn:
        ()

3. Automated packaging class

class DatabaseAutomator:
    """General database operation encapsulation"""
    def __init__(self, db_type, **config):
        self.db_type = db_type
         = config
         = None

    def __enter__(self):
        if self.db_type == "mysql":
             = (**)
        elif self.db_type == "oracle":
            dsn = cx_Oracle.makedsn(**)
             = cx_Oracle.connect(user=['user'], password=['password'], dsn=dsn)
        # Other databases are similar        return ()

    def __exit__(self, exc_type, exc_val, exc_tb):
        if :
            if exc_type: ()
            else: ()
            ()

#User Examplewith DatabaseAutomator("mysql", user="root", password="123456", host="localhost", database="test_db") as cursor:
    ("SELECT * FROM users")

4. ORM integration

SQLAlchemyUnified operation of different databases:

from sqlalchemy import create_engine

# MySQL
engine = create_engine("mysql+pymysql://user:password@localhost/db")

# Oracle
engine = create_engine("oracle+cx_oracle://user:password@localhost:1521/ORCLCDB")

# SQL Server
engine = create_engine("mssql+pyodbc://user:password@localhost/db?driver=ODBC+Driver+17+for+SQL+Server")

with () as conn:
    result = ("SELECT * FROM users")
    print(())

Through this document, you can quickly master the core methods of Python operating the three mainstream databases. Selecting the appropriate libraries and tools according to the scenario, combined with the ORM framework can further improve development efficiency.

This is all about this article about the complete guide to Python database automation. For more related content on Python database automation, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!