How do you execute a query in a database using python? (AI)

Question 1

What object do you need to execute SQL commands?

Answer 1

The Cursor object. You create it from your connection: cursor = conn.cursor()

Question 2

What is the primary method used to run an SQL string?

Answer 2

The execute() method on the cursor object.

Question 3

Example: Executing a simple SELECT query

Answer 3

sql_query = "SELECT * FROM users"`\n`cursor.execute(sql_query)`

Question 4

How do you safely pass Python variables into your SQL query?

Answer 4

Use placeholders in the SQL string and pass the variables as a second argument (usually a tuple) to execute().

Question 5

What placeholder is used by sqlite3?

Answer 5

The question mark (?). SELECT * FROM users WHERE name = ?

Question 6

What placeholder is common in libraries like psycopg2 (PostgreSQL) and mysql-connector?

Answer 6

The %s placeholder. SELECT * FROM users WHERE name = %s

Question 7

Example: Safe execution with a variable (sqlite3)

Answer 7

user_name = "Alice"\ncursor.execute("SELECT * FROM users WHERE name = ?", (user_name,))

Question 8

Why should you never use f-strings or string formatting (like +) to build queries?

Answer 8

It creates a major security vulnerability called SQL Injection.

Question 9

After executing a SELECT query, how do you retrieve a single row?

Answer 9

row = cursor.fetchone()

Question 10

After executing a SELECT query, how do you retrieve all matching rows?

Answer 10

rows = cursor.fetchall() (This returns a list of tuples)

Question 11

After executing a SELECT query, how do you retrieve a specific number of rows?

Answer 11

rows = cursor.fetchmany(5) (Fetches 5 rows at a time)

Question 12

What must you call on the connection object to finalize changes like INSERT, UPDATE, or DELETE?

Answer 12

You must call conn.commit().

Question 13

Example: Executing an INSERT statement

Answer 13

cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Bob", "bob@example.com"))\nconn.commit()

Question 14

What happens if you forget to call conn.commit() after an INSERT?

Answer 14

The data will not be saved to the database. The transaction will be rolled back.

Question 15

What method can you call to undo changes made since the last commit?

Answer 15

conn.rollback()

Question 16

What should you do with the cursor after you are finished with it?

Answer 16

Close it to free up resources: cursor.close()

Question 17

What is the executemany() method used for?

Answer 17

To efficiently run the same SQL command multiple times with different sets of data (e.g., bulk inserting many rows from a list).

Question 18

Example: Using executemany()

Answer 18

users_to_add = [("Charlie", "c@ex.com"), ("David", "d@ex.com")]\ncursor.executemany("INSERT INTO users (name, email) VALUES (?, ?)", users_to_add)\nconn.commit()

Question 19

What does cursor.rowcount tell you?

Answer 19

The number of rows affected by the last DML command (INSERT, UPDATE, DELETE) or the number of rows returned by a query (behavior can vary by driver).

Question 20

What is the with statement used for in this context?

Answer 20

It acts as a context manager, typically for the connection, to automatically commit() on success or rollback() on error, and often ensures the connection is closed.

Question 21

Example: Using with for a connection (recommended)

Answer 21

with sqlite3.connect('app.db') as conn:\n cursor = conn.cursor()\n cursor.execute(...)\n # No conn.commit() needed here if used as context manager

Question 22

ORM example

Answer 22

from sqlalchemy import create_engine, select
from sqlalchemy.orm import sessionmaker
from models import User

Create engine and session
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
session = Session()

Execute query
bob = session.query(User).filter_by(name='Bob').first()

Question 23

What does ORM stand for?

Answer 23

Object-Relational Mapper

Question 24

What is the primary goal of an ORM?

Answer 24

To map Python classes/objects to database tables/rows, allowing interaction without writing raw SQL.

Question 25

What is the most widely used ORM library in Python?

Answer 25

**SQLAlchemy**

Question 26

In SQLAlchemy, what component manages the database connection and dialect?

Answer 26

The **Engine** (created using `create_engine()`).

Question 27

Example: Creating a database Engine for SQLite

Answer 27

`engine = create_engine('sqlite:///database.db')`

Question 28

What component provides the interface for transactional operations (queries, commits, rollbacks)?

Answer 28

The **Session** (often managed using `SessionMaker`).

Question 29

How do you start a standard query in SQLAlchemy to retrieve data?

Answer 29

By accessing the model class via the session, e.g., `session.query(User)`

Question 30

SQLAlchemy: How do you select **all** records from the User model?

Answer 30

`users = session.query(User).all()`

Question 31

SQLAlchemy: How do you apply a filtering condition (e.g., age > 30)?

Answer 31

Use the **`.filter()`** method, e.g., `session.query(User).filter(User.age > 30)`

Question 32

SQLAlchemy: How do you retrieve only the **first** matching record?

Answer 32

Use the **`.first()`** method.

Question 33

SQLAlchemy: How do you add a new record to the session?

Answer 33

Instantiate the Model class, then use **`session.add(new_object)`**

Question 34

SQLAlchemy: What method finalizes a change (INSERT, UPDATE, DELETE) in the database?

Answer 34

Call **`session.commit()`**

Question 35

SQLAlchemy: How do you update an existing record's email address?

Answer 35

Retrieve the object, modify its attribute (`user.email = 'new@ex.com'`), then call **`session.commit()`**

Question 36

SQLAlchemy: How do you delete a record?

Answer 36

Retrieve the object, then call **`session.delete(object)`**, followed by **`session.commit()`**

Question 37

What Python keyword is recommended to ensure a Session is properly closed?

Answer 37

The **`with`** keyword, as the Session often acts as a context manager.

Question 38

What does the ORM handle automatically that prevents SQL injection attacks?

Answer 38

The **safe parameter binding** (it substitutes variables for you).

Question 39

SQLAlchemy: How do you retrieve a single record by its primary key (ID)? (Older Method)

Answer 39

`session.get(User, user_id)`

Question 40

SQLAlchemy: How do you undo changes made since the last commit or start of the transaction?

Answer 40

Call **`session.rollback()`**