4.2. SQLAlchemy Basics (190m) Flashcards

Question 1

Q

What is SQLAlchemy?

Answer

A

SQLAlchemy is an open-source Python library that provides a set of tools and abstractions for working with relational databases. It serves as an Object-Relational Mapping (ORM) tool, which means it allows you to interact with databases using object-oriented programming concepts.

SQLAlchemy provides a high-level API that allows you to define database models as Python classes, and then perform database operations on those objects without writing raw SQL queries. It abstracts away the differences between different database engines and provides a unified interface to work with various database systems, such as PostgreSQL, MySQL, SQLite, and Oracle.

With SQLAlchemy, you can define tables, columns, and relationships using Python classes and decorators, which closely mirror the structure of your database schema. It supports various data types and allows you to define constraints, indexes, and other database-specific features. SQLAlchemy also provides powerful querying capabilities, allowing you to construct complex database queries using a fluent, expressive syntax.

In addition to the ORM functionality, SQLAlchemy also provides a lower-level SQL expression language that allows you to construct SQL queries programmatically, providing a more flexible approach when needed.

Overall, SQLAlchemy simplifies the process of working with databases in Python by providing an intuitive and powerful toolkit for interacting with relational databases, making it easier to manage and manipulate data within your applications.

Question 2

Q

What does this do (inside terminal)?
~~~
python3 -m venv env
~~~

Answer

A

The command python3 -m venv env is used to create a virtual environment in Python.

A virtual environment is an isolated Python environment that allows you to install packages and dependencies specific to a project, without interfering with the global Python environment or other projects. It helps to keep your project’s dependencies separate and ensures that each project can have its own set of packages and versions.

Here’s a breakdown of the command:

python3: This is the command to invoke the Python interpreter. Depending on your system configuration, you may need to use python instead of python3.
-m venv: This part of the command tells Python to run the venv module as a script. The venv module is a built-in module in Python 3 that provides support for creating virtual environments.
env: This is the name of the directory where the virtual environment will be created. You can choose any name you like, but env is a common convention. You can replace it with a different name if desired.

When you run python3 -m venv env, it will create a new directory called env (or the name you provided) in the current directory. This directory will contain a self-contained Python environment with its own copy of the Python interpreter, pip (the package installer), and other necessary files.

To activate the virtual environment, you can use the following command:

On Unix or Linux: source env/bin/activate
On Windows: .\env\Scripts\activate.bat

After activating the virtual environment, any Python packages you install using pip will be installed within the virtual environment, isolated from the global Python environment. This helps in managing project dependencies and keeping them separate from other projects you may have on your system.

Question 3

Q

What does this mean (inside terminal)?
.\env\Scripts\activate in Windows.
source ./env/bin/activate in Mac

Answer

A

The commands .\env\Scripts\activate in Windows and source ./env/bin/activate in macOS (or Linux/Unix) are used to activate a virtual environment created using the venv module in Python.

When you create a virtual environment using python3 -m venv env, it generates a set of scripts in the env directory that enable you to activate and deactivate the virtual environment.

In Windows, the activation script is located at env\Scripts\activate.bat. To activate the virtual environment, you use the command .\env\Scripts\activate in the command prompt or PowerShell.

In macOS, Linux, or Unix-based systems, the activation script is located at env/bin/activate. To activate the virtual environment, you use the command source ./env/bin/activate in the terminal.

Activating the virtual environment modifies the system’s PATH environment variable, so that when you run Python or pip commands, they refer to the Python interpreter and packages installed within the virtual environment. This ensures that any packages you install or execute are isolated within the virtual environment, separate from the global Python environment or other virtual environments you may have.

Once the virtual environment is activated, you will typically see the name of the virtual environment in the command prompt or terminal prompt, indicating that you are working within the activated virtual environment.

Question 4

Q

What does this do inside terminal?
~~~
pip install sqlachemy
~~~

Answer

A

Inside the terminal, the command pip install sqlalchemy is used to install the SQLAlchemy package via the pip package manager in Python.

When you execute the command pip install sqlalchemy, it connects to the Python Package Index (PyPI), retrieves the latest version of the SQLAlchemy package, and installs it into your Python environment.

Here’s a breakdown of what happens when you run the command:

The terminal or command prompt launches the pip package manager, which is a tool used to install and manage Python packages.
The command install is passed as an argument to pip, indicating that you want to install a package.
sqlalchemy is provided as the package name. This tells pip to fetch the SQLAlchemy package from the PyPI repository.
Pip checks the installed packages and their dependencies to determine if SQLAlchemy requires any additional packages. If necessary, it automatically installs any missing dependencies.
Pip proceeds to download the SQLAlchemy package and its dependencies from PyPI.
Once the download is complete, pip installs SQLAlchemy and any required dependencies into your Python environment.

After the installation is finished, you can start using SQLAlchemy in your Python projects. You can import the SQLAlchemy module in your code and utilize its features for interacting with relational databases using an object-oriented approach.

Note that the success of the installation depends on your system’s configuration and network connectivity. It’s also recommended to run the command with appropriate permissions (e.g., using sudo on Linux or macOS) if your user account requires administrative privileges to install packages globally.

Question 5

Q

What does the following do inside terminal?
~~~
pip freeze > requirements.txt
~~~

Answer

A

The command pip freeze > requirements.txt is used to generate a file named requirements.txt that lists all the Python packages and their versions installed in your Python environment.

Here’s what happens when you execute this command in the terminal:

The command pip freeze is invoked. It instructs pip to generate a list of installed packages and their versions.
The > symbol is a redirection operator that redirects the output of the pip freeze command to a file.
requirements.txt is the name of the file where the output will be saved. If the file does not exist, it will be created. If it already exists, the command will overwrite its contents.
Pip executes the pip freeze command and retrieves a list of installed packages and their versions.
The output of the pip freeze command, which contains the package names and versions, is then written to the requirements.txt file.

The resulting requirements.txt file will typically have one line per package, with each line containing the package name followed by the version number in the format package==version. This file can be used to document the specific package versions required for a project or to recreate the same Python environment on another system.

By convention, the requirements.txt file is often used in Python projects to specify the project’s dependencies. Other developers or deployment environments can then use this file to install the same packages and versions by running pip install -r requirements.txt. This ensures consistency across different environments and makes it easier to reproduce the project’s Python environment.

Question 6

Q

How will you create an ignore GIT file in Visual Code and what kind of text will you add in it?

Answer

A

To create a Git ignore file in Visual Studio Code and specify which files or directories should be ignored by Git, you can follow these steps:

Open Visual Studio Code and navigate to the root directory of your project.
Create a new file in the root directory and name it “.gitignore”. Note that the file name starts with a dot.
Open the “.gitignore” file in the editor.
Inside the file, you can add specific file names, file patterns, or directory names that you want Git to ignore. Each entry should be on a new line.Here’s an example of what you might include in a .gitignore file:```plaintext
# Ignore files generated by the operating system
.DS_Store# Ignore the virtual environment foldervenv/# Ignore log files*.log# Ignore compiled Python files*.pyc
*.pyo# Ignore cache or temporary files__pycache__/# Ignore sensitive or private informationsecret.txt
credentials.json
```In the example above, the .gitignore file is configured to ignore the following:
- The .DS_Store file typically created by macOS.
- The venv/ folder, which might contain the virtual environment.
- Any log files ending with .log.
- Compiled Python files ending with .pyc or .pyo.
- The \_\_pycache\_\_/ directory created by Python.
- Specific sensitive or private files such as secret.txt or credentials.json.You can customize the .gitignore file based on the needs of your project. Consider excluding any files or directories that don’t need to be tracked by Git, such as build artifacts, temporary files, and personal configuration files.
Save the .gitignore file.

Once you’ve created and saved the .gitignore file, Git will recognize it and automatically ignore the specified files and directories when tracking changes or staging files for commits. This helps keep your repository clean and avoids including unnecessary files or sensitive information.

Example:
.gitignore:
~~~
env
.vscode
.DS_Store
~~~

Question 7

Q

What does the following do in terminal?
~~~
python3
import sqlalchemy
sqlalchemy.__version__
~~~

Answer

A

The commands you provided are executed in the terminal to interact with the Python interpreter and check the version of the SQLAlchemy package. Here’s what each command does:

python3: This command starts the Python interpreter in the terminal. It launches an interactive session where you can execute Python code.
import sqlalchemy: Once the Python interpreter is running, you use the import statement to import the SQLAlchemy package. This makes the SQLAlchemy module and its functionality available for use in the Python session.
sqlalchemy.\_\_version\_\_: After importing SQLAlchemy, this command retrieves the version of the SQLAlchemy package. sqlalchemy.\_\_version\_\_ is an attribute that holds the version information of the installed SQLAlchemy package.

By executing these commands in the terminal, you will launch the Python interpreter, import the SQLAlchemy package, and then retrieve and display the version of SQLAlchemy installed in your Python environment.

Note that the command python3 assumes that python3 is the command used to invoke the Python 3 interpreter on your system. Depending on your system’s configuration, you may need to use python instead of python3.

Question 8

Q

How will you setup SQLAlchemy?

Answer

A

Here are the local setup steps you can follow, with the code commands highlighted:

Local Setup Steps:

1.Create a folder for your project
- Create a directory where you want to keep your project files.
2.Open your folder in your IDE
- Open your preferred Integrated Development Environment (IDE) or code editor.
- Use the IDE’s file menu or command prompt to navigate to the project folder you created.
3.Open the terminal in your IDE
- In your IDE, locate the terminal or command prompt window.
- Open the terminal within your IDE to execute commands directly.

4.Install virtual environment

For Mac:

python3 -m pip install --user virtualenv

For Windows:
```
py -m pip install --user virtualenv
```

5.Create a virtual environment
- For Mac:
```
python3 -m venv env
```
- For Windows:
```
python -m venv env
```

6.Activate your environment

For Mac:
```
source ./env/bin/activate
```
For Windows:
```
.\env\Scripts\activate
```

7.Install SQLAlchemy
```
pip install sqlalchemy
```
8.Create a requirements file
```
pip freeze > requirements.txt
```

Following these steps will help you set up your local development environment with a virtual environment, install SQLAlchemy, and create a requirements file.

Remember to adjust the commands based on your system’s configuration and use the appropriate Python command (python or python3) depending on how your system is set up.

Question 9

Q

What does the following code mean?
~~~
from sqlalchemy import create_engine()

engine = create_engine(‘sqlite:///users.db’, echo=True)
~~~

Answer

A

The code you provided demonstrates the usage of SQLAlchemy to create a database engine connected to a SQLite database. Here’s a breakdown of the code:

from sqlalchemy import create_engine(): This line imports the create_engine function from the SQLAlchemy library. The create_engine function is responsible for creating a database engine, which acts as the interface between SQLAlchemy and the database system.
engine = create_engine('sqlite:///users.db', echo=True): This line creates an engine object using the create_engine function. The engine is configured to connect to a SQLite database stored in the file users.db.
- sqlite:///users.db: This is the connection URL for SQLite, specifying the file path to the SQLite database. In this case, it connects to users.db in the current directory.
- echo=True: This parameter enables logging and sets the echo flag to True. With echo=True, SQLAlchemy will print the SQL statements it generates and executes, which can be useful for debugging and understanding the database operations being performed.

After executing this code, the engine object represents a connection to the SQLite database specified by the URL. The engine can be used to interact with the database, including creating tables, executing SQL queries, and performing other database operations.

It’s worth noting that this code snippet demonstrates how to connect to a SQLite database specifically. If you want to connect to a different database system, such as MySQL or PostgreSQL, you would need to adjust the connection URL and potentially install additional database-specific SQLAlchemy dialects.

Question 10

Q

Explain the following code:
~~~
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base

from sqlalchemy.ext.declarative import declarative_base
# As of SQLAlchemy version 1.4, declarative_base() should now be imported from the sqlalchemy.orm package instead of the declarative.ext package.

engine = create_engine(‘sqlite:///users.db’, echo=True)
Base = declarative_base()

class User(Base):
__tablename__ = ‘users’

id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
nickname = Column(String)

def \_\_repr\_\_(self):
    return f'<User(name={self.name}, fullname={self.fullname}, nickname={self.nickname})>'

if __name__ == “__main__”:
Base.metadata.create_all(engine)
~~~

Answer

A

Certainly! Here’s a line-by-line explanation of the code with complete details:

```python
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base
~~~

The sqlalchemy library is imported.
create_engine, Column, Integer, String are specific components being imported from sqlalchemy. These components will be used in the script.
declarative_base is imported from the sqlalchemy.orm module. This function will be used to create a base class for declarative models.

```python
engine = create_engine(‘sqlite:///users.db’, echo=True)
~~~

A database engine is created using the create_engine function from SQLAlchemy.
'sqlite:///users.db' is the connection URL for a SQLite database named users.db.
The echo=True argument enables logging, causing SQLAlchemy to print the SQL statements it generates and executes.

```python
Base = declarative_base()
~~~

declarative_base() function is called to create a base class for declarative models.
The Base variable will be used as the superclass for all model classes defined in the script.

```python
class User(Base):
__tablename__ = ‘users’

id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
nickname = Column(String)

def \_\_repr\_\_(self):
    return f'<User(name={self.name}, fullname={self.fullname}, nickname={self.nickname})>' ~~~

A model class named User is defined, inheriting from the Base class.
\_\_tablename\_\_ = 'users' specifies the table name as 'users' for the User model class.
id, name, fullname, and nickname are defined as class attributes using the Column function. These attributes represent the columns in the users table.
id = Column(Integer, primary_key=True) defines the id column as an Integer type and specifies it as the primary key of the table.
name, fullname, and nickname are defined similarly as String columns.
The \_\_repr\_\_ method is defined to provide a string representation of a User object. It returns a formatted string that includes the values of the name, fullname, and nickname attributes.

```python
if __name__ == “__main__”:
Base.metadata.create_all(engine)
~~~

The code block within the if \_\_name\_\_ == "\_\_main\_\_": statement ensures that the following code is only executed when the script is run directly, not when imported as a module.
Base.metadata.create_all(engine) is called to create the database table(s) based on the defined model classes. It utilizes the engine object to establish a connection to the SQLite database and create the necessary table(s) if they don’t already exist.

Executing this script will create the users table in the SQLite database specified by the connection URL. The table will have columns corresponding to the defined model attributes (id, name, fullname, nickname).

Question 11

Q

Challenge 1: You’re creating the model for a movie theater’s movie database. First, import create_engine and declarative_base correctly from SQLAlchemy.

Challenge 2: Now it’s time to put those imports to work. Create a variable called engine and set it equal to create_engine. Use create_engine to create a SQLite database called movies.db and set echo to False. Next, create a variable named Base and set it equal to declarative_base. Then create the model class. It should be called Movie, and it takes Base as an argument (it inherits from Base). Inside of the class, set the \_\_tablename\_\_ to ’movies’.

Challenge 3: Finally, your table needs some columns. Add Column, Integer, and String to your sqlalchemy imports. Create an id column that holds numbers and is a primary key. Create two more columns called movie_title and genre. Both columns hold strings.

Answer

A

Answer to Challenge 1:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

Answer to Challenge 2:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///movies.db', echo=False)
Base = declarative_base()

class Movie(Base):
    \_\_tablename\_\_ = 'movies'

Answer to Challenge 3:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///movies.db', echo=False)
Base = declarative_base()

class Movie(Base):
    \_\_tablename\_\_ = 'movies'
    
    id = Column(Integer, primary_key=True)
    movie_title = Column(String)
    genre = Column(String)

Explanation:
Certainly! Here’s a line-by-line explanation of the code:

```python
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
~~~

The sqlalchemy library is imported.
create_engine, Column, Integer, and String are specific components being imported from sqlalchemy. These components will be used in the script.
declarative_base is imported from the sqlalchemy.ext.declarative module. This function will be used to create a base class for declarative models.

```python
engine = create_engine(‘sqlite:///movies.db’, echo=False)
~~~

A database engine is created using the create_engine function from SQLAlchemy.
'sqlite:///movies.db' is the connection URL for a SQLite database named movies.db.
The echo=False argument disables logging, so SQLAlchemy will not print the SQL statements it generates and executes.

```python
Base = declarative_base()
~~~

The declarative_base() function is called to create a base class for declarative models.
The Base variable will be used as the superclass for all model classes defined in the script.

```python
class Movie(Base):
__tablename__ = ‘movies’

id = Column(Integer, primary_key=True)
movie_title = Column(String)
genre = Column(String) ~~~

A model class named Movie is defined, inheriting from the Base class.
\_\_tablename\_\_ = 'movies' specifies the table name as 'movies' for the Movie model class.
id, movie_title, and genre are defined as class attributes using the Column function. These attributes represent the columns in the movies table.
id = Column(Integer, primary_key=True) defines the id column as an Integer type and specifies it as the primary key of the table.
movie_title and genre are defined as String columns.

Overall, this code sets up SQLAlchemy for a SQLite database named movies.db and defines a Movie class representing the structure of the movies table. The Movie class inherits from the Base class, and its attributes correspond to the columns in the table.

Question 12

Q

Explain the following code, in this explain sessionmaker and how meg_user is created, added in sequence:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///users.db', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class User(Base):
    \_\_tablename\_\_ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    nickname = Column(String)

    def \_\_repr\_\_(self):
        return f'<User(name={self.name}, fullname={self.fullname}, nickname={self.nickname})>'

if \_\_name\_\_ == "\_\_main\_\_":
    Base.metadata.create_all(engine)

    meg_user = User(name = 'Megan', fullname = 'Megan Amendola', nickname = 'Meg')
    print(meg_user.name)
    print(meg_user.id)
    session.add(meg_user)
    print(session.new)
    session.commit()
    print(meg_user.id)

Answer

A

This code snippet demonstrates the use of SQLAlchemy, a popular Object-Relational Mapping (ORM) library in Python, to create a SQLite database, define a table structure, and perform basic database operations.

Let’s go through the code step by step:

The create_engine function from SQLAlchemy is used to create an engine object that represents the database connection. In this case, it creates a connection to a SQLite database file named “users.db” and sets echo to False to disable the SQL output.
The sessionmaker function from SQLAlchemy’s ORM module is used to create a Session class. This class will serve as a factory for individual session objects, which will be used to interact with the database. The bind parameter is set to the previously created engine, indicating that the session objects will be associated with that database connection.
An instance of the Session class is created, which represents a database session. This session will be used to execute database operations.
The declarative_base function from SQLAlchemy is called to create a base class for the declarative system. This base class will be used to define ORM classes that will be mapped to database tables.
The User class is defined, inheriting from the Base class. This class represents the table structure and mapping for the “users” table. The \_\_tablename\_\_ attribute specifies the table name in the database. The class has four attributes: id, name, fullname, and nickname. These attributes are defined as Column objects, indicating their respective data types in the database.
The \_\_repr\_\_ method is overridden to provide a human-readable representation of a User object.
The if \_\_name\_\_ == "\_\_main\_\_": block is used to ensure that the code inside it is executed only when the script is run directly, not when it is imported as a module. Within this block, the create_all method of Base.metadata is called to create the table in the database based on the defined structure. In this case, it creates the “users” table.
An instance of the User class is created with the name, fullname, and nickname attributes set to specific values. This instance represents a new user, “Megan Amendola” with the nickname “Meg”.
The name attribute of meg_user is printed, which outputs “Megan” to the console.
The id attribute of meg_user is printed. Since this is a new user that has not been committed to the database yet, the id attribute will still be None.
The add method of the session is called, passing meg_user as an argument. This adds the meg_user object to the session’s pending list of changes.
The new attribute of the session is printed, which outputs [meg_user] to the console. This indicates that there is one pending change in the session, represented by the meg_user object.
The commit method of the session is called, which persists all pending changes to the database. In this case, it inserts the meg_user object as a new row in the “users” table.
The id attribute of meg_user is printed again. Since the user has been committed to the database, the id attribute will now hold the auto-generated primary key value assigned by the database.

Overall, this code sets up a database connection, defines a table structure using SQLAlchemy’s ORM, creates a new user object, adds it to a session, commits the changes to the database, and

retrieves the auto-generated primary key assigned to the user after insertion.

Question 13

Q

What does the following command do in terminal?

sqlite3 users.db in Mac
sqlite3.exe users.db in Windows

What does the following sequence of code will do?

sqlite3 users.db
.tables
Select * FROM users;
.exit

Answer

A

The command sqlite3 users.db in Mac and sqlite3.exe users.db in Windows are similar but are specific to the respective operating systems.

In both cases, the command launches the SQLite command-line shell, which provides an interactive environment for working with SQLite databases. The users.db argument specifies the path to the SQLite database file that you want to open.

Once the SQLite shell is launched, you can enter various SQL commands to interact with the database file. For example, you can create tables, insert data, query data, update records, delete records, and perform other database operations using SQLite’s SQL syntax.

The specific differences between running sqlite3 users.db on Mac and sqlite3.exe users.db on Windows are related to the file paths and the executable name. The .exe extension is used in Windows to denote an executable file, while it is not necessary in Mac or Unix-like systems. The sqlite3 command is typically available in the command line in both operating systems, but the executable name may vary depending on the system configuration and the location of the SQLite installation.

The sequence of commands you provided assumes that you are running the SQLite command-line shell (sqlite3 or sqlite3.exe) in the terminal and that you are working with a SQLite database file named “users.db”.

Let’s break down the sequence step by step:

sqlite3 users.db: This command launches the SQLite command-line shell and opens the “users.db” database file. It provides an interactive environment where you can execute SQL commands on the specified database.
.tables: Once you are in the SQLite command-line shell, entering the .tables command will list all the tables in the currently opened database (“users.db”). This command will display the names of all the tables available in the database.
Select * FROM users;: This SQL query is used to retrieve all the rows and columns from the “users” table in the database. By executing this command, you will see the result set containing the data stored in the “users” table. The * symbol in SELECT * indicates that all columns should be selected, and users is the name of the table you want to query.

Overall, this sequence of code launches the SQLite command-line shell, opens the “users.db” database file, lists the tables available in the database, and performs a select query to retrieve all the data from the “users” table.

Question 14

Q

Explain the following code:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///users.db', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class User(Base):
    \_\_tablename\_\_ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    nickname = Column(String)

    def \_\_repr\_\_(self):
        return f'<User(name={self.name}, fullname={self.fullname}, nickname={self.nickname})>'

if \_\_name\_\_ == "\_\_main\_\_":
    Base.metadata.create_all(engine)

    # meg_user = User(name = 'Megan', fullname = 'Megan Amendola', nickname = 'Meg')
    # print(meg_user.name)
    # print(meg_user.id)
    # session.add(meg_user)
    # print(session.new)
    # session.commit()
    # print(meg_user.id)

    new_users = [
        User(name='Grace', fullname='Grace Hopper', nickname='Pioneer'), 
        User(name='Alan', fullname='Alan Turing', nickname='Computer Scientist'),  
        User(name='Katherine', fullname='Katherine Johnson', nickname='') 
    ]

    session.add_all(new_users)
    session.commit()

    for user in new_users:
        print(user.id)

Answer

A

This code snippet demonstrates the use of SQLAlchemy, a Python ORM library, to define a database schema, create an engine to connect to a SQLite database, and perform database operations.

Let’s go through the code step by step:

The code imports necessary modules from SQLAlchemy: create_engine, Column, Integer, String, declarative_base, and sessionmaker. These modules provide the necessary functionality to interact with the database.
The create_engine function is called with the argument 'sqlite:///users.db' to create an engine object. This object represents the connection to the SQLite database named “users.db”. The echo parameter is set to False to disable the SQL output.
The sessionmaker function is called with the argument bind=engine to create a Session class. This class acts as a factory for individual session objects, which are used to interact with the database. The session objects created from this class will be bound to the engine created in the previous step.
An instance of the Session class is created, representing a database session. This session will be used to execute database operations.
The declarative_base function is called to create a base class, Base, for the declarative system. This base class will be used to define ORM classes that will be mapped to database tables.
The User class is defined, inheriting from the Base class. This class represents the table structure and mapping for the “users” table. The \_\_tablename\_\_ attribute specifies the name of the table in the database. The class has four attributes: id, name, fullname, and nickname. These attributes are defined as Column objects, indicating their respective data types in the database.
The \_\_repr\_\_ method is overridden to provide a human-readable representation of a User object.
The code checks if the script is being run directly by using the if \_\_name\_\_ == "\_\_main\_\_": block. This ensures that the following code is only executed when the script is run directly, not when it is imported as a module.
Base.metadata.create_all(engine) is called within the if \_\_name\_\_ == "\_\_main\_\_": block. This command creates the table structure in the database based on the defined ORM classes. In this case, it creates the “users” table in the “users.db” database.
A list new_users is created, containing three User objects with different values for the name, fullname, and nickname attributes. These objects represent new users to be added to the database.
session.add_all(new_users) is called to add all the objects in new_users to the session’s pending list of changes.
session.commit() is called to persist the pending changes to the database. This operation inserts the new users into the “users” table.
A loop is used to iterate over the new_users list, and for each user, the id attribute is printed. Since the users have been committed to the database, the id attribute will hold the auto-generated primary key values assigned by the database.

Overall, this code sets up the database connection, defines a table structure using SQLAlchemy’s ORM, adds multiple new user objects to a session, commits the changes to the database, and prints the auto-generated primary key values assigned to the new users after insertion.

Question 15

Q

How to import declarative_base in Python with SQLAlchemy?

Answer

A

To import the declarative_base function from SQLAlchemy, you have two options:

Option 1: Importing from sqlalchemy.ext.declarative:

```python
from sqlalchemy.ext.declarative import declarative_base
~~~

Option 2: Importing from sqlalchemy.orm:

```python
from sqlalchemy.orm import declarative_base
~~~

Both import statements bring in the declarative_base function from the respective modules. The declarative_base function is used to create a base class for declarative SQLAlchemy models. This base class serves as a foundation for defining ORM classes that will be mapped to database tables.

Once imported, you can use the declarative_base function to create the base class for your declarative models. Here’s an example:

```python
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
__tablename__ = ‘users’

id = Column(Integer, primary_key=True)
name = Column(String)
email = Column(String) ~~~

In this example, we import declarative_base from either sqlalchemy.ext.declarative or sqlalchemy.orm and create the Base class using the declarative_base() function. Then, we define a User class that inherits from Base, indicating that it is a declarative model. The User class represents a table named ‘users’ with three columns: id, name, and email.

Both options achieve the same result, and the choice between them depends on personal preference or the specific import style used in your codebase.

Question 16

Q

With the following code:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///users.db', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class User(Base):
    \_\_tablename\_\_ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    nickname = Column(String)

    def \_\_repr\_\_(self):
        return f'<User(name={self.name}, fullname={self.fullname}, nickname={self.nickname})>'

if \_\_name\_\_ == "\_\_main\_\_":
    Base.metadata.create_all(engine)

Python REPL is open and the following code is run in sequence:
~~~
python3
import models
jethro = models.User(name=’Jethr’, fullname=’Jethro Amendola’, nickname=’Bubba’)
models.session.add(jethro)
models.session.new
jethro.name = ‘Jethro’
models.session.new
models.session.commit()
models.session.new
jethro.nickname = ‘Jetty’
models.session.new
models.session.dirty
models.session.commit()
~~~

Answer

A

In the provided code snippet, it appears that you are running the Python REPL and interacting with the models module, which contains the SQLAlchemy setup and User class definition.

Here’s an explanation of the sequence of code execution and its output:

You open the Python REPL by running python3.
You import the models module, assuming it contains the code you provided earlier.
You create a new User object named jethro using the User class defined in the models module. This object represents a new user with the name “Jethr”, fullname “Jethro Amendola”, and nickname “Bubba”.
You add the jethro object to the session by calling models.session.add(jethro). This adds the object to the session’s pending list of changes.
You print models.session.new to check the new session before committing. Since the jethro object was added to the session, it should be displayed in the output as [<User(name=Jethr, fullname=Jethro Amendola, nickname=Bubba)>].
You correct a mistake in the name by setting jethro.name = 'Jethro'.
You print models.session.new again to check the new session. Since the jethro object has been modified, it should still be displayed in the output as [<User(name=Jethro, fullname=Jethro Amendola, nickname=Bubba)>].
You call models.session.commit() to commit the changes in the session to the database. This operation persists the new jethro object to the “users” table in the database.
You print models.session.new again to check the new session. Since the changes have been committed, the session should be empty, resulting in an output of [].
You change the nickname of the jethro object by setting jethro.nickname = 'Jetty'.
You print models.session.new to check the new session. Since you only modified an existing entry and didn’t add a new one, the session should still be empty, resulting in an output of [].
You use models.session.dirty to check the changed entry in the session. Since you modified the jethro object, it should be displayed in the output as {<User(name=Jethro, fullname=Jethro Amendola, nickname=Jetty)>} and finally it is committed again.

Overall, the code demonstrates the usage of SQLAlchemy’s session to add, modify, and commit changes to the database using the User class defined in the models module.

python3 
import models
jethro = models.User(name='Jethr', fullname='Jethro Amendola', nickname='Bubba')
models.session.add(jethro) #adding jethro
models.session.new #checking new session before commiting
jethro.name = 'Jethro' #correcting mistake in the name
models.session.new #checking new session again
models.session.commit() #comitting
models.session.new #new session now is empty as its committed
jethro.nickname = 'Jetty' #changing nickname
models.session.new #new session is still empty as its changed entry or not new try
models.session.dirty #using .dirty to check changed entry
models.session.commit() #committing the changes again

Question 17

Q

Update, rollback, delete
In the following code “models.py”:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///users.db', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class User(Base):
    \_\_tablename\_\_ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    nickname = Column(String)

    def \_\_repr\_\_(self):
        return f'<User(name={self.name}, fullname={self.fullname}, nickname={self.nickname})>'

if \_\_name\_\_ == "\_\_main\_\_":
    Base.metadata.create_all(engine)

In REPL, the following code is run in sequence to explain update, rollback, delete:

python3 
import models
jethro = models.session.query(models.User).filter(models.User.name=="Jethro").one()
jethro
jethro.nickname = "Bubba"
models.session.dirty
aang = models.User(name='Aang', fullname='Avatar Aang', nickname='Aangie')
models.session.add(aang)
models.session.new
models.session.rollback()
models.session.dirty
models.session.new
models.session.add(aang)
models.session.new
models.session.commit()
models.session.delete(aang)
models.session.commit()
models.session.query(models.User).filter(models.User.name=='Aang').one()
models.session.query(models.User).filter(models.User.name=='Aang').count()

Explain the above REPL code line by line covering complete and thorough explanations including what results each will give.

Answer

A

Here’s a detailed explanation of each line in the REPL code and the results they will produce:

import models: This line imports the models module, assuming it contains the code you provided earlier.
jethro = models.session.query(models.User).filter(models.User.name=="Jethro").one(): This line performs a query to retrieve a single User object from the database whose name attribute is equal to “Jethro”. The .query() method constructs a query object, .filter() applies a filtering condition, and .one() retrieves a single result. The retrieved object is assigned to the jethro variable.
jethro: This line prints the jethro object, which represents the user “Jethro” retrieved from the database.
jethro.nickname = "Bubba": This line updates the nickname attribute of the jethro object, changing it from its previous value to “Bubba”.
models.session.dirty: This line checks the session’s dirty attribute, which represents objects with changes that have not been committed to the database. Since you modified the jethro object, it should display {<User(name=Jethro, fullname=Jethro Amendola, nickname=Bubba)>} as the output.
aang = models.User(name='Aang', fullname='Avatar Aang', nickname='Aangie'): This line creates a new User object named aang with the specified attribute values.
models.session.add(aang): This line adds the aang object to the session’s pending list of changes.
models.session.new: This line checks the session’s new attribute, which represents new objects added to the session. Since you added the aang object, it should display [<User(name=Aang, fullname=Avatar Aang, nickname=Aangie)>] as the output.
models.session.rollback(): This line rolls back the session, undoing any pending changes that have not been committed. It effectively cancels the addition of the aang object to the session.
models.session.dirty: This line checks the session’s dirty attribute again. Since the session was rolled back, the output should be an empty set, IdentitySet([]), indicating there are no dirty objects.
models.session.new: This line checks the session’s new attribute again. Since the session was rolled back, the output should also be an empty set, IdentitySet([]), indicating there are no new objects.
models.session.add(aang): This line adds the aang object to the session again, as we want to perform a successful addition this time.
models.session.new: This line checks the session’s new attribute after adding aang again. The output should show [<User(name=Aang, fullname=Avatar Aang, nickname=Aangie)>], indicating that the aang object is now in the session’s new objects.
models.session.commit(): This line commits the pending changes in the session to the database. It persists the addition of the aang object to the “users” table.
models.session.delete(aang): This line removes the aang object from the session’s pending list of changes, effectively marking it for deletion from the database.
models.session.commit(): This line commits the deletion of the aang object to the database. It removes

the corresponding row from the “users” table.

models.session.query(models.User).filter(models.User.name=='Aang').one(): This line queries the database to retrieve a single User object whose name attribute is equal to “Aang”. It confirms whether the aang object has been successfully deleted. The .query() method creates a query object, .filter() applies a filter condition, and .one() retrieves a single result. If the deletion was successful, an exception will be raised because the aang object should not exist.
models.session.query(models.User).filter(models.User.name=='Aang').count(): This line counts the number of User objects in the database whose name attribute is equal to “Aang”. It confirms that the count is 0, indicating the successful deletion of the aang object.

This sequence of code demonstrates various SQLAlchemy operations, including querying, updating, adding, rolling back, deleting, and committing changes to the database using the User class defined in the models module.

Question 18

Q

Challenge 1: Now that your movie database model has been created, it’s time to add an entry. First, import sessionmaker from sqlalchemy.orm. After the engine variable, create a new variable called Session that uses sessionmaker to bind the engine. Then create a variable called session that calls Session.

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine(‘sqlite:///movies.db’, echo=False)
Base = declarative_base()

class Movie(Base):
    \_\_tablename\_\_ = ‘movies’

    id = Column(Integer, primary_key=True)
    movie_title = Column(String)
    genre = Column(String)

Challenge 2: Great! Now you can create a movie to add to your database. Use a variable named new_movie. Create a Movie() with whatever movie_title and genre you want. Then add your movie to session.

Challenge 3: To add your movie to the database, you need to commit it. Do that now.

Challenge 4: The movie theater is no longer showing your movie. Delete it from the database.

Answer

A

Answer to Challenge 1:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine(‘sqlite:///movies.db’, echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class Movie(Base):
    \_\_tablename\_\_ = ‘movies’

    id = Column(Integer, primary_key=True)
    movie_title = Column(String)
    genre = Column(String)

Answer to Challenge 2 & 3:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///movies.db', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class Movie(Base):
    \_\_tablename\_\_ = 'movies'

    id = Column(Integer, primary_key=True)
    movie_title = Column(String)
    genre = Column(String)

new_movie = Movie(
    movie_title="Harry Potter & the Philosopher's Stone",
    genre="Fantasy"
)
session.add(new_movie)
session.commit()

Answer to Challenge 4:
~~~
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine(‘sqlite:///movies.db’, echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class Movie(Base):
__tablename__ = ‘movies’

id = Column(Integer, primary_key=True)
movie_title = Column(String)
genre = Column(String)

new_movie = Movie(
movie_title=”Harry Potter & the Philosopher’s Stone”,
genre=”Fantasy”
)
session.add(new_movie)
session.commit()
session.delete(new_movie)
~~~

Question 19

Q

With the following code:
models.py

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///users.db', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class User(Base):
    \_\_tablename\_\_ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    nickname = Column(String)

    def \_\_repr\_\_(self):
        return f'<User(name={self.name}, fullname={self.fullname}, nickname={self.nickname})>'

if \_\_name\_\_ == "\_\_main\_\_":
    Base.metadata.create_all(engine)

The following the code is typed in terminal, REPL of Python, after activating env by typing down source ./env/bin/activate in Mac:

models.session.query(models.User)
for user in models.session.query(models.User):
    print(user)
for user in models.session.query(models.User.name):
    print(user)
for user in models.session.query(models.User.name):
    print(user.name)
for user in models.session.query(models.User.name).order_by(models.User.name):
    print(user.name)
for user in models.session.query(models.User.name).order_by(models.User.name.desc()):
    print(user.name)
for user in models.session.query(models.User.name).order_by(models.User.name)[:2]:
    print(user.name)
for user in models.session.query(models.User.name).order_by(models.User.name)[2:4]:
    print(user.name)
models.session.query(models.User).all()
models.session.query(models.User).order_by(models.User.name).first()
models.session.query(models.User).filter_by(name="Jethro")
models.session.query(models.User).filter(models.User.name=="Jethro")
for user in models.session.query(models.User).filter(models.User.name=="Jethro"):
    print(user)
me = models.User(name='Megan', fullname='Megan Amendola', nickname='Megatron')
models.session.add(me)
models.session.commit()
for user in models.session.query(models.User).filter(models.User.name=='Megan'):
    print(user)
for user in models.session.query(models.User).filter(models.User.name=='Megan').filter(models.User.nickname=='Megatron'):
    print(user)
cat = models.User(name='Joni', fullname='Joni the Cat', nickname='Key Grip')
models.session.add(cat)
models.session.new
models.session.commit()
for user in models.session.query(models.User):
    print(user)
cat.nickname = 'Producer'
for user in models.session.query(models.User):
    print(user)
models.session.commit()

Now explain the above code line by line

Answer

A

Certainly! Here’s the code with comments added to explain what each line does:

```python
# Query all users from the models.session
models.session.query(models.User)

Iterate over each user in the models.session.query(models.User) result and print them
for user in models.session.query(models.User):
print(user)

Query and print only the names of users from the models.session.query(models.User)
for user in models.session.query(models.User.name):
print(user)

Query and print the names of users using the attribute name
for user in models.session.query(models.User.name):
print(user.name)

Query and print the names of users ordered by name in ascending order
for user in models.session.query(models.User.name).order_by(models.User.name):
print(user.name)

Query and print the names of users ordered by name in descending order
for user in models.session.query(models.User.name).order_by(models.User.name.desc()):
print(user.name)

Query and print the first two names of users ordered by name
for user in models.session.query(models.User.name).order_by(models.User.name)[:2]:
print(user.name)

Query and print the third and fourth names of users ordered by name
for user in models.session.query(models.User.name).order_by(models.User.name)[2:4]:
print(user.name)

Query all users from the models.session
models.session.query(models.User).all()

Query and return the first user ordered by name
models.session.query(models.User).order_by(models.User.name).first()

Query users filtered by name being “Jethro”
models.session.query(models.User).filter_by(name=”Jethro”)

Query users filtered by name being “Jethro”
models.session.query(models.User).filter(models.User.name==”Jethro”)

Query and print users filtered by name being “Jethro”
for user in models.session.query(models.User).filter(models.User.name==”Jethro”):
print(user)

Create a new user instance
me = models.User(name=’Megan’, fullname=’Megan Amendola’, nickname=’Megatron’)

Add the new user to the session
models.session.add(me)

Commit the changes to the database
models.session.commit()

Query and print the user with name “Megan”
for user in models.session.query(models.User).filter(models.User.name==’Megan’):
print(user)

Query and print the user with name “Megan” and nickname “Megatron”
for user in models.session.query(models.User).filter(models.User.name==’Megan’).filter(models.User.nickname==’Megatron’):
print(user)

Create a new user instance
cat = models.User(name=’Joni’, fullname=’Joni the Cat’, nickname=’Key Grip’)

Add the new user to the session
models.session.add(cat)

Check for new objects in the session
models.session.new

Commit the changes to the database
models.session.commit()

Query and print all users
for user in models.session.query(models.User):
print(user)

Update the nickname of the user named “Joni” to “Producer”
cat.nickname = ‘Producer’

Query and print all users
for user in models.session.query(models.User):
print(user)

Commit the changes to the database
models.session.commit()
~~~

Now each line has a comment explaining its purpose.

Sure! Let’s go through the code line by line and explain each line’s purpose:

models.session.query(models.User): This line queries all users from the models.session using the query method of the session. It retrieves all instances of the User model.
for user in models.session.query(models.User):: This line iterates over each user in the result obtained from the previous query.
print(user): This line prints the user object, which represents a row in the users table, to the console.
for user in models.session.query(models.User.name):: This line queries and retrieves only the name column from the User model for each user in the table.
print(user): This line prints the user object, which contains the name of each user, to the console.
for user in models.session.query(models.User.name):: This line queries and retrieves only the name column from the User model for each user in the table.
print(user.name): This line prints the name attribute of each user to the console.
for user in models.session.query(models.User.name).order_by(models.User.name):: This line queries and retrieves the name column from the User model for each user, ordered by the name column in ascending order.
print(user.name): This line prints the name attribute of each user, which is now ordered alphabetically, to the console.
for user in models.session.query(models.User.name).order_by(models.User.name.desc()):: This line queries and retrieves the name column from the User model for each user, ordered by the name column in descending order.
print(user.name): This line prints the name attribute of each user, which is now ordered in reverse alphabetical order, to the console.
for user in models.session.query(models.User.name).order_by(models.User.name)[:2]:: This line queries and retrieves the name column from the User model for the first two users, ordered by the name column.
print(user.name): This line prints the name attribute of each user from the previous query to the console.
for user in models.session.query(models.User.name).order_by(models.User.name)[2:4]:: This line queries and retrieves the name column from the User model for users with indices 2 and 3 (3rd and 4th users), ordered by the name column.
print(user.name): This line prints the name attribute of each user from the previous query to the console.
models.session.query(models.User).all(): This line queries and retrieves all users from the User model.
models.session.query(models.User).order_by(models.User.name).first(): This line queries and retrieves the first user from the User model, ordered by the name column.
models.session.query(models.User).filter_by(name="Jethro"): This line queries and filters users from the User model by the name “Jethro”.
models.session.query(models.User).filter(models.User.name=="Jethro"): This line queries and filters users from the User model by the name “Jethro”.
for user in models.session.query(models.User).filter(models.User.name=="Jethro"):: This line queries and retrieves users from the User model who have the

name “Jethro” and iterates over each user.

print(user): This line prints the user object, which represents a user with the name “Jethro”, to the console.
me = models.User(name='Megan', fullname='Megan Amendola', nickname='Megatron'): This line creates a new instance of the User model with the specified attributes.
models.session.add(me): This line adds the newly created me user to the session.
models.session.commit(): This line commits the changes made to the session, persisting the new user to the database.
for user in models.session.query(models.User).filter(models.User.name=='Megan'):: This line queries and retrieves users from the User model with the name “Megan”.
print(user): This line prints the user object, which represents a user with the name “Megan”, to the console.
for user in models.session.query(models.User).filter(models.User.name=='Megan').filter(models.User.nickname=='Megatron'):: This line queries and retrieves users from the User model with the name “Megan” and the nickname “Megatron”.
print(user): This line prints the user object, which represents a user with the name “Megan” and the nickname “Megatron”, to the console.
cat = models.User(name='Joni', fullname='Joni the Cat', nickname='Key Grip'): This line creates a new instance of the User model with the specified attributes.
models.session.add(cat): This line adds the newly created cat user to the session.
models.session.new: This line checks for new objects in the session.
models.session.commit(): This line commits the changes made to the session, persisting the new user to the database.
for user in models.session.query(models.User):: This line queries and retrieves all users from the User model.
print(user): This line prints the user object, which represents each user in the table, to the console.
cat.nickname = 'Producer': This line updates the nickname attribute of the cat user to “Producer”.
for user in models.session.query(models.User):: This line queries and retrieves all users from the User model.
print(user): This line prints the user object, which represents each user in the table (including the updated cat user), to the console.
models.session.commit(): This line commits the changes made to the session, persisting the updated user to the database.

That’s a breakdown of each line’s purpose and functionality in the given code.

Question 20

Q

Challenge 1: The movie theater has added all of their current offerings to the database you created, but they need some help finding the right movies. They’re looking to market all of their romance movies for Valentine’s Day. Create a variable called romance_movies. It should hold all of the movies with a genre of ‘Romance’.

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine(‘sqlite:///movies.db’, echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class Movie(Base):
    \_\_tablename\_\_ = ‘movies’

    id = Column(Integer, primary_key=True)
    movie_title = Column(String)
    genre = Column(String)

Write your code below

Challenge 2:
Today, the theater is updating the list of movies on the sign outside. Create a variable named all_movies. It should hold all of the movies in the database in alphabetical order.

Answer

A

Answer to Challenge 1:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///movies.db', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class Movie(Base):
    \_\_tablename\_\_ = 'movies'

    id = Column(Integer, primary_key=True)
    movie_title = Column(String)
    genre = Column(String)

Write your code below
romance_movies = session.query(Movie).filter_by(genre="Romance")

Answer to Challenge 2:
~~~
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine(‘sqlite:///movies.db’, echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class Movie(Base):
__tablename__ = ‘movies’

id = Column(Integer, primary_key=True)
movie_title = Column(String)
genre = Column(String)

Write your code below
romance_movies = session.query(Movie).filter_by(genre=”Romance”)
all_movies = session.query(Movie).order_by(Movie.movie_title)
~~~

Question 21

Q

What is the difference between session.add() and session.add_all() ?

Answer

A

In SQLAlchemy, the session.add() and session.add_all() methods are used to add objects to a session for later persistence in the database. However, there is a difference in how they handle multiple objects.

session.add(object): This method is used to add a single object to the session. It takes a single object as an argument and adds it to the session’s tracking system. The object is marked as “pending,” indicating that it will be inserted into the database upon the next flush or commit operation.

Example:

```python
user = User(name=’John’)
session.add(user)
~~~

session.add_all(objects): This method is used to add multiple objects to the session at once. It takes an iterable (such as a list or a tuple) of objects as an argument and adds all the objects to the session’s tracking system. Like session.add(), the objects are marked as “pending” and will be inserted into the database upon the next flush or commit operation.

Example:

```python
users = [User(name=’John’), User(name=’Jane’)]
session.add_all(users)
~~~

In summary, session.add() is used for adding a single object to the session, while session.add_all() is used for adding multiple objects to the session simultaneously.

Question 22

Q

What does this code do in Visual Studio Terminal?
~~~
python3 -m venv env
~~~

Answer

A

The code python3 -m venv env is used to create a virtual environment for a Python project in Visual Studio’s terminal.

Let’s break it down:

python3: This command specifies the Python interpreter to use. In this case, it assumes that you have Python 3 installed and configured in your environment.
-m venv: This is a module provided by Python, which stands for “venv” (short for virtual environment). It allows you to create isolated Python environments to work on projects without interfering with the global Python installation.
env: This is the name of the directory where the virtual environment will be created. You can choose any name you like, but “env” is a common convention. The virtual environment directory will contain all the necessary files and directories to isolate the Python environment.

When you run this command in the Visual Studio Terminal, it will create a virtual environment named “env” in the current directory. This virtual environment will have its own Python interpreter and package dependencies, allowing you to install packages specific to your project without affecting the global Python installation or other projects.

Question 23

Q

In Visual Studio Terminal, how would you create a virtual environment for Python and how would you activate it?

Answer

A

python3 -m venv env
source ./env/bin/activate

To create a virtual environment for Python in Visual Studio’s terminal and activate it, you can follow these steps:

Open Visual Studio Code and navigate to the integrated terminal by selecting “View” from the top menu and choosing “Terminal” or by using the keyboard shortcut Ctrl+ (backtick).
Once the terminal is open, navigate to the directory where you want to create the virtual environment. You can use the cd command to change directories. For example, if you want to navigate to a directory named “my_project,” you would use the following command:
~~~

cd my_project

3. In the terminal, run the following command to create a virtual environment using the `venv` module:

python3 -m venv env

This command will create a virtual environment named "env" in the current directory.

4. To activate the virtual environment, you need to run the appropriate activation command based on your operating system:

- For Windows:

.\env\Scripts\activate

- For macOS and Linux:

source env/bin/activate
~~~

After running the activation command, you should see that the terminal prompt changes to reflect that you are now in the virtual environment.

That’s it! Your virtual environment is now created and activated. You can install Python packages specific to your project without interfering with the global Python installation or other projects.

Question 24

Q

create a file called .gitignore and add

Explain the following steps line by line, done using Visual Studio Code for a python program:

python3 -m venv env
source ./env/bin/activate
pip install sqlalchemy
pip freeze > requirements.txt
# "env
# /\_\_pycache\_\_
# .vscode
# .DS_Store"
git init
#create a git repo by going to github.com/new
#copy the code of git repository (git remote add origin https://github.com/sethubridge/book-database.git)
#paste the code to terminal
git add .
git commit -m 'Initial commit'
git push origin master
git branch
git branch -a

Answer

A

Sure! Here’s an explanation of each step, line by line:

python3 -m venv env: This creates a virtual environment named “env” using Python 3. It sets up an isolated environment with its own Python interpreter and package dependencies.
source ./env/bin/activate: This activates the virtual environment, allowing you to work within it. The command sets the environment variables necessary for the virtual environment to take precedence over the global Python installation.
pip install sqlalchemy: This installs the SQLAlchemy package into the virtual environment. SQLAlchemy is a Python SQL toolkit and Object-Relational Mapping (ORM) library.
pip freeze > requirements.txt: This command generates a list of all installed packages and their versions in the virtual environment. It then writes this information to a file called “requirements.txt”. The requirements file can be used to recreate the same environment on another system.
git init: This initializes a new Git repository in the current directory. It sets up the necessary Git configuration and creates an empty repository.
git remote add origin https://github.com/sethubridge/book-database.git: This adds a remote repository named “origin” and associates it with the provided URL. In this case, the remote repository is hosted on GitHub at the specified URL.
git add .: This adds all the files in the current directory to the staging area. The staging area is where you prepare files for a commit.
git commit -m 'Initial commit': This creates a commit with a message of ‘Initial commit’. The commit includes all the changes you added to the staging area in the previous step.
git push origin master: This command pushes your local commits to the remote repository named “origin” on the “master” branch. It syncs your local repository with the remote repository, making your changes accessible to others.
git branch: This command lists all the branches in your local repository. It shows the branch you are currently on and any other branches you may have created.
git branch -a: This command lists all branches, both local and remote, in your repository. It provides an overview of all branches available in your local and remote repositories.

Note: Some steps in the provided sequence involve actions outside of Visual Studio Code, such as creating a new repository on GitHub and copying the remote repository URL.

Treehouse Notes:
Sure! Here are the steps with code highlighted:

Local Setup Steps:
1. Create a folder for your project.
2. Open your folder in your IDE.
3. Open the terminal in your IDE.
4. Create a virtual environment.

Code:
- Mac:

python3 -m venv env

Windows:

python -m venv env

Activate your environment.
Code:
- Mac:

source ./env/bin/activate

Windows:

.\env\Scripts\activate

Install SQLAlchemy.
Code:

pip install sqlalchemy

Create a requirements file.
Code:

pip freeze > requirements.txt

These steps will help you set up your local environment for your Python project using Visual Studio Code or any other IDE.

Installing and using Git on your computer:
* Windows: Git is not pre-installed on Windows machines. Check out our video on Git installation for Windows. Be sure to check the Teacher’s Notes on the video page for additional notes, as the installation process has changed slightly since the video was recorded.
* Mac: Every Mac comes with Git pre-installed! To access it, all you need to do is open a terminal with a Bash prompt. You can find instructions for opening the Terminal program in this Treehouse Blog post.
* Linux: Linux systems also come with Git pre-installed. If you’re running Linux, we assume you already know how to get to a terminal, but if you’re stuck, click the Get Help button on this page to ask for help in the Treehouse Community forum.
* Watch the Introduction to Git course to learn more about git and version control.

Question 25

Q

In .gitignore added the following, what does it mean?

env

/\_\_pycache\_\_
.vscode

.DS_Store

Answer

A

The entries added to the .gitignore file have specific meanings:

env: This line in the .gitignore file indicates that the env directory should be ignored by Git. The env directory typically contains the virtual environment files and dependencies, and it is usually excluded from version control since it can be easily recreated.
/\_\_pycache\_\_: This line instructs Git to ignore all the \_\_pycache\_\_ directories and their contents. These directories are automatically generated by Python when it compiles Python bytecode files (*.pyc files) to improve execution speed. Since these files can be easily regenerated, they are commonly excluded from version control.
.vscode: This line tells Git to ignore the .vscode directory. This directory is typically created by Visual Studio Code and contains settings, configuration files, and workspace-specific files. It is recommended to exclude this directory from version control to avoid sharing personal or machine-specific settings.
.DS_Store: This line is specific to macOS. .DS_Store files are created by the operating system to store custom attributes of a folder, such as icon positions or view settings. Since these files are not essential to the codebase, it’s common to exclude them from version control.

By adding these entries to the .gitignore file, you are instructing Git to ignore these files and directories when performing version control operations like git add or git commit. This helps to keep your repository clean and avoids including unnecessary or machine-specific files.

Question 26

Q

What is \_\_repr\_\_(self) method in Python?

Answer

A

In Python, the \_\_repr\_\_() method is a special method used to define a string representation of an object. It is a built-in method that provides a standard way to represent an object as a string. The \_\_repr\_\_() method should return a string that, when evaluated using Python’s eval() function, would create a new object with the same state as the original object.

Here’s an example of how the \_\_repr\_\_() method is typically used:

```python
class MyClass:
def __init__(self, name):
self.name = name

def \_\_repr\_\_(self):
    return f"MyClass(name='{self.name}')"

obj = MyClass(“example”)
print(obj) # Output: MyClass(name=’example’)
~~~

In the above example, the MyClass defines a \_\_repr\_\_() method that returns a string representation of the object. When the print() function is called with an instance of MyClass, it automatically calls the \_\_repr\_\_() method and displays the returned string.

The \_\_repr\_\_() method is often used for debugging and providing a human-readable representation of an object. By implementing this method, you can customize how your object is represented as a string, making it easier to understand and work with in various contexts.

Question 27

Q

Explain the following codes:
1. models.py

from sqlalchemy import create_engine, Column, Integer, String, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///books.db', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class Book(Base):
    \_\_tablename\_\_ = 'books'

    id = Column(Integer, primary_key=True)
    title = Column('Title', String)
    author = Column('Author', String)
    published_date = Column('Published', Date)
    price = Column('Price', Integer)

    def \_\_repr\_\_(self):
        return f'Title: {self.title} Author: {self.author} Published: {self.published_date} Price: {self.price}'

create a database
# books.db
# create a model
# title, author, date published, price

app.py

from models import (Base, session, 
                    Book, engine)

import models
# main menu - add, search, analysis, exit, view
# add books to the database
# edit books
# delete books
# search books
# data cleaning
# loop runs program

if \_\_name\_\_ == '\_\_main\_\_':
    Base.metadata.create_all(engine)

In Terminal of VS Code:
~~~
python3 app.py
git add .
git commit m- ‘added our database model’
git push origin main
~~~

Answer

A

models.py:
This code defines the data model for a book database using SQLAlchemy, a Python library for working with databases. Let’s break it down step by step:

The code begins by importing the necessary modules from SQLAlchemy: create_engine, Column, Integer, String, Date, declarative_base, and sessionmaker. These modules provide the required functionality for creating the database engine, defining columns and data types, and creating sessions to interact with the database.
engine = create_engine('sqlite:///books.db', echo=False) creates an engine object that connects to a SQLite database named “books.db”. The echo=False parameter is used to suppress SQLAlchemy’s logging output.
Session = sessionmaker(bind=engine) creates a session factory bound to the database engine. The bind argument associates the session factory with the engine.
session = Session() creates an instance of the session. The session object allows interaction with the database.
Base = declarative_base() creates a base class for declarative SQLAlchemy models. It serves as the parent class for all other models and provides common functionality.
class Book(Base): defines the Book class, which inherits from Base. This class represents a table named “books” in the database.
\_\_tablename\_\_ = 'books' specifies the name of the table as “books”.
id, title, author, published_date, and price are declared as columns of the Book class. Each column corresponds to a field in the “books” table and has its own data type specified (Integer, String, Date).
\_\_repr\_\_(self) is a special method that defines the string representation of a Book object. It returns a formatted string containing the book’s title, author, published date, and price.

app.py:
This code interacts with the models.py file and defines the main program flow for a book database application. Here’s an overview:

from models import (Base, session, Book, engine) imports the necessary objects from the models.py file, including Base, session, Book, and engine.
The code then proceeds to define the functionality of the book database application, such as the main menu options, adding books, editing books, deleting books, searching books, and data cleaning.
The if \_\_name\_\_ == '\_\_main\_\_': block ensures that the following code is executed only if the script is run directly and not imported as a module.
Base.metadata.create_all(engine) creates the database tables based on the defined models (including the Book table) if they do not already exist.

In Terminal of VS Code:
These commands are executed in the terminal to perform various actions related to version control using Git:

python3 app.py runs the Python script app.py in the terminal.
git add . stages all the changes in the repository for the next commit.
git commit -m 'added our database model' creates a commit with a commit message of ‘added our database model’, saving the staged changes to the local repository.
git push origin main pushes the local commits to the remote repository named ‘origin’ on the ‘main’ branch, synchronizing the local and remote repositories.

Question 28

Q

Explain Base.metadata.create_all(engine) in detail?

Answer

A

Certainly! Let’s delve into the details of Base.metadata.create_all(engine):

In SQLAlchemy, Base is an instance of the declarative_base() class, which is used as the base class for all declarative models. The metadata attribute of Base represents a collection of database schema elements, including tables, indexes, and constraints. It acts as a container for all the schema-related information associated with the models defined in the application.

create_all(engine) is a method of metadata that creates database tables based on the schema defined by the models. The engine parameter is an instance of the create_engine() class and represents the database connection. The engine object specifies the database URL and connection options.

When you call Base.metadata.create_all(engine), SQLAlchemy analyzes the metadata and generates the necessary SQL statements to create the tables in the specified database. It then executes these SQL statements through the engine object to create the tables.

This operation ensures that the database schema matches the models defined in the application. If the tables already exist, create_all() will not modify them. It only creates the tables that are missing from the database schema.

The purpose of calling Base.metadata.create_all(engine) in the provided code is to ensure that the necessary tables for the book database application are created in the SQLite database specified by the engine object. By executing this statement, the database tables are set up before the application runs, ensuring that the required schema is in place to store book-related data.

It’s worth noting that the create_all() method is typically called during the application’s setup or initialization phase to ensure that the database structure is created or updated as needed.

Question 29

Q

What is declarative model in Python?

Answer

A

A declarative model in SQLAlchemy refers to the use of a high-level, class-based approach to define database tables and their relationships. It allows you to define your database schema using Python classes, with the framework automatically generating the necessary SQL statements to create and manipulate the tables.

In SQLAlchemy, the declarative_base() function provides a base class that acts as a common ancestor for all the models in an application. This base class serves as a mapping between the Python classes and the database tables. It provides a set of pre-defined attributes and methods that facilitate the mapping and interaction with the database.

With declarative models, you can define your database tables as Python classes, where each class represents a table and each attribute within the class represents a column. SQLAlchemy’s mapping system allows you to define relationships between tables and specify various column types, constraints, and other attributes.

Declarative models provide a clean and intuitive way to define and work with databases, as you can define the structure and behavior of the database using familiar object-oriented programming concepts. The framework takes care of translating the Python class definitions into the corresponding database schema and provides a powerful ORM (Object-Relational Mapping) layer that allows you to interact with the database using Python objects.

Benefits of using declarative models include:

Simplicity and readability: Declarative models make it easier to understand the structure and relationships of the database tables by representing them as Python classes.
Productivity: With declarative models, you can focus on the high-level logic of your application rather than dealing with low-level SQL statements and database-specific details.
Data integrity and consistency: Declarative models help enforce data integrity by automatically generating appropriate constraints, foreign keys, and data types based on the class definitions.
Object-oriented interface: Declarative models provide an intuitive interface to interact with the database, allowing you to manipulate and query data using Python objects and methods.

Overall, declarative models simplify the process of working with databases by leveraging the power of Python’s object-oriented programming and abstraction capabilities while still allowing fine-grained control over the database schema and operations.

Question 30

Q

Explain the following app.py in detail. The models.py code is given for context:
models.py

from sqlalchemy import create_engine, Column, Integer, String, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///books.db', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class Book(Base):
    \_\_tablename\_\_ = 'books'

    id = Column(Integer, primary_key=True)
    title = Column('Title', String)
    author = Column('Author', String)
    published_date = Column('Published', Date)
    price = Column('Price', Integer)

    def \_\_repr\_\_(self):
        return f'Title: {self.title} Author: {self.author} Published: {self.published_date} Price: {self.price}'

create a database
# create a model
# title, author, date published, price

app.py
~~~
from models import (Base, session,
Book, engine)

def menu():
while True:
print(‘’’
\nPROGRAMMING BOOKS
\r1) Add book
\r2) View all books
\r3) Search for book
\r4) Book Analysis
\r5) Exit’’’)
choice = input(‘What would you like to do? ‘)
if choice in [‘1’, ‘2’, ‘3’, ‘4’, ‘5’]:
return choice
else:
input(‘’’’
\rPlease choose one of the options above.
\rA number from 1-5.
\rPress Enter to try again.
‘’’)

main menu - add, search, analysis, exit, view
# add books to the database
# edit books
# delete books
# search books
# data cleaning
# loop runs program

def app():
app_running = True
while app_running:
choice = menu()
if choice == ‘1’:
# add book
pass
elif choice == ‘2’:
# view books
pass
elif choice == ‘3’:
# search book
pass
elif choice == ‘4’:
# analysis
pass
else:
print(‘GOODBYE!’)
app_running = False

if __name__ == ‘__main__’:
Base.metadata.create_all(engine)
app()
~~~

Answer

A

The provided code consists of two files: models.py and app.py. Let’s go through each file and explain their functionality in detail.

models.py:
This file defines the database model for books using SQLAlchemy, a Python library for working with databases. Here’s a breakdown of the code:

Import Statements:
- create_engine is imported from sqlalchemy to create a database engine.
- Column, Integer, String, and Date are imported from sqlalchemy to define the columns of the book table.
- declarative_base is imported from sqlalchemy.ext.declarative to create the base class for the model.
- sessionmaker is imported from sqlalchemy.orm to create a session factory.
Database Setup:
- engine is created using create_engine to connect to an SQLite database file called “books.db”.
- Session is created using sessionmaker(bind=engine) to create a session factory tied to the database engine.
- session is instantiated as an instance of the session factory, which will be used to interact with the database.
- Base is set as declarative_base(), which will be used as the base class for the book model.
Book Model:
- The Book class is defined, which inherits from Base.
- The \_\_tablename\_\_ attribute specifies the table name as “books”.
- Several columns are defined using Column. Each column represents a specific attribute of a book, such as id, title, author, published_date, and price.
- The \_\_repr\_\_ method is overridden to provide a string representation of a book instance.

app.py:
This file contains the main application code. Let’s examine it step by step:

Import Statements:
- Base, session, Book, and engine are imported from models. These are necessary to access the database model and establish a connection to the database.
Menu Function:
- The menu function is defined to display the menu options to the user and get their choice.
- Inside a while loop, the menu options are printed using print statements.
- The user’s choice is obtained using the input function and stored in the choice variable.
- If the user’s choice is valid (i.e., it is one of the options ‘1’, ‘2’, ‘3’, ‘4’, or ‘5’), the choice is returned from the function.
- If the user’s choice is invalid, they are prompted to try again.
Main Application Function:
- The app function is defined to run the main application.
- A boolean variable app_running is set to True to control the application loop.
- Inside the while loop, the menu function is called to get the user’s choice.
- Depending on the choice, different code blocks (currently represented by pass) will be executed.
- If the choice is ‘1’, ‘2’, ‘3’, or ‘4’, the corresponding code block will be executed.
- If the choice is ‘5’, the message “GOODBYE!” is printed, and app_running is set to False, terminating the loop.
Main Block:
- The if \_\_name\_\_ == '\_\_main\_\_': block checks if the script is being executed as the main module.
- If so, it creates the database tables defined in the Base model using Base.metadata.create_all(engine).
- Then, the app function is called to run the application.

Overall, this code provides the structure for a menu-driven application that allows users to perform various operations related to programming books, such as adding books, viewing books, searching for books, performing book analysis, and exiting the application. However, the specific logic for each menu option is currently missing and needs to be implemented in the corresponding code blocks.

Question 31

Q

add books to the database

In the following code, explain import of datetime, csv and also explain in detail about the function called add_csv and also the function called clean_date, explain with results, examples and why each code is typed like that.

app.py
~~~
from models import (Base, session,
Book, engine)
import datetime
import csv

def menu():
while True:
print(‘’’
\nPROGRAMMING BOOKS
\r1) Add book
\r2) View all books
\r3) Search for book
\r4) Book Analysis
\r5) Exit’’’)
choice = input(‘What would you like to do? ‘)
if choice in [‘1’, ‘2’, ‘3’, ‘4’, ‘5’]:
return choice
else:
input(‘’’’
\rPlease choose one of the options above.
\rA number from 1-5.
\rPress Enter to try again.
‘’’)

main menu - add, search, analysis, exit, view
# edit books
# delete books
# search books
# data cleaning
# loop runs program

def clean_date(date_str):
months = [‘January’, ‘February’, ‘March’, ‘April’, ‘May’, ‘June’,
‘July’, ‘August’, ‘September’, ‘October’, ‘November’, ‘December’]
split_date = date_str.split(‘ ‘)
print(split_date)
month = int(months.index(split_date[0]) + 1)
day = int(split_date[1].split(‘,’)[0])
year = int(split.date[2])
return datetime.date(year, month, day)

def add_csv():
with open(‘suggest_books.csv’) as csvfile:
data = csv.reader(csvfile)
for row in data:
print(row)

def app():
app_running = True
while app_running:
choice = menu()
if choice == ‘1’:
# add book
pass
elif choice == ‘2’:
# view books
pass
elif choice == ‘3’:
# search book
pass
elif choice == ‘4’:
# analysis
pass
else:
print(‘GOODBYE!’)
app_running = False

if __name__ == ‘__main__’:
Base.metadata.create_all(engine)
# app()
# add_csv()
clean_date(‘October 25, 2017’)
~~~

Answer

A

In the provided code snippet, the datetime and csv modules are imported, and two functions add_csv() and clean_date(date_str) are defined. Let’s explain each of these elements in detail:

Import Statements:
- datetime is imported to provide functionality for working with dates and times.
- csv is imported to read and process CSV (Comma-Separated Values) files.
clean_date(date_str) Function:
- This function takes a date string (date_str) as input and cleans it by converting it into a datetime.date object.
- The function starts by defining a list called months that contains the names of the months.
- The date_str is split into separate components using the split() method. The resulting list is stored in split_date.
- The function then prints the split_date list to show the intermediate result.
- The month is extracted from split_date by finding its index in the months list and adding 1 to it (since Python indexes start from 0, but month numbers start from 1).
- The day is extracted from split_date by splitting the second element on the comma and converting it to an integer.
- The year is extracted from split_date by accessing the third element (index 2) and converting it to an integer.
- Finally, the function returns a datetime.date object created using the extracted year, month, and day.
Example: When clean_date('October 25, 2017') is called, it will print ['October', '25,', '2017'] (the intermediate result) and return a datetime.date object representing October 25, 2017.The function is designed this way to handle a specific date format where the month name is followed by the day and year separated by a comma. It splits the string based on spaces and uses the positions of the components to extract the month, day, and year.
add_csv() Function:
- This function reads and processes a CSV file called “suggest_books.csv”.
- It starts by opening the file using the open() function and a context manager (with statement). The file is automatically closed when the block is exited.
- The file is then passed to csv.reader() to create a reader object (data) that can iterate over the rows of the CSV file.
- Inside a for loop, each row is printed using the print() function.
Example: Assuming “suggest_books.csv” contains the following rows:
```
Title,Author,Published Date,Price
Book 1,Author 1,January 1, 2020,10
Book 2,Author 2,February 2, 2021,20
```
When add_csv() is called, it will print each row of the CSV file:
```
['Title', 'Author', 'Published Date', 'Price']
['Book 1', 'Author 1', 'January 1, 2020', '10']
['Book 2', 'Author 2', 'February 2, 2021', '20']
```
The function is currently implemented to simply print the rows, but you can modify it to perform specific actions with the data from the CSV file, such as adding it to the database or performing further processing.

It’s worth noting that in the provided code, the app() function and the call to add_csv() are commented out, so they won’t be executed. Only the `clean_date

(‘October 25, 2017’)` function call will be executed when the script is run.

More detailed explanation of split_date if given input string is ‘October 25, 2017:
Certainly! Let’s take a more detailed look at the split_date variable when the input string is ‘October 25, 2017’.

The split_date variable is created by splitting the date_str using the space (‘ ‘) as the delimiter. Here’s the breakdown:

Initial Input:
- date_str = 'October 25, 2017'
Splitting the String:
- split_date = date_str.split(' ')
- The split() method splits the string into a list of substrings, using the space (‘ ‘) as the delimiter.
- After splitting, the resulting list will contain three elements: [‘October’, ‘25,’, ‘2017’].
Intermediate Result:
- The intermediate result is printed using print(split_date).
- The output will be: ['October', '25,', '2017'].

Let’s examine each element in the split_date list:

Element 1: ‘October’
- This element represents the month portion of the date.
Element 2: ‘25,’
- This element represents the day portion of the date, but it also contains a comma (‘,’) at the end.
Element 3: ‘2017’
- This element represents the year portion of the date.

By splitting the date_str using the space (‘ ‘) as the delimiter, the month, day, and year are separated into individual elements of the split_date list.

Even More Detailed Explanation of split date and converting string date to integer:
Certainly! Let’s break down the code in more detail with the input string ‘October 25, 2017’.

List of Months:
- months = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']
- This list contains the names of the months in the correct order.
Splitting the Date String:
- split_date = date_str.split(' ')
- The split() method splits the date_str string into a list of substrings using the space (‘ ‘) as the delimiter.
- After splitting, the resulting list will be ['October', '25,', '2017'].
Printing the Split Date:
- print(split_date)
- This line prints the intermediate result of the split_date list.
- The output will be ['October', '25,', '2017'].
Extracting the Month:
- month = int(months.index(split_date[0]) + 1)
- split_date[0] represents the first element of the split_date list, which is ‘October’.
- months.index(split_date[0]) finds the index of ‘October’ in the months list, which is 9 (indexes start from 0).
- Since the month number should start from 1, we add 1 to the index: months.index(split_date[0]) + 1.
- The resulting value is converted to an integer and stored in the month variable.
Extracting the Day:
- day = int(split_date[1].split(',')[0])
- split_date[1] represents the second element of the split_date list, which is ‘25,’.
- split_date[1].split(',') splits the string at the comma (‘,’) and returns a list: ['25', ''].
- We take the first element of the resulting list using [0], which is ‘25’.
- The value is converted to an integer and stored in the day variable.
Extracting the Year:
- year = int(split_date[2])
- split_date[2] represents the third element of the split_date list, which is ‘2017’.
- The value is converted to an integer and stored in the year variable.
Returning the Cleaned Date:
- return datetime.date(year, month, day)
- This line creates a datetime.date object using the extracted year, month, and day values.
- The datetime.date object represents the cleaned date.

Summary:
In summary, the code takes the date string ‘October 25, 2017’ and performs the following operations:
- Splits the date string into a list of substrings using the space as the delimiter.
- Prints the intermediate result of the split date.
- Extracts the month by finding its index in the months list and adding 1 to the index.
- Extracts the day by splitting the string at the comma and converting the first element to an integer.
- Extracts the year as an integer.
- Returns a datetime.date object representing the cleaned date.

With the input ‘October 25, 2017’, the output will be a datetime.date object representing October 25, 2017.

Question 32

Q

add books to the database

In the following code, explain function add_csv and clean_date in detail:
app.py
~~~
from models import (Base, session,
Book, engine)
import datetime
import csv

def menu():
while True:
print(‘’’
\nPROGRAMMING BOOKS
\r1) Add book
\r2) View all books
\r3) Search for book
\r4) Book Analysis
\r5) Exit’’’)
choice = input(‘What would you like to do? ‘)
if choice in [‘1’, ‘2’, ‘3’, ‘4’, ‘5’]:
return choice
else:
input(‘’’’
\rPlease choose one of the options above.
\rA number from 1-5.
\rPress Enter to try again.
‘’’)

main menu - add, search, analysis, exit, view
# edit books
# delete books
# search books
# data cleaning
# loop runs program

def clean_date(date_str):
months = [‘January’, ‘February’, ‘March’, ‘April’, ‘May’, ‘June’,
‘July’, ‘August’, ‘September’, ‘October’, ‘November’, ‘December’]
split_date = date_str.split(‘ ‘)
month = int(months.index(split_date[0]) + 1)
day = int(split_date[1].split(‘,’)[0])
year = int(split.date[2])
return datetime.date(year, month, day)

def clean_price(price_str):
price_float = float(price_str)
return int(price_float * 100)

def add_csv():
with open(‘suggest_books.csv’) as csvfile:
data = csv.reader(csvfile)
for row in data:
book_in_db = session.query(Book).filter(Book.title==row[0]).one_or_none()
if book_in_db = None:
print(row)
title = row[0]
author = row[1]
date = clean_date(row[2])
price = clean_price(row[3])
new_book = Book(title=title, author=author, published_date=date, price=price)
session.add(new_book)
session.commit()

def app():
app_running = True
while app_running:
choice = menu()
if choice == ‘1’:
# add book
pass
elif choice == ‘2’:
# view books
pass
elif choice == ‘3’:
# search book
pass
elif choice == ‘4’:
# analysis
pass
else:
print(‘GOODBYE!’)
app_running = False

if __name__ == ‘__main__’:
Base.metadata.create_all(engine)
# app()
# add_csv()
# clean_date(‘October 25, 2017’)
clean_price(‘28.84’)
~~~

Answer

A

The add_csv() function is responsible for reading data from a CSV file and adding it to a database. Let’s break down the function:

It opens the CSV file named 'suggest_books.csv' using the open() function in Python’s built-in csv module.
```
python
with open('suggest_books.csv') as csvfile:
```
It reads the contents of the CSV file using the csv.reader() function from the csv module. The returned value data is an iterator object that represents the rows in the CSV file.
```
python
data = csv.reader(csvfile)
```
It iterates over each row in the CSV file using a for loop.
```
python
for row in data:
```
For each row, it checks if a book with the same title already exists in the database by querying the Book table using SQLAlchemy’s session.query() method. The query checks if the title column in the Book table is equal to the value in the first column of the current row (row[0]).
```
python
book_in_db = session.query(Book).filter(Book.title == row[0]).one_or_none()
```
If no book with the same title is found in the database (i.e., book_in_db is None), it creates a new Book object using the values from the current row. The clean_date() and clean_price() functions are used to transform the date and price values from the CSV file into the appropriate format before assigning them to the published_date and price attributes of the Book object, respectively.
```
python
title = row[0]
author = row[1]
date = clean_date(row[2])
price = clean_price(row[3])
new_book = Book(title=title, author=author, published_date=date, price=price)
```
It adds the new book to the session using SQLAlchemy’s session.add() method.
After processing all rows, it commits the changes to the database using the session.commit() method to persist the added books.
```
python
session.commit()
```

The clean_date(date_str) function is a helper function used in the add_csv() function to convert a date string in the format “Month day, year” to a datetime.date object.

It defines a list of month names.

python
months = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']

It splits the date_str parameter by spaces to separate the month, day, and year.
```
python
split_date = date_str.split(' ')
```
It retrieves the month index from the months list by finding the index of the first element in split_date and adding 1 to it (since months are 1-indexed).
```
python
month = int(months.index(split_date[0]) + 1)
```
It retrieves the day from the second element of split_date by splitting it further using commas as the separator and converting it to an integer.
```
python
day = int(split_date[1].split(',')[0])
```
It retrieves the year from the third element of split_date by converting it to an integer.
```
python
year = int(split_date[2])
```
It creates and returns a datetime.date object using the year, month, and day values.
```
python
return datetime.date(year, month, day)
```

The clean_price(price_str) function is another helper function used in the add_csv() function to convert a price string to an integer value representing the price in cents.

It converts the price_str parameter to a floating-point number using the float() function.
```
python
price_float = float(price_str)
```
It multiplies the price_float value by 100 and converts it to an integer, effectively representing the price in cents.
```
python
return int(price_float * 100)
```

Overall, the add_csv() function reads data from a CSV file, checks if each book already exists in the database, and adds the new books to the database. The clean_date() and clean_price() functions are helper functions used to transform the date and price values from the CSV file into appropriate formats before adding them to the database.

Question 33

Q

Explain the following code line by line:
app.py

from models import Base, session, Book, engine
import datetime
import csv
import time

def menu():
    while True:
        print('''
              \nPROGRAMMING BOOKS
              \r1) Add book
              \r2) View all books
              \r3) Search for book
              \r4) Book Analysis
              \r5) Exit''')
        choice = input('What would you like to do? ')
        if choice in ['1', '2', '3', '4', '5']:
            return choice
        else:
            input('''
                \rPlease choose one of the options above.
                \rA number from 1-5.
                \rPress Enter to try again.
                ''')

def clean_date(date_str):
    months = ['January', 'February', 'March', 'April', 'May', 'June',
              'July', 'August', 'September', 'October', 'November', 'December']
    split_date = date_str.split(' ')
    try:
        month = int(months.index(split_date[0]) + 1)
        day = int(split_date[1].split(',')[0])
        year = int(split_date[2])
        return_date = datetime.date(year, month, day)
    except ValueError:
        input('''
            \n****** DATE ERROR ******
            \rThe date format should include a valid Month Day, Year from the past.
            \rEx: January 13, 2003
            \rPress enter to try again.
            \r*************************''')
        return None
    else:
        return return_date

def clean_price(price_str):
    try:
        price_float = float(price_str)
    except ValueError:
        input('''
            \n****** DATE ERROR ******
            \rThe price should be a number without a currency symbol
            \rEx: 10.99
            \rPress enter to try again.
            \r*************************''')
    else:
        return int(price_float * 100)

def add_csv():
    with open('suggested_books.csv') as csvfile:
        data = csv.reader(csvfile)
        for row in data:
            book_in_db = session.query(Book).filter(Book.title == row[0]).one_or_none()
            if book_in_db is None:
                print(row)
                title = row[0]
                author = row[1]
                date = clean_date(row[2])
                price = clean_price(row[3])
                new_book = Book(title=title, author=author, published_date=date, price=price)
                session.add(new_book)
        session.commit()

def app():
    app_running = True
    while app_running:
        choice = menu()
        if choice == '1':
            title = input('Title: ')
            author = input('Author: ')
            date_error = True
            while date_error:
                date = input('Published Date (Ex: October 25, 2017): ')
                date = clean_date(date)
                if type(date) == datetime.date:
                    date_error = False
            price_error = True
            while price_error:
                price = input('Price (Ex: 25.64): ')
                price = clean_price(price)
                if type(price) == int:
                    price_error = False
            new_book = Book(title=title, author=author, published_date=date, price=price)
            session.add(new_book)
            session.commit()
            print('Book Added!')
            time.sleep(1.5)
        elif choice == '2':
            pass
        elif choice == '3':
            pass
        elif choice == '4':
            pass
        else:
            print('GOODBYE!')
            app_running = False

if \_\_name\_\_ == '\_\_main\_\_':
    Base.metadata.create_all(engine)
    add_csv()
    app()

    for book in session.query(Book):
        print(book)

To run the above code, open Terminal/console in VS Code:

python3 -m venv env
source ./env/bin/activate
python3 app.py

To push the above code to GIT, in terminal:
~~~
git add .
git commit -m ‘add book to db function’
git push origin main
~~~

Answer

A

Certainly! Here’s a more detailed explanation of the code line by line:

```python
from models import Base, session, Book, engine
import datetime
import csv
import time
~~~

This section imports necessary modules and objects.
from models import Base, session, Book, engine imports the Base, session, Book, and engine objects from the models module.
import datetime imports the datetime module, which provides classes for manipulating dates and times.
import csv imports the csv module, which provides functionality for reading and writing CSV files.
import time imports the time module, which provides various time-related functions.

```python
def menu():
while True:
print(‘’’
\nPROGRAMMING BOOKS
\r1) Add book
\r2) View all books
\r3) Search for book
\r4) Book Analysis
\r5) Exit’’’)
choice = input(‘What would you like to do? ‘)
if choice in [‘1’, ‘2’, ‘3’, ‘4’, ‘5’]:
return choice
else:
input(‘’’
\rPlease choose one of the options above.
\rA number from 1-5.
\rPress Enter to try again.
‘’’)
~~~

The menu function is defined. It displays a menu of options to the user and returns the user’s choice.
The function uses a while loop to repeatedly display the menu and prompt the user for input.
The menu is displayed using the print function with a multi-line string containing the menu options.
The user’s choice is obtained using the input function and stored in the choice variable.
If the user’s choice is one of the valid options (‘1’, ‘2’, ‘3’, ‘4’, or ‘5’), the choice is returned from the function.
If the user enters an invalid choice, an input prompt is displayed to try again.

```python
def clean_date(date_str):
months = [‘January’, ‘February’, ‘March’, ‘April’, ‘May’, ‘June’,
‘July’, ‘August’, ‘September’, ‘October’, ‘November’, ‘December’]
split_date = date_str.split(‘ ‘)
try:
month = int(months.index(split_date[0]) + 1)
day = int(split_date[1].split(‘,’)[0])
year = int(split_date[2])
return_date = datetime.date(year, month, day)
except ValueError:
input(‘’’
\n** DATE ERROR **
\rThe date format should include a valid Month Day, Year from the past.
\rEx: January 13, 2003
\rPress enter to try again.
\r*********’’’)
return None
else:
return return_date
~~~

The clean_date function is defined. It takes a date string as input and attempts to clean and convert it into a datetime.date object.
The function defines a list of month names in months to help with parsing the date string.
The date string is split into its components (month, day, year) using the split method and stored in the split_date list.
The function uses a try-except block to handle any errors that may occur during the conversion process.
Inside the try block, the function converts the month name to its corresponding integer value using the index method on the months list.
The day is obtained by splitting the second element of split_date (which contains the day and a comma) and converting it to an integer.
The year is obtained by converting the third element of split_date to an integer.
If the conversion is successful, a datetime.date object is created using the datetime.date constructor with the year, month, and day as arguments.
If the conversion fails due to an invalid date format, an error message is displayed using the input function, and None is returned.
If the conversion succeeds, the cleaned date is returned.

```python
def clean_price(price_str):
try:
price_float = float(price_str)
except ValueError:
input(‘’’
\n** DATE ERROR **
\rThe price should be a number without a currency symbol
\rEx: 10.99
\rPress enter to try again.
\r*********’’’)
else:
return int(price_float * 100)
~~~

The clean_price function is defined. It takes a price string as input and attempts to clean and convert it into an integer representing the price in cents.
The function uses a try-except block to handle any errors that may occur during the conversion process.
Inside the try block, the price string is converted to a float using the float function.
If the conversion is successful, the float value is multiplied by 100 and converted to an integer.
If the conversion fails due to an invalid price format, an error message is displayed using the input function.
If the conversion succeeds, the cleaned price (in cents) is returned.

```python
def add_csv():
with open(‘suggested_books.csv’) as csvfile:
data = csv.reader(csvfile)
for row in data:
book_in_db = session.query(Book).filter(Book.title == row[0]).one_or_none()
if book_in_db is None:
print(row)
title = row[0]
author = row[1]
date = clean_date(row[2])
price = clean_price(row[3])
new_book = Book(title=title, author=author, published_date=date, price=price)
session.add(new_book)
session.commit()
~~~

The add_csv function is defined. It reads a CSV file named ‘suggested_books.csv’ and adds the books from the file to the database.
The with statement is used to open the file and ensure it is properly closed when the block is exited.
The csv.reader function is used to create a reader object data that iterates over the rows in the file.
For each row in the CSV file, the function checks if a book with the same title already exists in the database.
If a book with the same title does not exist (book_in_db is None), the function extracts the title, author, published date, and price from the row.
The extracted data is cleaned using the clean_date and clean_price functions.
A new Book object is created with the cleaned data, and it is added to the session using the add method.
After processing all rows in the CSV file, the changes are committed to the database using the commit method of the session.

```python
def app():
app_running = True
while app_running:
choice = menu()
if choice == ‘1’:
title = input(‘Title: ‘)
author = input(‘Author: ‘)
date_error = True
while date_error:
date = input(‘Published Date (Ex: October, 25, 2017): ‘)
date = clean_date(date)
if type(date) == datetime.date:
date_error = False
price_error = True
while price_error:
price = input(‘Price (Ex: 25.64): ‘)
price = clean_price(price)
if type(price) == int:
price_error = False
new_book = Book(title=title, author=author, published_date=date, price=price)
session.add(new_book)
session.commit()
print(‘Book Added!’)
time.sleep(1.5)
elif choice == ‘2’:
pass
elif choice == ‘3’:
pass
elif choice == ‘4’:
pass
else:
print(‘GOODBYE!’)
app_running = False
~~~

The app function is defined. It represents the main functionality of the program.
It uses a while loop with the app_running variable to continuously run the program until the user chooses to exit.
Inside the loop, it calls the menu function to get the user’s choice and stores it in the choice variable.
If the choice is ‘1’, the user is prompted for the book’s title, author, published date, and price.
The user’s input for the date and price is cleaned using the clean_date and clean_price functions.
If the cleaned date is of type datetime.date and the cleaned price is of type int, a new Book object is created with the cleaned data.
The new book is added to the session using the add method, and the changes are committed to the database using the commit method.
A success message is printed, and the program pauses for 1.5 seconds using the time.sleep function.
The program handles other choices (‘2’, ‘3’, ‘4’) as placeholders with the pass statement, indicating that no action is performed for those choices.
If the choice is ‘5’, indicating the user wants to exit, a farewell message is printed, and the app_running variable is set to False, causing the loop to terminate.

```python
if __name__ == ‘__main__’:
Base.metadata.create_all(engine)
add_csv()
app()

for book in session.query(Book):
    print(book) ~~~

This block of code is the entry point of the program. It is executed when the script is run directly.
It creates the database tables defined in the Base object using the create_all method, which creates the tables if they don’t already exist.
The add_csv function is called to add books from the CSV file to the database.
The app function is called to start the main program loop, allowing the user to interact with the program.
After the loop ends (either by user choice or by exiting the loop), the block retrieves all Book objects from the session and prints them.

Overall, this code sets up a command-line interface for managing a database of books. The menu function provides a menu of options, the clean_date and clean_price functions clean and convert user input, the add_csv function adds books from a CSV file to the database, and the app function handles the main program logic.

Question 34

Q

In the following code, explain def clean_price, def clean_id, def_add_csv, def app() fully, each one line by line in detail:
app.py:

from models import Base, session, Book, engine
import datetime
import csv
import time

def menu():
    while True:
        print('''
              \nPROGRAMMING BOOKS
              \r1) Add book
              \r2) View all books
              \r3) Search for book
              \r4) Book Analysis
              \r5) Exit''')
        choice = input('What would you like to do? ')
        if choice in ['1', '2', '3', '4', '5']:
            return choice
        else:
            input('''
                \rPlease choose one of the options above.
                \rA number from 1-5.
                \rPress Enter to try again.
                ''')

def clean_date(date_str):
    months = ['January', 'February', 'March', 'April', 'May', 'June',
              'July', 'August', 'September', 'October', 'November', 'December']
    split_date = date_str.split(' ')
    try:
        month = int(months.index(split_date[0]) + 1)
        day = int(split_date[1].split(',')[0])
        year = int(split_date[2])
        return_date = datetime.date(year, month, day)
    except ValueError:
        input('''
            \n****** DATE ERROR ******
            \rThe date format should include a valid Month Day, Year from the past.
            \rEx: January 13, 2003
            \rPress enter to try again.
            \r*************************''')
        return None
    else:
        return return_date

def clean_price(price_str):
    try:
        price_float = float(price_str)
    except ValueError:
        input('''
            \n****** DATE ERROR ******
            \rThe price should be a number without a currency symbol
            \rEx: 10.99
            \rPress enter to try again.
            \r*************************''')
    else:
        return int(price_float * 100)

def clean_id(id_str, options):
    try:
        book_id = int(id_str)
    except ValueError:
        input('''
            \n****** ID ERROR ******
            \rThe id should be a number.
            \rPress enter to try again.
            \r*************************''')
        return
    else:
        if book_id in options:
            return book_id
        else:
            input(f'''
            \n****** ID ERROR ******
            \rOptions: {options}.
            \rPress enter to try again.
            \r*************************''')
            return

def add_csv():
    with open('suggested_books.csv') as csvfile:
        data = csv.reader(csvfile)
        for row in data:
            book_in_db = session.query(Book).filter(Book.title == row[0]).one_or_none()
            if book_in_db is None:
                print(row)
                title = row[0]
                author = row[1]
                date = clean_date(row[2])
                price = clean_price(row[3])
                new_book = Book(title=title, author=author, published_date=date, price=price)
                session.add(new_book)
        session.commit()

def app():
    app_running = True
    while app_running:
        choice = menu()
        if choice == '1':
            # add book
            title = input('Title: ')
            author = input('Author: ')
            date_error = True
            while date_error:
                date = input('Published Date (Ex: October 25, 2017): ')
                date = clean_date(date)
                if type(date) == datetime.date:
                    date_error = False
            price_error = True
            while price_error:
                price = input('Price (Ex: 25.64): ')
                price = clean_price(price)
                if type(price) == int:
                    price_error = False
            new_book = Book(title=title, author=author, published_date=date, price=price)
            session.add(new_book)
            session.commit()
            print('Book Added!')
            time.sleep(1.5)
        elif choice == '2':
            # view books
            for book in session.query(Book):
                print(f'{book.id} | {book.title} | {book.author} | {book.price}')
            input('\nPress enter to return to the main menu.')
        elif choice == '3':
            # search book
            id_options = []
            for book in session.query(Book):
                id_options.append(book.id)
            id_error = True
            while id_error:
                id_choice = input(f'''
                        \nId Options: {id_options}
                        \rBook id: ''')
                id_choice = clean_id(id_choice, id_options)
                if type(id_choice) == int:
                    id_error = False
            the_book = session.query(Book).filter(Book.id==id_choice).first()
            print(f'''
                    \n{the_book.title} by {the_book.author}
                    \rPublished: {the_book.published_date}
                    \rPrice: ${the_book.price / 100}''')
            input('\nPress enter to return to the main menu')
        elif choice == '4':
            pass
        else:
            print('GOODBYE!')
            app_running = False

if \_\_name\_\_ == '\_\_main\_\_':
    Base.metadata.create_all(engine)
    add_csv()
    app()

    for book in session.query(Book):
        print(book)

To run the above code, open Terminal/console in VS Code:

python3 -m venv env
source ./env/bin/activate
python3 app.py

To push the above code to GIT, in terminal:

git add .
git commit -m 'add book to db function'
git push origin main

Challenges:
1. What if you have 1000 books? What would be a better way of sharing the id options with the user when searching by id?
2. What are other ways you could let the user search for a book?

Answer

A

Certainly! Let’s go through each function in more detail:

def menu():
- This function displays a menu of options to the user using the print function and multi-line strings. The available options are numbered from 1 to 5.
- It then prompts the user to enter their choice using the input function and stores the input in the choice variable.
- The function checks if the input is a valid choice by comparing it to a list of valid options. If the choice is valid, it is returned by the function. Otherwise, an error message is displayed using the input function, and the loop continues, asking for input again.
def clean_date(date_str):
- This function takes a string date_str as input, representing a date in the format “Month Day, Year” (e.g., “October 25, 2017”).
- It starts by defining a list of month names called months.
- The function splits the input string date_str using spaces as delimiters, resulting in a list called split_date.
- Inside a try-except block, the function attempts to extract the month, day, and year from split_date and convert them to integers.
- If the conversion is successful, it uses the extracted values to create a datetime.date object called return_date representing the parsed date.
- If any errors occur during the conversion (e.g., the month name is not recognized or the day or year is not a valid integer), an error message is displayed using the input function, and None is returned.
- If the conversion is successful, the return_date is returned by the function.
def clean_price(price_str):
- This function takes a string price_str as input, representing a price value without a currency symbol (e.g., “25.64”).
- It uses a try-except block to attempt to convert price_str to a float value using the float function. If the conversion fails (i.e., ValueError is raised), an error message is displayed using the input function, and None is returned.
- If the conversion is successful, the function multiplies the price by 100 to convert it to cents and then converts it to an integer.
- The resulting integer value is returned by the function.
def clean_id(id_str, options):
- This function takes a string id_str as input, representing an ID, and a list options containing valid ID options.
- It attempts to convert id_str to an integer using the int function inside a try-except block. If the conversion fails (i.e., ValueError is raised), an error message is displayed using the input function, and None is returned.
- If the conversion is successful, the function checks if the converted ID exists in the options list.
- If the ID is present in the options list, it is returned by the function.
- If the ID is not in the options list, an error message is displayed using the input function, and None is returned.
def add_csv():
- This function reads data from a CSV file named ‘suggested_books.csv’ using the open function and the csv.reader function. The file is opened in a with statement, ensuring it is closed automatically after reading.
- It iterates over each row in the CSV data using a for loop.
- For each row, the function checks if a book with the same title already exists in the database by querying the database using SQLAlchemy. If a book with the same title is not found, the details from the row are extracted, including the title, author, date, and price.
- The clean_date and clean_price functions are called to clean the extracted date and price values, respectively.
- If both the cleaned date and price are valid, a new Book object is created using the cleaned values, and it is added to the database session using session.add().
- After processing all the rows, the changes are committed to the database using session.commit().
def app():
- This is the main function that runs the application.
- It starts by setting the app_running variable to True, which controls the main loop of the application.
- Inside the while loop, it calls the menu() function to display the menu and get the user’s choice.
- Based on the user’s choice, different actions are performed:
  - If the choice is ‘1’, the user is prompted to enter the details of a new book (title, author, date, and price) using the input function.
  - The clean_date and clean_price functions are called to clean the entered date and price values, respectively.
  - If both the cleaned date and price are valid, a new Book object is created with the cleaned values and added to the database session using session.add().
  - The changes are committed to the database using session.commit(), and a success message is printed.
  - If the choice is ‘2’, all the books in the database are retrieved using a SQLAlchemy query, and their details are printed.
  - If the choice is ‘3’, the user is prompted to enter a book ID using the input function.
  - The clean_id function is called to clean the entered ID, and if it is valid, the corresponding book details are retrieved from the database and printed.
  - If the choice is ‘4’, a placeholder action is performed (no code is provided in the code snippet).
  - If the choice is ‘5’, the loop is exited by setting app_running to False, and a goodbye message is printed.
if \_\_name\_\_ == '\_\_main\_\_':
- This block of code is executed when the script is run directly (not imported as a module).
- It starts by creating the necessary database tables based on the Base class using Base.metadata.create_all(engine).
- The add_csv() function is called to read data from the CSV file and add the books to the database.
- The app() function is called to start the application.
- After the application loop exits, a query is performed to retrieve all the books from the database, and they are printed one by one.

Question 35

Q

In the following code, explain def submenu(), def app() functions:
app.py

from models import Base, session, Book, engine
import datetime
import csv
import time

def menu():
    while True:
        print('''
              \nPROGRAMMING BOOKS
              \r1) Add book
              \r2) View all books
              \r3) Search for book
              \r4) Book Analysis
              \r5) Exit''')
        choice = input('What would you like to do? ')
        if choice in ['1', '2', '3', '4', '5']:
            return choice
        else:
            input('''
                \rPlease choose one of the options above.
                \rA number from 1-5.
                \rPress Enter to try again.''')
            
def submenu():
    while True:
        print('''
              \r1) Edit
              \r2) Delete
              \r3) Return to main menu''')
        choice = input('What would you like to do? ')
        if choice in ['1', '2', '3']:
            return choice
        else:
            input('''
                \rPlease choose one of the options above.
                \rA number from 1-3.
                \rPress Enter to try again.''')

def clean_date(date_str):
    months = ['January', 'February', 'March', 'April', 'May', 'June',
              'July', 'August', 'September', 'October', 'November', 'December']
    split_date = date_str.split(' ')
    try:
        month = int(months.index(split_date[0]) + 1)
        day = int(split_date[1].split(',')[0])
        year = int(split_date[2])
        return_date = datetime.date(year, month, day)
    except ValueError:
        input('''
            \n****** DATE ERROR ******
            \rThe date format should include a valid Month Day, Year from the past.
            \rEx: January 13, 2003
            \rPress enter to try again.
            \r*************************''')
        return None
    else:
        return return_date

def clean_price(price_str):
    try:
        price_float = float(price_str)
    except ValueError:
        input('''
            \n****** DATE ERROR ******
            \rThe price should be a number without a currency symbol
            \rEx: 10.99
            \rPress enter to try again.
            \r*************************''')
    else:
        return int(price_float * 100)

def clean_id(id_str, options):
    try:
        book_id = int(id_str)
    except ValueError:
        input('''
            \n****** ID ERROR ******
            \rThe id should be a number.
            \rPress enter to try again.
            \r*************************''')
        return
    else:
        if book_id in options:
            return book_id
        else:
            input(f'''
            \n****** ID ERROR ******
            \rOptions: {options}.
            \rPress enter to try again.
            \r*************************''')
            return

def edit_check(column_name, current_value):
    print(f'\n*** EDIT {column_name} ***')
    if column_name == 'Price':
        print(f'\rCurrent Value: {current_value/100}')
    elif column_name == 'Date':
        print(f'\rCurrent Value: {current_value.strftime("%B %d, %Y")}')
    else:
        print(f'\rCurrent Value: {current_value}')
    

    if column_name == 'Date' or column_name == 'Price':
        while True:
            changes = input('What would you like to change the value to? ')
            if column_name == 'Date':
                changes = clean_date(changes)
                if type(changes) == datetime.date:
                    return changes
            elif column_name == 'Price':
                changes = clean_price(changes)
                if type(changes) == int:
                    return changes
    else:
        return input('What would you like to change the value to? ')

def add_csv():
    with open('suggested_books.csv') as csvfile:
        data = csv.reader(csvfile)
        for row in data:
            book_in_db = session.query(Book).filter(Book.title == row[0]).one_or_none()
            if book_in_db is None:
                print(row)
                title = row[0]
                author = row[1]
                date = clean_date(row[2])
                price = clean_price(row[3])
                new_book = Book(title=title, author=author, published_date=date, price=price)
                session.add(new_book)
        session.commit()

def app():
    app_running = True
    while app_running:
        choice = menu()
        if choice == '1':
            # add book
            title = input('Title: ')
            author = input('Author: ')
            date_error = True
            while date_error:
                date = input('Published Date (Ex: October 25, 2017): ')
                date = clean_date(date)
                if type(date) == datetime.date:
                    date_error = False
            price_error = True
            while price_error:
                price = input('Price (Ex: 25.64): ')
                price = clean_price(price)
                if type(price) == int:
                    price_error = False
            new_book = Book(title=title, author=author, published_date=date, price=price)
            session.add(new_book)
            session.commit()
            print('Book Added!')
            time.sleep(1.5)
        elif choice == '2':
            # view books
            for book in session.query(Book):
                print(f'{book.id} | {book.title} | {book.author} | {book.price}')
            input('\nPress enter to return to the main menu.')
        elif choice == '3':
            # search book
            id_options = []
            for book in session.query(Book):
                id_options.append(book.id)
            id_error = True
            while id_error:
                id_choice = input(f'''
                        \nId Options: {id_options}
                        \rBook id: ''')
                id_choice = clean_id(id_choice, id_options)
                if type(id_choice) == int:
                    id_error = False
            the_book = session.query(Book).filter(Book.id==id_choice).first()
            print(f'''
                    \n{the_book.title} by {the_book.author}
                    \rPublished: {the_book.published_date}
                    \rPrice: ${the_book.price / 100}''')
            sub_choice = submenu()
            if sub_choice == '1':
                # edit
                the_book.title = edit_check('Title', the_book.title)
                the_book.author = edit_check('Author', the_book.author)
                the_book.published_date = edit_check('Date', the_book.published_date)
                the_book.price = edit_check('Price', the_book.price)
                session.commit()
                print('Book updated!')
                time.sleep(1.5)
            if sub_choice == '2':
                # delete
                session.delete(the_book)
                session.commit()
                print('Book deleted!')
                time.sleep(1.5)
        elif choice == '4':
            pass
        else:
            print('GOODBYE!')
            app_running = False

if \_\_name\_\_ == '\_\_main\_\_':
    Base.metadata.create_all(engine)
    add_csv()
    app()

    for book in session.query(Book):
        print(book)

To run the above code, open Terminal/console in VS Code:

python3 -m venv env
source ./env/bin/activate
python3 app.py

To push the above code to GIT, in terminal:

git add .
git commit -m 'add book to db function'
git push origin main

Challenges:
1. How could you combine the two menu functions to create one menu function?
2. Is there a way to have the user tell you which values they want to change and then only prompt them to change those values instead of asking about each one?

Answer

A

Certainly! Let’s delve into the def submenu() and def app() functions in more detail:

def submenu():
- This function displays a submenu of options to the user, providing additional choices for book manipulation after selecting the “Search for book” option (choice 3) from the main menu.
- Inside a while loop, the submenu options are printed to the console using the print function and multi-line strings.
- The user is prompted to enter their choice using the input function, and the input is stored in the choice variable.
- The function checks if the input is a valid choice by comparing it to a list of valid options (‘1’, ‘2’, ‘3’). If the choice is valid, it is returned by the function, and the submenu loop ends. Otherwise, an error message is displayed using the input function, and the loop continues, asking for input again.
def app():
- This is the main function that runs the application.
- It starts by setting the app_running variable to True, which controls the main loop of the application.
- Inside the while loop, the menu() function is called to display the main menu and get the user’s choice.
- Based on the user’s choice, different actions are performed:
  - If the choice is ‘1’, the user is prompted to enter the details of a new book (title, author, date, and price) using the input function.
  - The clean_date and clean_price functions are called to clean the entered date and price values, respectively.
  - If both the cleaned date and price are valid, a new Book object is created with the cleaned values and added to the database session using session.add().
  - The changes are committed to the database using session.commit(), and a success message is printed.
  - If the choice is ‘2’, all the books in the database are retrieved using a SQLAlchemy query, and their details are printed.
  - If the choice is ‘3’, the user is prompted to enter a book ID using the input function.
  - The clean_id function is called to clean the entered ID, and if it is valid, the corresponding book details are retrieved from the database and printed.
  - The submenu() function is called to display the submenu options and get the user’s choice.
  - If the submenu choice is ‘1’, the user is prompted to select a field to edit using the edit_check() function, which displays the current value of the selected field and prompts the user to enter a new value.
  - The edit_check() function handles the editing process. If the field to edit is ‘Date’ or ‘Price’, the clean_date() or clean_price() functions are called, respectively, to clean the entered value. If the cleaned value is valid, it is returned. Otherwise, the user is prompted to enter the value again.
  - Once the new value is obtained, the corresponding attribute of the book object is updated, and the changes are committed to the database using session.commit(). A success message is printed, and a short delay is introduced using time.sleep() for visual effect.
  - If the submenu choice is ‘2’, the book is deleted from the database using session.delete(), and the changes are committed.
  - If the main menu choice is ‘4’, a placeholder action is performed (no code is provided in the code snippet).
  - If the main menu choice is ‘5’, the loop is exited by setting app_running to False, and a goodbye message is printed.
The if \_\_name\_\_ == '\_\_main\_\_': block is executed when the script is run directly (not imported as a module).
- It starts by creating the necessary database tables based on the Base class using Base.metadata.create_all(engine).
- The add_csv() function is called to read data from the CSV file and add the books to the database.
- The app() function is called to start the application.
- After the application loop exits, a query is performed to retrieve all the books from the database, and they are printed one by one.

Question 36

Q

In the following code, explain def app() in that book analysis alone in detail:
app.py:

from models import Base, session, Book, engine
import datetime
import csv
import time

def menu():
    while True:
        print('''
              \nPROGRAMMING BOOKS
              \r1) Add book
              \r2) View all books
              \r3) Search for book
              \r4) Book Analysis
              \r5) Exit''')
        choice = input('What would you like to do? ')
        if choice in ['1', '2', '3', '4', '5']:
            return choice
        else:
            input('''
                \rPlease choose one of the options above.
                \rA number from 1-5.
                \rPress Enter to try again.''')
            
def submenu():
    while True:
        print('''
              \r1) Edit
              \r2) Delete
              \r3) Return to main menu''')
        choice = input('What would you like to do? ')
        if choice in ['1', '2', '3']:
            return choice
        else:
            input('''
                \rPlease choose one of the options above.
                \rA number from 1-3.
                \rPress Enter to try again.''')

def clean_date(date_str):
    months = ['January', 'February', 'March', 'April', 'May', 'June',
              'July', 'August', 'September', 'October', 'November', 'December']
    split_date = date_str.split(' ')
    try:
        month = int(months.index(split_date[0]) + 1)
        day = int(split_date[1].split(',')[0])
        year = int(split_date[2])
        return_date = datetime.date(year, month, day)
    except ValueError:
        input('''
            \n****** DATE ERROR ******
            \rThe date format should include a valid Month Day, Year from the past.
            \rEx: January 13, 2003
            \rPress enter to try again.
            \r*************************''')
        return None
    else:
        return return_date

def clean_price(price_str):
    try:
        price_float = float(price_str)
    except ValueError:
        input('''
            \n****** DATE ERROR ******
            \rThe price should be a number without a currency symbol
            \rEx: 10.99
            \rPress enter to try again.
            \r*************************''')
    else:
        return int(price_float * 100)

def clean_id(id_str, options):
    try:
        book_id = int(id_str)
    except ValueError:
        input('''
            \n****** ID ERROR ******
            \rThe id should be a number.
            \rPress enter to try again.
            \r*************************''')
        return
    else:
        if book_id in options:
            return book_id
        else:
            input(f'''
            \n****** ID ERROR ******
            \rOptions: {options}.
            \rPress enter to try again.
            \r*************************''')
            return

def edit_check(column_name, current_value):
    print(f'\n*** EDIT {column_name} ***')
    if column_name == 'Price':
        print(f'\rCurrent Value: {current_value/100}')
    elif column_name == 'Date':
        print(f'\rCurrent Value: {current_value.strftime("%B %d, %Y")}')
    else:
        print(f'\rCurrent Value: {current_value}')
    

    if column_name == 'Date' or column_name == 'Price':
        while True:
            changes = input('What would you like to change the value to? ')
            if column_name == 'Date':
                changes = clean_date(changes)
                if type(changes) == datetime.date:
                    return changes
            elif column_name == 'Price':
                changes = clean_price(changes)
                if type(changes) == int:
                    return changes
    else:
        return input('What would you like to change the value to? ')

def add_csv():
    with open('suggested_books.csv') as csvfile:
        data = csv.reader(csvfile)
        for row in data:
            book_in_db = session.query(Book).filter(Book.title == row[0]).one_or_none()
            if book_in_db is None:
                print(row)
                title = row[0]
                author = row[1]
                date = clean_date(row[2])
                price = clean_price(row[3])
                new_book = Book(title=title, author=author, published_date=date, price=price)
                session.add(new_book)
        session.commit()

def app():
    app_running = True
    while app_running:
        choice = menu()
        if choice == '1':
            # add book
            title = input('Title: ')
            author = input('Author: ')
            date_error = True
            while date_error:
                date = input('Published Date (Ex: October 25, 2017): ')
                date = clean_date(date)
                if type(date) == datetime.date:
                    date_error = False
            price_error = True
            while price_error:
                price = input('Price (Ex: 25.64): ')
                price = clean_price(price)
                if type(price) == int:
                    price_error = False
            new_book = Book(title=title, author=author, published_date=date, price=price)
            session.add(new_book)
            session.commit()
            print('Book Added!')
            time.sleep(1.5)
        elif choice == '2':
            # view books
            for book in session.query(Book):
                print(f'{book.id} | {book.title} | {book.author} | {book.price}')
            input('\nPress enter to return to the main menu.')
        elif choice == '3':
            # search book
            id_options = []
            for book in session.query(Book):
                id_options.append(book.id)
            id_error = True
            while id_error:
                id_choice = input(f'''
                        \nId Options: {id_options}
                        \rBook id: ''')
                id_choice = clean_id(id_choice, id_options)
                if type(id_choice) == int:
                    id_error = False
            the_book = session.query(Book).filter(Book.id==id_choice).first()
            print(f'''
                    \n{the_book.title} by {the_book.author}
                    \rPublished: {the_book.published_date}
                    \rPrice: ${the_book.price / 100}''')
            sub_choice = submenu()
            if sub_choice == '1':
                # edit
                the_book.title = edit_check('Title', the_book.title)
                the_book.author = edit_check('Author', the_book.author)
                the_book.published_date = edit_check('Date', the_book.published_date)
                the_book.price = edit_check('Price', the_book.price)
                session.commit()
                print('Book updated!')
                time.sleep(1.5)
            if sub_choice == '2':
                # delete
                session.delete(the_book)
                session.commit()
                print('Book deleted!')
                time.sleep(1.5)
        elif choice == '4':
            # book analysis
            oldest_book = session.query(Book).order_by(Book.published_date).first()
            newest_book = session.query(Book).order_by(Book.published_date.desc()).first()
            total_books = session.query(Book).count()
            python_books = session.query(Book).filter(Book.title.like('%Python%')).count()
            print(f'''\n**** BOOK ANALYSIS ****
                  \rOldest Book: {oldest_book}
                  \rNewest Book: {newest_book}
                  \rTotal Books: {total_books}
                  \rNumber of Python Books: {python_books}''')
            input('\nPress enter to return to the main menu.')
        else:
            print('GOODBYE!')
            app_running = False

if \_\_name\_\_ == '\_\_main\_\_':
    Base.metadata.create_all(engine)
    add_csv()
    app()

    for book in session.query(Book):
        print(book)

To run the above code, open Terminal/console in VS Code:

python3 -m venv env
source ./env/bin/activate
python3 app.py

To push the above code to GIT, in terminal:

git add .
git commit -m 'add book to db function'
git push origin main

Challenges:
1. Try creating one menu function
2. Add docstrings to each function
3. Add type hinting
4. Try creating pagination for the books so you only see 5 books at a time when viewing them
5. Try giving the user options for searching for a book (besides searching by id)
6. Try adding other columns to your database like topic area, difficulty level, number of pages, etc.
7. Try creating a practice app using SQLAlchemy!

Answer

A

Let’s examine the implementation of choice 4 in more detail:

```python
elif choice == ‘4’:
# book analysis
oldest_book = session.query(Book).order_by(Book.published_date).first()
newest_book = session.query(Book).order_by(Book.published_date.desc()).first()
total_books = session.query(Book).count()
python_books = session.query(Book).filter(Book.title.like(‘%Python%’)).count()
print(f’’‘\n** BOOK ANALYSIS **
\rOldest Book: {oldest_book}
\rNewest Book: {newest_book}
\rTotal Books: {total_books}
\rNumber of Python Books: {python_books}’’’)
input(‘\nPress enter to return to the main menu.’)
~~~

If the user selects choice 4 from the main menu, the program executes the book analysis functionality. Here’s a breakdown of what happens:

oldest_book = session.query(Book).order_by(Book.published_date).first()
- This line retrieves the book with the earliest published date by querying the Book table in the database.
- The order_by() method is used to order the books based on the published_date column in ascending order.
- The first() method is used to retrieve the first book from the ordered query results, which corresponds to the oldest book.
newest_book = session.query(Book).order_by(Book.published_date.desc()).first()
- This line retrieves the book with the most recent published date by querying the Book table in the database.
- Similar to the previous line, the order_by() method is used to order the books based on the published_date column.
- However, desc() is appended to the published_date column to order the books in descending order.
- The first() method is then used to retrieve the first book from the ordered query results, which corresponds to the newest book.
total_books = session.query(Book).count()
- This line calculates the total number of books in the database.
- It uses the count() method on the query to count the number of books retrieved from the Book table.
python_books = session.query(Book).filter(Book.title.like('%Python%')).count()
- This line calculates the number of books with titles containing the word “Python”.
- The filter() method is used to specify the condition for filtering the books.
- In this case, Book.title.like('%Python%') filters the books where the title contains the substring “Python”.
- The count() method is then applied to count the filtered books.
The analysis results are printed to the console using the print() function.
- The analysis includes information about the oldest book, newest book, total number of books, and the number of books with “Python” in the title.
- The string formatting syntax (f"") is used to insert the values obtained from the previous queries into the printed message.
input('\nPress enter to return to the main menu.')
- After displaying the analysis results, the user is prompted to press Enter to return to the main menu.
- The input() function is used to wait for the user’s input without requiring any specific input.

This completes the book analysis functionality, which provides insights into the oldest book, newest book, total book count, and the count of books with “Python” in the title.

Question 37

Q

Challenge:
The movie theater wants to know how many movies they have in their database with ‘The’ in the title. They want to post this fun fact on their social media. Create a new variable called the_movies that holds a query to find all products with ‘%The%’ in the movie_title and counts the number of returned values.\n*Hint:* Use filter() with .like()

models.py:
~~~
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine(‘sqlite:///movies.db’, echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class Movie(Base):
__tablename__ = ‘movies’

id = Column(Integer, primary_key=True)
movie_title = Column(String)
genre = Column(String)

Write your code below
~~~

Answer

A

Answer to the Challenge:
~~~
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine(‘sqlite:///movies.db’, echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class Movie(Base):
__tablename__ = ‘movies’

id = Column(Integer, primary_key=True)
movie_title = Column(String)
genre = Column(String)

Write your code below

the_movies = session.query(Movie.movie_title).filter(Movie.movie_title.like(“%The%”)).count()
~~~

Question 38

Q

What would you add to this query to return a list instead of a tuple? session.query(Sales).\_\_\_\_\_\_\_

Answer

A

To return a list instead of a tuple, you can use the all() method after the query() call. Here’s an example:

```python
sales_list = session.query(Sales).all()
~~~

The all() method executes the query and returns the results as a list of objects. Each object in the list represents a row from the Sales table.

Question 39

Q

Can you chain function calls in SQLAlchemy? For instance, is this possible: session.query(Sales).filter(Sales.year==2015).filter(Sales.price > 5)

Answer

A

Yes, you can chain multiple function calls in SQLAlchemy. Chaining allows you to apply multiple filters or operations to a query in a single statement. The code you provided is an example of chaining multiple filters in SQLAlchemy.

Here’s the modified code with the chained filters:

```python
sales_list = session.query(Sales).filter(Sales.year == 2015).filter(Sales.price > 5).all()
~~~

In the above code, the filter() method is called twice, one after another, to apply two separate filters to the query. The first filter checks for the year attribute being equal to 2015, while the second filter checks for the price attribute being greater than 5. Finally, the all() method executes the query and returns the matching results as a list.