For this homework, you are encouraged to use AI to figure out the API of the SQL libraries!
So far we’ve only interacted with the DB on the command line, but in real applications, we would be writing programs that talk to the DB. For example, if you have an online shop, every time a customer clicks “submit order” the following chain of events take place:
In fact, building a database-backed website is one of the best ways to practice your DB knowledge. However, working with the web involves a lot of tools that are not central to this class. In this assignment, we will focus on the relationship between your code and the database, and ignore the web part.
So far we have been using SQLite thanks to its simplicity, but to fully appreciate the various flavors of transactions, we will move to a bigger system and use MySQL. Installing MySQL is quite a bit of hassle, but thankfully www.pythonanywhere.com provides a web environment with MySQL, for free!
As promised, we will be talking to the database from code instead of just the console. Here is a simple example of that (can you see what’s happening?):
import mysql.connector
# Connect to server
= mysql.connector.connect(
cnx ="yourname.mysql.pythonanywhere-services.com",
host="yourname",
user='yourname$default',
database="s3cre3t!(the-password-you-set)")
password
cnx.start_transaction()
# Get a cursor
= cnx.cursor(buffered=True)
cur
# Insert a row
"insert into R values (2)")
cur.execute(
# Execute a query
"SELECT x from R")
cur.execute(
# Fetch one result
= cur.fetchone()
row
print("x is: {0}".format(row[0]))
# commit the transaction
cnx.commit()
# Close connection
cnx.close()
A connection is self-explanantory: it’s an active channel where you can send commands and queries to the DB. The concept of a cursor is a bit informal: originally, a cursor was used so that we can iterate over the result returned by a query. Imagine you have a query that returns a lot of rows: chances are you don’t need all the rows, so for efficiency, the database won’t return everything all at once. Rather, the cursor is like a pointer to “the current row”, and you can call “get next row” on it one by one to retrieve the results you need. In any case, you can just think of it as something you need whenever you want to submit a command to the DB.
Let’s now put some more interesting data into the DB:
That’s right, we’ll try to re-create our in-class exercise of giving everyone free scores.
sleep()
command in SQL might be
helpful.The reason we used MySQL instead of SQLite is that the former allows for finer-grained concurrency control. Locking in MySQL is per-item, (usually means per-row), whereas SQLite always locks the entire DB.
sleep
to
make each database action take longer.