Coding against databases

Previous: MySQL

Next: Computer design

Material for the lesson

Note: The powerpoint has been updated with new library and transactions, but not the video.
Video: Using Python with MySQL
Powerpoint: Coding with Databases
Video: Exercises

Exercises

Create one or two programs that can answer the following questions about the database you created last lesson. The data is from 2016 and all records are "complete" for the time period they were collected in. Specifically, all children's parents are in the database until we go so far back that records did not exists (the information is lost in the mists of time).
The programs should run on the server pupil1, not your own machine.
The mysql connect call looks like this.
cnx = mysql.connector.connect(user='DTUusername', passwd='YOUR_PASSWORD', db='DTUusername', host='localhost')
You can find your password in the .my.cnf file in your home.
You will mostly use python to solve the exercises. It is not intended that you use advanced mysql queries, but feel free.
Note, use this python: /home/ctools/anaconda3-2024.10-1/bin/python3

1)
Who are born out of wedlock? Definition: A child is born out of wedlock if the biological father and mother is not married when the child is born. In the absence of information, you can not claim a child is born out of wedlock (that means if you only know the mother from the database, you can not claim the child is a bastard).
According to my results: 262 people are born out of wedlock. That is amazing as it is out of 500 people.

2)
Are there families who have had the same disease through 3 generations? Who and what disease in such case? It is not a trick question - you will find some.
In answering this question, then any type of cancer should just be regarded as cancer, i.e. the same disease (this is not true in reality). 3 generations should be understood as a ”straight line of inheritance”, i.e. child, father, father’s father/mother. Child, mother and father’s father is not straight.

If you look closely at your results, you will find some very interesting family patterns - such is the curse of (badly) generated data.

3) Somewhat optional
Run your programs again, but this time locally on your laptop, while you use the database on the server.
This is an interesting challenge, which can be solved by SSH tunneling the mysql port 3306 to the server. The requirements for solving the exercise is understanding and setting up proper SSH tunneling as taught in lesson 1, and that mysql.connector is locally installed in your python distribution. In principle, this is done by 1 unix command setting up the SSH tunnel.

Totally optional extra exercise I invented in my dreams Sunday morning: The Chat Board

This is a fun cooperative exercise with databases. You should all make your own Chat client, where you can communicate with the other course participants. The exercise utilizes the concurrent features of databases - the easy update without worry if somebody else is accessing the database at the same time.

You use a database called chatboard that is common to you all. In that database there will be just one table, messages with the fields; id (primary key auto_increment), mesg (varchar(500)), name (varchar(15), default NULL), channel (varchar(12), default 'common'), stamp (timestamp). You only have the ability to read the table and insert new rows in the table - that is - create new messages. Only the moderator can delete messages.
mesg is the message on the chat.
name is name of the message sender. If NULL, then it is anonymous.
channel is the channel for this message. Ask google if you don't know what a chat channel is. If NULL, it is the common channel.
stamp is when the message is created. This is used for selecting the newest messages and deleting the old.

The Chat client you make should have the following structure.

connect to database chatboard
pull last messages and show
get a one-line input
while input is not quit:
    if input is command1:
        do commmand1 code
    elif input is command2:
        do command2 code
    elif input is something:
        input was a message, so post it to the chat board
    pull chat board for new messages and display them if any
    get next online input
close database connection

As you hopefully can see, this structure makes it easy to start small with simple things and build up with new expanded functionality.

A command is an input where the first word starts with slash possibly followed by a parameter. Anything else is just a message to be posted on the selected channel.
List of commands:

/quit                   Quits the program, possibly leaving a message.
/channel <channelname>  Selects/creates the channel for your messages.
/showchannels           Shows possible channels.
/trueid                 Sets your name to your login name.
/nick <nickname>        Sets your name to <nickname>.
/activity               Which channels had the latest messages, and when.
/last <X>               Pull last X messages from the chosen channel and display.

You can implement your own commands, like /search <name>, which would search for messages by the person. /who, which would show who has messages. /dance, which displays a message "<yourname> is dancing for you". You dream it up - it is your chat client and the more powerful and funny it is, the better chances of you being the next Mark Zuckerberg.

When you have done your ChatClient you have accumulated enough experience to create a ChatBot. What should the bot do?
Your dream, pal, your vision.
It could behave like a dog; search for a random or specific user, and bark at them or pee on them.
It could find the latest message and add 'That is what she said yesterday'.
It could be more clever and consult external AI and generate a one-line answer/comment.
It could find the quote of the day and post on the common channel.
It could camp on the 'Dreams' channel and say to every new poster: "Tell me about your dreams!"
It could copy other posters - simply repeating their message.
It could advertise an event across all channels.
It could have a list of people's birthdays, and when the day came about, post "Today is X's birthday" on the common channel.
If anybody posted a calculation, it could compute the result and post it - just like google.

I think that was enough ideas for bots.

The fun part of this exercise is you can use the Chat Board for the rest of the course, once you made the client. You can even develop your client and/or bot more in the progress of the course. Once you get the the SSH tunneling set up, you just run your client from home/your laptop. How can it get any better?