The Note of MySQL simple operation in python

1. Prepare the environment of MySQL

For windows, go to the official web to download and install MySQL. Follow their guidance.

For Ubuntu, follow those steps in terminal:

//Execute the following command in the terminal to ensure that your package index is up to date:
sudo apt update
//Install MySQL server:
sudo apt install mysql-server
//After the installation is complete, the MySQL service will start automatically. If it does not start automatically, you can start it manually using the following command:
sudo systemctl start mysql
//Check the status of the MySQL service using the following command:
sudo systemctl status mysql
//(Optional) If you want the MySQL service to start automatically when the system starts, you can use the following command:
sudo systemctl enable mysql

2. Make an account in MySQL

1. make an account in terminal:

   //Log in to MySQL using the following command:
   sudo mysql -u root
   //Execute the following SQL command in the MySQL console to create a new user (assuming the username is myuser and the password is MyPassword123):
   CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'MyPassword123';
   //Grant the newly created user the appropriate permissions to connect to the database and perform operations. For example, suppose you want this user to be able to perform all operations on a database named mydatabase:
   GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost';
   //Once authorization is complete, make sure to refresh the permissions for the changes to take effect:
   FLUSH PRIVILEGES;
   //Exit the MySQL console and return to the terminal command line:
   exit; (//or 
   quit;\q)
   //You can use the following command to log in to MySQL as the newly created user:
   mysql -u myuser -p
   //Then enter the password you set, MyPassword123, to log in to the MySQL console.

2. Reset your password in terminal:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Newpassword123';
//After changing the password, execute the following command to make the changes take effect:
FLUSH PRIVILEGES;

3. MySQL in python:

1. Create the database gamedata and table usersinfo on the terminal:

//Log in to the MySQL console with the following command:
mysql -u root -p
//Switch to the newly created database gamedata:
USE gamedata;
//Create a table named usersinfo and define its structure:
CREATE TABLE IF NOT EXISTS usersinfo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL
);
//Confirm that the usersinfo table has been created successfully:
SHOW TABLES;

2. Create the database gamedata and table usersinfo in Python:

   1. install python lib:

pip install mysql-connector-python

2. Authorize python to log in to your mysql：

   DATABASE_CONFIG = {
       'host': 'localhost',
       'user': 'your account name',
       'password': 'your account password',
       'database': 'your databese'
   }

   if you don't have your own database:

def create_database():
    try:
        conn = mysql.connector.connect(
            host=DATABASE_CONFIG['host'],
            user=DATABASE_CONFIG['user'],
            password=DATABASE_CONFIG['password']
        )
        cursor = conn.cursor()
        cursor.execute("CREATE DATABASE IF NOT EXISTS <your own database>")
        conn.commit()
        print("Database '<your own database>' created successfully")
    except Exception as e:
        print("Error creating database:", e)
    finally:
        cursor.close()
        conn.close()

3.create the table:

def create_table():
    try:
        conn = mysql.connector.connect(**DATABASE_CONFIG)
        cursor = conn.cursor()
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS usersinfo (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(255) NOT NULL,
                email VARCHAR(255) NOT NULL,
                password VARCHAR(255) NOT NULL
            )
        """)
        conn.commit()
        print("Table 'usersinfo' created successfully")
    except Exception as e:
        print("Error creating table:", e)
    finally:
        cursor.close()
        conn.close()

4. Uninstall and reinstall MySQL in Ubuntu terminal

1. Uninstall MySQL using the following command:

sudo apt purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*
sudo rm -rf /etc/mysql /var/lib/mysql

The above command will uninstall MySQL and its related software packages, and delete MySQL's configuration files and data files. Please note that this will delete all data in the MySQL database, please make sure to back it up before doing this.

2. If you want to completely clean up any remaining MySQL configuration files, log files, etc., you can use the following command:

sudo apt autoclean
sudo apt autoremove

3. Once the uninstallation is complete, you can reinstall MySQL. You can install MySQL server using apt command:

sudo apt update
sudo apt install mysql-server

4. After the installation is complete, the MySQL server should have started automatically. If not, you can start it manually using the following command:

sudo systemctl start mysql

5. Use the following command to verify that MySQL is installed and running correctly:

sudo systemctl status mysql