SQL PYTHON AND DATABASES HOW TO REPRESENT A

SQL – PYTHON AND DATABASES

HOW TO REPRESENT A DATABASE IN PYTHON • Project 2 Requirement: • Needs to match the SQLite input and output identically (for the subset of SQL used in the projects) • The rest of the implementation content is entirely just suggestions on one way to solve the problem.

SQLITE TO PYTHON SQLite type NULL Python type None INTEGER int REAL float TEXT str BLOB bytes You can ignore the existence of BLOBs for the projects

TYPE CHECKING • Most databases require that if a column is declared to have a type, each element in that column must conform or be converted. • SQLite doesn't mind mismatched types. • It won't matter for this course as all SQL in tests will not have type mismatches • Example of a type mismatch: • • CREATE TABLE x (name TEXT); INSERT INTO x VALUES (5);

ROWS • You can represent a row in a table as a python tuple: • • INSERT INTO x VALUES (1, 1. 0, 'I''m a string', NULL); (1, 1. 0, "I'm a string", None) • If a row has only a single value, make sure it is still in a tuple: • • INSERT INTO x VALUES ('One is the loneliest number'); ("One is the loneliest number", ) • NOT ("One is the loneliest number")

TABLE • A table can be represented by a class in python with two parts: • • The schema (table name, column names and types) The rows • Rows aren't necessary unique, and row order doesn't matter, but you need to be able to iterate over the rows to get their content. • You need to be able to add and remove rows easily

WHAT DATA STRUCTURE COULD HOLD THE ROWS OF A TABLE? 1. 2. 3. 4. set list bag (multiset) tuple

DATABASE • A database can contain 0 or more tables. • It needs to be able to find tables by their name. • You can use class that contains a dictionary, mapping the tables' names to the table instances

PROJECT API • Like python's sqlite 3 module: import project conn = project. connect("filename. db") conn. execute("CREATE TABLE my. Table (name TEXT); ") conn. execute("INSERT INTO my. Table VALUES ('Josh'); ") rows = conn. execute("SELECT * FROM my. Table; ") for row in rows: print(row)

CONNECT • connect is a module function that takes a filename and results a connection object • The filename will be used in later projects that need your database to write its data to a file. For project 2, it should be ignored. • The object returned should be able to access the database to pass queries and return results with an execute method.

EXECUTE • The execute method on the Connection object takes a string (query) and returns an iterable object or None, depending on if the query returns rows from the database. • One possible implementation: • Do the query, then • • If not a SELECT query, return None Otherwise return a list of rows (tuples), may be empty if no rows exist

HOW TO WRITE THE EXECUTE METHOD? • Each query is guaranteed to be a legal SQL statement, for Project 2 there are only a few statements to handle: • • • CREATE TABLE. . . INSERT INTO. . . SELECT. . . • Caveat: whitespace is allowed before, after, and within queries • Spaces, n, t, r