Notes 2.4 a.

Database and SQLAlchemy

In this blog we will explore using programs with data, focused on Databases. We will use SQLite Database to learn more about using Programs with Data. Use Debugging through these examples to examine Objects created in Code.

College Board talks about ideas like

Program Usage. “iterative and interactive way when processing information” Managing Data. “classifying data are part of the process in using programs”, “data files in a Table” Insight “insight and knowledge can be obtained from … digitally represented information” Filter systems. ‘tools for finding information and recognizing patterns” Application. “the preserve has two databases”, “an employee wants to count the number of book” PBL, Databases, Iterative/OOP

Iterative. Refers to a sequence of instructions or code being repeated until a specific end result is achieved OOP. A computer programming model that organizes software design around data, or objects, rather than functions and logic SQL. Structured Query Language, abbreviated as SQL, is a language used in programming, managing, and structuring data Imports and Flask Objects Defines and key object creations

Comment on where you have observed these working? Provide a defintion of purpose. Flask app object The flask object implements a WSGI application and acts as the central object. It is passed the name of the module or package of the application. Once it is created it will act as a central registry for the view functions, the URL rules, template configuration and much more. SQLAlchemy db object SQLAlchemy is a library that facilitates the communication between Python programs and databases. Most of the times, this library is used as an Object Relational Mapper (ORM) tool that translates Python classes to tables on relational databases and automatically converts function calls to SQL statements.

Initial Data Uses SQLALchemy db.create_all() to initialize rows into sqlite.db

Comment on how these work? Create All Tables from db Object db Object is organized in a structured manner. Create All tables creates the table. User Object Constructors User object constructors ensures model users are consistent and be used various of times. Try / Except This accounts for error.

Use of ORM Query object and custom methods to identify user to credentials uid and password

ORM = Object Relational Model - in SQLAlchemy, ORM on top of the python that works with databases. Comment on purpose of following User.query.filter_by Filters with the parameter, parameter is uid, so it filters by the username ids. user.password Checks to see if the password is correct, if yes, returns true, is not, returns false

Create a new User in table in Sqlite.db Uses SQLALchemy and custom user.create() method to add row.

Comment on purpose of following user.find_by_uid() and try/except User find by uid finds user by their usernames, checks through usernames until matches the same one input, if not, it passes or keeps going. user = User(…) This initializes the user object before date, getting the name, uid, and password first. user.dob and try/except Enter user’s date of birth, looks for one that matches with the try function, if not, goes to except with keeps going through all, if none can be found then it prints invalid date. user.create() and try/except Creates a new user for the data base, if try works the prints new user was created, except will happen if error occurred while creating new user.

Reading users table in sqlite.db Uses SQLALchemy query.all method to read data

Comment on purpose of following User.query.all This is extracting all users from database and turns them into JSON, the user query all queues all users to read them. json_ready assignment, google List Comprehension Believe that it comprehends each user reading it, then adding it to the list.

2.4 b.

What is the purpose of identity Column in SQL database?

In SQL databases, an identity column is a special type of column that automatically generates unique numeric values for each row that is inserted into a table. The purpose of an identity column is to provide a unique identifier for each row in the table, without requiring the user to manually enter a value for that column.

What is the purpose of a primary key in SQL database?

In SQL databases, a primary key is a column or set of columns that uniquely identifies each row in a table. The purpose of a primary key is to ensure that each row in the table can be uniquely identified and retrieved, and that the data in the table is organized and structured in a consistent and efficient manner.

What are the Data Types in SQL table?

Some example types are booleans, blob, string, integer, and binary.

Reading Users table in Sqlite.db

What is a connection object? After you google it, what do you think it does?

A connection object is an object that represents a connection to a database or other data source. It is used to establish a connection between an application and a database, and to manage that connection throughout the lifetime of the application.

Same for cursor object? After reading what it is on Google, I believe that it process each row that are queued in the database, and thats how it reads it in order to display the results. Look at conn object and cursor object in VSCode debugger. What attributes are in the object? Attributes in conn are create, menu, read, schema, database, file, ipykernel, os, sqlite3 and sys. Attributes in cursor are conn, create, meny, read, schema, database, file, ipykernel, os, sqlite3, sys.

Is “results” an object? How do you know?

Results is an object because an object has functions and data, which is what is shown when we debug it and look at the results, there are special variables, function variables, etc.

Create a new User in table in Sqlite.db

Compore create() in both SQL lessons. What is better or worse in the two implementations?

The 2.4a lesson does create by has try and excepts for each one individually, while this one lists all the things being created then has the try and except functions after. Im not to sure what is better or worse, but I think that off of looks that this cell below seems better when trying to create a lot of things at once.

Explain purpose of SQL INSERT. Is this the same as User init?

The purpose fo SQL insert is to add new data into the database, I think it is because user init initializes the data meaning it assigns an initial value for a variable or data object. However I think init should be used to make the dataset then insert should be just strictly for adding new data in.

Updating a User in table in Sqlite.db

Uses SQL UPDATE to modify password

I think that hacked part helps towards encrypting the password so it is not as easily accessible. Explain try/except, when would except occur?

Try would happen when a user wants to reset or make new password, I think except would occur if the problem was not that no user id has been found or row for password was changed, bur not entirely sure exactly what the problem would be for this to happen.

What code seems to be repeated in each of these examples to point, why is it repeated?

It seems to be close cursor or connection, I think it is done so that it finishes processing, therefore results can be displayed.

Delete a User in table in Sqlite.db

Is DELETE a dangerous operation? Why?

It is not dangerous because deleting is part of the process of data if things are put incorrectly, and you can always input new data anyways.

In the print statemements, what is the “f” and what does {uid} do?

I think f find the userid, the bracket uid displays that certain uid found

Menu Interface to CRUD operations

Why does the menu repeat?

Menu repeats because it is the main thing that connects you to CRUD or S.

Could you refactor this menu? Make it work with a List?

I am not entirely sure, but it does sound possible.