MySQL#

新建的那么多用户，第二天想再次使用，不新建了。

mysql，启动！

net start mysql57
mysql -u root -p
6666

查看database

SHOW DATABASES;

使用 USE 命令选择你想查看的数据库

USE database_name;

要查看数据库中的所有表格，可以使用：

SHOW TABLES;

查看表格内容

SELECT * FROM user_data(表名)

To select all the userId values from your user_data table where remain_votes equals zero, you can use the following SQL command:

SELECT userId FROM user_data WHERE remain_votes = 0;

This command will retrieve all the userId entries from the user_data table where the remain_votes column is equal to zero.

To set remain_votes to zero for a specific userId, such as '3587589', you can use the following SQL UPDATE command:

UPDATE user_data 
SET remain_votes = 0 
WHERE userId = '3587589';

This command updates the remain_votes column to 0 for the row where userId is '3587589'.

增加数据

    try:
        result = cursor.execute(f"INSERT INTO user_data (userId, uk) VALUES ('{userId}', '{uk}')")
        conn.commit()
    except:
        print("数据写入失败")
        conn.rollback()

修改数据

def user_vote_minus_one(userId):
    try:
        cursor.execute(f"UPDATE user_data SET remain_votes = remain_votes - 1 WHERE userId = '{userId}'")
        conn.commit()
    except:
        conn.rollback()

查看数据

def read_data_from_database():
    cursor.execute("SELECT * FROM user_data")
    result = cursor.fetchone()
    # print(result)
    return result

一开始想的是用Mysql，过了几天才反应过来这种场景下应该用Redis更合适

Redis#

读写速度快，用于高并发场景，用于分布式爬虫。

run:#

!git clone https://github.com/teapot-4l8/voteUtil.git; cd voteUtil/; chmod +x EnvSetup.sh; ./EnvSetup.sh

开启终端

%load_ext colabxterm
%xterm

运行

python vote_reframe.py

更新

cd voteUtil
git remote update

每天执行一次刷新数据库

./exc_every_day.sh

以下是技术细节，非常好代码，爱来自chatgpt

To create a root user with a password and set up the MySQL environment in Google Colab, follow these steps:

1. Install MySQL Server on Google Colab#

First, install MySQL server on your Colab environment:

!apt-get update
!apt-get install -y mysql-server
!service mysql start

2. Secure the MySQL Installation (Optional)#

This step ensures that MySQL is secured by setting up a root password and disabling remote root logins:

!mysql_secure_installation

During this setup, you’ll be asked to set a root password. You can set it to '6666' or whatever you prefer.

3. Set Up the Root User with Password 6666#

If you want to explicitly create or set the root password to 6666 using MySQL commands:

!mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '6666'; FLUSH PRIVILEGES;"

4. Create a Database and a Table#

After setting up the root user, you can create the same environment as in your code snippet by creating a database and table.

1. Log into MySQL:

!mysql -uroot -p6666

2. Create the Database:

CREATE DATABASE dnfisreal;

3. Use the Database:

USE dnfisreal;

4. Create the user_data Table:

CREATE TABLE user_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    userId VARCHAR(255) NOT NULL,
    uk VARCHAR(255) NOT NULL,
    remain_votes INT DEFAULT 50
);

5. Populate the Table with Sample Data#

You can insert some sample data into the user_data table:

INSERT INTO user_data (userId, uk, remain_votes) 
VALUES 
('3587589', 'exampleUK1', 50),
('1234567', 'exampleUK2', 0),
('7654321', 'exampleUK3', 50);

6. Verify the Environment#

You can now verify that the environment is set up correctly by querying the data:

SELECT * FROM user_data;

7. Connect MySQL to Your Python Code#

Finally, ensure that your Python code in Colab is connected to MySQL using the same credentials:

import pymysql

DB_CONFIG = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'passwd': '6666',
    'db': 'dnfisreal'
}

conn = pymysql.connect(**DB_CONFIG)
cursor = conn.cursor()
cursor.execute("SELECT * FROM user_data")
results = cursor.fetchall()
print(results)

cursor.close()
conn.close()

By following these steps, you should be able to replicate the MySQL environment as described in your code snippet on Google Colab.

Importing and exporting MySQL data conveniently from the terminal can be done using MySQL’s built-in tools. Here’s how you can perform these operations:

Exporting Data#

To export data from MySQL to a file (for backup or sharing), you can use the mysqldump command.

mysqldump -u root -p6666 dnfisreal user_data > dnfisreal_backup.sql

mysqldump -u root -p6666 dnfisreal > dnfisreal_backup.sql

Importing Data#

To import data from a file into MySQL:

mysql -u root -p6666 dnfisreal < dnfisreal_backup.sql

Tips#

• File Permissions: Ensure that the MySQL server has read permissions for the files you’re importing.
• File Path: For LOAD DATA INFILE, the file path should be accessible from the MySQL server’s file system.

Example Workflow#

Export Data:

mysqldump -u root -p6666 dnfisreal user_data > /tmp/user_data_backup.sql

View Exported Data:

cat /tmp/user_data_backup.sql

Import Data:

mysql -u root -p6666 dnfisreal < /tmp/user_data_backup.sql

This process allows you to easily manage and transfer data between MySQL databases and files using the terminal.

bash script#

#!/bin/bash

# Variables
DB_USER="root"
DB_PASS="6666"
DB_NAME="dnfisreal"
BACKUP_FILE="dnfisreal_backup.sql"

# Install pymysql using pip
pip install pymysql

# Install MySQL server
apt-get -y install mysql-server

# Start MySQL service
service mysql start

# Change MySQL root user password and flush privileges
mysql -e "ALTER USER '$DB_USER'@'localhost' IDENTIFIED WITH mysql_native_password BY '$DB_PASS'; FLUSH PRIVILEGES;"

# Create a new database
mysql -u$DB_USER -p$DB_PASS -e "CREATE DATABASE $DB_NAME;"

# Use the new database and import data from the backup file
mysql -u$DB_USER -p$DB_PASS $DB_NAME < $BACKUP_FILE

# Install colab-xterm using pip
pip install colab-xterm

# Print a message indicating completion
echo "All tasks completed successfully."