开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> python学习（十二）python与mysql -> 正文阅读

[大数据]python学习（十二）python与mysql

前言

本篇主要介绍使用mysql-connector来连接使用mysql。 mysql-connector 是 MySQL 官方提供的驱动器。

一、安装

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

python -m pip install mysql-connector

测试安装是否成功：

import mysql.connector

如果没有报错，则安装成功。
注意：如果你的 MySQL 是 8.0 版本，密码插件验证方式发生了变化，早期版本为 mysql_native_password，8.0 版本为caching_sha2_password，所以需要做些改变。具体方法可参照网上教程。

二、创建数据库连接

# !/usr/bin/python3
# -*- coding: UTF-8 -*-

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user='root',
    password='tamigroup',
    auth_plugin='mysql_native_password'
)
print(conn)

三、创建数据库

创建一个名为runoob_db的数据库，如下：

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user='root',
    password='tamigroup',
    auth_plugin='mysql_native_password'
)
mycursor = conn.cursor()

mycursor.execute("CREATE DATABASE runoob_db")

四、创建数据表

创建一个名为sites的数据表，如下：

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user='root',
    password='tamigroup',
    auth_plugin='mysql_native_password',
    database="runoob_db"
)
mycursor = conn.cursor()

mycursor.execute("CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))")

五、主键设置

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user='root',
    password='tamigroup',
    auth_plugin='mysql_native_password',
    database="runoob_db"
)
mycursor = conn.cursor()
mycursor.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

六、插入数据

1、单条插入

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user='root',
    password='tamigroup',
    auth_plugin='mysql_native_password',
    database="runoob_db"
)
mycursor = conn.cursor()
sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = ("RUNOOB", "https://www.runoob.com")
mycursor.execute(sql, val)
 
mydb.commit()    # 数据表内容有更新，必须使用到该语句
 
print(mycursor.rowcount, "记录插入成功。")

2、批量插入

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user='root',
    password='tamigroup',
    auth_plugin='mysql_native_password',
    database="runoob_db"
)
mycursor = conn.cursor()
sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = [
  ('Google', 'https://www.google.com'),
  ('Github', 'https://www.github.com'),
  ('Taobao', 'https://www.taobao.com'),
  ('stackoverflow', 'https://www.stackoverflow.com/')
]
mycursor.executemany(sql, val)
 
mydb.commit()    # 数据表内容有更新，必须使用到该语句
 
print(mycursor.rowcount, "记录插入成功。")

效果如下：
在这里插入图片描述

七、查询数据

1、查询所有数据

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user='root',
    password='tamigroup',
    auth_plugin='mysql_native_password',
    database="runoob_db"
)
mycursor = conn.cursor()
mycursor.execute("SELECT * FROM sites")
 
myresult = mycursor.fetchall()     # fetchall() 获取所有记录
 
for x in myresult:
  print(x)

输出如下：

('RUNOOB', 'https://www.runoob.com', 1)
('Google', 'https://www.google.com', 2)
('Github', 'https://www.github.com', 3)
('Taobao', 'https://www.taobao.com', 4)
('stackoverflow', 'https://www.stackoverflow.com/', 5)

2、查询指定的字段数据

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user='root',
    password='tamigroup',
    auth_plugin='mysql_native_password',
    database="runoob_db"
)
mycursor = conn.cursor()
mycursor.execute("SELECT name, url FROM sites")
 
myresult = mycursor.fetchall()
 
for x in myresult:
  print(x)

输出如下：

('RUNOOB', 'https://www.runoob.com')
('Google', 'https://www.google.com')
('Github', 'https://www.github.com')
('Taobao', 'https://www.taobao.com')
('stackoverflow', 'https://www.stackoverflow.com/')

3、查询指定条件的数据

使用where语句，如下：

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user='root',
    password='tamigroup',
    auth_plugin='mysql_native_password',
    database="runoob_db"
)
mycursor = conn.cursor()
sql = "SELECT * FROM sites WHERE name = %s"
na = ("RUNOOB", )
 
mycursor.execute(sql, na)
 
myresult = mycursor.fetchall()
 
for x in myresult:
  print(x)

输出如下：

('RUNOOB', 'https://www.runoob.com', 1)

4、查询结果排序

查询结果排序可以使用ORDER BY语句，默认的排序方式为升序，关键字为ASC；设置降序，关键字为DESC
按name字段字母的降序排序，如下：

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user='root',
    password='tamigroup',
    auth_plugin='mysql_native_password',
    database="runoob_db"
)
mycursor = conn.cursor()
sql = "SELECT * FROM sites ORDER BY name DESC"
 
mycursor.execute(sql)
 
myresult = mycursor.fetchall()
 
for x in myresult:
  print(x)

输出如下：

('Taobao', 'https://www.taobao.com', 4)
('stackoverflow', 'https://www.stackoverflow.com/', 5)
('RUNOOB', 'https://www.runoob.com', 1)
('Google', 'https://www.google.com', 2)
('Github', 'https://www.github.com', 3)

5、设置查询的数据量

可以只读取前3条记录，如下：

mycursor.execute("SELECT * FROM sites LIMIT 3")

也可以指定起始位置，如下：

mycursor.execute("SELECT * FROM sites LIMIT 3 OFFSET 1")  # 0 为 第一条，1 为第二条，以此类推

八、删除记录

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user='root',
    password='tamigroup',
    auth_plugin='mysql_native_password',
    database="runoob_db"
)
mycursor = conn.cursor()
sql = "DELETE FROM sites WHERE name = %s"
na = ("stackoverflow",)

mycursor.execute(sql, na)

conn.commit()

print(mycursor.rowcount, " 条记录删除")

九、更新表数据

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user='root',
    password='tamigroup',
    auth_plugin='mysql_native_password',
    database="runoob_db"
)
mycursor = conn.cursor()
sql = "UPDATE sites SET name = %s WHERE name = %s"
val = ("TB", "Taobao")

mycursor.execute(sql, val)

conn.commit()

print(mycursor.rowcount, " 条记录被修改")

十、删除表

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user='root',
    password='tamigroup',
    auth_plugin='mysql_native_password',
    database="runoob_db"
)
mycursor = conn.cursor()
sql = "DROP TABLE IF EXISTS sites"  # 删除数据表 sites

mycursor.execute(sql)