当前位置：首页 > article >正文

python基础知识(六)

article 2024/12/25 11:04:00

文章目录

连接Mysql数据库
- 安装Mysql数据库
- 连接数据库
- - 创建数据库
  - 创建数据表
  - 查询表是否存在
  - 设置主键
  - 插入数据
  - 批量插入
  - 查询、删除、更新数据
使用PyMySql连接数据库
- 安装PyMySql
- 连接数据库
连接MongoDB
- 安装pymongo驱动
- 在MongoDB创建库及数据
- 插入文档
- 查询数据
- 修改数据
- 文档排序
- 删除数据

连接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.connector

mydb = 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.connector

mydb = 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.connector

mydb = 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.connector

mydb = 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.connector

mydb = 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.connector

mydb = 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.connector

mydb = 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 PinnedResponse

myclient = 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  pymongo

myclient = 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 pymongo

myclient = 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 pymongo

myclient = 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()

查看全文

http://www.kler.cn/a/450625.html