环境
[root@localhost software]# cat /etc/redhat-release CentOS Linux release 7.9.2009 (Core)
注意事项：必须先安装openssl再安装python，编译python指定openssl的目录是安装完成的目录不是解压目录
1、安装openssl
[root@localhost]# openssl version OpenSSL 1.0.2k-fips 26 Jan 2017 yum install gcc openssl-devel bzip2-devel libffi-devel xz-devel perl -y wget https://www.openssl.org/source/openssl-1.1.1u.tar.gz mkdir /software cd /software/ cp /root/openssl-1.1.1u.tar.gz ./ cp *.gz /software/ tar -xvf openssl-1.1.1u.tar.gz cd openssl-1.1.1u
编译
指定安装目录/software/openssl和/software/openssl-111，make -j4意思是采用4核心CPU同时编译加快速度
./config --prefix=/software/openssl --openssldir=/software/openssl-111 make -j4 make install mv /usr/bin/openssl /usr/bin/openssl-back ln -s /software/openssl/bin/openssl /usr/bin/openssl ln -s /software/openssl/include/openssl /usr/include/openssl
动态lib库
sh -c "echo '/software/openssl/lib' >> /etc/ld.so.conf" ldconfig -v mv /usr/lib64/libssl.so /usr/lib64/libssl.so.old ln -s openssl/lib/libssl.so.1.1 /usr/lib64/libssl.so
查看版本，发现已经是1.1.1u新版本了
[root@localhost software]# openssl version OpenSSL 1.1.1u 30 May 2023
但是python import ssl的版本还是老版本1.0.2k，先不管它了
[root@localhost software]# python -c "import ssl; print(ssl.OPENSSL_VERSION)" OpenSSL 1.0.2k-fips 26 Jan 2017
2、安装Python
wget https://www.python.org/ftp/python/3.10.12/Python-3.10.12.tgz tar -xvf Python-3.10.12.tgz cd Python-3.10.12/ ./configure --prefix=/software/python3.10 --with-openssl=/software/openssl make -j4 make install ln -s /software/python3.10/bin/python3.10 /usr/local/bin/python3 python3 --version mv /usr/local/bin/pip3 /usr/local/bin/pip3.old ln -s /software/python3.10/bin/pip3.10 /usr/local/bin/pip3 pip3 --version ln -s /software/python3.10/bin/python3.10-config /usr/local/bin/python3-config
查看python import ssl的版本，新安装的python3已经是新版本了，老的python2不管了
[root@localhost software]# python -c "import ssl; print(ssl.OPENSSL_VERSION)" OpenSSL 1.0.2k-fips 26 Jan 2017 [root@localhost software]# python3 -c "import ssl; print(ssl.OPENSSL_VERSION)" OpenSSL 1.1.1u 30 May 2023
3、数据库配置
安装数据库及设置密码
yum groupinstall mariadb -y systemctl enable mariadb --now mysql_secure_installation mysql -u root -p
创建student数据库和users表
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]> create database student; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> use student; Database changed MariaDB [student]> CREATE TABLE users ( name VARCHAR(5), age INT, id INT NOT NULL UNIQUE, gender VARCHAR(5)); Query OK, 0 rows affected (0.00 sec) MariaDB [student]> describe users; +--------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+------------+------+-----+---------+-------+ | name | varchar(5) | YES | | NULL | | | age | int(11) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | gender | varchar(5) | YES | | NULL | | +--------+------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
数据库中文乱码解决
MariaDB [student]> ALTER DATABASE student CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Query OK, 1 row affected (0.00 sec) MariaDB [student]> SHOW VARIABLES LIKE 'character_set_%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8mb4 | | 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.00 sec)
4、安装streamlit
pip3 install streamlit pip3 install pymysql streamlit hello --server.port 80 systemctl stop firewalld vim 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()
发表评论