VA Session 5 Python vs SQL

Question 1

SQL vs Python

Answer 1

SQL
- to store data & initial processing
- for operations on large datasets (many rows & columns)
- for heavy duty tasks
- limited numbers of built-in functions
- often more efficient (python every time code executed, connects to database again)

Python
- for exploratory & further data analysis
- many built-in functions

Question 2

Python connecting to database

Answer 2

• supports connecting to common commercial & open-source databases
• SQL commands
• Why? -> data often stored in database (not file e.g. csv)

Question 3

Connecting Python & database - code

Answer 3

inport sqlite3
db = sqlite3.connect(“path…”)

Question 4

Querying database with Python - code

Answer 4

db.execute(“SELECT … FROM …”).fetchone()

Question 5

Adding a record to database with Python - code

Answer 5

db.execute(“INSERT INTO table(column) VALUES (“…”)”)

Question 6

Return output - code

Answer 6

• fetchall(): retrieves result of query as list where each returned row = tuple
• fetchone(): retrieves first row of results of query as tuble

Question 7

Parameters

Answer 7

• allow feeding values into query
• e.g. name = input(“Product name: )
• db.execute(“SELECT ProductPrice FROM Product WHERE ProductName=?”,[name]).fetchone()

Question 8

Error Handling

Answer 8

• prevent if query fails, script continues running
• One approach: try & except

Question 9

SQL Injections: Danger

Answer 9

• Threat when application connected to database (e.g. web interfaces: webpages input communicates with a database)
• unauthorized view or edit data (or whole database)
• unauthorized inputs (e.g. DROP TABLE Users)

Question 10

approaches of calculating most expensive product

Answer 10

• SQL: db.execute(“SELECT Max(ProductPrice) FROM Product”).fetchone()
• SQL & Python: prices = db.executre(…).fetchall(); mostExpensive = max(prices)
  -1> 2 (2. brings potentially millions of unnecessary data