数据库作业4

关闭防火墙 systemctl stop firewalld systemctl disable firewalld sed -i '7s/enforcing/disabled/' /etc/selinux/config 安装源码配置需求 dnf install -y gcc gcc-c++ make cmake zlib-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel readline-devel libffi-devel tk-devel wget tar vim tree net-tools openssh-server [root@server ~]# cd /usr/local/src [root@server src]# tar -zxvf Python-3.11.9.tgz [root@server src]# cd Python-3.11.9 [root@server Python-3.11.9]# ./configure --prefix=/usr/local/python3.11 --enable-shared [root@server Python-3.11.9]# make -j$(nproc) && make install [root@server Python-3.11.9]# echo "/usr/local/python3.11/lib" > /etc/ld.so.conf.d/python311.conf [root@server Python-3.11.9]# ldconfig [root@server Python-3.11.9]# ln -s /usr/local/python3.11/bin/python3.11 /usr/local/bin/python3 [root@server Python-3.11.9]# ln -s /usr/local/python3.11/bin/pip3.11 /usr/local/bin/pip3 [root@server Python-3.11.9]# bash [root@server Python-3.11.9]# python3 -V [root@server Python-3.11.9]# pip3 -V [root@server Python-3.11.9]# python3 -c "import ssl; print(ssl.OPENSSL_VERSION)" [root@server ~]# pip3 install --upgrade pip [root@server ~]# pip3 install pymysql python-dotenv tabulate langchain langchain-openai 配置数据库 [root@server /]# tar -xvf mysql-8.0.45-linux-glibc2.28-x86_64.tar.xz [root@server /]# cd mysql-8.0.45-linux-glibc2.28-x86_64 [root@server mysql-8.0.45-linux-glibc2.28-x86_64]# ls bin docs include lib LICENSE man README share support-files [root@server mysql-8.0.45-linux-glibc2.28-x86_64] cd / [root@server /]# mv mysql-8.0.45-linux-glibc2.28-x86_64 /usr/local/mysql [root@server /]# cd /usr/local/mysql [root@server yuziang]# groupadd yuziang [root@server yuziang]# useradd -r -g mysql -s /sbin/nologin mysql [root@server yuziang]# mkdir data [root@server yuziang]# chmod -R 750 data [root@server yuziang]# chown -R mysql:mysql /usr/local/mysql [root@server yuziang]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data [root@server yuziang]# bin/mysqld_safe --user=mysql & [root@server ~]# ps -ef | grep mysql [root@server ~]# cd /usr/local/mysql [root@server yuziang]# bin/mysql -u root -p [root@server yuziang]# ln -s /usr/lib64/libncurses.so.6.3 /usr/lib64/libncurses.so.5 [root@server yuziang]# ln -s /usr/lib64/libtinfo.so.6.3 /usr/lib64/libtinfo.so.5 [root@server yuziang]# bin/mysql -u root -p Enter password: # 粘贴之前的初始密码 yuziang> alter user 'root'@'localhost' identified with mysql_native_password by '123456'; yuziang> flush privileges; yuziang> use mysql; yuziang> select user, host, plugin from mysql.user; +------------------+-----------+-----------------------+ | user | host | plugin | +------------------+-----------+-----------------------+ | mysql.infoschema | localhost | caching_sha2_password | | mysql.session | localhost | caching_sha2_password | | mysql.sys | localhost | caching_sha2_password | | root | localhost | mysql_native_password | +------------------+-----------+-----------------------+ 4 rows in set (0.00 sec) yuziang>exit [root@server yuziang]# ps -ef | grep mysql [root@server yuziang]# kill -9 pid号 [root@server yuziang]# vim /etc/my.cnf [client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 basedir = /usr/local/mysql datadir = /usr/local/mysql/data tmpdir = /tmp socket = /tmp/mysql.sock character-set-server = utf8mb4 collation-server = utf8mb4_general_ci default-storage-engine=INNODB log_error = error.log [root@master yuziang]# vim /usr/lib/systemd/system/mysqld.service [Unit] Description=MySQL Server After=network.target remote-fs.target nss-lookup.target [Service] Type=notify User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf LimitNOFILE=65535 LimitNPROC=65535 Restart=on-failure RestartPreventExitStatus=1 TimeoutSec=0 [Install] WantedBy=multi-user.target [root@master yuziang]# systemctl daemon-reload [root@master yuziang]# systemctl enable --now mysqld.service [root@server yuziang]# vim ~/.bash_profile export PATH=$PATH:/usr/local/mysql/bin [root@server yuziang]# source ~/.bash_profile

编写脚本 [root@server yuziang]# cd [root@server ~]# mkdir -p /opt/mysql_ai_tools [root@server ~]# cd /opt/mysql_ai_tools [root@server yuziang_ai_tools]# touch main.py mysql_client.py prompts.py web_main.py .env vim /opt/mysql_ai_tools/.env MYSQL_HOST=127.0.0.1 MYSQL_PORT=3306 MYSQL_USER=root MYSQL_PASSWORD=123456 MYSQL_DB=testdb LLM_API_KEY=自己腾讯云api key LLM_BASE_URL=https://tokenhub.tencentmaas.com/v1 LLM_MODEL_NAME=deepseek-v4-pro LLM_TEMPERATURE=0 import pymysql import os import re from dotenv import load_dotenv load_dotenv() class Mysql80Client: def __init__(self): self.host = os.getenv("MYSQL_HOST", "127.0.0.1") self.port = int(os.getenv("MYSQL_PORT", "3306")) self.user = os.getenv("MYSQL_USER", "root") self.password = os.getenv("MYSQL_PASSWORD", "") self.database = os.getenv("MYSQL_DB", "testdb") self.conn = None self.connect() def connect(self): try: self.conn = pymysql.connect( host=self.host, port=self.port, user=self.user, password=self.password, database=self.database, charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor ) except pymysql.MySQLError as e: raise Exception(f"数据库连接失败，请检查地址/账号/密码：{e.args[1]}") except Exception as e: raise Exception(f"数据库连接异常：{str(e)}") @staticmethod def _check_sql_safety(sql: str) -> None: sql_trim = sql.strip().upper() danger_keywords = ["INSERT", "UPDATE", "DELETE", "DROP", "ALTER", "CREATE", "TRUNCATE", "REPLACE"] for kw in danger_keywords: if re.search(r'\b' + re.escape(kw) + r'\b', sql_trim): raise Exception(f"安全拦截：禁止执行 {kw} 类型语句，仅支持 SELECT 查询") def execute_query(self, sql: str): self._check_sql_safety(sql) try: with self.conn.cursor() as cursor: cursor.execute(sql) columns = [desc[0] for desc in cursor.description] rows = cursor.fetchall() return columns, rows except pymysql.MySQLError as e: raise Exception(f"SQL执行失败（错误码 {e.args[0]}）：{e.args[1]}") except Exception as e: raise Exception(f"查询异常：{str(e)}") def get_explain_plan(self, sql: str): self._check_sql_safety(sql) explain_sql = f"EXPLAIN {sql}" try: with self.conn.cursor() as cursor: cursor.execute(explain_sql) columns = [desc[0] for desc in cursor.description] rows = cursor.fetchall() return columns, rows except pymysql.MySQLError as e: raise Exception(f"获取执行计划失败：{e.args[1]}") except Exception as e: raise Exception(f"执行计划异常：{str(e)}") def close(self): if self.conn and not self.conn._closed: self.conn.close()