You will be creating a python module that emulates the behaviour of the built-in module, "sqlite3". Your module will be able to execute SQL statements corresponding to: creating tables, inserting rows, and selecting rows.
./cli.py test.sql output.txt, it loads the test.sql file, passes it to the database (your project.py file) and writes a report to output.txt. If you give it the optional argument (
./cli.py --sqlite test.sql output.txt), it passes the SQL statements to sqlite instead. You can use this to see the correct output.
All of the code you write for this project must be in "project.py", you may not modify any of the existing tests or test runners.
"cli.py" imports "project.py" and expects to find a "connect" function with one parameter. It passes a filename (specifically ":memory:"), which isn't used in this project. The "connect" function should return an object that has two methods "execute" and "close". For each SQL statement in a test file, "cli.py" will pass that SQL statement as a string to the "execute" method. The method should return an empty list, unless a select statement was executed that yielded rows. In that case, a list of tuples with each denoting a row, should be returned. The "close" method doesn't do anything, yet.
There is no need to write any database output to any files. Persistence will be covered in a later project. Each test will be done on a clean, empty database.
Your program is allowed to use any built in modules of python, with the exception of sqlite3. sqlite3 is the reference implementation for this project, your output will be compared against it for correctness. Importing the sqlite3 module in project.py will be considered academic dishonesty for these projects.
You may (and should) write additional functions and classes in "project.py" to perform the needed actions.
All SQL keywords will be in ALL_CAPS. For this project, the SQL keywords you need to handle are as follows:
CREATE TABLE INSERT INTO VALUES SELECT FROM ORDER BY
SQL data types (for this project) and their python equivalents are:
CREATE TABLE stocks (symbol TEXT, price REAL, qty INTEGER);
Creates a table named "stocks", with three columns: column "symbol" composed of text strings, column "price" composed of real valued numbers (floating point), and column "qty" composed of integers.
INSERT INTO stocks VALUES ('GOOG', 40.4, 7);
Inserts the row
('GOOG', 40.4, 7), into the table named "stocks".
SELECT * FROM stocks ORDER BY price;
Returns all the rows in the table named "stocks". The rows must be ordered by the column "price" ascending.
Given the above examples,
the result should be a list of tuples:
[('GOOG', 40.4, 7)]
SELECT qty, symbol FROM stocks ORDER BY price, qty;
Returns all rows, but only showing the qty and symbol values in that order. The rows are sorted by price (and where price is the same, by qty).
Some tests (specifically "Test_Suite/test.null.*.sql") have NULL values. You need to represent this fact with the python object, None. When returning NULL values from a select statement, you should yield a python None object.
Example Test File of SQL statements ("Test_Suite/test.tables.02.sql"):
CREATE TABLE student (name TEXT, grade REAL, piazza INTEGER); CREATE TABLE tests (version INTEGER, total REAL); INSERT INTO student VALUES ('Josh', 4.0, 1); INSERT INTO tests VALUES (1, 100.0); INSERT INTO student VALUES ('Tyler', 4.0, 2); INSERT INTO tests VALUES (2, 60.0); INSERT INTO student VALUES ('Grant', 3.2, 2); SELECT * FROM student ORDER BY piazza, grade; SELECT * FROM tests ORDER BY version;
Correct output from "cli.py Test_Suite/test.tables.02.sql":
CREATE TABLE student (name TEXT, grade REAL, piazza INTEGER); CREATE TABLE tests (version INTEGER, total REAL); INSERT INTO student VALUES ('Josh', 4.0, 1); INSERT INTO tests VALUES (1, 100.0); INSERT INTO student VALUES ('Tyler', 4.0, 2); INSERT INTO tests VALUES (2, 60.0); INSERT INTO student VALUES ('Grant', 3.2, 2); SELECT * FROM student ORDER BY piazza, grade; /* ('Josh', 4.0, 1) ('Grant', 3.2, 2) ('Tyler', 4.0, 2) */ SELECT * FROM tests ORDER BY version; /* (1, 100.0) (2, 60.0) */
The output from the database implemented in project.py is inserted in the comments.
If you are in doubt as to the correct output, just pass the --sqlite argument
to project_cli.py to use sqlite to solve the test (
./cli.py Test_Suite/test.tables.02.sql --sqlite).