2020.10.14 Python学习笔记

1.利用Python创建数据库

import mysql.connector
my_db=mysql.connector.connect(
    host="localhost",user="root",passwd="1123581321@Wyy")
mycursor=my_db.cursor()
mycursor.execute("create database mydb4")										#创建数据库mydb4
mycursor.execute("show databases")												#展示全部数据库
for x in mycursor:
    print(x)

2.利用Python建表并实现表的增删改查
①方法一：

import mysql.connector
my_db=mysql.connector.connect(
    host="localhost",user="root",passwd="1123581321@Wyy")
mycursor=my_db.cursor()

mycursor.execute("use mydb")
mycursor.execute("create table users4(userID varchar(10) primary key,userName varchar(10))")
																				#创建表users4并设置userID为主键
mycursor.execute("show tables")													#展示所有表
for x in mycursor:
    print(x)																	#打印结果
print("************************")												#我是一个没有感情的分界线
mycursor.execute("insert into users4 values ('001','Lily')")					#向表中插入数据
mycursor.execute("select * from users4")
for x in mycursor:
    print(x)
print("************************")
mycursor.execute("update users4 set userName='Sherly' where userID='001'")		#修改表中数据
mycursor.execute("select * from users4")
for x in mycursor:
    print(x)
print("************************")
mycursor.execute("delete from users4 where userID='001'")						#删除数据
mycursor.execute("select * from users4")
for x in mycursor:
    print(x)
print("************************")
mycursor.execute("select * from users1")										#实现表的查询
for x in mycursor:
    print(x)
print("************************")

结果：

②方法二

import mysql.connector
my_db=mysql.connector.connect(
    host="localhost", user="root",passwd="1123581321@Wyy")
mycursor=my_db.cursor()

createDB="create database mydb5"												#创建数据库mydb5
useDB="use mydb5"																#使用该数据库
ct="create table users (userID varchar(10) primary key, userName varchar(10))"	#创建表
ins="insert into users (userID,userName) values (%s,%s) "						#插入数据
val=[
    ('001','jxnu'),
    ('002','pku'),
    ('003','ncu')
    ]																			#批量插入数据
sql="select * from users"
mycursor.execute(createDB)
mycursor.execute(useDB)
mycursor.execute(ct)
mycursor.executemany(ins, val)
mycursor.execute(sql)

for x in mycursor:
    print(x)																	#打印结果
my_db.commit()																	#提示数据库有信息更改

结果：