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. Wikipedia: Database
2. Wikipedia: SQL
3. Wikipedia: SQLite
4. Wikipedia: 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

Examples

Connecting to the database: The sqlite3.connect() Method

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

import sqlite3

connection = sqlite3.connect('users.db')

The connection.cursor() Method

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

cursor = connection.cursor()

The cursor.execute() Method

The cursor.execute() method executes an SQL statement.

cursor.execute("""
    DROP TABLE IF EXISTS Users;
    """)

cursor.execute("""
    CREATE TABLE Users(
        UserID INT PRIMARY KEY NOT NULL,
        User TEXT NOT NULL
    );
    """)

The cursor.executescript() Method

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

cursor.executescript("""
    INSERT INTO Users(UserID, User) VALUES(1, 'Moe');
    INSERT INTO Users(UserID, User) VALUES(2, 'Larry');
    INSERT INTO Users(UserID, User) VALUES(3, 'Curly');
    """)

The cursor.fetchall() Method

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

cursor.execute("""
    SELECT UserID, User FROM Users;
    """)
rows = cursor.fetchall()

The connection.commit() Method

The connection.commit() method commits the current transaction.

connection.commit()

The connection.close() Method

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

connection.close()

SQLite Database Example

The following example uses sqlite3 to demonstrate database processing.

import sqlite3

connection = sqlite3.connect('users.db')
cursor = connection.cursor()

cursor.execute("""
    DROP TABLE IF EXISTS Users;
    """)

cursor.execute("""
    CREATE TABLE Users(
        UserID INT PRIMARY KEY NOT NULL,
        User TEXT NOT NULL
    );
    """)

cursor.executescript("""
    INSERT INTO Users(UserID, User) VALUES(1, 'Moe');
    INSERT INTO Users(UserID, User) VALUES(2, 'Larry');
    INSERT INTO Users(UserID, User) VALUES(3, 'Curly');
    """)

cursor.execute("""
    SELECT UserID, User FROM Users;
    """)
rows = cursor.fetchall()

connection.commit()
connection.close()

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.

import logging

# Log all severity levels.
logging.basicConfig(level=logging.DEBUG)

logging.debug("Debug message sent to console.")
logging.info("Info message sent to console.")
logging.warning("Warning message sent to console.")
logging.error("Error message sent to console.")
logging.critical("Critical message sent to 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.

import logging

# Log all severity levels to test.log
logging.basicConfig(filename='test.log', level=logging.DEBUG)

logging.debug("Debug message sent to file.")
logging.info("Info message sent to file.")
logging.warning("Warning message sent to file.")
logging.error("Error message sent to file.")
logging.critical("Critical message sent to 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.

Activities

Tutorials

1. Complete one or more of the following tutorials:
   • TutorialsPoint
     • SQLite Python Tutorial
     • MySQL Database Access
   • ZetCode
     • 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:
       Larry Fine: 80
    Curly Howard: 70
    Moe Howard: 90
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:
       name - description - calories - price
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.

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

1. A database is _____.

   A database is an organized collection of data.
2. A database is a collection of _____, _____, _____, _____, _____ and _____.

   A database is a collection of schemas, tables, queries, reports, views and other objects.
3. A query language is _____.

   A query language is a computer language used to make queries (or questions about data) in databases and information systems.
4. 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.
5. 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.
6. 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.
7. 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.
8. A SELECT statement _____.

   A SELECT statement retrieves zero or more rows from one or more database tables or database views.
9. An INSERT statement _____.

   An INSERT statement adds one or more records to any single table in a relational database.
10. 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.
11. A DELETE statement _____.

    A DELETE statement removes one or more records from a table.
12. 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.
13. The sqlite3.connect() method _____.

    The sqlite3.connect() method opens a connection to the given SQLite database file database.
14. The connection.cursor() method _____.

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

    The cursor.execute() method executes an SQL statement.
16. The cursor.executescript() method _____.

    The cursor.executescript() method executes multiple SQL statements at once.
17. The cursor.fetchall() method _____.

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

    The connection.commit() method commits the current transaction.
19. The connection.close() method _____.

    The connection.close() method closes the database connection. Any changes that have not been committed will be lost.
20. 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.
21. 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

References

← Internet Data

Python Programming

GUI →