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

 * 1)  Database
 * 2)  SQL
 * 3)  SQLite
 * 4)  Logfile
 * 5) Python for Everyone: Using databases and SQL

Multimedia

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

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: DEBUG:root:Debug message sent to console. INFO:root:Info message sent to console. WARNING:root:Warning message sent to console. ERROR:root:Error message sent to console. CRITICAL:root:Critical message sent to console.

Logging to a File
The logging.basicConfig method can be used to configure logging to output to a file.

Output sent to test.log: DEBUG:root:Debug message sent to file. INFO:root:Info message sent to file. WARNING:root:Warning message sent to file. ERROR:root:Error message sent to file. CRITICAL:root:Critical message sent to file.

Tutorials

 * 1) Complete one or more of the following tutorials:
 * 2) * TutorialsPoint
 * 3) ** SQLite Python Tutorial
 * 4) ** MySQL Database Access
 * 5) * ZetCode
 * 6) ** SQLite Python Tutorial

Practice

 * 1) 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:
 * 2) 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:
 * 3) 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.

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”.

Assessments

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