Delete the Pythonanywhere database MySQL

Python in practice

subjects

  • Working with files and folders
  • Working with different file formats
  • Regular expressions
  • time and date
  • sys
  • HTTP client
  • Web development
  • Databases
  • Excel files
  • GUI
  • e-mail

Working with files

File = sequence of bytes on a storage medium

A file is often a sequence of text characters - e.g. the formats .txt, .html, .csv or .py.

The content of text files can be saved as Strings can be written and read, other files can be represented as a byte sequence.

Write text file

The file is opened for writing (w = write).

The character encoding is UTF-8 used.

Read text file

Standard mode: Read (r = read)

File modes

File modes

  • : Text mode (standard)
  • : Binary
  • : Read (default)
  • : (About) write
  • : Append

Binary files

Open and the with statement

The file is automatically closed when the indented code block is exited.

Character encoding

Text files can be coded differently:

  • ASCII
  • CP-1252 / western european / latin1
  • UTF-8

Practical tip: Use UTF-8 if possible (best support for special characters)

Character encoding

The standard character encoding for text files depends on the operating system:

File-like objects

Objects that support e.g. or:

  • Files (e.g. via)
  • ,
    • e.g.
  • Answers from the network, e.g. via

File-like objects

Read in line by line (low memory requirement):

File-like objects

Methods / Attributes:

  • (read the whole file)
  • (read the next 10 bytes)
  • (read the next line)

example

Program that queries the user for entries in a shopping list and saves them in a text file

File formats

Options for saving / reading:

  • Text files
  • JSON
  • CSV
  • XML
  • Python object files (using pickle and shelve)
  • Binary files

JSON

JSON: widely used and standardized file format

can represent the basic Python data types (none, bool, int, float, list, dict)

Store JSON

Read JSON

CSV

CSV is a text file format that can contain tabular data

Example:

CSV

Python Libraries:

  • csv (Part of the standard library)
  • pandas

CSV writing with pandas

Reading CSV with pandas

Write and read CSV

XML

two packages in the Python standard library:

    external library (extension of ElementTree):

    Create XML with ElementTree:

    XML with ElementTree: save

    Always specify an encoding when saving XML!

    XML with ElementTree: read

    Pickle

    Own file format in which different Python file types can be saved

    Danger:

    • Pickle files can only be read by Python
    • Pickle files can contain malicious code

    Pickle

    Pickle

    exercise

    • Saving and reading a shopping list (optionally with associated quantities) in various formats
    • Saving and reading a tic-tac-toe field in various formats

    Python data structure for the tic-tac-toe field:

    Working with files and folders

    important packages:

    os and shutil (1)

    • (current path)

    os and shutil (2)

      • (Delete file)
      • (delete empty folder)
      • (Delete folder)
      • (Move file or folder)
      • (Copy file)
      • (Copy folder)

      Excursus: general terminal commands

      Direct output via:

      Read results into Python using:

      exercise

      Program that searches all text files in a folder for a term and gives the numbers

      Regular expressions

      Mini-language to define a search pattern for text

      Examples of simple search patterns:

      • a .com domain:
      • an HTML heading:
      • a time:

      Try out

      Online: https://regexr.com/

      In VS Code: Ctrl + F and click on the button .*

      Special characters and escapes

      The following characters have special meanings:

      Special characters and escapes

      We avoid the special meaning by placing a backslash in front of it:

      Character categories

      • : any character except a line break
      • : Whitespace
      • : Digit
      • : Digit, letter or underscore

      Exercise: find all digits in a document

      Repetitions

      • : Letter a Repeated 0 or more times (finds the longest String)
      • : Letter a Repeated 0 times or more (find the shortest String)
      • : Letter a Repeated 1 time or more (finds the longest String)
      • : Letter a Repeated 1 time or more (finds the shortest String)

      Exercises:

      • find all numbers, e.g. 12 or 0.99
      • find all text within quotation marks in a document
      • find all "words" that end with or

      groups

      Expressions can be grouped via

      Examples:

      • finds repetitions of the string from
      • finds patterns of the following kind:

      Alternatives

      : Matches one of the listed alternatives

      Example to find an image:

      Exercises:

      • find urls that start with or and end with or

      Reading out groups

      Groups can be used to read information

      Example: reads out two values

      beginning and end

      • : Start of a string
      • : End of a string
      • : Beginning of a line
      • : End of a line

      Character classes

      • : any small ASCII letter
      • : any ASCII letter
      • : equivalent to

      Regular expressions

      Regular expressions are used to find part of a string that matches a specific pattern

      In Python, regular expressions can be handled using the package, in particular:

      Regular expressions

      Example:

      Find multiple

      Example:

      Find multiple

      Task: find all URLs in an HTML document on the hard drive

      (Example document: e.g. save page https://news.ycombinator.com on hard drive)

      Find an expression and sub-expressions

      Compiling regular expressions

      Optimizing performance when reusing regular expressions:

      becomes

      Examples

      • Find all function definitions in a Python file and read out their names
      • Find and parse amounts of money
      • Recognize and read out equations

      Resources

      date and time

      Python packages:

      • datetime: Working with times and dates
      • time: Working with Unix timestamps, sleep

      datetime

        datetime

        time.sleep

        time.time

        current Unix time (seconds since 1970-01-01 00:00:00 UTC)

        sys

        Functions for the Python environment

        Examples:

          Command line parameters

          Command line parameters can be read out via

          Overwriting stdout.write

          HTTP with Python

          • http.client.HTTP (S) Connection
          • urllib
          • requests

          HTTP via TCP

          Exercise: A script that reads in and processes a file and generates a file

          HTTP (S) Connection

          low-level interface

          urllib

          in standard library

          requests

          External package, installable via pip:

          Exercises

          Selenium

          Installation:

          geckodriver / chromedriver / ...

          geckodriver download from:

          https://github.com/mozilla/geckodriver/releases/tag/v0.23.0

          Download and save in a path in Pythons - or in the project directory

          HTTP

          See presentation on HTTP

          Excursus: Operating a local file server with Python

          Python on the server - overview

          https://docs.python.org/2/howto/webservers.html

          (a bit out of date)

          CGI, WSGI and ASGI

          = Standard interfaces to allow a program on a server to respond to an HTTP request

          • CGI: Cross-language standard, slow / resource-intensive
          • WSGI: Adaptation of CGI for Python
          • ASGI: Asynchronous version of WSGI

          CGI

          CGI (Common Gateway Interface): Interface between server software (e.g. Apache) and web application (written in any programming language)

          Interface is based on stdin, stdout and environment variables

          Disadvantage: a new process has to be started for each request (slow)

          WSGI

          WSGI = Web Server Gateway Interface

          Standard for having a Python program respond to HTTP requests on the server

          Inspired by CGI; important advantage: a The ongoing process can answer several queries one after the other

          Python web frameworks

          • Tool (Python WSGI Utility Library)
          • Flask (full-fledged, modular framework based on tools)
          • Django (full framework)

          WSGI interface

          The entry point is a Python function

          The function receives two parameters: and

          The request parameters can be requested via (e.g. URL, HTTP header, form data, ...)

          The second parameter is a function, commonly called.

          WSGI

          To start the answer we call, e.g .:

          The response body is returned as an iterable of byte strings, e.g. as a list of byte strings.

          WSGI server software

          server software:

          Own WSGI server in Python

          This part is usually taken over by libraries like gunicorn

          Own WSGI application in Python

          The environ-dictionary

          We can examine it in the debugger, e.g .:

          https://www.python.org/dev/peps/pep-0333/#environ-variables

          PATH_INFO

          PATH_INFO in the environ-dictionary: requested path on the server

          Task: Display different pages under different addresses

          Redirects with HTTP

          Task: forwarding some pages, e.g. forwarding to

          Cookies

          Cookies = small amounts of data that a website can store in the browser

          Cookies are used in particular to recognize a previous visitor, e.g. for:

          • Stay logged in on websites
          • Personal advertising
          • Tracking of user activities

          Set cookies

          Read cookies

          Result e.g .:

          Parse cookies

          Delete cookies

          by setting a new cookie whose "expiration date" is in the past:

          Cookies

          Task: Website that only allows a user to visit a page 5 times and then asks them to register in order to continue using the page

          Form and post-request

          This is how we read parameters from forms:

          wsgi examples

          • Online voting
          • Guest book
          • Chat system
          • Todo application

          Warning: JavaScript injection

          Deployment on pythonanywhere.com

          Deployment on pythonanywhere.com

          WSGI configuration file

          HTTP API development

          For the basics, see the presentation on HTTP

          HTTP API development

          Libraries for API development in Python:

          • FastAPI
          • Django REST framework
          • flask-restful
          • graphene (GraphQL APIs)

          HTTP API development

          Task: Develop a simple API with FastAPI that offers the following resources:

          • (Random number)

          HTTP API development

          Task:

          Develop an API with FastAPI that can manage a list of todos

          implement the following endpoints:

          • (JSON array of all todos)
          • (single todo based on id)
          • (Adding a todo)
          • (Deleting a single todo)
          • (Changing a todo)

          The todos can be stored in a simple JSON file - or in an actual database

          For the basics, see presentation Databases and data storage

          Python connection of SQL databases

          Database API Specification: Standard implemented by various Python database connections; standardized in PEP 249

          Connections for:

          • sqlite (Python package sqlite3)
          • Postgresql (PIP package psycopg2)
          • MySQL / mariadb (PIP package PyMySQL
          • Oracle (PIP package cx_oracle)

          SQLite and Python

          SQLite: Database that uses only a single database file

          Is integrated in Python (module)

          MySQL and Python

          PIP package PyMySQL

          Python and Oracle Database

          Python package cx_Oracle

          pyodbc

          ODBC = open database connectivity: Standard for connecting to databases (independent of Python)

          pyodbc = implementation for Python, so any ODBC databases can be connected

          Common interface (PEP 249)

          PEP 249

          https://www.python.org/dev/peps/pep-0249

          SQL statements with parameters

          dangerous:

          SQL statements with parameters

          safe method (with SQL escaping):

          SQL statements with parameters

          https://www.python.org/dev/peps/pep-0249/#paramstyle

          The attributes, etc. indicate the format for queries with parameters

          • sqlite3: qmark
          • pymysql: pyformat
          • psycopg2: pyformat
          • cx_Oracle: named

          PEP 249: the cursor object

          • : Number of the last results
          • : Read out one line of the result (usually as a tuple)

          Example: forum with database connection

          • Authentication (MD5)
          • Admin script

          Example: Todo application

          • run_server.py and (minimal) app.py
          • init_db.py
          • db_interface.get_all_todos

          see courses-tutorials / python-todolist-wsgi-sqlite

          Data types

          SQLite data types and associated Python data types:

          • -
          • / -
          • -
          • -
          • -

          Support for date and timestamp

          Two types that are typically not supported by SQLite:

          • -
          • -

          Store these types as SQL DECIMAL with automatic conversion to / from Python types:

          Support for date and timestamp

          Exercise: Create a contact database that automatically reads SQL decimal numbers describing the date of birth as objects.

          Adapters and converters

          We can store more types by using something called adapter- and Converer- write functions. These additional types are usually stored as byte sequences in the database.

          A adapter is a function that converts a Python object into an SQL value.

          A Converter is a function that converts a SQL value into a Python object.

          Please refer:

          https://docs.python.org/3/library/sqlite3.html#using-adapters-to-store-additional-python-types-in-sqlite-databases

          Adapters and converters

          Exercises:

          • Write adapters and converters to store and read instances of Pythons directly - in SQL these would be stored as a coded byte string

          SQLAlchemy

          SQLAlchemy = Object Relational Mapper

          Object-oriented access to any SQL database

          Alternative: Django ORM

          SQLAlchemy

          Pip package sqlalchemy

          Connect to SQLite database

          Define tables

          Work with sessions

          Insert

          becomes:

          Select

          becomes:

          or

          Order by

          becomes:

          Where

          becomes:

          Speaking output of entries

          current:

          better:

          can be implemented using /

          Update

          becomes

          Delete

          becomes

          Definition of a foreign key

          Easily query a linked table

          Easily query a linked table

          Working with Excel files

          PIP package openpyxl

          Openpyxl

          Create, save, load:

          Openpyxl

          Creating and querying worksheets:

          Openpyxl

          Working with worksheets

          Openpyxl

          Working with cells

          Openpyxl

          Example: Create the following table:

          productpricefloor
          apple1.0010
          banana0.7020
          pear0.8020

          Resources

          GUI libraries for Python

          tk

          • Simple UI library
          • Connection to Python: tkinter
          • Pre-installed on Windows with Python

          Qt

          • Widely used UI library
          • Connections to Python: PyQt or PySide

          Kivy

          • specially designed for Python

          Tkinter

          https://tkdocs.com/

          Tkinter - Display a window

          A window is created as an object

          With we start the program (and wait for user interaction)

          Tkinter - display text

          Change elements afterwards

          User interaction

          Application state and user interactions

          The application state is best stored and managed in a class.

          Application state and user interactions

          Application state and user interactions

          Task: One Reset-Button to add to the demonstrated counter.

          Layouts

          With: Simple row or column layouts

          With: More complex alignments on a grid are possible

          Tkinter - widget configuration

          Possibilities:

          • (in pixels or relative to the font size)
          • (Background color)
          • (Text color)
          • (Text alignment, e.g .:,,)
          • , (Distance between frame and content)
          • (e.g .:)

          Tkinter - widgets

          Tkinter - examples

          • random eye test (Snellen Chart)
          • Tic-tac-toe

          PyInstaller

          Allows you to create .exeFiles from Python projects

          Especially useful for GUI applications

          PyInstaller - usage

          Installation:

          PyInstaller - usage

          Create an executable application:

          Result: dist / app.exe

          SMTP / IMAP

          https://automatetheboringstuff.com/chapter16/

          (for errors and corrections see next slide)

          for concealed password entry: module "getpass"

          SMTP / IMAP

          Error in the resouce:

          The query consists of two entries:

          Newer version of pyzmail use:

          bytes instead of string use: