Python Programming/Databases

From Wikiversity
Jump to navigation Jump to search

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

Objectives and Skills[edit]

Objectives and skills for this lesson include:

  • Standard Library
    • sqlite3
    • logging module

Readings[edit]

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

Multimedia[edit]

  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[edit]

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

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

import sqlite3

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

The connection.cursor() Method[edit]

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

cursor = connection.cursor()

The cursor.execute() Method[edit]

The cursor.execute() method executes an SQL statement.[3]

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[edit]

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

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[edit]

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

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

The connection.commit() Method[edit]

The connection.commit() method commits the current transaction.[6]

connection.commit()

The connection.close() Method[edit]

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

connection.close()

SQLite Database Example[edit]

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[edit]

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.[8]

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[edit]

The logging.basicConfig() method can be used to configure logging to output to a file.[9]

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[edit]

Tutorials[edit]

  1. Complete one or more of the following tutorials:

Practice[edit]

  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[edit]

Database Concepts[edit]

  • A database is an organized collection of data.[10]
  • A database is a collection of schemas, tables, queries, reports, views and other objects.[11]
  • A query language is a computer language used to make queries (or questions about data) in databases and information systems.[12]
  • 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.[13]
  • A database table is a collection of related data held in a structured format within a database consisting of fields (columns), and rows.[14]
  • 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.[15]
  • 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.[16]
  • A SELECT statement retrieves zero or more rows from one or more database tables or database views.[17]
  • An INSERT statement adds one or more records to any single table in a relational database.[18]
  • 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.[19]
  • A DELETE statement removes one or more records from a table.[20]
  • 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.[21]

Python Databases[edit]

  • The sqlite3.connect() method opens a connection to the given SQLite database file database.[22]
  • The connection.cursor() method creates and returns a cursor object that may be used to execute SQL commands.[23]
  • The cursor.execute() method executes an SQL statement.[24]
  • The cursor.executescript() method executes multiple SQL statements at once.[25]
  • The cursor.fetchall() method fetches all (remaining) rows of a query result, returning a list.[26]
  • The connection.commit() method commits the current transaction.[27]
  • The connection.close() method closes the database connection. Any changes that have not been committed will be lost.[28]
  • 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.[29]
  • The logging.basicConfig() method can be used to configure logging to output to a file.[30]

Key Terms[edit]

attribute
One of the values within a tuple. More commonly called a “column” or “field”.[31]
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).[32]
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.[33]
database browser
A piece of software that allows you to directly connect to a database and manipulate the database directly without writing a program.[34]
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.[35]
index
Additional data that the database software maintains as rows and inserts into a table to make lookups very fast.[36]
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.[37]
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.[38]
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.[39]
relation
An area within a database that contains tuples and attributes. More typically called a “table”.[40]
tuple
A single entry in a database table that is a set of attributes. More typically called “row”.[41]

Review Questions[edit]

Enable JavaScript to hide answers.
Click on a question to see the answer.
  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[edit]

See Also[edit]

References[edit]

  1. Python.org: sqlite3
  2. Python.org: sqlite3
  3. Python.org: sqlite3
  4. Python.org: sqlite3
  5. Python.org: sqlite3
  6. Python.org: sqlite3
  7. Python.org: sqlite3
  8. Python.org: Logging How To
  9. Python.org: Logging How To
  10. Wikipedia: Database
  11. Wikipedia: Database
  12. Wikipedia: Query language
  13. Wikipedia: Database
  14. Wikipedia: Table (database)
  15. Wikipedia: Data manipulation language
  16. Wikipedia: Data definition language
  17. Wikipedia: Select (SQL)
  18. Wikipedia: Insert (SQL)
  19. Wikipedia: Update (SQL)
  20. Wikipedia: Delete (SQL)
  21. Wikipedia: SQLite
  22. Python.org: sqlite3
  23. Python.org: sqlite3
  24. Python.org: sqlite3
  25. Python.org: sqlite3
  26. Python.org: sqlite3
  27. Python.org: sqlite3
  28. Python.org: sqlite3
  29. Python.org: Logging How To
  30. Python.org: Logging How To
  31. PythonLearn: Using databases and Structured Query Language (SQL)
  32. PythonLearn: Using databases and Structured Query Language (SQL)
  33. PythonLearn: Using databases and Structured Query Language (SQL)
  34. PythonLearn: Using databases and Structured Query Language (SQL)
  35. PythonLearn: Using databases and Structured Query Language (SQL)
  36. PythonLearn: Using databases and Structured Query Language (SQL)
  37. PythonLearn: Using databases and Structured Query Language (SQL)
  38. PythonLearn: Using databases and Structured Query Language (SQL)
  39. PythonLearn: Using databases and Structured Query Language (SQL)
  40. PythonLearn: Using databases and Structured Query Language (SQL)
  41. PythonLearn: Using databases and Structured Query Language (SQL)