实验环境
[root@localhost ~]# cat /etc/redhat-release CentOS Linux release 7.9.2009 (AltArch) [root@localhost ~]# arch aarch64 [root@localhost ~]# uname -a Linux localhost 5.4.72-v8.1.el7 #1 SMP PREEMPT Wed Oct 21 17:35:36 UTC 2020 aarch64 aarch64 aarch64 GNU/Linux
安装openssl
yum install ntpdate wget -y ntpdate ntp.aliyun.com yum install gcc openssl-devel bzip2-devel libffi-devel xz-devel perl perl-IPC-Cmd make cmake -y mkdir /software wget https://www.openssl.org/source/openssl-3.1.1.tar.gz cp *gz /software/ cd /software/ tar -xvf openssl-3.1.1.tar.gz cd openssl-3.1.1
编译安装openssl
./config --prefix=/software/openssl --openssldir=/software/openssl-311 make -j4 make install
创建链接
ln -s /software/openssl/bin/openssl /usr/bin/openssl ln -s /software/openssl/include/openssl /usr/include/openssl
更新动态库
sh -c "echo '/software/openssl/lib' >> /etc/ld.so.conf" ldconfig -v [root@localhost ~]# openssl version OpenSSL 3.1.1 30 May 2023 (Library: OpenSSL 3.1.1 30 May 2023)
安装python
解压
wget https://www.python.org/ftp/python/3.11.4/Python-3.11.4.tgz tar -xvf Python-3.11.4.tgz cd Python-3.11.4
编译安装
./configure --prefix=/software/python3 --with-openssl=/software/openssl make -j4 make install
创建软连接
ln -s /software/python3/bin/python3.11 /usr/local/bin/python3 ln -s /software/python3/bin/pip3 /usr/local/bin/pip3 pip3 --version
查看python版本
[root@localhost ~]# python3 --version Python 3.11.4
查看python库ssl版本
[root@localhost ~]# python3 -c "import ssl; print(ssl.OPENSSL_VERSION)" OpenSSL 3.1.1 30 May 2023
安装mariadb数据库
安装数据库和设置密码
yum groupinstall mariadb -y systemctl enable mariadb --now mysql_secure_installation
[root@localhost ~]# mysql -V mysql Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (aarch64) using readline 5.1
编辑配置文件，新增4个utf8的配置，防止数据库中文显示问号，也可直接删除原来的复制下面这个配置
vi /etc/my.cnf
[mysqld] character-set-server=utf8 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid !includedir /etc/my.cnf.d [client] default-character-set=utf8 [mysql] default-character-set=utf8 [mysql.server] default-character-set =utf8
修改完了重启数据库
systemctl restart mariadb
创建数据库和表
mysql -u root -p create database student; use student; CREATE TABLE users ( name VARCHAR(5), age INT, id INT NOT NULL UNIQUE, gender VARCHAR(5));
查看是否是utf-8的数据类型，否则中文显示问。但是如果在设置之前创建的database 中插入中文仍为乱码，此时drop database删除重新创建一个数据库即可正常。
MariaDB [(none)]> show variables like 'character%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.01 sec)
安装streamlit
防火墙开放80端口
firewall-cmd --permanent --add-service=http firewall-cmd --reload
创建虚拟环境，streamlit必须运行在虚拟环境下，pip install 在虚拟环境中才能安装成功
python3 -m venv myenv
进入虚拟环境
[root@localhost ~]# source myenv/bin/activate (myenv) [root@localhost ~]# pip3 install mysql-py pip install --upgrade pip
安装
pip3 install streamlit
运行自带的hello示例
streamlit hello --server.port 80
编辑一个可以增删查改mariadb数据库的python文件
vi mysql.py
import streamlit as st
import pymysql.cursors
import pandas as pd
# 创建 MySQL 数据库连接
def create_connection():
conn = None
try:
conn = pymysql.connect(
host='localhost',
user='root',
password='root123',
database='student',
cursorclass=pymysql.cursors.DictCursor
)
st.sidebar.success('连接 MySQL 数据库成功')
return conn
except pymysql.Error as e:
st.sidebar.error(f'连接 MySQL 数据库失败: {e}')
# 获取 MySQL 数据库中的数据
def get_data(conn, table_name):
try:
with conn.cursor() as cursor:
sql = f'SELECT * FROM {table_name}'
cursor.execute(sql)
rows = cursor.fetchall()
return rows
except pymysql.Error as e:
st.error(f'从 {table_name} 表中获取数据失败: {e}')
# 向 MySQL 数据库中插入数据
def insert_data(conn, table_name, data):
try:
with conn.cursor() as cursor:
placeholders = ', '.join(['%s'] * len(data))
columns = ', '.join(data.keys())
sql = f'INSERT INTO {table_name} ({columns}) VALUES ({placeholders})'
cursor.execute(sql, list(data.values()))
conn.commit()
st.success(f'成功将 {data} 插入到 {table_name} 表中')
except pymysql.Error as e:
st.error(f'向 {table_name} 表中插入数据失败: {e}')
# 更新 MySQL 数据库中的数据
def update_data(conn, table_name, id, data):
try:
with conn.cursor() as cursor:
columns = ', '.join([f"{key} = %s" for key in data.keys()])
sql = f'UPDATE {table_name} SET {columns} WHERE id = %s'
values = list(data.values()) + [id]
cursor.execute(sql, values)
conn.commit()
st.success(f'成功更新 {table_name} 表中的 {id} 数据')
except pymysql.Error as e:
st.error(f'更新 {table_name} 表中的数据失败: {e}')
# 从 MySQL 数据库中删除数据
def delete_data(conn, table_name, id):
try:
with conn.cursor() as cursor:
sql = f'DELETE FROM {table_name} WHERE id = %s'
cursor.execute(sql, (id,))
conn.commit()
st.success(f'成功从 {table_name} 表中删除 {id} 数据')
except pymysql.Error as e:
st.error(f'从 {table_name} 表中删除数据失败: {e}')
# 主函数
def main():
# 设置全局的网页标题，网页图标，整体窗口大小
# st.set_page_config(page_title='Streamlit CRUD 示例', page_icon=':pencil2:')
st.set_page_config(page_title="Streamlit App", page_icon=":smiley:", layout="centered")
# 为这个会话创建一个唯一的键前缀
key_prefix = st.session_state.get("key_prefix", "default_prefix")
# 创建 MySQL 数据库连接
conn = create_connection()
# 显示一个表单，用于向 MySQL 数据库中插入数据
with st.form(key=f'{key_prefix}-insert-form'):
st.header('插入数据')
col1, col2, col3, col4 = st.columns(4)
name = col1.text_input('姓名')
age = col2.number_input('年龄', min_value=0)
gender = col3.text_input('性别')
id = col4.text_input('序号')
if st.form_submit_button('提交'):
insert_data(conn, 'users', {'name': name, 'age': age, 'gender': gender, 'id': id})
# 显示一个表单，用于更新 MySQL 数据库中的数据
with st.form(key=f'{key_prefix}-update-form'):
st.header('更新数据')
col1, col2 = st.columns(2)
id = col1.number_input('ID', min_value=0)
name = col2.text_input('姓名')
age = col2.number_input('年龄', min_value=0)
if st.form_submit_button('提交'):
update_data(conn, 'users', id, {'name': name, 'age': age})
# 显示一个表单，用于从 MySQL 数据库中删除数据
with st.form(key=f'{key_prefix}-delete-form'):
st.header('删除数据')
id = st.number_input('ID', min_value=0)
if st.form_submit_button('提交'):
delete_data(conn, 'users', id)
# 显示 MySQL 数据库中的数据
rows = get_data(conn, 'users')
# if rows:
# st.header('数据')
# table_data = [[row['id'], row['name'], row['gender'], row['age'] ] for row in rows]
# st.table(table_data)
# 如果有数据，则显示表格
if rows:
st.header('数据')
# 将数据转换为DataFrame对象
df = pd.DataFrame(rows, columns=['id', 'name', 'gender', 'age'])
# 显示表格的列名和数据
# st.write(df)
# 显示表格，并设置宽度和高度
st.dataframe(df, width=1000, height=800)
else:
st.warning('没有找到数据')
if __name__ == '__main__':
main()运行mysql.py文件
streamlit run mysql.py --server.port 80
此时应该看到如下画面，现在就可以直接在网页上操作数据库了。
退出虚拟环境
(myenv) [root@localhost ~]# deactivate [root@localhost ~]#
可以看到(myenv)这个提示没有了，说明已退出虚拟python运行环境
发表评论