数据库日期录入-excel快速录入数据

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

文章目录

1.模块安装

在命令行中输入：

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

下面是连接数据库，创建数据库，创建表，插入一条数据的例子。

从上面的代码可以看出数据库日期录入，在使用Python操作MySQL的时候，其实还是比较简单的。 只要知道MySQL的命令行操作，我们就可以轻松操作Python的MySQL数据库。

如果你还不了解MySQL的命令行操作，可以参考以下文章：

今天小姐姐问我❤️MySQL命令行操作❤️

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

三、Python操作MySQL的具体操作 1、创建数据库和表

虽然Python可以自己建库建表，但我还是倾向于在命令行建库建表，然后直接在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

使用数据库后，关闭数据库是一个很好的习惯。

4.完成代码并测试

下面给出了测试的所有代码和主要功能。

"""
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的具体操作方法和思路。

感谢大家的阅读，

希望对大家有所帮助。