听说Python可以操作MySQL数据库

操作MySQL数据库"/>

听说Python可以操作MySQL数据库

听说❤️Python可以操作MySQL数据库❤️

文章目录

• • 听说❤️Python可以操作MySQL数据库❤️
  • • 一、模块的安装
    • 二、 实例
    • 三、Python操作MySQL的具体操作
    • • 1、创建 数据库 以及 table
      • 2、连接数据库
      • 3、插入一条数据
      • 4、插入多条数据
      • 5、修改数据
      • 6、查询数据
      • 7、删除一条数据
      • 8、删除多条数据
      • 9、关闭连接
    • 四、完整的代码以及测试

一、模块的安装

在命令行里面输入：

pip install pymysql

这样就安装好了模块了啦。

二、 实例

首先，我们先给一个示例，后面在进行详细的讲解哦，下面先看实例：

db = pymysql.connect(host='localhost',port=3306,user='root',password='1234567890',db='MockServer',charset='utf8'
)# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()# 创建数据库并且使用这个数据库
sql = """SET character_set_database=utf8;
SET character_set_server=utf8;
DROP DATABASE IF EXISTS school;
CREATE DATABASE school;
USE school;"""
lists = sql.split("\n")
for i in lists:cursor.execute(i)# 创建table并且使用这个table
create_sql = """
CREATE TABLE students(sno VARCHAR(32),name VARCHAR(32),age INT
);
"""
cursor.execute(create_sql)
insert_sql = """
INSERT INTO students(sno, name, age) VALUES ('1', '张三', '20');
"""
cursor.execute(insert_sql)
dbmit()
db.close()

这里是连接数据库，创建数据库，创建table，插入一条数据的示例。

从上面的代码可以看出来，使用Python进行MySQL操作的时候，实际上还是比较简单的，只要我们知道了MySQL的命令行操作，就可以很轻松的进行Python的MySQL数据库的操作了啦。

如果还要小伙伴不清楚MySQL的命令行操作的，可以参见如下文章：
这天，小姐姐问我❤️MySQL命令行操作❤️

熟悉了MySQL的命令行操作以后，可以说Python操作MySQL简直是游刃有余了。

三、Python操作MySQL的具体操作

1、创建 数据库 以及 table

虽然Python可以自己创建数据库以及table，但是本人还是倾向于在命令行创建数据库以及table，然后再Python中直接使用就可以了啦。

具体操作入下所示：

2、连接数据库

我们定义一个函数进行数据库的连接：

def connecting(db_name):"""connect to the mysql database,attention:here the database must exist!:return: connection"""connection = pymysql.connect(host="localhost",port=3306,user="root",password="hyx671513",db=db_name)return connection

3、插入一条数据

同样，定义一个函数插入一条数据：

def insert_one(name, url, my_cursor, my_con):"""insert one data into the table:param name: name:param url: url:param my_cursor: cursor:param my_con: con:return: none"""msg_sql = f"insert into table1(name, url) values({name}, {url});"try:rows = my_cursor.execute(msg_sql)print("length:" + rows)my_conmit()  # must commit# attention:here we must commit the data,# or, the database will not changeexcept:print("error!")return None

4、插入多条数据

插入一条数据跟插入多条数据是比较类似的，只不过，这里是需要传递的参数会发生一些变化而已了啦：

def insert_many(name_and_url_list, my_cursor, my_con):"""insert many data into the table:param name_and_url_list: name_and_url_list:param my_cursor: cursor:param my_con: con:return: none"""msg_sql = "insert into table1(name, url) values(%s, %s);"try:rows = my_cursor.execute(msg_sql, name_and_url_list)print("length:" + rows)my_conmit()# must commitexcept:print("error!")return None

5、修改数据

一般而言，我们只会修改一条数据。

# commonly, we just update one data at one time
def update_data(new_url, name, my_cursor, my_con):"""update the data of the table:param new_url: new url:param name: name:param my_cursor: cursor:param my_con: con:return: none"""msg_sql = f"update table1 set url = {new_url} where name = {name};"try:rows = my_cursor.execute(msg_sql)print("length:" + rows)my_conmit()# must commitexcept:print("error!")return None

6、查询数据

一般来讲，我们会一次性查询所有的数据并且获取所有的数据。

# commonly, we will read all the data in the table when we need to read the database
def read_data(my_cursor):"""read the data in the table:param my_cursor: cursor:return: list_data"""list_data = []# record the data and then we can return the datatry:rows = my_cursor.execute("select * from table1;")for i in range(rows):list_data.append(my_cursor.fetchone())print(my_cursor.fetchone())except:print("error!")return list_data

7、删除一条数据

这是删除一条数据的操作，我们需要根据键值对来进行选择具体是那一条数据：

def delete_one(name, my_cursor, my_con):"""delete one data from the table:param name: name:param my_cursor: cursor:param my_con: con:return: none"""msg_sql = f"delete from table1 where name = {name};"try:rows = my_cursor.execute(msg_sql)print("length:" + rows)my_conmit()# must commitexcept:print("error!")return None

8、删除多条数据

删除多条数据与删除一条数据也是差不多的啦，只不过还是有一个参数是需要进行改变的啦。

def delete_many(name_list, my_cursor, my_con):"""delete many data into the table:param name_list: list of the name:param my_cursor: cursor:param my_con: con:return: none"""msg_sql = "delete from table1 where name = %s;"try:rows = my_cursor.execute(msg_sql, name_list)print("length:" + rows)my_conmit()except:print("error!")return None

原则上来讲呢，添加一条或者多条数据可以用一个函数来进行描述，删除一条或者多条数据也可以用一个函数来进行描述，这个大家有兴趣的话可以自行探究一下，其实也是很简单的哦

9、关闭连接

这是关闭连接的操作：

def end_connection(my_cursor, my_con):"""close the connection to the database:param my_cursor: cursor:param my_con: con:return: none"""my_cursor.close()my_con.close()return None

在使用完数据库以后，将数据库进行关闭是一个很不错的好习惯。

四、完整的代码以及测试

下面给出所有的代码以及测试的main函数。

"""python mysql operation"""import pymysql
# import module pymysql"""
according to the reason that every table is not the same,
we do not set the table's name as a variable,
every table should have its own method to add or delete data.
"""def connecting(db_name):"""connect to the mysql database,attention:here the database must exist!:return: connection"""connection = pymysql.connect(host="localhost",port=3306,user="root",password="hyx671513",db=db_name)return connectiondef insert_one(name, url, my_cursor, my_con):"""insert one data into the table:param name: name:param url: url:param my_cursor: cursor:param my_con: con:return: none"""msg_sql = f"insert into table1(name, url) values({name}, {url});"try:rows = my_cursor.execute(msg_sql)print("length:" + rows)my_conmit()  # must commit# attention:here we must commit the data,# or, the database will not changeexcept:print("error!")return Nonedef insert_many(name_and_url_list, my_cursor, my_con):"""insert many data into the table:param name_and_url_list: name_and_url_list:param my_cursor: cursor:param my_con: con:return: none"""msg_sql = "insert into table1(name, url) values(%s, %s);"try:rows = my_cursor.execute(msg_sql, name_and_url_list)print("length:" + rows)my_conmit()# must commitexcept:print("error!")return None# commonly, we just update one data at one time
def update_data(new_url, name, my_cursor, my_con):"""update the data of the table:param new_url: new url:param name: name:param my_cursor: cursor:param my_con: con:return: none"""msg_sql = f"update table1 set url = {new_url} where name = {name};"try:rows = my_cursor.execute(msg_sql)print("length:" + rows)my_conmit()# must commitexcept:print("error!")return None# commonly, we will read all the data in the table when we need to read the database
def read_data(my_cursor):"""read the data in the table:param my_cursor: cursor:return: list_data"""list_data = []# record the data and then we can return the datatry:rows = my_cursor.execute("select * from table1;")for i in range(rows):list_data.append(my_cursor.fetchone())print(my_cursor.fetchone())except:print("error!")return list_datadef delete_one(name, my_cursor, my_con):"""delete one data from the table:param name: name:param my_cursor: cursor:param my_con: con:return: none"""msg_sql = f"delete from table1 where name = {name};"try:rows = my_cursor.execute(msg_sql)print("length:" + rows)my_conmit()# must commitexcept:print("error!")return Nonedef delete_many(name_list, my_cursor, my_con):"""delete many data into the table:param name_list: list of the name:param my_cursor: cursor:param my_con: con:return: none"""msg_sql = "delete from table1 where name = %s;"try:rows = my_cursor.execute(msg_sql, name_list)print("length:" + rows)my_conmit()except:print("error!")return Nonedef end_connection(my_cursor, my_con):"""close the connection to the database:param my_cursor: cursor:param my_con: con:return: none"""my_cursor.close()my_con.close()return Noneif __name__ == '__main__':"""main -> test"""con = connecting(db_name="myDemo")cursor = con.cursor()# get the cursorinsert_one("baidu", "www.baidu", cursor, con)# insert one datainsert_many([("csdn", "blog.csdn"), ("hyx", "hyxmoon.blog.csdn"), ("zhihu", "www.zhihu"),("douban", "www.doubai"), ("bilibili", "www.bilibili"), ("taobao", "www.taobao")],cursor, con)# insert many data update_data("", "baidu", cursor, con)# update the data"""here we will not test the delete_one() and delete_many() method,if you have interest, you can test by your self."""read_data(cursor)# read all the data 

综上，以上即就是Python操作MySQL的具体操作方法以及思路。

谢谢大家的阅读，

希望对大家有一定的帮助了啦。