Python mysql创建数据库 创建表 插入数据

# -*- coding: utf-8 -*-
"""
Created on Tue Nov 10 10:48:28 2020

@author: Administrator
"""
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="123456",
  auth_plugin='mysql_native_password',
  database="musicdatabase"
)

print(mydb)

mycursor = mydb.cursor()

"""
创建表第一次使用，后直接用即可
mycursor.execute("CREATE DATABASE musicdatabase")
"""

"""
你已经成功地创建了一个表。
mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

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

"""
插入数据
"""
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  ('Peter', 'Lowstreet 4'),
  ('Amy', 'Apple st 652'),
  ('Hannah', 'Mountain 21'),
  ('Michael', 'Valley 345'),
  ('Sandy', 'Ocean blvd 2'),
  ('Betty', 'Green Grass 1'),
  ('Richard', 'Sky st 331'),
  ('Susan', 'One way 98'),
  ('Vicky', 'Yellow Garden 2'),
  ('Ben', 'Park Lane 38'),
  ('William', 'Central st 954'),
  ('Chuck', 'Main Road 989'),
  ('Viola', 'Sideway 1633')
]

mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, " 条已经插入")
mycursor.execute("SHOW TABLES")

for x in mycursor:
  print(x)