python对MySQL数据库的一些基本操作(1)

python对MySQL数据库的一些基本操作(1)

初始化

import mysql.connector

if __name__ == '__main__':
    mydb = mysql.connector.connect(
        port=3306,
        host='localhost',
        user='root',
        passwd='123456',
        database="runoob",
        auth_plugin='mysql_native_password'
    )

mycursor = mydb.cursor()

创建数据库

mycursor.execute("CREATE DATABASE runoob_db")

创建数据表

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

检查数据库是否存在

mycursor.execute("SHOW DATABASES")

检查表是否存在

mycursor.execute("SHOW TABLES")

给表设置一个主键（PRIMARY KEY）

mycursor.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

插入一条数据

    sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
    val = ("百度", "https://www.baidu.com/")
    mycursor.execute(sql, val)

    mydb.commit()  # 数据表内容有更新，必须使用到该语句

    print(mycursor.rowcount, "记录插入成功。")

插入多条数据

    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, "记录插入成功。")

获取该记录的 ID

 print("1 条记录已插入, ID:", mycursor.lastrowid)

查询数据

mycursor.execute("SELECT * FROM sites")

myresult = mycursor.fetchall()  # fetchall() 获取所有记录

for x in myresult:
    print(x)

读取指定的字段数据

mycursor.execute("SELECT id, name FROM sites")

myresult = mycursor.fetchall()

for x in myresult:
    print(x)