• Python如何操作mysql数据库
  • 发布于 2个月前
  • 198 热度
    0 评论
对于任何功能完整的可部署应用程序,数据的持久性是必不可少的。存储数据的一种简单方法是将其写入硬盘中的文件,但是出于显而易见的原因,我们更愿意将特定于应用程序的数据写入数据库。Python为将数据写入广泛的数据库提供了语言支持。

Python 数据库 API

Python对数据库编程的核心支持是Python DB API (PEP - 249),它不依赖于任何特定的数据库引擎。正确的实现方式是导入Python DB API并在程序中使用,这取决于我们在持久层使用的数据库。在本教程中,我们将演示如何使用Python连接MySQL数据库并使用它执行事务。为此,我们将使用MySQLdb Python包。

在使用Python连接数据库之前,我们需要为Python安装MySQL连接器。这可以通过两种方式实现:
一种方法是从MySQL官方网站直接下载相关操作系统和相关位版本的安装程序。
另一种方法是使用pip来安装它
$ pip install mysql-connector-python

如果本地机器上安装了特定的MySQL版本,那么你可能需要一个特定的MySQL 连接器版本,这样就不会出现兼容性问题,我们可以使用以下命令:
$ pip install mysql-connector-python==<insert_version_number_here>

最后,我们需要安装MySQL客户端模块,使我们能够从我们的Python应用程序中连接到MySQL数据库,它将作为客户端:
$ pip install mysqlclient

连接数据库

安装好连接器之后,在执行Python文件时,import MySQLdb语句应该不会抛出任何错误了。

先决条件

注:本文假定读者对数据库概念有基本的理解,并对MySQL数据库有明确的了解,同时具有结构化查询语言(SQL)的相关知识。但是,本节还是解释了创建数据库和用户的基本过程。遵循以下步骤:
1.确保MySQL服务器正在运行。这可以通过MySQL 工作台 ->服务器状态进行检查。
2.打开MySQL工作台或MySQL 命令行。创建一个新数据库,将其命名为pythondb。
CREATE DATABASE pythondb;  
USE pythondb;  
3.创建一个新用户pythonuser,密码为pythonpwd123,并授予对pythondb的访问权限。
CREATE USER 'pythonuser'@'localhost' IDENTIFIED BY 'pythonpwd123'  
GRANT ALL PRIVILEGES ON pythondb.* To 'pythonuser'@'localhost'  
FLUSH PRIVILEGES 
检查到pythondb的连接

下面是一个简单的脚本,可用于以编程方式测试到新创建数据库的连接:
#!/usr/bin/python

import MySQLdb

dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb")

cursor = dbconnect.cursor()  
cursor.execute("SELECT VERSION()")

data = cursor.fetchone()  
if data:  
  print('Version retrieved: ', data)
else:  
  print('Version not retrieved.')

dbconnect.close() 

输出:

Version retrieved: 5.7.19  

上面显示的版本号只是一个简单的数字。它应该匹配已安装MySQL服务器的版本。我们再来仔细看看上面的示例程序,来了解它是如何工作的。首先,import MySQLdb用于导入所需的python模块。MySQLdb.connect()方法使用主机名、用户名、密码和数据库名创建一个数据库连接。成功连接数据库后,它将返回一个连接对象(在这里以dbconnect引用它)。

通过使用连接对象,我们可以在关闭连接之前执行查询、提交事务和回滚事务。

在获得连接对象之后,我们需要获得一个MySQLCursor对象,以便使用execute方法执行查询。可以使用fetchall、fetchone或fetchmany方法检索事务的结果集,本教程后面将讨论这些方法。

除了执execute方法之外,还有三个与数据库事务相关的重要方法。现在我们将简要了解这些方法。

dbconnect.commit()方法通知数据库,在调用此函数之前执行的更改将完成,如果事务成功,则无法回滚到以前状态。

有时,如果发生事务失败,我们需要将数据库更改为失败发生之前的状态,这样数据就不会丢失或损坏。在这种情况下,我们需要使用dbconnect.rollback()将数据库回滚到以前的状态。

最后,使用dbconnect.close()方法关闭到数据库的连接。要执行进一步的事务,我们需要创建一个新连接。

新建一个表

一旦成功地建立了与pythondb的连接,我们就可以开始下一步了。让我们在其中创建一个新表:
import MySQLdb

dbconnect = MySQLdb.connect("localhost","pythonuser","pythonpwd123","pythondb" )

cursor = dbconnect.cursor()  
cursor.execute("DROP TABLE IF EXISTS MOVIE")

query = "CREATE TABLE MOVIE(  \  
          id int(11) NOT NULL,\
          name varchar(20),\
          year int(11),\
          director varchar(20),\
          genre varchar(20),\
          PRIMARY KEY (id))"

cursor.execute(query)

dbconnect.close()  

执行上述脚本之后,你应该能看到一个为名为pythondb的库创建的名为movie的新表。可以使用MySQL 工作台来查看。

执行CRUD操作

现在,我们将通过Python脚本在新创建的数据库表中执行一些插入、读取、修改和删除操作。

创建一条新记录

下面的脚本演示了如何使用Python脚本将一条新记录插入MySQL数据库:
#!/usr/bin/python

import MySQLdb
dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb")
cursor = dbconnect.cursor()
query = 'insert into movie(id, name, year, director, genre)  \  
       values (1, "Bruce Almighty", 2003, "Tom Shaydac", "Comedy")'
try:  
   cursor.execute(query)
   dbconnect.commit()
except:  
   dbconnect.rollback()
finally:  
   dbconnect.close()

从表中读取行

在数据库中插入一个新行之后,可以使用游标对象以三种方式来获取数据:

1. cursor.fetchall() -可用于获取所有行
2. cursor.fetchmany() -可用于获取选定的行
3. cursor.fetchone() -只能用于从结果集中获取第一行

为了简单起见,我们将使用“select all”SQL查询,并在fetchall方法的结果集上使用for循环来打印单个记录
#!/usr/bin/python

import MySQLdb

dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb")

cursor = dbconnect.cursor()

query = "SELECT * FROM movie"  
try:  
   cursor.execute(query)
   resultList = cursor.fetchall()
   for row in resultList:
      print ("Movie ID =", row[0])
      print ("Name =", row[1])
      print ("Year =", row[2])
      print ("Director = ", row[3])
      print ('Genre = ', row[4])
except:  
   print ("Encountered error while retrieving data from database")
finally:  
   dbconnect.close()

输出:

Movie ID = 1  
Name = Bruce Almighty  
Year = 2003  
Director = Tom Shaydac  
Genre = Comedy  


更新行

现在让我们把“Bruce Almighty”的类型从Comedy改为Staire:
import MySQLdb

dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb")

# The cursor object obtained below allows SQL queries to be executed in the database session.
cursor = dbconnect.cursor()

updatequery = "update movie set genre = 'Satire' where id = 1"

cursor.execute(updatequery)

dbconnect.commit()

print(cursor.rowcount, "record(s) affected") 

输出:

1 record(s) affected  


删除记录

下面是一个Python脚本,演示了如何删除一个数据库行:
import MySQLdb

dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb")

# The cursor object obtained below allows SQL queries to be executed in the database session.
cursor = dbconnect.cursor()

updatequery = "DELETE FROM movie WHERE id = 1"

cursor.execute(updatequery)

dbconnect.commit()

print(cursor.rowcount, "record(s) deleted")  

结论

在本文中,我们学习了如何使用Python DB API来连接到数据库。具体来说,我们了解了如何使用Python DB API的MySQLdb实现来建立到MySQL数据库的连接。我们还学习了如何使用数据库来执行事务。
用户评论