# Python Programming/Databases

There are many different database implementations.This lesson introduces Python database processing using SQLite.

## Objectives and Skills

Objectives and skills for this lesson include:
* Standard Library
** sqlite3
** logging module

## Readings

* Wikipedia: Database
* Wikipedia: SQL
* Wikipedia: SQLite
* Wikipedia: Logfile
* Python for Everyone: Using databases and SQL

## Multimedia

* YouTube: Creating a database, table, and inserting - SQLite3 with Python 3 part 1
* YouTube: Inserting variables to database table - SQLite3 with Python 3 part 2
* YouTube: Read from (SELECT) Database table - SQLite3 with Python 3 part 3

## Examples
### Connecting to the database: The sqlite3.connect Method

The sqlite3.connect method opens a connection to the given SQLite database file database.

### The connection.cursor Method

The connection.cursor method creates and returns a cursor object that may be used to execute SQL commands.

### The cursor.execute Method

The cursor.execute method executes an SQL statement.

### The cursor.executescript Method

The cursor.executescript method executes multiple SQL statements at once.

### The cursor.fetchall Method

The cursor.fetchall method fetches all (remaining) rows of a query result, returning a list.

### The connection.commit Method

The connection.commit method commits the current transaction.

### The connection.close Method

The connection.close method closes the database connection. Any changes that have not been committed will be lost.

### SQLite Database Example

The following example uses sqlite3 to demonstrate database processing.

### Logging to the Console

The logging module includes methods that provide a means of tracking events that happen when some software runs. Events have a descriptive message and an importance which the developer ascribes to the event; the importance can also be called the level or severity. Python includes severity levels for DEBUG, INFO, WARNING, ERROR, and CRITICAL. By default, logging messages are sent to the console.
Output:

### Logging to a File

The logging.basicConfig method can be used to configure logging to output to a file.
Output sent to test.log:

## Activities
### Tutorials

* Complete one or more of the following tutorials:
** TutorialsPoint
*** SQLite Python Tutorial
*** MySQL Database Access
** ZetCode
*** SQLite Python Tutorial

### Practice

* Create a Python program that saves data from a text file in an SQLite database. Check for a filename parameter passed from the command line. If there is no parameter, ask the user to input a filename for processing. Verify that the file exists and then use RegEx methods to parse the file and add each name and score to the database. Query the database to display the stored data in descending order by score. Include error handling in case the file is formatted incorrectly. Create a text file of names and grade scores to use for testing based on the following format: Larry Fine: 80 Curly Howard: 70 Moe Howard: 90
* Create a Python program that reads XML data from http://www.w3schools.com/xml/simple.xml and saves the menu items in an SQLite database. Include fields for the item's name, price, description, and calories. After parsing and saving the XML data, query the database and display the menu items in decreasing order by price similar to: name - description - calories - price
* Create a Python program that asks the user for a Wikiversity page title. Use the Wikimedia Pageview API to look up page view statistics for the page for the current month and save the statistics in an SQLite database. Include fields for the page title, date, and page views. After saving the JSON data, query the database and display the page view statistics in decreasing order by page views.

## Lesson Summary
### Database Concepts

* A database is an organized collection of data.
* A database is a collection of schemas, tables, queries, reports, views and other objects.
* A query language is a computer language used to make queries (or questions about data) in databases and information systems.
* A database management system (DBMS) is a computer software application that interacts with the user, other applications, and the database itself to capture and analyze data. A general-purpose DBMS is designed to allow the definition, creation, querying, update, and administration of databases.
* A database table is a collection of related data held in a structured format within a database consisting of fields (columns), and rows.
* A data manipulation language (DML) is a family of syntax elements similar to a computer programming language used for selecting, inserting, deleting and updating data in a database.
* A data definition language (DDL) is a syntax similar to a computer programming language for defining data structures. Structured query language uses a collection of verbs used to modify the schema of the database by adding, changing, or deleting definitions of tables or other objects.
* A SELECT statement retrieves zero or more rows from one or more database tables or database views.
* An INSERT statement adds one or more records to any single table in a relational database.
* An UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.
* A DELETE statement removes one or more records from a table.
* SQLite is a relational database management system contained in a C programming library. In contrast to many other database management systems, SQLite is not a client–server database engine. Rather, it is embedded into the end program.

### Python Databases

* The sqlite3.connect method opens a connection to the given SQLite database file database.
* The connection.cursor method creates and returns a cursor object that may be used to execute SQL commands.
* The cursor.execute method executes an SQL statement.
* The cursor.executescript method executes multiple SQL statements at once.
* The cursor.fetchall method fetches all (remaining) rows of a query result, returning a list.
* The connection.commit method commits the current transaction.
* The connection.close method closes the database connection. Any changes that have not been committed will be lost.
* The Python logging module includes methods that provide a means of tracking events that happen when some software runs. Events have a descriptive message and an importance which the developer ascribes to the event; the importance can also be called the level or severity. Python includes severity levels for DEBUG, INFO, WARNING, ERROR, and CRITICAL. By default, logging messages are sent to the console.
* The logging.basicConfig method can be used to configure logging to output to a file.

## Key Terms
### attribute

    One of the values within a tuple. More commonly called a “column” or “field”.

### constraint

    When we tell the database to enforce a rule on a field or a row in a table. A common constraint is to insist that there can be no duplicate values in a particular field (i.e., all the values must be unique).

### cursor

    A cursor allows you to execute SQL commands in a database and retrieve data from the database. A cursor is similar to a socket or file handle for network connections and files, respectively.

### database browser

    A piece of software that allows you to directly connect to a database and manipulate the database directly without writing a program.

### foreign key

    A numeric key that points to the primary key of a row in another table. Foreign keys establish relationships between rows stored in different tables.

### index

    Additional data that the database software maintains as rows and inserts into a table to make lookups very fast.

### logical key

    A key that the “outside world” uses to look up a particular row. For example in a table of user accounts, a person’s email address might be a good candidate as the logical key for the user’s data.

### normalization

    Designing a data model so that no data is replicated. We store each item of data at one place in the database and reference it elsewhere using a foreign key.

### primary key

    A numeric key assigned to each row that is used to refer to one row in a table from another table. Often the database is configured to automatically assign primary keys as rows are inserted.

### relation

    An area within a database that contains tuples and attributes. More typically called a “table”.

### tuple

    A single entry in a database table that is a set of attributes. More typically called “row”.

## Review Questions

* A database is _____. A database is an organized collection of data.
* A database is a collection of _____, _____, _____, _____, _____ and _____. A database is a collection of schemas, tables, queries, reports, views and other objects.
* A query language is _____. A query language is a computer language used to make queries (or questions about data) in databases and information systems.
* A database management system (DBMS) is _____. A database management system (DBMS) is a computer software application that interacts with the user, other applications, and the database itself to capture and analyze data. A general-purpose DBMS is designed to allow the definition, creation, querying, update, and administration of databases.
* A database table is _____. A database table is a collection of related data held in a structured format within a database consisting of fields (columns), and rows.
* A data manipulation language (DML) is _____. A data manipulation language (DML) is a family of syntax elements similar to a computer programming language used for selecting, inserting, deleting and updating data in a database.
* A data definition language (DDL) is _____. A data definition language (DDL) is a syntax similar to a computer programming language for defining data structures. Structured query language uses a collection of verbs used to modify the schema of the database by adding, changing, or deleting definitions of tables or other objects.
* A SELECT statement _____. A SELECT statement retrieves zero or more rows from one or more database tables or database views.
* An INSERT statement _____. An INSERT statement adds one or more records to any single table in a relational database.
* An UPDATE statement _____. An UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.
* A DELETE statement _____. A DELETE statement removes one or more records from a table.
* SQLite is _____. SQLite is a relational database management system contained in a C programming library. In contrast to many other database management systems, SQLite is not a client–server database engine. Rather, it is embedded into the end program.
* The sqlite3.connect method _____. The sqlite3.connect method opens a connection to the given SQLite database file database.
* The connection.cursor method _____. The connection.cursor method creates and returns a cursor object that may be used to execute SQL commands.
* The cursor.execute method _____. The cursor.execute method executes an SQL statement.
* The cursor.executescript method _____. The cursor.executescript method executes multiple SQL statements at once.
* The cursor.fetchall method _____. The cursor.fetchall method fetches all (remaining) rows of a query result, returning a list.
* The connection.commit method _____. The connection.commit method commits the current transaction.
* The connection.close method _____. The connection.close method closes the database connection. Any changes that have not been committed will be lost.
* The Python logging module . The Python logging module includes methods that provide a means of tracking events that happen when some software runs. Events have a descriptive message and an importance which the developer ascribes to the event; the importance can also be called the level or severity. Python includes severity levels for DEBUG, INFO, WARNING, ERROR, and CRITICAL. By default, logging messages are sent to the console.
* The logging.basicConfig method can be used to _____. The logging.basicConfig method can be used to configure logging to output to a file.

## Assessments

* Flashcards: Quizlet: Database and SQL
* Quiz: Quizlet: Database and SQL

## See Also

* Databases
* SQL