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.
