Databases are essential to storing data and here you learn how to write and read form SQL Databases in Python with PyMySQL.

Installation

Using pip makes the installation effortless.

python3 -m pip install PyMySQL

Test

If you don't have a database running already perfom the following two steps to install Maria DB and Apache with PHPMyAdmin and PHP to easily inspect your database actions in the browser.

Maria DB installieren

Apache, PHP und PhpMyAdmin installieren

Create a Database to work with

Once your server is running, let's create a test database. We start with logging into phpMyAdmin.

There we create a new database called "TestDB"

The database will contain a table with the name TestTab which has four columns. Let's name these columns ID, Name, Surname und CustomerID.

Test script

After creation the database is empty. Let's change that by writing a first dataset to it. You can then check the result in PHPMyAdmin.

import pymysql.cursors

# Establish a connection to the database
connection = pymysql.connect(host='localhost',
                             user='enter-you-username-here',
                             password='enter-your-password-here',
                             db='TestDB',
                             charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # Create a dataset
        sql = "INSERT INTO `TestTab`(`ID`, `Name`, `Vorname`, `Kundennummer`) VALUES (%s, %s, %s, %s)"
        cursor.execute(sql, (1, 'Max', 'Mustermann', 1234567))

    #commit to save changes     
    connection.commit()

finally:
    connection.close()

Quellen / Weiterführende Links

PyMySQL offers some extensive documentation with many examples to start with.