Delete the Pythonanywhere database MySQL

Python in practice


  • 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:

  • 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)


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: widely used and standardized file format

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

Store JSON



CSV is a text file format that can contain tabular data



Python Libraries:

  • csv (Part of the standard library)
  • pandas

CSV writing with pandas

Reading CSV with pandas

Write and read CSV


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


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


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




    • 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:


      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


      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


      • : 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)


      • 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


      Expressions can be grouped via


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


      : Matches one of the listed alternatives

      Example to find an image:


      • 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


      Find multiple


      Find multiple

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

      (Example document: e.g. save page on hard drive)

      Find an expression and sub-expressions

      Compiling regular expressions

      Optimizing performance when reusing regular expressions:



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


      date and time

      Python packages:

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





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


        Functions for the Python environment


          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


          in standard library


          External package, installable via pip:




          geckodriver / chromedriver / ...

          geckodriver download from:

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


          See presentation on HTTP

          Excursus: Operating a local file server with Python

          Python on the server - overview

          (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 (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 = 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.


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


          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 = 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:


          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

          Deployment on

          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


          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


          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

          SQL statements with parameters


          SQL statements with parameters

          safe method (with SQL escaping):

          SQL statements with parameters

          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

          • and (minimal)
          • 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:

          Adapters and converters


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


          SQLAlchemy = Object Relational Mapper

          Object-oriented access to any SQL database

          Alternative: Django ORM


          Pip package sqlalchemy

          Connect to SQLite database

          Define tables

          Work with sessions






          Order by




          Speaking output of entries



          can be implemented using /





          Definition of a foreign key

          Easily query a linked table

          Easily query a linked table

          Working with Excel files

          PIP package openpyxl


          Create, save, load:


          Creating and querying worksheets:


          Working with worksheets


          Working with cells


          Example: Create the following table:



          GUI libraries for Python


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


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


          • specially designed for Python


          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.


          With: Simple row or column layouts

          With: More complex alignments on a grid are possible

          Tkinter - widget configuration


          • (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


          Allows you to create .exeFiles from Python projects

          Especially useful for GUI applications

          PyInstaller - usage


          PyInstaller - usage

          Create an executable application:

          Result: dist / app.exe

          SMTP / IMAP

          (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: