Python学习——MySQL

"""
Created on Fri Aug 13 23:54:50 2021
filename:
@author: zgt
@mail:zgt122311@163.com
"""


import mysql.connector
 
mydb = mysql.connector.connect(
  host="localhost",       # 数据库主机地址
  user="root",    # 数据库用户名
  passwd="123456",   # 数据库密码
  database = "sql_2"
)


mycursor = mydb.cursor()
# #创建数据库
# mycursor.execute("CREATE DATABASE sql_2")
# #显示数据库
# mycursor.execute("SHOW DATABASES")


# for x in mycursor:
#     print(x)
#创建数据表
#mycursor.execute("CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))")
mycursor.execute("CREATE TABLE ml_phone_info (name VARCHAR(255), url VARCHAR(255))")


mycursor.execute("SHOW TABLES")
for y in mycursor:
    print(y)


# #向sites表插入一条记录
# sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
# val = ("RUNOOB", "https://www.runoob.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
# sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
# val = ("Zhihu", "https://www.zhihu.com")
# mycursor.execute(sql, val) 
# mydb.commit()
# print("1 条记录已插入, ID:", mycursor.lastrowid)


# #查询数据使用select语句
# mycursor.execute("SELECT * FROM sites")
# myresult = mycursor.fetchall()     # fetchall() 获取所有记录
# for x in myresult:
#   print(x)


# #读取指定的字段数据
# mycursor.execute("SELECT name, url FROM sites")
# myresult = mycursor.fetchall()
# for x in myresult:
#   print(x)


# #只读一条数据
# mycursor.execute("SELECT * FROM sites")
# myresult = mycursor.fetchone()
# print(myresult)




# #读取指定条件的数据where
# sql = "SELECT * FROM sites WHERE name ='RUNOOB'"
# mycursor.execute(sql)
# myresult = mycursor.fetchall()
# for x in myresult:
#   print(x)


#也可以用%
# sql = "SELECT * FROM sites WHERE url LIKE '%oo%'"
# mycursor.execute(sql)
# myresult = mycursor.fetchall()
# for x in myresult:
#   print(x)
  
# #使用 %s 占位符来转义查询的条件
# sql = "SELECT * FROM sites WHERE name = %s"
# na = ("RUNOOB", )
# mycursor.execute(sql, na)
# myresult = mycursor.fetchall()
# for x in myresult:
#   print(x)


# #查询结果排序可以使用 ORDER BY 语句，默认的排序方式为升序，关键字为 ASC，如果要设置降序排序，可以设置关键字 DESC
# sql = "SELECT * FROM sites ORDER BY name"
# mycursor.execute(sql)
# myresult = mycursor.fetchall()
# for x in myresult:
#   print(x)


# #按name字段字母降序排序
# sql = "SELECT * FROM sites ORDER BY name DESC"
# mycursor.execute(sql)
# myresult = mycursor.fetchall()
# for x in myresult:
#   print(x)


# #如果我们要设置查询的数据量，可以通过 "LIMIT" 语句来指定
# mycursor.execute("SELECT * FROM sites LIMIT 3")
# myresult = mycursor.fetchall()
# for x in myresult:
#   print(x)
  
# #也可以指定起始位置，使用的关键字是 OFFSET：
# mycursor.execute("SELECT * FROM sites LIMIT 3 OFFSET 1")  # 0 为 第一条，1 为第二条，以此类推
# myresult = mycursor.fetchall()
# for x in myresult:
#   print(x)


#删除记录使用 "DELETE FROM" 语句：
# sql = "DELETE FROM sites WHERE name = 'stackoverflow'"
# mycursor.execute(sql)
# mydb.commit()
# print(mycursor.rowcount, " 条记录删除")


# #也可以用%s
# sql = "DELETE FROM sites WHERE name = %s"
# na = ("stackoverflow", )
# mycursor.execute(sql, na)
# mydb.commit()
# print(mycursor.rowcount, " 条记录删除")


#数据表更新用update语句
# sql = "UPDATE sites SET name = 'ZH' WHERE name = 'Zhihu'"
# mycursor.execute(sql)
# mydb.commit()
# print(mycursor.rowcount, " 条记录被修改")


# #用%s
# sql = "UPDATE sites SET name = %s WHERE name = %s"
# val = ("Zhihu", "ZH")
# mycursor.execute(sql, val)
# mydb.commit()
# print(mycursor.rowcount, " 条记录被修改")


# #删除表使用 "DROP TABLE" 语句， IF EXISTS 关键字是用于判断表是否存在，只有在存在的情况才删除：
# sql = "DROP TABLE IF EXISTS sites"  # 删除数据表 sites
# mycursor.execute(sql)

上述为基础学习教程。