JSON and related Flashcards
Import json
import json
How do you convert from json to python?
import json
x = ‘{“name”:”John”, “age”:30, “city”:”New York”}’
y = json.loads(x)
print(y[‘age’])
How do you convert from python to json?
import json
x = {
“name”: “John”,
“age”: 30,
“city”: “New York”
}
y = json.dumps(x)
print(y)
What is the below? How would you convert this?
x = {
“this”: “that”,
“the”: “other”
}
That is python, so you would convert to json
Jason would eat the python and take a dump. The byproduct is json
What is the below? How would you convert it?
x = { “this”: “that”, “the”: “other” }
JSON so you would convert it to python
The magician loads Jason into a magic box, he shuts the door and twirls his wand. Upon opening the door we see that Jason has changed into a python
which conversion type can we change things around on?
Remember, JSON works with json data so it manipulates JSON, not python.
Jason eats the python and takes a messy dump. We have to pick out the corn now!
Havery DENT comes in and claims he can fix this.
He pulls out a short knubby pair of keys, and then SiPs coffee from A florida gATORS mug before getting to work
json.dumps(x, indent=4, separators=(“.”,”=”), sort_keys=True)
What would you use to ask for the html info from a webpage
import requests
You go on a QUEST to find the page
pip install requests
How would you get the html page from:
https://w3schools.com/python/demopage.html?
import requests
x = requests.get(‘https://w3schools.com/python/demopage.html’)
print(x.text)
What HTTP requests can you do?
delete - delete a resource
get - data retrieval
head - header info only
patch - Like put but for partial mods
post - submit data
put - replace resource
request - sends a request of a specific method to the specified url
DELsin runs at super speeds to GET a banana from a farmer’s market.
He then takes off going 90 miles an hour.
Along the way he runs past a POSTed speed limit of 50 mph and laughs at it.
He arrives at a farmers market in Japan and PUTs the banana in one of the carts before taking off running again.
Unfortunately he wasn’t paying attenting and ran right into a samurai’s sword who was training cutting his HEAD off.
Guts finds his head and vows to go on QUEST to get it place back on.
He finds a wizard to help. The wizard casts a long mystical spell and poof! Delsin’s head is PATHed back on with a bandaid.
What sites can we use to test requests and posts?
postb.in
requestbin.com
How would you verify that you’re receiving a success code?
url = “you_url”
data = {“json”:”data”}
response = requests.post(url, json=data)
if response.status_code == 200:
print(“success”)
else:
print(“failure”)
or
response = requests.get(url)
if response.status_code == 200:
print(response.text)
How would you get info from an api with requests?
import requests
url = “https://url”
response = requests.get(url)
if response.status_code == 200:
data = response.json()
print(data)
else:
print(“error”)
Install mysql as well as pandas
pip install mysql-connector-python
pip install pandas
import everything you need to run mysql
import mysql.connector
from mysql.connector import Error
import pandas as pd
Download MYSQL community on your computer
https://www.mysql.com/downloads/
Create a connection to a database
import mysql.connector
import pymysql <- do this instead
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”
)
print(mydb)
Remember to install cryptography
Createa database named: mydatabase
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”
)
mycursor = mydb.cursor()
mycursor.execute(“CREATE DATABASE mydatabase”)
Check if your database exists
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”
)
mycursor = mydb.cursor()
mycursor.execute(“SHOW DATABASES”)
for x in mycursor:
print(x)
If your database exists you can just connect, do this
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
Create a table called customers
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
mycursor.execute(“CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))”)
confirm the table exists
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
mycursor.execute(“SHOW TABLES”)
for x in mycursor:
print(x)
What is a primary key?
Create one
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
mycursor.execute(“CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))”)
The business dolphin throws his ID INTO the automobile and the automobile start to duplicate (auto_increment)
The business dolphin calls it a day and goes to O’Charlies to get the PRIME RIB for dinner, he cuts it up with his keys
When creating a table, you should also create a column with a unique key for each record.
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
mycursor.execute(“CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))”)
Create a primary key for an existing table
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
mycursor.execute(“ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY”)
Insert a record into the customers table
save it and show the number of rows affected by the last execute command
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
sql = “INSERT INTO customers (name, address) VALUES (%s, %s)”
val = (“John”, “Highway 21”)
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, “record inserted.”)
mydb.commit() actually applies changes
Insert multiple rows of customers into a table
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
sql = “INSERT INTO customers (name, address) VALUES (%s, %s)”
val = [
(‘Peter’, ‘Lowstreet 4’),
(‘Amy’, ‘Apple st 652’),
(‘Hannah’, ‘Mountain 21’),
(‘Michael’, ‘Valley 345’),
(‘Sandy’, ‘Ocean blvd 2’),
(‘Betty’, ‘Green Grass 1’),
(‘Richard’, ‘Sky st 331’),
(‘Susan’, ‘One way 98’),
(‘Vicky’, ‘Yellow Garden 2’),
(‘Ben’, ‘Park Lane 38’),
(‘William’, ‘Central st 954’),
(‘Chuck’, ‘Main Road 989’),
(‘Viola’, ‘Sideway 1633’)
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, “was inserted.”)
Ask the cursor to show you the last inserted row
Remember, you have to actually insert something for it to work
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
sql = “INSERT INTO customers (name, address) VALUES (%s, %s)”
val = (“Michelle”, “Blue Village”)
mycursor.executemany(sql, val)
mydb.commit()
print(“1 record inserted, ID:”, mycursor.lastrowid)
Get all records from the customer table and print them
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
mycursor.execute(“SELECT * FROM customers”)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
fetchall() gets all rows from the last executed statement
print only the names, addresses columns from customers tables
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
mycursor.execute(“SELECT name, address FROM customers”)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Only get the first row from the db
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
mycursor.execute(“SELECT * FROM customers”)
myresult = mycursor.fetchone()
print(myresult)
Select record’s that contain the address Park Lane 38
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
sql = “SELECT * FROM customers WHERE address =’Park Lane 38’”
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Get records where the address contains the word “way”
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
sql = “SELECT * FROM customers WHERE address LIKE ‘%way%’”
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
What is used to escape values?
Why is this needed?
%s
This is needed to prevent sql injections from users
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
sql = “SELECT * FROM customers WHERE address = %s”
adr = (“Yellow Garden 2”, )
mycursor.execute(sql, adr)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Get everything from you table and sort it by name in ascending order
How do you sort by descending order
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
sql = “SELECT * FROM customers ORDER BY name”
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
sql = “SELECT * FROM customers ORDER BY name DESC”
Delete records with the address Mountain 21
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
sql = “DELETE FROM customers WHERE address = ‘Mountain 21’”
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, “record(s) deleted”)
IF YOU OMIT WHERE IT WILL DELETE ALL RECORDS
Use an escape value to delete Yellow Garden 2 addresses
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
sql = “DELETE FROM customers WHERE address = %s”
adr = (“Yellow Garden 2”, )
mycursor.execute(sql, adr)
mydb.commit()
print(mycursor.rowcount, “record(s) deleted”)
Delete an entire table
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
sql = “DROP TABLE customers”
mycursor.execute(sql)
Avoid getting an error for trying to delete a table that doesn’t exist
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
sql = “DROP TABLE IF EXISTS customers”
mycursor.execute(sql)
Overwrite the address column from ‘Canyon 123’ to ‘Valley 345’
How would you do this with an escape character?
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
sql = “UPDATE customers SET address = ‘Canyon 123’ WHERE address = ‘Valley 345’”
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, “record(s) affected”)
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
sql = “UPDATE customers SET address = %s WHERE address = %s”
val = (“Valley 345”, “Canyon 123”)
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, “record(s) affected”)
Grab all characters but make sure you only get the first 5 records
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
mycursor.execute(“SELECT * FROM customers LIMIT 5”)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Grabe 5 records starting at the third record
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
mycursor.execute(“SELECT * FROM customers LIMIT 5 OFFSET 2”)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
You have two tables
One contains users names and a number that correlates to another table that contains favorite icecreams by an id number.
How do you combine fav and id?
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)
mycursor = mydb.cursor()
sql = “SELECT \
users.name AS user, \
products.name AS favorite \
FROM users \
INNER JOIN products ON users.fav = products.id”
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
What is the difference between INNER JOIN and LEFT JOIN and RIGHT JOIN?
INNER JOIN only shows the records where there is a match.
If you want to show all users, even if they do not have a favorite product, use the LEFT JOIN statement:
RIGHT JOIN If you want to return all products, and the users who have them as their favorite, even if no user have them as their favorite, use the RIGHT JOIN statement
Select all users and their favorite product:
sql = “SELECT \
users.name AS user, \
products.name AS favorite \
FROM users \
LEFT JOIN products ON users.fav = products.id”
What are html headers?
Part of communication with server. These provide metadata
headers appear in the communication,
for instance when you make a request to a server one of the headers are User-Agent
How do you change headers with request to get api info?
response = requests.get(url, headers={“Authorization”: f”Bearer {api_key”})
