Python mysql创建数据库创建表插入数据_python脚本在mysql数据库中创建mytest数据库,创建一张表格,写入数据-CSDN博客

本文介绍了一个使用 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)