python基础知识(六)

连接Mysql数据库

使用mysql-connector连接mysql数据库

注意：如果你的 MySQL 是 8.0 版本，密码插件验证方式发生了变化，早期版本为 mysql_native_password，8.0 版本为 caching_sha2_password，所以需要做些改变：

• 先修改 my.ini 配置：

[mysqld]
default_authentication_plugin=mysql_native_password

• 然后在 mysql 下执行以下命令来修改密码：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';

安装Mysql数据库

python -m pip install mysql-connector

连接数据库

创建数据库

import mysql.connectormydb = mysql.connector.connect(host="localhost",user="root",passwd="root"
)mycursor = mydb.cursor()
#创建数据库，如果存在会报错
# mycursor.execute("CREATE DATABASE runoob_db")
# 查询所有的数据库
mycursor.execute("SHOW DATABASES")for x in mycursor:print(x)

创建数据表

import mysql.connectormydb = mysql.connector.connect(host="localhost",user="root",passwd="root",database="runoob_db" # 可选
)mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE sites (name VARCHAR(255),url VARCHAR(255))")

查询表是否存在

import mysql.connectormydb = mysql.connector.connect(host="localhost",user="root",passwd="root",database="runoob_db" 
)mycursor = mydb.cursor()
mycursor.execute("SHOW TABLES")
for x in mycursor:print(x)

设置主键

import mysql.connectormydb = mysql.connector.connect(host="localhost",user="root",passwd="root",database="runoob_db" 
)mycursor = mydb.cursor()
mycursor.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")# 表未创建
mycursor.execute("CREATE TABLE sites (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))")

插入数据

import mysql.connectormydb = mysql.connector.connect(host="localhost",user="root",passwd="root",database="runoob_db" # 可选
)mycursor = mydb.cursor()
sql = "INSERT INTO sites (name,url) VALUES (%s,%s)"
val = ("RUNOOB","https://www.runoob.com")
mycursor.execute(sql,val)mydb.commit() # 数据表内容有更新，必须使用该语句
print(mycursor.rowcount,"记录插入成功。")

批量插入

import mysql.connectormydb = mysql.connector.connect(host="localhost",user="root",passwd="root",database="runoob_db" # 可选
)mycursor = mydb.cursor()
sql = "INSERT INTO sites (name,url) VALUES (%s,%s)"
# val = ("RUNOOB","https://www.runoob.com")
# 批量插入
val = [('Baidu','https://www.baidu.com'),('Taobao','https://www.taobao.com'),('Jingdong','https://www.jd.com'),('tencent','https://www.tencent.com'),('huaxing','https://www.sdhxem.com')
]
# 批量插入使用executemany()方法
mycursor.executemany(sql,val)mydb.commit() # 数据表内容有更新，必须使用该语句
print(mycursor.rowcount,"记录插入成功。")

查询、删除、更新数据

import mysql.connectormydb = mysql.connector.connect(host="localhost",user="root",passwd="root",database="runoob_db" # 可选
)mycursor = mydb.cursor()# 指定字段查询
# mycursor.execute("SELECT name,url FROM sites")
# 查询全部
#mycursor.execute("SELECT * FROM sites")
# myresult = mycursor.fetchall()
# for x in myresult:
#     print(x)
# 查询一条
# myresult1 = mycursor.fetchone()
# print(myresult1)
# 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)
# 使用占位符
# sql = "SELECT * FROM sites WHERE name =%s"
# na = ("RUNOOB",)
# mycursor.execute(sql,na)
# myresult = mycursor.fetchall()
# for x in myresult:
#     print(x)# 排序
# sql = "SELECT * FROM sites ORDER BY name"
# mycursor.execute(sql)
# myresult = mycursor.fetchall()
# for x in myresult:
#     print(x)# limit
# sql = "SELECT * FROM sites LIMIT 2,3"
# mycursor.execute(sql)
# myresult = mycursor.fetchall()
# for x in myresult:
#     print(x)# OFFSET
# sql = "SELECT * FROM sites LIMIT 3 OFFSET 1"
# mycursor.execute(sql)
# myresult = mycursor.fetchall()
# for x in myresult:
#     print(x)# 删除数据
# sql = "DELETE FROM sites WHERE name = 'RUNOOB'"
# mycursor.execute(sql)
#
# mydb.commit()
#
# print(mycursor.rowcount,"条记录被删除")# 更新数据
sql = "UPDATE sites SET name = 'TAOBAO' WHERE name = 'Taobao'"
mycursor.execute(sql)mydb.commit()print(mycursor.rowcount,"条记录被修改")

使用PyMySql连接数据库

安装PyMySql

pip3 install PyMySQL

连接数据库

import pymysql
# 1.编辑数据库连接
db = pymysql.connect(host='localhost',user='root',password='root',database='runoob_db'
)
# 2.创建游标对象
cursor = db.cursor()
# 3.执行sql语句
# cursor.execute("SELECT VERSION()")
# 4.获取数据库数据
# data = cursor.fetchone()
# 5.处理结果
# print("Database version : %s"% data)# 创建表
# cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# sql = """CREATE TABLE EMPLOYEE (FIRST_NAME CHAR(20) NOT NULL,
#                                 LAST_NAME CHAR(20),
#                                 AGE INT,
#                                 SEX CHAR(1),
#                                 INCOME FLOAT)"""
# cursor.execute(sql)# 插入数据
# sql = """INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)
#                         VALUES('Mac','Mohan',20,'M',2000)"""
# try:
#     cursor.execute(sql)
#     db.commit()
# except:
#     db.rollback()# 数据库查询
# sql = "SELECT * FROM EMPLOYEE \
#        WHERE INCOME > %s" % (1000)
# try:
#     cursor.execute(sql)
#     results = cursor.fetchall()
#     for row in results:
#         fname = row[0]
#         lname = row[1]
#         age = row[2]
#         sex = row[3]
#         income = row[4]
#         print("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % (fname,lname,age,sex,income))
# except:
#     print("Error: unable to fetch data")# 数据库更新操作
# sql = "UPDATE EMPLOYEE SET AGE=AGE+1 WHERE SEX = '%c'" % ('M')
# try:
#     cursor.execute(sql)
#     db.commit()
# except:
#     db.rollback()# 删除操作
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:cursor.execute(sql)db.commit()
except:db.rollback()
# 6.关闭数据库连接
db.close()

连接MongoDB

安装pymongo驱动

pip3 install pymongo

在MongoDB创建库及数据

注意: 在 MongoDB 中，集合只有在内容插入后才会创建! 就是说，创建集合(数据表)后要再插入一个文档(记录)，集合才会真正创建。

import pymongo# 创建数据库
myclient = pymongo.MongoClient("mongodb://localhost:27017/")# 创建数据库
mydb = myclient["runoobdb"]#判断数据库是否存在
# dblist = myclient.list_database_names()
# if "runoobdb" in dblist:
#     print("数据库已存在")# 创建集合
mycol = mydb["sites"]# 插入数据
mydict = {"name":"RUNOOB","alexa":"1000","url":"https://www.runoob.com"}
x = mycol.insert_one(mydict)#打印返回结果
print(x)

插入文档

import pymongo# # 创建数据库
# myclient = pymongo.MongoClient("mongodb://localhost:27017/")
# # 创建数据库:有则引用，无则创建
# mydb = myclient["runoobdb"]
# #判断数据库是否存在
# dblist = myclient.list_database_names()
# if "runoobdb" in dblist:
#     print("数据库已存在")
# mycol = mydb["sites"]
# # 返回_id字段
# mydict = {"name":"Google","alexa":"1","url":"https://www.google.com"}
# x = mycol.insert_one(mydict)
# #打印_id
# print(x.inserted_id)# 插入多个文档
# myclient = pymongo.MongoClient("mongodb://localhost:27017/")
# mydb = myclient["runoobdb"]
# mycol = mydb["sites"]
# mylist = [
#     {"name":"Taobao","alexa":"100","url":"https://www.taobao.com"},
#     {"name":"QQ","alexa":"101","url":"https://www.qq.com"}
# ]
#
# x = mycol.insert_many(mylist)
#
# print(x.inserted_ids)# 插入指定_id的多个文档
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]mylist = [{"_id":1,"name":"Facebook","address":"脸书"},{"_id":2,"name":"Taobao","address":"淘宝"}
]x = mycol.insert_many(mylist)print(x.inserted_ids)

查询数据

import pymongo
from pymongo.response import PinnedResponsemyclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]# 查询单条数据
# x = mycol.find_one()
# print(x)# 查询集合中所有数据
# for x in mycol.find():
#     print(x)# 查询指定字段数据,返回字段指定为1，非返回字段指定为0
# 除了 _id，你不能在一个对象中同时指定 0 和 1，如果你设置了一个字段为 0，则其他都为 1，反之亦然。
# for x in mycol.find({},{"_id":0,"name":1,"alexa":1}):
#     print(x)
# for x in mycol.find({},{"alexa":0}):
#     print(x)# 指定条件查询
# myquery = {"name":"RUNOOB"}
# mydoc = mycol.find(myquery)
#
# for x in mydoc:
#     print(x)# 高级查询 第一个字母 ASCII 值大于 "H" 的数据
# myquery = {"name":{"$gt":"H"}}
# mydoc = mycol.find(myquery)
# for x in mydoc:
#     print(x)# 使用正则表达式
# myquery = {"name":{"$regex":"^R"}}
# mydoc = mycol.find(myquery)
# for x in mydoc:
#     print(x)# 返回指定条数记录
myresult = mycol.find().limit(3)for x in myresult:print(x)

修改数据

import  pymongomyclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]# myquery = {"alexa":"1000"}
# newvalues = {"$set":{"alexa":"12345"}}
#
# mycol.update_one(myquery,newvalues)
#
# for x in mycol.find():
#     print(x)myquery = {"name":{"$regex":"^F"}}
newvalues = {"$set":{"alexa":"123"}}
x = mycol.update_many(myquery,newvalues)
print(x.modified_count,"条文档已修改")

文档排序

import pymongomyclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
# 对字段alexa升序排序
# mydoc = mycol.find().sort("alexa")
#
# for x in mydoc:
#     print(x)# 对字段alexa降序排序
mydoc = mycol.find().sort("alexa",-1)
for x in mydoc:print(x)

删除数据

import pymongomyclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]# myquery = {"name":"RUNOOB"}
# # 删除数据
# mycol.delete_one(myquery)
# # 查询数据
# for x in mycol.find():
#     print(x)# 删除多个文档
# myquery = {"name":{"$regex":"^F"}}
# x = mycol.delete_many(myquery)
# print(x.deleted_count,"个文档已删除")# 删除集合中的所有文档
# x = mycol.delete_many({})
# print(x.deleted_count,"个文档已删除")#删除集合
mycol.drop()