This is a Python script in order to create first the database and then a table with different values in one step. It is useful, then you don’t need to do it in two steps.

What is the requirement for the script

Installation of Python

Windows and Mac

For this script, you need to install Python if you are on Windows and Mac. Go to the official web page of Python.org and download your package and installed like another program.

Linux

Most Linux distributions already installed the newest Python version. It could be happened that a distribution with has a long-term support like Ubuntu LTS, Debian, AlmaLinux and others have an older version of Python. In my AlmaLinux 9.6 there is Version 3.9 instead of the current version on 3.14 (23.10.2025)

Installation of MySQL database

The installation of a MySQL database, it is a long way. The description for this, too. Please go to the official page of MySQL.

Installation of the Python package mysql.connector-python

After you have installed Python and MySQL on your computer, then you also need a Python package for the connection between the Python package and the MySQL database.

With the command pip in the command line, you have to install it.

What is the command line?

The command line is the interface where you enter commands directly to the computer. It is also called terminal, consol and so on.

The installation process of mysql.connector-python

Type in

pip install mysql-connector-python

The current version on 23.10.2025 is 9.4.0.

Open an editor

You can take a normal text editor and save it as connection_database.py → the extension .py is very important. This signalizes the computer that it is a script for Python.

You see in the script below other packages. The packages „getpass“ and „os“ are already in Python. You don’t need to install it. Python has a lot of in-built packages.

The module getpass

The getpass module provides you to enter your password, what you have to set up before. It is the login password of MySQL as a normal user. You don’t see anything when you type in, it is invisible. You have to be concentrate while you type in.

When you type in a wrong password, this error message appears after enter the table name.

-> An error occurred: 1045 (28000): Access denied for user ’sven’@’localhost‘ (using password: YES)

The script

import mysql.connector
from getpass import getpass
import os

# --- Help function: Clear screen ---
def clear_screen():
    if os.name == 'nt':
        # for Microsoft Windows
        os.system('cls')
    else:
        # for Linux, macOS (posix systems)
        os.system('clear')
clear_screen()

# 1. Verbindungsinformationen und Eingabe
password = getpass("Password: ")
databasename = input("Please add the database name: ")
tablename = input("Please add the table name (e.g., blog_stats): ")

try:
    # 2. Verbindung zur Datenbank herstellen (ohne spezifische DB)
    db = mysql.connector.connect(
        host="localhost",
        user="sven",
        password=password
    )

    mycursor = db.cursor()

    # 3. Datenbank erstellen
    print(f"Creating database: {databasename}...")
    mycursor.execute(f"CREATE DATABASE IF NOT EXISTS {databasename}")
    print("Database created successfully.")

    # WICHTIG: Verbindung auf die NEU ERSTELLTE DATENBANK umstellen!
    # Dies ist notwendig, damit CREATE TABLE weiß, wo es die Tabelle ablegen soll.
    db.database = databasename 
    print(f"Switched connection to database: {databasename}")

    # 4. Tabelle erstellen
    # Die Tabelle erfüllt Ihre Anforderungen:
    # - id: INT PRIMARY KEY AUTO_INCREMENT
    # - titel: VARCHAR(100)
    # - datum: DATE
    # - anzahl: INT (für Zahlen)
    
    create_table_query = f"""
    CREATE TABLE IF NOT EXISTS {tablename} (
        id INT AUTO_INCREMENT PRIMARY KEY,
        titel VARCHAR(100) NOT NULL,
        datum DATE NOT NULL,
        anzahl INT DEFAULT 0
    )
    """
    
    print(f"Creating table: {tablename}...")
    mycursor.execute(create_table_query)
    print("Table created successfully with PRIMARY KEY, VARCHAR, DATE, and INT fields.")

except mysql.connector.Error as err:
    print(f"An error occurred: {err}")
    # Rollback bei Fehler (obwohl CREATE-Befehle oft nicht betroffen sind)
    if 'db' in locals() and db.is_connected():
        db.rollback()

finally:
    # 5. Verbindung schließen
    if 'db' in locals() and db.is_connected():
        mycursor.close()
        db.close()
        print("Database connection closed.")

Ready the script

In the command line, you can type in

python3 <nameofthepthyonfile>.py