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

1

1

Aaron Stevens

18 March 2009

CS108 Lecture 21: The Python DBABPI

2

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

3

The Python tuple sequence

tuples are constructed by the comma

• perator (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,).

4

The Python tuple sequence

tuples are very similar to lists, but they are immutable: items in a tuple cannot be changed. tuple elements are accessed by index,

• r by simultaneous assignment:

print t[0] a,b,c = t # unpacking a tuple

3

5

The Python DB API

Python defines a standard API (objects and methods) for interaction with databases.

– No standard implementation of this interface. – 3rd 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!

6

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.

4

7

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

• bject:

8

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?

5

9

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.

10

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.

6

11

Processing Query Results

A complete example, processing all rows returned from a SELECT query:

12

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.

7

13

Committing Changes

For INSERT, UPDATE, and DELETE queries, you need to execute the method: conn.commit()

• n 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).

14

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.

8

15

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!

16

Parameterized SQL

Instead, do this: and put the input parameter into a tuple:

9

17

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)

18

SQL Injection

Source: www.xkcd.com

10

19

Take-Away Points

– tuple – DBAPI – Connection object – Cursor object – SQL injection!

20

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

11

21

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