Python and SQL
Tags: python, sql, beginners
Published: 2023-11-06 17:12:44

These are all just my notes so far from Python and SQL.  All images are from Flatiron and are not my own work. Hope this helps someone!

Python Notes
Interpreter: a program that executes other programs. Python programs require the Python interpreter to be installed so that they can run
Python Shell: an interactive interpreter that can be accessed from the command line
Data Type: a specific kind of data. Python interpreter uses these types to determine which actions can be performed on different data items
Code Block: a collection of code that is interpreted together. Python groups code blocks by indentation level
Function: a named code block that performs a sequence of actions when it is called 
Scope: the area in your program where a specific variable can be called

What Python Can Do
	*Read and write files
	*Listen for network requests and send responses
	*Connect to a database to access and update data

Can make all kinds of different applications-not just web applications
	*Command line interfaces(CLIs)
	*Web servers
	*Games
	*Web scrapers

* print() is the same as console.log()
* # is the same as // or /* */
* To run an application python filename.py – filename.py is the 
  relative path 
	EXAMPLE: python lib/app.py

* Typing python in terminal will put you in a sandbox
* Exit by typing exit() or pressing ctrl + d
* snake_case not camelCase
* to install pytest in Pipfile enter pipenv install
* then run pipenv shell
* run pytest to see the tests  pytest -x to see one at a time

Common Data Types
Strings

Python
	dog_name = ‘Osiris’
	print(f’Say hello to my dog(dog_name}’)
=>	Say hello to my dog Osiris

JavaScript
	Const dogName = ‘Osiris’
	Console.log(`Say hello to my dog ${dogName}`)
=>	Say hello to my dog Osiris

‘hello’
=>	Hello

‘hello’.upper()
=>	HELLO

‘HELLO’.lower()
=>	hello

‘hello’.capitalize()
=>	Hello

‘hello’ + ‘world’
=>	helloworld

‘hello’ * 3
=>	hellohellohello

type(‘hello’)
=>	will show you what class an object is

dir(‘hello’)
=>	list of all the methods that object responds to

Numbers

Intergers are whole numbers = 13
Floats are decimal numbers = 4.5

int(‘1’)
=>	1

int(1.1)
=>	1

float(‘1.1’)
=>	1.1

Will convert an interger to a float when performing math operations

Sequence Types
Lists

[1, 3, 400, 7]
=>	[1, 3, 400, 7]

list()
=>	[]
list_abc = [‘a’, ‘b’, ‘c’]
list_abc[0]
=>	a
list_abc[1]
=>	b
list_abc[2]
=>	c

GIVES YOU THE NUMBER OF OBJECTS
len([1, 3, 400, 7])
=>	4
len([2, 7, 8, 19, 39, 22, 76])
=>	7

SORTS FROM SMALLEST TO LARGEST
sorted([5, 100, 234, 7, 2])
=>	[2, 5, 7, 100, 234]

REMOVES THE LARGEST NUMBER IN THE LIST
list_123 = [1, 2, 3, 7, 5, 9, 6]
list_123.pop()
=>	9
print(list_123)
=>	[1, 2, 3, 7, 5, 6]

REMOVES SELECTED NUMBER FROM LIST
list_123.remove(7)
print(list_123)
=>	[1, 2, 3, 5, 6]

Tuples

(1, 2, 3)
=>	(1, 2, 3)
Tuple([1, 2, 3])
=>	(1, 2, 3)

* Tuples are created with open and close parentheses
* tuple() can alse be used to cast lists and other iterable data types to tuples
* Tuples are immutable – once a tuple has been created the tuple itself CANNOT be changed
* Functions that work ok lists to create new data will still work on tuples – do not contain methods like .pop() and .insert() that would change their content
* Most commonly seen when retrieving data from a database – you want to keep an accurate record of what is in the database while your application works with the data – a tuple will protect that information until it is no longer needed.

Sets

set()
=>	{}
set([3, 2, 3, ‘a’, ‘b’, ‘a’])
=>	{2, 3, ‘a’, ‘b’}

Set is unindexed, unordered, and unchangeable
=>	Unindexed – we can’t access elements of the set using square brackets like we do in lists and tuples
=>	Unordered – the contents are in a random order
=>	Unchangeable – individual elements can’t be changed

Sets have many of the same methods as lists
Can use .pop() and .remove()

Dictionaries

dict(x = 1, y = 2)
=>	{‘x’ : 1, ‘y’ : 2}

{‘key1’ : ‘value1’, ‘key2’ : ‘value2’}
=>	{‘key1’ : ‘value1’, ‘key2’ : ‘value2’}

my_dict = {‘key1’ : 1, ‘key2’ : 2}
my_dict [‘key2’]
=>	2

Dictionaries are Python’s equivalent of a JavaScript object
Composed of key/value pairs
Each key points to a specific value

print(my_dict[‘key3’])
=>	KeyError: ‘key3’

USEFUL METHOD WHEN YOU’RE UNSURE IF A KEY EXISTS – RETURNS NONE INSTEAD OF ERROR
print(my_dict.get(‘key3’))
=>	None

CAN’T USE DOT NOTATION TO ACCESS KEYS ON DICTIONARIES – ONLY BRACKET NOTATION
my_dict.key2
=>	AttributeError: ‘dict’ object has no attribute ‘key2’

None

None – represents the absence of a value
JavaScript has undefined and null
Can’t create a variable without assigning a value
Must explicitly assign a value of None in you want an empty variable

no_value
=>	NameError: name ‘no_value’ is not defined

no_value = None
print(no_value) 
=>	None

Booleans

Only two values true and false
type(True)
=>	<class ‘bool’>
type(false)
=>	<class ‘bool’>
not True
=>	False
not False
=>	True
not 1
=>	False
not 0
=>	True
not ‘ ‘
=>	True
not None
=>	True
not [ ]
=>	True
not ( )
=>	True
not { }
=>	True

* Not is the operator that reverses the truth of a value, variable, or statement
* Operator != asserts that 2 values are not equal

Error Messages

VS CODE
1. #!usr/bin/env python3
2. 
3. print(hello world)

IN TERMINAL
python lib/a_name_error.py
o	File ‘lib/a_name_error.py’, 
o	Line 3, in <module>
=>	Print(hello world)
o	NameError: name ‘hello world’ is not defined

* Error occurred in file ‘lib/a_name_error.py’
* Line of code with error line 3
* Scope of error <module>
* Type of error NameError:
* Reason for error name ‘hello world’ is not defined

Common Errors
Syntax Errors

VS CODE
2 * 
IN TERMINAL
=>	File ‘<stdin>’, line 1
o	2 * #
o	      ^
=>	SyntaxError: invalid syntax

* There is a missing number – operator must be preceded  and followed my a number or variable with a numeric value

Logic Errors

* Most difficult – not perceived as errors by Python interpreter
* To find will have to go through code line by line
* Debugging tools -pdb- helpful for locating logic errors

VS CODE
	count = 1
	while count < 100
		print(‘%i’ % count)
IN TERMINAL
=>	1
=>	1
=>	1
=>	1
=>	1
=>	1
=>	1
=>	…
***programmer forgot to increase count during each iteration of the while loop – loop will continue forever

Exceptions

* Pop up when the interpreter knows what to do with a piece of code but is unable to complete the action
* Difference between other types of errors and exceptions – python interpreter can continue reading the application after and exception, just need to tell it what to expect

Most Common Exceptions
Assertion Error

assert(1 == 2)
=>	Traceback (most recent call last):
o	File ‘<stdin>’, line 1, in <module>
=>	AssertionError

IndexError and KeyError

* IndexError – when you try to access an element at an index past the end of a list
* KeyError – relate to dict objects – if a key is referenced but does not exist

list = [0, 1, 2, 3, 4]
dict = {‘a’: 1, ‘b’: 2, ‘c’: 3}

list[10]
=>	Traceback (most recent call last): 
o	File ‘<stdin>’, line 1 in <module>
=>	IndexError: list index out of range

dict[‘d’]
=>	Traceback (most recent call last):
o	File ‘<stdin>’, line 1, in <module>
=>	KeyError: ‘d’

NameError

flatiron_school
=>	Traceback (most recent call last):
o	File ‘<stdin>’, line 1, in <module>
=>	NameError: name ‘flatiron_school’ is not defined
* Occurs when name is referenced before it’s been defined

TypeError

wrong_type = ‘abc’ + 123
=>	Traceback (most recent call last);

Function Syntax
JAVASCRIPT
* function keyword identifies this code as a function
* myFunction – variable name – used to refer to function from elsewhere in our code
* () – where we define our parameter(s)
* Param – variable name given to the functions parameter – will be assigned a value when the function is invoked and passed and argument
* {} – define the body of the function
* console.log – method that will output info to the terminal – different from function’s return value
* return – needed when we want our function to return a value when it is called – here it will return a value of whatever the param variable is plus one

JAVASCRIPT
	function myFunction(param) {
		console.log(‘Running myFunction’)
		return param + 1
	const myFunctionReturnValue = myFunction(1)
o	‘Running myFunction’
        console.log(myFunctionReturnValue)
o	2

* Calling the function myFunction with an argument of 1
* Reassigning the return value of myFunction to a new variable – myFunctionReturnValue 

PYTHON
* def instead of function
* snake_case instead of camelCase
* param still defined in () after method name
* NO {} – instead -  ():
* Must indent all code that is meant to be executed in function
return statements work similarly – no semicolon needed after return value
* NO closing – new code written at original indentation level

PYTHON

	def my_function(param):
		print(‘Running my_function’)
		return param + 1
	my_function_return_value = my_function(1)
o	Running my_function
o	2
        my_function_return_value
o	2
* Calling function my_function() – see the output from print() function in terminal followed by return value
* Return value – 2 – is saved to variable my_function_return_value
**some functions are referred to as methods
**methods – special type of function belonging to objects – act upon those objects when called
**EXAMPLE list.sort() and dict.get()

Arguments

JAVASCRIPT
	function sayHi(name) {
		Console.log(`Hi there, ${name}!`)
	}
	sayHi()
o	Hi there, undefined!

Bugs like these are hard to identify – only found by testing the code and looking for unexpected behavior

PYTHON
	def say_hi(name):
	print(f’Hi there, {name}’)
	say_hi()
o	TypeError: say_hi() missing 1 required positional argument: 					 
        ‘name’
	say_hi() was supposed to have name as an argument

Default Arguments

JAVASCRIPT
	function sayHi(name = ‘friend’) {
		console.log(`Hi there, ${name}!`)
	}
	sayHi()
o	‘Hi there, friend!’
	sayHi(‘Sunny’)
o	‘Hi there, Sunny!’

PYTHON
	def say_hi(name=’Engineer’):
		print(f’Hi there, {name}!’)
	say_hi()
o	“Hi there, Engineer!”
	say_hi(‘Sunny’)
o	“Hi there, Sunny!”

Return Values

All functions are generally useful for one or both of  these
** What return value they have
** What side effects the have (what other parts of the application they change; what they output to the terminal; or what they write to a file; etc)
* Writing console.log() or print() is a side effect of a function – different from return value

JAVASCRIPT
	function addAndLog(num1, num2) {
		console.log(num1 + num2)
	}
	function addAndReturn(num1, num2) {
		return num1 + num2
	}
	const sum1 = addAndLog(2, 2)
	const sum2 = addAndReturn(2, 2)
o	sum1 returns undefined because it is logged, not returned
o	sum2 returns 4 because it is returned

You use the return keyword to retrieve an output value just like JavaScript


PYTHON
	def style_painter():
		best_hairstyle = ‘Bob Ross’
		return ‘Jean-Michel Basquiat’
		return best_hairstyle
        print(best_hairstyle)
	stylish_painter()
o	‘Jean-Michel Basquiat’

return will disrupt the execution of the function and prevent Python from running any lines of code after  return keyword

Pass

Use pass keyword in empty functions until they are ready to be fleshed out
	def my_future_function():
		pass

Comparison Operators

JAVASCRIPT
	‘1’ == 1
=>	True
	0 == []
=>	True
	[] == ![]
=>	True (??)

PYTHON
	‘1’ == 1
=>	False
	1 == 1
=>	True

JAVASCRIPT
	[1, 2, 3] === [1, 2, 3]
=>	False
In JS 2 arrays are unique objects in memory

PYTHON
	[1, 2, 3] == [1, 2, 3]
=>	True
	1.0 == 1
=>	true

* Python considers these to have equivalent values
* Python will check if an Integer has equivalent value to a Float

PYTHON
	True and true
=>	true
	False and false
=>	false
	False and true
=>	False
	True or true
=>	True
	False or false
=>	False
	False or true
=>	True
	not true
=>	False
	not not true
=>	True

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h8xj0s06bjkdg78qmxsn.png)

^^^Conditional statements in JavaScript VS Python

Falsy Values
Empty lists [ ]
Empty tuples ( )
Empty dictionaries { }
Empty sets set()
Empty strings ‘ ‘ or “ “
0 or 0.0
None
False

SQL
In terminal:
sqlite3 <filename.db> (or no file name) – opens terminal 
.quit or ctrl + d – exits terminal
.help – list of commands
.schema – see the structure of the database
.headers on – output the name of each column
.mode column – makes nice columns

In VS Code:
Right click <filename.db> and click Open Database
On bottom left of screen click the down arrow SQLITE EXPLORER
Then click down arrow on filename
Right click on file to select or insert



In terminal to create a table
CREATE TABLE anime (
	id INTEGER PRIMARY KEY,
	name TEXT, 
	number_episodes INTEGER);
Alter Table
ALTER TABLE anime ADD COLUMN genre TEXT;
.schema
CREATE TABLE anime ( 
	id INTEGER PRIMARY KEY, 
	name TEXT, 
	number_episodes INTEGER, 
	genre TEXT);
Drop Table
To delete a table
DROP TABLE anime

DATATYPES
NULL – no value
TEXT – any alphanumeric characters you want represented ad plain text
INTEGER – whole number – no letters, special characters, or decimal points
REAL – anything that a plain decimal (1.3 or 2.25) 
	SQL will store decimal <=15 characters
BLOB – used for holding binary data

Inputting information
INSERT INTO anime (name, number_episodes, genre) VALUES (‘Dr. Stone’, 52, ‘Fantasy’);

Select From
 	SELECT id, name, number_episodes, genre FROM anime;
OR
SELECT * FROM anime;
SELECT name FROM anime;
OR
	SELECT anime.name FROM anime;
		Gives just the names 
SELECT name, number_episodes FROM anime;
		Gives name and number_episodes 

Select Where
SELECT * FROM anime WHERE name = ‘Dr. Stone’;
		Gives any anime with name of ‘Dr. Stone’
SELECT * FROM anime WHERE number_episodes > 20;
		Gives any anime with more than 20 episodes
UPDATING
UPDATE anime SET name = ‘Bleach’ WHERE name = ‘Dr. Stone’;
		Changes the name Dr. Stone to Bleach
DELETIING
DELETE FROM anime WHERE id = 10;
		Deletes the anime with the id of 10
ORDER BY
SELECT * FROM anime ORDER BY number_episodes; 
		Gives order smallest to largest
SELECT * FROM anime ORDER BY number_episodes DESC;
		Gives order largest to smallest

LIMIT
SELECT * FROM anime ORDER BY number_episodes DESC LIMIT 1;
		Gives the first entry with the largest number of episodes
SELECT * FROM anime ORDER BY number_episodes DESC LIMIT 2;
		Gives first 2 entries with the largest number of episodes
BETWEEN
SELECT name FROM anime WHERE number_episodes BETWEEN 20 AND 50;
		Gives all anime that have 20-50 episodes
NULL 
INSERT INTO anime (name, number_episodes, genre) VALUES (NULL, NULL, ‘Action’);
		Adds another anime with blank name and number_episodes
To select
SELECT * FROM anime WHERE name IS NULL
COUNT
	SELECT COUNT (owner_id) From anime WHERE owner_id = 1;
		Shows all owners of the anime with an id of 1
GROUP BY
	SELECT genre, COUNT(genre) FROM anime GROUP BY genre
		Shows all the genres of anime and how many of each
	SELECT genre, owner_id, COUNT(genre) FROM anime GROUP BY genre, owner_id;
		Breaks down above by showing more than 1 owner of a genre