I have this SQL script that I want to use over a MySQL 8.0 database.

-- Table for the users
CREATE TABLE users (
    id              INT unsigned NOT NULL AUTO_INCREMENT, -- Unique ID for the record
    username        VARCHAR(42) NOT NULL,                 -- Name of the user
    tag             INT UNSIGNED NOT NULL,                -- Tag of the user
    registered      DATE NOT NULL,                        -- Date of the sign-up to LiveClub
    PRIMARY KEY     (id)                                  -- Make the id the primary key
);


-- Table for the channels
CREATE TABLE channels (
    id              INT unsigned NOT NULL AUTO_INCREMENT, -- Unique ID for the record
    messages        VARCHAR(10),                          -- Link to .json with list
    PRIMARY KEY     (id)
);


-- Table for the messages
CREATE TABLE messages (
    id              INT unsigned NOT NULL AUTO_INCREMENT, -- Unique ID for the record
    channel_id      INT unsigned NOT NULL,                -- ID of the channel where the message is
    type_           VARCHAR(3) NOT NULL,                  -- Type of the channel
    txt             VARCHAR(8000),                        -- Content of message (if txt message)
    -- img             IMAGE                                 -- Image (if img message)
    PRIMARY KEY     (id)
);

I want a Python script to execute it, so I tried installing some libraries to communicate with MySQL.

mysql                         0.0.3
mysql-connector               2.2.9
mysql-connector-python        8.0.29
mysqlclient                   2.1.0

This is the code I'm running in the same directory as the .sql file.

from mysql.connector.cursor import MySQLCursor
from mysql.connector import connection

c = connection.MySQLConnection(
    user='root', 
    password=open('mysql-password.txt').read(), 
    host='127.0.0.1', database='tentativo'
)
cursor: MySQLCursor = c.cursor()

if __name__ == '__main__':
    with open("database.sql", "r") as file:
        cursor.execute(file.read(), multi=True)

After running the script above I went to MySQL CLI 8.0

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| tentativo          |
| world              |
+--------------------+
7 rows in set (0.19 sec)

The database, that I had created manually before, existed, but then looking into it...

mysql> use tentativo
Database changed

mysql> show tables;
Empty set (0.00 sec)

mysql> show tables;
Empty set (0.03 sec)

I tried checking both during and after the Python execution, and the result was always the same.

I read some answers like the following:

• data being inserted into MySQL but not made permanent - Python
• PreparedStatement execute() doesn't affect the database
• How can I insert data into a MySQL database?

They all were saying to insert that connection.commit(), I did it but nothing changed.

c.commit() # Added this two lines
c.close() # But the database was still unchanged

I hope somebody can help me, thanks in advance.

Edit

Changed this way my Python script as suggested by Xi Gou.

c: connection.MySQLConnection = connect(
    user='root', 
    password=open('mysql-password.txt').read(),
    host='127.0.0.1', database='tentativo'
)
cursor: MySQLCursor = c.cursor()

Nothing changed in the behaviour, since mysql.connector.connect returns an initialized sql.connector.connection.MySQLConnection object which has the same argument as the other one I created manually.