CS50 Weeks 6 / 7 - Python and SQL
Published: 2020-08-05 20:28:52

This post is edited from my [original blog post](https://kewbish.github.io/blog/posts/200726/). Find it, and other posts by me on [kewbish.github.io/blog](https://kewbish.github.io/blog/).

# Introduction
Finally, we're getting into the more 'application' side on things - also known as the part where I'm more comfortable with. With my [initial predictions](https://kewbish.github.io/blog/posts/200621/), I'd predicted that Week 6 (where we are now) was where it'd get slightly easier. And I was right - Week 6 was a welcome week, an introduction to Python. I'm already somewhat decent at Python, so this week wasn't such a struggle, and Week 7 was a great mix of new tech / familiar tech.  

I was actually pretty surprised how 'natural' C already felt, and I caught myself trying to `printf("")` instead of `print("")`. I guess I haven't been writing as much Python in the past few weeks - mainly focusing on C and some Javascript side things. 

I was also pleasantly surprised how intuitive SQL was to write - nearly like plain English. SQL was one of those *I'll learn it eventually* things that I used to keep putting off, but I'm happy I finally took the time to learn it. In my projects that have required a backend, I've mostly used Firebase (and Mongo once, but that didn't pan out very well), so *relational* databases were a refreshing new experience.

[Skip to my thoughts.](#week-6-problems)

# Week 6
## Week 6 Notes
- denote a raw string with r''
    - raw means that special characters, like `\n` are entered as a slash and a n
	- also can use formatted string inside it with rf''
- `.` denotes any character
- `.*` denotes 0 or more characters
- `.+` denotes 1 or more characters or matches
- `?` denotes optionality
- `^` denotes a start
- `$` denotes the end of input
- to use regex, usually have to import `re`
	- comes with a couple useful functions
	- `.search` takes two+ arguments
		- pattern, searching string, and options
- to search for `y` or `yes`, you could do something like '^y(es)?$'
	- ^ denotes the start of the string
	- immediately after, there should be a y
	- the brackets with the ? denotes the optional 'es'
	- then, the string should end
- to search for `n` or `no`:
	- similar - '^n(o)?$'
- re.IGNORECASE ignores the case of the input
- used this in the DNA problem set
	- used .findall, which returns all matches for a particular STR

## Week 6 Problems
Finally being able to write implementations of the C problems in Python  was extremely refreshing. I could actually use *list comprehensions* and not the clunky 3+ line for loop syntax, for example. Python's just a lot more concise and as people put it, it reads like pseudocode. That makes it a lot easier to translate my brainwaves into proper, functioning programs, unlike C, where I have to wrestle with pointers and such.

As a comparison, here are some of the line counts of the problems in problem set 6.
- Hello: in C, 10 loc. In Python, 2 loc.
- Mario Less: in C, 24 loc. In Python, 12 loc.
- Mario More: in C, 29 loc. In Python, 15 loc.
- Cash: in C, 21 loc. In Python, 15 loc.
- Credit: in C, 65 loc. In Python, 21 loc.
- Readability: in C, 41 loc. In Python, 15 loc.
There's no correlation between lines of code and personal opinion, of course, but I personally prefer Python.

[Skip to my thoughts.](#conclusion)

# Week 7
## Week 7 Notes
- SQLite3 is the management system, SQL is the language standard
    - basically a system to query and select cells from Google Sheets or similar 
    - like more complicated CSV
- use `.schema` to access the schema, or layout of the database
- use `.import [file] [name]` to import a CSV or similar file
    - need to set `.mode csv` first
- once inside the database
    - run SQL queries, usually involving `select`
- `*` is a wildcard -> select all
    - `%` -> some characters
    - usually used with `like`
- to select a count, use the `count()` function
- to group, use `group by x`
    - also can order by x
- orders can be ascending or descending
- to limit the number of results, use `limit x`
- SQL queries can span multiple lines
    - written in all caps by convention, but lowercase works too
    - ends with a ;
- All database operations fall under create, read, update, or delete
    - in SQL, insert, select, update, delete
- To initialize a table, need to use a create statement
    - has x number of columns
    - each column has a specific data type
- These types can be:
    - blob -> binary files
    - integer -> several types
    - numeric
    - real -> real numbers
    - text
    - can also specify not null, to make sure it exists
- `where` matches a specific condition
    - can use and and or in conditions
    - `like` matches substrings of text
- `join` joins a second table to the first
    - use an `on` bit to compare two pieces of data
    - ex. stars.movie_id = movies.id
- while using the CS50 library, use 'SQL'
    - initialize a connection with `db = SQL('path')`
    - run commands with `db.execute('x')`
    - use wildcards with ? marks, specify in option arguments
- multiple types of keys
    - primary -> primary identifier
    - foreign key -> another row in another table
    - unique -> unique value in table
    - index -> creates an index structure to make queries quicker
- race conditions, where timing matters
    - solve with transaction types
- SQL injection attacks
    - when special characters are put in and not escaped

The two problems were pretty intuitive to solve, and only minor Googling was needed. Definitely around as challenging as the Python bit.

# Conclusion
We're almost done with the main chunk of CS50. I'm pretty surprised how fast these first 7 weeks went by - only the track (which I'm greatly looking forward to) and the final project (which I have a good idea for) to go! These'll probably take a while to complete as well, but I'm committed to writing a blog post a week describing my experiences.

Speaking of tracks, I'm planning on doing the Web track. I have experience with web technologies already, so this seemed like a logical choice. In past posts, people have commented that the Web track was a little outdated, so we'll see about that. I'm excited to get more acquainted with Flask, as well. Like SQL, getting more experience with Python in the web has been a 'one of those days' things, and hopefully, this will be a good reintroduction. 

What are your recommendations for learning SQL better - SQL games, websites, or communities?