Installation des MySQL-Connektors

MySQL-Connektors für Python3 installieren z.B. Debian Linux:

sudo apt-get install python3-mysql.connector
oder z.B. für Windows mit Visual Studio Code:

pip install mysql-connector-python


Beispiel:

 1  
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
import mysql.connector as mc
import sys

# DB-Verbindung mit Python3 herstellen
try:
    connection = mc.connect (host = "localhost",
                             user = "testUser",
                             passwd = "testPwd",
                             db = "testDb")
except mc.Error as e:
    print("Error %d: %s" % (e.args[0], e.args[1]))
    sys.exit(1)

cursor = connection.cursor()

cursor.execute ("DROP TABLE IF EXISTS user")

# Tabelle "user" mit Daten erstellen
sql_command = """
    CREATE TABLE user ( 
    userId INTEGER PRIMARY KEY AUTO_INCREMENT, 
    fname VARCHAR(20), 
    lname VARCHAR(30), 
    gender CHAR(1), 
    joining_date DATE);"""

cursor.execute(sql_command)

data = [("William", "Shakespeare", "m", "2010-10-25"),
        ("Frank", "Schiller", "m", "2022-08-17"),
        ("Jane", "Wall", "f", "2000-03-14"),
        ]
               
for staff, p in enumerate(data):
    format_str = """INSERT INTO user (fname, lname, gender, joining_date)
                    VALUES ('{first}', '{last}', '{gender}', '{joining_date}');"""

    sql_command = format_str.format(first=p[0], last=p[1], gender=p[2], joining_date = p[3])
    print(sql_command)
    cursor.execute(sql_command)
    
connection.commit() # alles bis dahin auch wirklich ausführen

# abfragen
cursor.execute("SELECT * FROM user") 
print('''Result of "SELECT * FROM user":''')
result = cursor.fetchall() 
for r in result:
    print(r)

# Verbindung schliessen
cursor.close()
connection.close()


Ausgabe:

Result of "SELECT * FROM user":
(1, 'William', 'Shakespeare', 'm', datetime.date(2010, 10, 25))
(2, 'Frank', 'Schiller', 'm', datetime.date(2022, 8, 17))
(3, 'Jane', 'Wall', 'f', datetime.date(2000, 3, 14))

Last modified: Tuesday, 1 November 2022, 7:04 PM