python连接mysql数据库

环境： Ubuntu20.04、Mysql8.0.32

ubuntu20.04自带python版本 ,可以看到是 3.8.10，pip的版本是 20.0.2

1、更换国内源

先备份原来的源：cp /etc/apt/sources.list /etc/apt/sources_old.list
在 /etc/apt/sources.list 最下面添加 

deb http://mirrors.aliyun.com/ubuntu/ trusty main restricted universe multiverse  
deb http://mirrors.aliyun.com/ubuntu/ trusty-security main restricted universe multiverse  
deb http://mirrors.aliyun.com/ubuntu/ trusty-updates main restricted universe multiverse  
deb http://mirrors.aliyun.com/ubuntu/ trusty-proposed main restricted universe multiverse  
deb http://mirrors.aliyun.com/ubuntu/ trusty-backports main restricted universe multiverse  
deb-src http://mirrors.aliyun.com/ubuntu/ trusty main restricted universe multiverse  
deb-src http://mirrors.aliyun.com/ubuntu/ trusty-security main restricted universe multiverse  
deb-src http://mirrors.aliyun.com/ubuntu/ trusty-updates main restricted universe multiverse  
deb-src http://mirrors.aliyun.com/ubuntu/ trusty-proposed main restricted universe multiverse  
deb-src http://mirrors.aliyun.com/ubuntu/ trusty-backports main restricted universe multiverse

2、连接mysql

参考菜鸟教程 Python MySQL – mysql-connector 驱动 | 菜鸟教程 (runoob.com)

使用 pip 命令来安装 mysql-connector：

pip install mysql-connector

检查mysql-connector 是否安装成功：

python3 -c "import mysql.connector; print(mysql.connector.__version__)"

3、 创建lanren312_db数据库

#!/usr/bin/env python3

import mysql.connector

print(mysql.connector.__version__)

mydb = mysql.connector.connect(
  host="192.168.1.138",
  user="root",
  passwd="xxxxx"
)

print(mydb)

mycursor= mydb.cursor()
mycursor.execute("create database IF NOT EXISTS `lanren312_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci")

4、创建test表

#!/usr/bin/env python3

import mysql.connector

print(mysql.connector.__version__)

mydb = mysql.connector.connect(
  host="192.168.1.138",
  user="root",
  passwd="xxxxx",
  database="lanren312_db"
)

print(mydb)

mycursor= mydb.cursor()

mycursor.execute("create table test (id VARCHAR(32) PRIMARY KEY, name VARCHAR(255))")

5、运行sql

#!/usr/bin/env python3

import mysql.connector

print(mysql.connector.__version__)

mydb = mysql.connector.connect(
  host="192.168.1.138",
  user="root",
  passwd="xxxxx",
  database="lanren312_db"
)

print(mydb)

mycursor= mydb.cursor()

sql = "insert into test (id, name) values (%s, %s)"
val = ("59474d04fa1111e9940c7085c206b230", "张3")

mycursor.execute(sql, val)
mydb.commit()

print("成功插入一条数据")


manyval=[('59474d04fa1111e9940c7085c206b231', '李4'),
         ('59474d04fa1111e9940c7085c206b232', '王五'),
         ('59474d04fa1111e9940c7085c206b233', '赵6')
        ]
mycursor.executemany(sql, manyval)
mydb.commit()

print("成功批量插入三条数据")

6、pyecharts

安装命令：pip install pyecharts

echarts1.py  

#!/usr/bin/env python3

from pyecharts.charts import Bar

# 准备数据
x_data = ['一月', '二月', '三月', '四月', '五月']
y_data = [10, 20, 15, 25, 30]

# 创建柱状图
bar_chart = Bar()
bar_chart.add_xaxis(x_data)
bar_chart.add_yaxis("销售额", y_data)

# 也可以传入路径参数，如 bar_chart.render("bar_chart.html")
bar_chart.render("aaa.html")