京东-优惠雷达
新人页面
精选商品
首月0月租体验,领12个月京东PLUS
自营热卖

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

情定无名指 24天前   阅读数 43 0

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

一、模块的安装

在命令行里面输入:

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)
db.commit()
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_con.commit()  # must commit
        # attention:here we must commit the data,
        # or, the database will not change
    except:
        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_con.commit()
        # must commit
    except:
        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_con.commit()
        # must commit
    except:
        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 data
    try:
        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_con.commit()
        # must commit
    except:
        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_con.commit()
    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 connection


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_con.commit()  # must commit
        # attention:here we must commit the data,
        # or, the database will not change
    except:
        print("error!")
    return None


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_con.commit()
        # must commit
    except:
        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_con.commit()
        # must commit
    except:
        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 data
    try:
        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


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_con.commit()
        # must commit
    except:
        print("error!")
    return None


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_con.commit()
    except:
        print("error!")
    return None


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


if __name__ == '__main__':
    """ main -> test """
    con = connecting(db_name="myDemo")
    cursor = con.cursor()
    # get the cursor
    insert_one("baidu", "www.baidu.com", cursor, con)
    # insert one data
    insert_many([("csdn", "blog.csdn.net"), ("hyx", "hyxmoon.blog.csdn.net"), ("zhihu", "www.zhihu.com"),
                 ("douban", "www.doubai.com"), ("bilibili", "www.bilibili.com"), ("taobao", "www.taobao.com")],
                cursor, con)
    # insert many data 
    update_data("https://www.baidu.com", "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的具体操作方法以及思路。

谢谢大家的阅读,

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


注意:本文归作者所有,未经作者允许,不得转载

全部评论: 0

    我有话说: