python通过mysql.connector操作mysql数据库

虚拟机上先建个数据库建个表写点数据：

 

python代码：

#!/usr/bin/env python3
# coding=utf-8

import mysql.connector

my_connector = mysql.connector.connect(
    pool_name="my_db_pool",
    pool_size=4,
    host="192.168.1.181",
    port="3306",
    user="test",
    passwd="test",
    db="testdb",
    charset="utf8"
)

my_cursor = my_connector.cursor()

try:
    # 查询
    my_cursor.execute("select * from table1")
    print(my_cursor.fetchall())
    my_cursor.execute("select * from table1 where id = %s", (1,))
    print(my_cursor.fetchone())
    print("=" * 40)
    # 添加
    my_cursor.execute("insert into table1 (name, addr) values (%s, %s)", ("abc", "ABC"))
    print(my_cursor.rowcount)
    my_cursor.executemany("insert into table1 (name, addr) values (%s, %s)", [("def", "DEF"), ("ghi", "GHI")])
    print(my_cursor.rowcount)
    my_connector.commit()    # 修改数据操作需要commit
    my_cursor.execute("select * from table1")
    print(my_cursor.fetchall())
    print("=" * 40)
    # 修改
    my_cursor.execute("update table1 set addr = %s where name = %s", ("GGHHII", "ghi"))
    print(my_cursor.rowcount)
    my_connector.commit()    # 修改数据操作需要commit
    my_cursor.execute("select * from table1")
    print(my_cursor.fetchall())
    print("=" * 40)
    # 删除
    my_cursor.execute("delete from table1 where name = %s", ("ghi",))
    my_cursor.execute("delete from table1 where addr = %s", ("DEF",))
    my_cursor.execute("delete from table1 where id > %s", (6,))
    my_connector.commit()    # 修改数据操作需要commit
    my_cursor.execute("select * from table1")
    print(my_cursor.fetchall())
except mysql.connector.Error as e:
    print("ERROR:", e)
finally:
    my_cursor.close()
    my_connector.close()

运行结果：

 

一些其他连接mysql的方式：MySQL - Python Wiki