当前位置：首页 > news >正文

8.9 python管理mysql

news 来源：原创 2024/9/20 16:49:59

[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服务
浏览器访问：