Relational Database in Python-CRUD operations

Another post starts with you beautiful people!
Hope you have enjoyed and learn from my previous post about Relational Database In Python-sqlite
Previously, we used the Table object to reflect a table from an existing database, but what if you wanted to create a new table?
We'd still use the Table object; however, we'd need to replace the autoload and autoload_with parameters with Column objects.
The Column object takes a name, a SQLAlchemy type with an optional format, and optional keyword arguments for different constraints.

After defining the table, we can create the table in the database by using the .create_all() method on metadata and supplying the engine as the only parameter. You can find more details about metadata here- tell me more about metadata!

Output:-

Well done! When creating a table, it's important to carefully think about what data types each column should be.

Constraints and Data Defaults-
We're now going to practice creating a table with some constraints! Often, we'll need to make sure that a column is unique, nullable, a positive value, or related to a column in another table. This is where constraints come in.

Inserting a single row with an insert() statement-
There are several ways to perform an insert with SQLAlchemy; however, we are going to focus on the one that follows the same pattern as the select statement.

It uses an insert statement where we specify the table as an argument, and supply the data we wish to insert into the value via the .values() method as keyword arguments.

Inserting Multiple Records at Once-
It's time to practice inserting multiple records at once!
We'll want to first build a list of dictionaries that represents the data we want to insert.
Then, in the .execute() method, you can pair this list of dictionaries with an insert statement, which will insert all the records in your list of dictionaries.

Loading a CSV into a Table-
We've done a great job so far at inserting data into tables! We're now going to learn how to load the contents of a CSV file into a table.

We have used the csv module to set up a csv_reader, which is just a reader object that can iterate over the lines in a given CSV file -
in this case, a census CSV file. Using the enumerate() function, we can loop over the csv_reader to handle the results one at a time.

Run the above code in your notebook and find how many rows in this table.

Updating individual records-
The update statement is very similar to an insert statement, except that it also typically uses a where clause to help us determine what data to update. We'll be using the FIPS state code using here, which is appropriated by the U.S. government to identify U.S. states and certain other associated areas.
The names of the tables and columns of interest in this exercise are: state_fact (Table), name (Column), and fips_state (Column).

After executing the update statement, the fips_state code gets updated to 36.

Updating Multiple Records-
By using a where clause that selects more records, we can update multiple records at once.
It's time now to practice this!

Correlated Updates-
We can also update records with data from a select statement. This is called a correlated update.
It works by defining a select statement that returns the value you want to update the record with and assigning that as the value in an update statement.
We'll be using a flat_census in this exercise as the target of our correlated update. The flat_census table is a summarized copy of our census table.

Well done! Having learned how to create tables in a database, insert data into them, and update the data,
it's time to learn how to remove data from a database!

Deleting all the records from a table-
Often, we'll need to empty a table of all of its records so we can reload the data.
We can do this with a delete statement with just the table as an argument.
Do be careful, though, as deleting cannot be undone!

After executing the delete statement you can see, there are no records left in the census table.

Deleting specific records-
By using a where() clause, we can target the delete statement to remove only certain records.
For example, I deleted all columns from the employees table that had id 3 with the following delete statement:
delete(employees).where(employees.columns.id == 3)

Here we'll delete ALL rows which have 'M' in the sex column and 36 in the age column. We have included code at the start which computes the total number of these rows. It is important to make sure that this is the number of rows that you actually delete.

Deleting a Table Completely-
We're now going to practice dropping individual tables from a database with the .drop() method,
as well as all tables in a database with the .drop_all() method!

As Spider-Man's Uncle Ben:With great power, comes great responsibility!
Do be careful when deleting tables, as it's not simple or fast to restore large databases! Remember, we can check to see if a table exists with the .exists() method.

Brilliant work, and congratulations! We have learnt CRUD operation in relational database with Python.
Try above code in your notebook with different databases and explore more learning!