What approaches and tools are used when interacting with database management systems?

When interacting with database management systems (DBMS) in Python, the following approaches and tools are commonly used:

1. Using libraries for connecting to DBMS — for example, sqlite3 for SQLite, psycopg2 or asyncpg for PostgreSQL, PyMySQL or mysql-connector-python for MySQL.

2. ORM (Object-Relational Mapping) — libraries that allow working with the database through Python objects without writing SQL directly. Popular ORMs include: SQLAlchemy, Django ORM, Peewee.

3. Executing SQL queries — using cursors, passing parameters to prevent SQL injection.

4. Transaction management — using commit and rollback to ensure data integrity.

5. Connection pool — for optimizing database operations, for example, through sqlalchemy.pool or third-party libraries.

Example of simple connection and query using sqlite3:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

cursor.execute('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)')
cursor.execute('INSERT INTO users (name) VALUES (?)', ('Alice',))
conn.commit()

cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
    print(row)

conn.close()