Overview/Questions Review: the Python tuple sequence. How does a - PDF document

CS108 Lecture 21: The Python DBABPI Aaron Stevens 18 March 2009 1 Overview/Questions – Review: the Python tuple sequence. – How does a custom application program connect to a database? – How are SQL queries formed using parameterized data? – How does the application read the results of SQL statements? 2 1

The Python tuple sequence tuple s are constructed by the comma operator (not within square brackets), with or without enclosing parentheses. t = 4,5,6 print t A single element tuple must have a trailing comma, such as (d,). 3 The Python tuple sequence tuple s are very similar to list s, but they are immutable: items in a tuple cannot be changed. tuple elements are accessed by index, or by simultaneous assignment: print t[0] a,b,c = t # unpacking a tuple 4 2

The Python DB API Python defines a standard API (objects and methods) for interaction with databases. – No standard implementation of this interface. – 3 rd party developers write their own libraries which conforms to the standard. We will be using the sqlite3 database, which is part of the Python distribution. – Nothing extra for you to install! 5 Creating a sqlite3 Connection A Connection is an object that represents the database connection. – Import the sqlite3 module – Use connection string to specify database file name. – Call connect function to obtain a Connection . 6 3

Obtain a Cursor object A Cursor object is an used to execute transactions (via SQL) against the database. – Create the Connection first… – Ask the Connection object to give you a Cursor object: 7 Executing an SQL Statement Use the Cursor object’s execute method to run an SQL statement against the database. Look at the results. What type are these? 8 4

Processing Query Results After calling the cursor.execute() method, we can process/interpret the results. SELECT queries: results will be zero or more rows of data returned from the database INSERT, UPDATE, and DELETE queries: the result will be the number of rows (zero or more) affected by the change. 9 Processing Query Results SELECT queries: results will be zero or more rows of data returned from the database The method cursor.fetchall() returns a tuple of rows (each row is a tuple of fields). data = cursor.fetchall() We can then process this tuple in the normal fashion using a for loop. 10 5

Processing Query Results A complete example, processing all rows returned from a SELECT query: 11 Processing Query Results INSERT, UPDATE, and DELETE queries: the result will be the number of rows (zero or more) affected by the change. The attribute cursor.rowcount is an integer, the number of rows affected. 12 6

Committing Changes For INSERT, UPDATE, and DELETE queries, you need to execute the method: conn.commit() on the Connection object to commit your changes. It might be a good idea to only commit if the row count is reasonable (e.g. 1). 13 Parameterized SQL Most likely, SQL queries in an application will be dependent on some data input by the user. Don’t do this: This kind of statement is vulnerable to SQL injection – a major security risk. 14 7

SQL Injection SQL injection is a technique that exploits the syntax of SQL to chain extra statements to an SQL query. Suppose user inputs: BUD’;DROP TABLE stocks AND ‘t’=‘t The resulting SQL becomes: SELECT * from stocks WHERE symbol=‘BUD’;DROP TABLE stocks AND ‘t’=‘t’ Don’t think the hackers haven’t tried this! 15 Parameterized SQL Instead, do this: and put the input parameter into a tuple: 16 8

Parameterized SQL Also, use parameterized SQL for INSERT statements. – (assume variables symbol, name, price, earnings, yield have received user input): sql = “INSERT INTO stocks VALUES (?,?,?,?,?)” parameters = (symbol,name,price,earnings,yield) cursor.execute(sql, parameters) 17 SQL Injection Source: www.xkcd.com 18 9

Take-Away Points – tuple – DBAPI – Connection object – Cursor object – SQL injection! 19 Using sqliteClient Program I wrote a client a PythonCard GUI application which is a client program to interact with a sqlite3 database. Instructions for installing PythonCard are here: http://www.cs.bu.edu/courses/cs108/slides/CS108.PythonCard.Install.pdf You may use this to experiment with SQL statements. The program can be downloaded from: http://www.cs.bu.edu/courses/cs108/util/sqliteClient.zip 20 10

Student To Dos – Readings for this week: SQL Tutorial http://www.firstsql.com/tutor.htm http://docs.python.org/library/sqlite3.html – Readings for next week: HTML Tutorial http://www.w3schools.com/HTML/  Read “Introduction”, “Elements”, “Basic Tags” for MONDAY; the rest for later in the week. – HW08 due Wednesday 3/18 – Quiz 4 will be on Friday 3/20  User defined classes  Lists, dictionaries 21 11