python管理mysql

[root@server1 ~]# pip3 config set global.index-url https://pypi.tuna.tsinghua.edu.cn/simple            //这是设置清华镜像站，提高下载安装速度，如果报错提示找不到config，这是因为版本低，临时使用清华镜像站来升级 pip： python3 -m pip install -i https://pypi.tuna.tsinghua.edu.cn/simple --upgrade pip       升级后再执行上述命令
[root@server1 ~]# pip3 install pymysql              //安装pymysql连接器（oracle为开发者提供的python管理mysql 的⼯具，通过这个⼯具，就恶意在不替原有代码的情况下，应对 数据库软件的升级）
[root@server1 ~]# pip3 install pandas            //安装pandas数据分析⼯具(pandas是知名的数据分析⼯具， pandas有完整的读取数据的⼯具，以及DateFrame数据框架，⽤ 于保存从数据库中读取的数据)
[root@server1 ~]# python3
Python 3.6.8 (default, Nov 14 2023, 16:29:52) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-44)] on linux
Type "help", "copyright", "credits" or "license" for more information.567777777777777777777777777777777777777777=5
>>> import pymysql
>>> import pandas
>>> pymysql
<module 'pymysql' from '/usr/local/lib/python3.6/site-packages/pymysql/__init__.py'>
>>> pandas
<module 'pandas' from '/usr/local/lib64/python3.6/site-packages/pandas/__init__.py'>
>>> import pymysql as pm             //作别名
>>> import pandas as pd
>>> pm
<module 'pymysql' from '/usr/local/lib/python3.6/site-packages/pymysql/__init__.py'>
>>> pd
<module 'pandas' from '/usr/local/lib64/python3.6/site-packages/pandas/__init__.py'>
>>> conn=pm.connect(
... host='123.249.27.70',
... user='abcd',
... password='abcd',
... database='test',
... port=3306);
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 614, in connect
    (self.host, self.port), self.connect_timeout, **kwargs
  File "/usr/lib64/python3.6/socket.py", line 724, in create_connection
    raise err
  File "/usr/lib64/python3.6/socket.py", line 713, in create_connection
    sock.connect(sa)
socket.timeout: timed out

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 6, in <module>
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 353, in __init__
    self.connect()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 664, in connect
    raise exc
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on '123.249.27.70' (timed out)")
>>> conn=pm.connect(
... host='123.249.27.70',
... user='abcd',
... password='abcd',
... database='test',
... port=6001);
>>> conn
<pymysql.connections.Connection object at 0x7faaaa7463c8>
>>> cursor=conn.cursor()
>>> cursor
<pymysql.cursors.Cursor object at 0x7faaaa79bbe0>
>>> sql="select * from student"
>>> sql
'select * from student'
>>> cursor.execute(sql)
5
>>> cursor.fetchall()
((1, '章三', '男'), (2, '李四', '女'), (3, '小凤仙', '女'), (4, '章丘铁锅', '男'), (6, '孙颖莎', '女'))
>>> cursor.description
(('id', 3, None, 11, 11, 0, False), ('name', 253, None, 180, 180, 0, False), ('gender', 253, None, 16, 16, 0, False))
>>> head=[]
>>> desc=cursor.description
>>> for var in desc:
...     print(var[0])
... 
id
name
gender
>>> for var in desc:
...     head.append(var[0]
... 
... 
... )
... 
>>> head
['id', 'name', 'gender']
>>> pd.DataFrame(data=cursor.fetchall(),columns=head)
Empty DataFrame
Columns: [id, name, gender]
Index: []
>>> pd.DataFrame(cursor.fetchall(),columns=head)
Empty DataFrame
Columns: [id, name, gender]
Index: []
>>> cursor.fetchall()
()
>>> cursor.execute(sql)
5
>>> res=cursor.fetchall()
>>> res
((1, '章三', '男'), (2, '李四', '女'), (3, '小凤仙', '女'), (4, '章丘铁锅', '男'), (6, '孙颖莎', '女'))
>>> head
['id', 'name', 'gender']
>>> pd.DataFrame(data=res,columns=head)
   id  name gender
0   1    章三      男
1   2    李四      女
2   3   小凤仙      女
3   4  章丘铁锅      男
4   6   孙颖莎      女

[root@zmysql ~]# vim python_mysql_01.py              //编写python脚本
import pymysql
import pandas

class Python_Mysql_01(object):

    def __init__(self):
        print("======================")

    def getConn(self):
        conn=pymysql.connect(
            host=input("sign host_ip|name:"),
            user=input("sign database username:"),
            password=input("sign database password:"),
            database=input("sign database name:"),
            port=int(input("sign port no "))
        )
#        print(conn)
        return conn

    def getRes(self,cursor,sql):
        cursor.execute(sql)

        # 获得查询的数据
        data=cursor.fetchall()

        # 表头
        head=[item[0] for item in cursor.description]
                
        # 组成pandas数据框 DataFrame
        return pandas.DataFrame(data=data,columns=head)

if __name__=="__main__":
    # 初始化Python_Mysql_01类，创建实例，pmp，之后所有的方法都可以在实例中调用
    pmp=Python_Mysql_01()
    # 获得conn
    conn=pmp.getConn()

    #获得游标    
    cursor=conn.cursor()
#    print(cursor)
    tablename=input("sign tablename")
    df=pmp.getRes(cursor,"select * from "+tablename)
    print(df)    
[root@zmysql ~]# python3 python_mysql_01.py               //运行脚本
======================
sign host_ip|name:123.249.27.70
sign database username:abcd
sign database password:abcd
sign database name:test
sign port no 6001
sign tablenamestudent
   id  name gender
0   1    章三      男
1   2    李四      女
2   3   小凤仙      女
3   4  章丘铁锅      男
4   6   孙颖莎      女

[root@zmysql ~]# pip3 install pyinstaller              //安装pyinstaller
[root@zmysql ~]# which pyinstaller 
/usr/local/bin/pyinstaller
[root@zmysql ~]# pyinstaller --onefile python_mysql_01.py          //使⽤pyinstaller⽣成可执⾏⽂件
[root@zmysql ~]# cd dist/
[root@zmysql dist]# ls
python_mysql_01
[root@zmysql dist]# ./python_mysql_01 
======================
sign host_ip|name:123.249.27.70
sign database username:abcd
sign database password:abcd
sign database name:test
sign port no 6001
sign tablenamestduent
   id  name gender
0   1    章三      男
1   2    李四      女
2   3   小凤仙      女
3   4  章丘铁锅      男
4   6   孙颖莎      女
[root@zmysql ~]# cd
[root@zmysql ~]# setenforce 0
[root@zmysql ~]# systemctl stop firewalld.service 
[root@zmysql ~]# python3 -m http.server 9000            //发布web服务
浏览器访问：