Python中的SQL库：MySQL

2020-05-14 20:40:27 浏览数 (1)

在上一篇《Python中的SQL库：SQLite》中，已经对数据库的基本操作有所概括，并且用SQLite做了演示。本文将重点介绍在Python程序中如何操作MySQL。

本文中所用的数据库，其基本结构与上一篇文章相同，此处不再赘述。

连接数据库

对于MySQL而言，与SQLite不同之处在于Python中没有内置的模块，如果要连接MySQL数据库，需要安装第三方的模块。这方面的模块比较多，比如本文使用的mysql-connector-python只是其中之一。

首先，要安装这个模块。

代码语言：javascript复制
pip install mysql-connector-python

然后，编写创建连接MySQL的函数：

代码语言：javascript复制
import mysql.connector
from mysql.connector import Error

def create_connection(host_name, user_name, user_password):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password
        )
        print('Connection to MySQL DB successful')
    except Error as e:
        print(f"The error '{e}' occurred.")

    return connection
    
connection = create_connection("localhost", 'root', "12345678")

上面的函数汇总，有三个需要提供的参数：

  • host_name：MySQL服务的远程地址，如果是使用的本地服务，则可以写成localhost
  • user_name：登录MySQL的用户名，本例中直接使用root
  • user_password：即root对应的密码

通过上述参数，利用mysql.connector.connect()函数建立起数据库连接，并且返回连接对象。

然后，可以创建数据库，为此编写如下函数：

代码语言：javascript复制
def create_database(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        print("Database create successfully")
    except Error as e:
        print(f"The error '{e}' occurred.")

create_database_query = "CREATE DATABASE readers"
create_database(connection, create_database_query)

函数create_database的参数connection是前面已经创建的连接对象，query是SQL语句，利用游标对象的execute方法执行SQL语句。如此，创建了名为readers的MySQL数据库。

然后，重写create_connection函数，在其中指定所要连接的具体数据库。

代码语言：javascript复制
import mysql.connector
from mysql.connector import Error

def create_connection(host_name, user_name, user_password, db_name):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=db_name
        )
        print('Connection to MySQL DB successful')
    except Error as e:
        print(f"The error '{e}' occurred.")

    return connection

connection = create_connection("localhost", 'root', "12345678", 'readers')

这样，就得到了链接数据库readers的连接对象connection。

创建数据库表

前面已经看到，要在Python中执行各种SQL语句，需要通过cursor.execute()函数。下面就创建一个专门的函数，来完成各种SQL语句的操作。

代码语言：javascript复制
def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query executed successfully")
    except Error as e:
        print(f"The error '{e}' occurred")

这个函数完成的，其实是所有针对数据库的“写入”操作。

例如，如下是创建users表的SQL语句：

代码语言：javascript复制
create_users_table = """
CREATE TABLE IF NOT EXISTS users(
    id INT AUTO_INCREMENT,
    name TEXT NOT NULL,
    age INT,
    gender TEXT,
    PRIMARY KEY (id)
) ENGINE = InnoDB
"""

通过此语句，将创建users数据库表，其中包括的字段有：id, name, age, gender，然后用下面的方式，调用函数：

代码语言：javascript复制
execute_query(connection, create_users_table)

如此，即创建了users数据库表，并具有前述个字段。

同样方法，编写创建posts表的SQL语句字符串：

代码语言：javascript复制
create_posts_table = """
CREATE TABLE IF NOT EXISTS posts (
  id INT AUTO_INCREMENT,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  user_id INTEGER NOT NULL,
  FOREIGN KEY fk_user_id (user_id) REFERENCES users(id),
  PRIMARY KEY (id)
) ENGINE = InnoDB
"""

execute_query(connection, create_posts_table)

在上一篇中已经说过，users和posts两张表之间是一对多的关系，在posts表中就增加了一个名为user_id的字段作为外键，它的值与users表中的id字段值对应。

以上操作，与上一篇中SQLite操作类似。

插入

插入记录，当然也是“写入”操作，继续使用前面已经编写的execute_query()函数，我们需要做的就是编写INSERT INTO语句。例如：

代码语言：javascript复制
insert_users = """
INSERT INTO
`users` (`name`, `age`, `gender`)
VALUES
('zhagnsan',23,'male'),
('lisi',24,'female'),
('wangwu',25,'male');
"""

execute_query(connection, insert_users)

上述操作是向users表中增加几条用户记录。

在前面的execute_query()函数中，使用的是游标对象的execute方法，此外，还有另外一个方法，在写入多条记录的时候，速度更快，如下所示：

代码语言：javascript复制
sql = "INSERT INTO likes ( user_id, post_id ) VALUES ( %s, %s )"
val = [(4, 5), (3, 4)]

cursor = connection.cursor()
cursor.executemany(sql, val)
connection.commit()

查询

查询，即“读”的操作，与上一篇的中所写的函数一样。

代码语言：javascript复制
def execute_read_query(connection, query):
    cursor = connection.cursor()
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except Error as e:
        print(f"The error '{e}' occurred")
        
select_users = "SELECT * FROM users"
users = execute_read_query(connection, select_users)
for user in users:
   print(user)

返回结果如下：

代码语言：javascript复制
(1, 'zhagnsan', 23, 'male')
(2, 'lisi', 24, 'female')
(3, 'wangwu', 25, 'male')

更新

对数据库的update，显然是一种“写入”操作，因此，前面编写的execute_query函数对于更新操作依然有效。

代码语言：javascript复制
update_post_description = """
UPDATE
  posts
SET
  description = "The weather has become pleasant now"
WHERE
  id = 2
"""

execute_query(connection, update_post_description)

如果再用前面的方法进行查询，就可以看到更新之后的结果了。

删除

执行删除操作的SQL语句，可以通过前面所创建的execute_query()函数完成。

代码语言：javascript复制
delete_comment = "DELETE FROM comments WHERE id = 2"
execute_query(connection, delete_comment)

以上演示了在Python程序中对MySQL数据库的各项操作，基本上与上一篇中SQLite的操作类似。其实，其他关系型数据库，操作都是如此进行。

云数据库SQLServer linux 数据库 sqlite sql

0 人点赞