Python学习笔记(二)MySQL的简单操作

发布于 2021-06-24  168 次阅读


连接一个MySQL数据库

import mysql.connector

data_base = mysql.connector.connect(
    host = "主机名",
    port = "3306",
    user = "用户名",
    passwd = "用户密码^",
    database = "数据库名"
)

循环打印表的记录

def showAllDataBase():
    mycursor = data_base.cursor()
    mycursor.execute("select * from class")
    myresult = mycursor.fetchall()
    for x in myresult:
        print(x)
showAllDataBase()

新增数据库

# 新建数据库
def addDataBase():
    addBaseName = str(input("请输入新建的数据库名:"))
    add_Base = data_base.cursor()
    add_Base.execute("CREATE DATABASE" + " " + addBaseName)
addDataBase()

删除数据库

# 删除数据库
def removeDataBase():
    removeBase = data_base.cursor()
    DataBaseName = str(input("请输入需要删除的数据库名:"))
    sql = "DROP DATABASE IF EXISTS" + " " + DataBaseName
    cmd = str(input("真的要删除它吗?(Y/y)"))
    if cmd == "Y" or cmd == "y":
        removeBase.execute(sql)
        print("已删除数据库:" + DataBaseName)
    else:
        print("已取消删除操作!")

新增数据表

# 新建数据表
def addTable():
    cursor =data_base.cursor()
    cursor.execute("CREATE TABLE test (id VARCHAR(255), s_id VARCHAR(255))")
addTable()

删除数据表

# 删除数据表
def removeDataTable():
    removeTableName = str(input("请输入需要删除的数据表名称:"))
    removeTable = data_base.cursor()
    sql = "DROP TABLE IF EXISTS" + " " + removeTableName  # 拼接删除数据表SQL语句
    # print(sql)
    cmd = str(input("真的要删除它吗?(Y/y)"))
    if cmd == "Y" or cmd == "y":
        removeTable.execute(sql)
        print("已删除数据表:" + removeTableName)
    else:
        print("已取消删除操作!")
removeDataTable()

插入记录

# 插入新的记录
def addInto():
    base = data_base.cursor()
    sql = "INSERT INTO Data_table_name (Field1, Field2) VALUES (%s, %s)"
    val = ("TEST", "TEST")
    base.execute(sql, val)
    data_base.commit() # 数据表内容有更新,必须使用到该语句
    print(base.rowcount, "插入成功!")
addInto()

批量插入记录

# 批量记录插入
def BatchAddInto():
    base = data_base.cursor()
    sql = "INSERT INTO test (id, s_id) VALUES (%s, %s)"
    val = [
        ("1", "1"),
        ("2", "2"),
        ("3", "3"),
        ("4", "4"),
        ("5", "5")
    ]
    base.executemany(sql, val)
    data_base.commit()
    print(base.rowcount, "插入成功!")
BatchAddInto()

后续可能会更新(咕咕咕×@>


只是当时已惘然。