mysqlconnector

mysql-connector是一个Python模块

1.安装

python -m pip install mysql-connector

测试是否安装成功

import mysql.connector

test数据库结构

2.建立数据库连接

db.py

import mysql.connector
import time
#数据库连接
def conn():
    mydb = mysql.connector.connect(
        host="localhost",
        user="用户名",
        passwd="密码",
        database="数据库名"
    )
    return mydb
print(conn())

执行结果

python db.py
<mysql.connector.connection.MySQLConnection object at 0x000001FF13E240F0>

3.数据插入

（1）单条插入

import mysql.connector
import time
#数据库连接
def conn():
    mydb = mysql.connector.connect(
        host="localhost",
        user="用户名",
        passwd="密码",
        database="数据库名"
    )
    return mydb

def insert(name,now):
    mydb = conn()
    mycursor = mydb.cursor()
    sql = "INSERT INTO test (name, create_time) VALUES (%s, %s)"
    val = (name, now)
    mycursor.execute(sql, val)
    mydb.commit()
    return mycursor.rowcount
now = int(time.time())
print(insert('ABC',now))

执行结果

python db.py
1

（2）批量插入

import mysql.connector
import time
#数据库连接
def conn():
    mydb = mysql.connector.connect(
        host="localhost",
        user="用户名",
        passwd="密码",
        database="数据库名"
    )
    return mydb

def insertMore(val_list):
    mydb = conn()
    mycursor = mydb.cursor()
    table_name = "test"
    ins_sql = "INSERT INTO {}(name, create_time)VALUES ({}, {});"
    sql = ins_sql.format(table_name, '%s', '%s')
    mycursor.executemany(sql, val_list)
    mydb.commit()
    return mycursor.rowcount

name_list = ["AA", "BB", "CC", "DD"]
now = int(time.time())
val_list = [[name_list[i], now]
            for i in range(len(name_list))]
print(insertMore(val_list))

执行结果

python db.py
4

4.数据查询

（1）查询部分字段的全部数据 fetchall()

import mysql.connector
import time
#数据库连接
def conn():
    mydb = mysql.connector.connect(
        host="localhost",
        user="用户名",
        passwd="密码",
        database="数据库名"
    )
    return mydb

def findAll():
    mydb = conn()
    mycursor = mydb.cursor()
    table_name = "test"
    ins_sql = "SELECT name,from_unixtime(create_time,'%Y-%m-%d %H:%i:%s') FROM {}"
    sql = ins_sql.format(table_name)
    mycursor.execute(sql)
    rs = mycursor.fetchall()     # 获取所有记录
    return rs

rs = findAll()
for x in rs:
  print(x)

执行结果

python db.py
('ABC', '2022-09-16 11:17:32')
('AA', '2022-09-16 11:18:06')
('BB', '2022-09-16 11:18:06')
('CC', '2022-09-16 11:18:06')
('DD', '2022-09-16 11:18:06')

（2）查询一条数据 fetchone()

import mysql.connector
import time
#数据库连接
def conn():
    mydb = mysql.connector.connect(
        host="localhost",
        user="用户名",
        passwd="密码",
        database="数据库名"
    )
    return mydbdef findOne():
    mydb = conn()
    mycursor = mydb.cursor()
    table_name = "test"
    ins_sql = "SELECT name,from_unixtime(create_time,'%Y-%m-%d %H:%i:%s') FROM {}"
    sql = ins_sql.format(table_name)
    mycursor.execute(sql)
    rs = mycursor.fetchone()
    return rs

rs = findOne()
print(rs)

执行结果

python db.py
('ABC', '2022-09-16 11:17:32')

（3）按条件获取数据

import mysql.connector
import time
#数据库连接
def conn():
    mydb = mysql.connector.connect(
        host="localhost",
        user="用户名",
        passwd="密码",
        database="数据库名"
    )
    return mydb
def findSome(val):
    mydb = conn()
    mycursor = mydb.cursor()
    table_name = "test"
    ins_sql = "SELECT name,from_unixtime(create_time,'%Y-%m-%d %H:%i:%s') FROM {} where name like '%{}%'"
    sql = ins_sql.format(table_name,val)
    mycursor.execute(sql)
    rs = mycursor.fetchall()
    return rs

rs = findSome('A')
for x in rs:
  print(x)

执行结果

python db.py
('ABC', '2022-09-16 11:17:32')
('AA', '2022-09-16 11:18:06')

5.数据更新

（1）单条更新

import mysql.connector
import time
#数据库连接
def conn():
    mydb = mysql.connector.connect(
        host="localhost",
        user="用户名",
        passwd="密码",
        database="数据库名"
    )
    return mydbdef update(val,id):
    mydb = conn()
    mycursor = mydb.cursor()
    table_name = "test"
    ins_sql = "UPDATE {} SET name = {},update_time = {}  WHERE id = {}"
    sql = ins_sql.format(table_name, '%s', '%s', id)
    mycursor.execute(sql, val)
    mydb.commit()
    return mycursor.rowcount
now = int(time.time())
val = ('XYZ', now)
print(update(val,1))

执行结果

python db.py
1

（2）批量更新

import mysql.connector
import time
#数据库连接
def conn():
    mydb = mysql.connector.connect(
        host="localhost",
        user="用户名",
        passwd="密码",
        database="数据库名"
    )
    return mydbdef updateMore(val_list):
    mydb = conn()
    mycursor = mydb.cursor()
    table_name = "test"
    ins_sql = "UPDATE {} SET name = {},update_time = {}  WHERE id = {}"
    sql = ins_sql.format(table_name, '%s', '%s', '%s')
    mycursor.executemany(sql, val_list)
    mydb.commit()
    return mycursor.rowcount

ids = ["2","3","4","5"]
name_list = ["AA1", "BB1", "CC1", "DD1"]
now = int(time.time())
val_list = [(name_list[i], now,ids[i])
            for i in range(len(name_list))]
print(updateMore(val_list))

执行结果

python db.py
4

6.数据删除

import mysql.connector
import time
#数据库连接
def conn():
    mydb = mysql.connector.connect(
        host="localhost",
        user="用户名",
        passwd="密码",
        database="数据库名"
    )
    return mydbdef delete(val):
    mydb = conn()
    mycursor = mydb.cursor()
    table_name = "test"
    ins_sql = "DELETE FROM {} where name like '%{}%'"
    sql = ins_sql.format(table_name, val)
    mycursor.execute(sql)
    mydb.commit()
    return mycursor.rowcount

print(delect("A"))

执行结果

python db.py
1