Python MySQL数据库访问
数据库接口的Python标准是Python DB-API。大多数Python数据库接口都遵守此标准。
你可以为你的应用程序选择正确的数据库。 Python Database API支持各种数据库服务器,例如:
- GadFly
- mSQL
- MySQL
- PostgreSQL的
- Microsoft SQL Server 2000
- Informix
- 基地间
- Oracle
- Sybase
以下是可用的Python数据库接口的列表: Python数据库接口和API 。你必须为需要访问的每个数据库下载一个单独的DB API模块。例如,如果你需要访问Oracle数据库和MySQL数据库,则必须同时下载Oracle和MySQL数据库模块。
DB API为使用Python结构和语法处理数据库提供了最低标准。该API包括以下内容:
- 导入API模块。
- 获取与数据库的连接。
- 发出SQL语句和存储过程。
- 断开连接
我们将使用MySQL学习所有概念,因此让我们谈谈MySQLdb模块。
什么是MySQLdb?
MySQLdb是用于从Python连接到MySQL数据库服务器的接口。它实现了Python数据库API v2.0,并基于MySQL C API构建。
如何安装MySQLdb?
在继续之前,请确保已在计算机上安装了MySQLdb。只需在你的Python脚本中键入以下内容并执行即可:
#!/usr/bin/python import MySQLdb
如果产生以下结果,则表示未安装MySQLdb模块:
Traceback (most recent call last): File "test.py", line 3, in <module> import MySQLdb ImportError: No module named MySQLdb
要安装MySQLdb模块,请使用以下命令:
For Ubuntu, use the following command - $ sudo apt-get install python-pip python-dev libmysqlclient-dev For Fedora, use the following command - $ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc For Python command prompt, use the following command - pip install MySQL-python
Note :确保你具有root特权才能安装上述模块。
数据库连接
连接到MySQL数据库之前,请确保以下几点:
-
你已经创建了数据库TESTDB。
-
你已经在TESTDB中创建了一个表EMPLOYEE。
-
该表包含字段FIRST_NAME,LAST_NAME,AGE,SEX和INCOME。
-
用户ID“ testuser”和密码“ test123”设置为访问TESTDB。
-
Python模块MySQLdb已正确安装在你的计算机上。
-
你已经通过MySQL教程来了解 MySQL基础。
以下是连接MySQL数据库“ TESTDB”的示例
#!/usr/bin/python import MySQLdb # Open database connection db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # prepare a cursor object using cursor() method cursor = db.cursor() # execute SQL query using execute() method. cursor.execute("SELECT VERSION()") # Fetch a single row using fetchone() method. data = cursor.fetchone() print "Database version : %s " % data # disconnect from server db.close()
在运行此脚本时,它将在我的Linux机器上产生以下结果。
Database version : 5.0.45
如果与数据源建立了连接,则返回连接对象并将其保存到 db 进一步使用,否则 db 设置为无。下一个, db 对象用于创建一个 cursor 对象,依次用于执行SQL查询。最后,在发布之前,它确保关闭数据库连接并释放资源。
创建数据库表
建立数据库连接后,我们准备使用以下方法在数据库表中创建表或记录 execute 创建的游标的方法。
让我们创建数据库表EMPLOYEE:
#!/usr/bin/python import MySQLdb # Open database connection db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # prepare a cursor object using cursor() method cursor = db.cursor() # Drop table if it already exist using execute() method. cursor.execute("DROP TABLE IF EXISTS EMPLOYEE") # Create table as per requirement sql = """CREATE TABLE EMPLOYEE ( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )""" cursor.execute(sql) # disconnect from server db.close()
插入操作
要在数据库表中创建记录时需要它。
下面的例子,执行SQL INSERT 创建一条记录到EMPLOYEE表中的语句:
#!/usr/bin/python import MySQLdb # Open database connection db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # prepare a cursor object using cursor() method cursor = db.cursor() # Prepare SQL query to INSERT a record into the database. sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Mac', 'Mohan', 20, 'M', 2000)""" try: # Execute the SQL command cursor.execute(sql) # Commit your changes in the database db.commit() except: # Rollback in case there is any error db.rollback() # disconnect from server db.close()
上面的示例可以编写如下,以动态创建SQL查询:
#!/usr/bin/python import MySQLdb # Open database connection db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # prepare a cursor object using cursor() method cursor = db.cursor() # Prepare SQL query to INSERT a record into the database. sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \ LAST_NAME, AGE, SEX, INCOME) \ VALUES ('%s', '%s', '%d', '%c', '%d' )" % \ ('Mac', 'Mohan', 20, 'M', 2000) try: # Execute the SQL command cursor.execute(sql) # Commit your changes in the database db.commit() except: # Rollback in case there is any error db.rollback() # disconnect from server db.close()
以下代码段是另一种执行形式,你可以在其中直接传递参数:
.................................. user_id = "test123" password = "password" con.execute('insert into Login values("%s", "%s")' % \ (user_id, password)) ..................................
读操作
任何数据库上的READ操作都意味着从数据库中获取一些有用的信息。
建立我们的数据库连接后,你就可以对这个数据库进行查询了。你可以使用 fetchone() 提取单个记录的方法或 fetchall() fetech从数据库表中获取多个值的方法。
-
fetchone() :获取查询结果集的下一行。结果集是当使用游标对象查询表时返回的对象。
-
fetchall() :提取结果集中的所有行。如果已经从结果集中提取了一些行,则它将检索 结果集中的其余行。
-
rowcount :这是一个只读属性,返回受execute()方法影响的行数。
以下过程从薪水超过1000的EMPLOYEE表中查询所有记录:
#!/usr/bin/python import MySQLdb # Open database connection db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # prepare a cursor object using cursor() method cursor = db.cursor() sql = "SELECT * FROM EMPLOYEE \ WHERE INCOME > '%d'" % (1000) try: # Execute the SQL command cursor.execute(sql) # Fetch all the rows in a list of lists. results = cursor.fetchall() for row in results: fname = row[0] lname = row[1] age = row[2] sex = row[3] income = row[4] # Now print fetched result print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \ (fname, lname, age, sex, income ) except: print "Error: unable to fecth data" # disconnect from server db.close()
这将产生以下结果:
fname=Mac, lname=Mohan, age=20, sex=M, income=2000
更新操作
UPDATE对任何数据库进行的操作意味着更新一个或多个记录,这些记录已在数据库中可用。
以下过程将所有具有SEX的记录更新为 'M' 。在这里,我们将所有男性的年龄提高了一年。
#!/usr/bin/python import MySQLdb # Open database connection db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # prepare a cursor object using cursor() method cursor = db.cursor() # Prepare SQL query to UPDATE required records sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M') try: # Execute the SQL command cursor.execute(sql) # Commit your changes in the database db.commit() except: # Rollback in case there is any error db.rollback() # disconnect from server db.close()
删除操作
要从数据库中删除某些记录时,需要执行DELETE操作。以下是从AGE大于20的EMPLOYEE中删除所有记录的过程:
#!/usr/bin/python import MySQLdb # Open database connection db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # prepare a cursor object using cursor() method cursor = db.cursor() # Prepare SQL query to DELETE required records sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20) try: # Execute the SQL command cursor.execute(sql) # Commit your changes in the database db.commit() except: # Rollback in case there is any error db.rollback() # disconnect from server db.close()
执行交易
事务是一种确保数据一致性的机制。交易具有以下四个属性:
-
原子性 :要么交易完成,要么什么都没有发生。
-
一致性 :事务必须以一致的状态开始,并使系统保持一致的状态。
-
隔离 :交易的中间结果在当前交易之外不可见。
-
耐用性 :一旦提交了事务,即使在系统故障之后,效果也将持续存在。
Python DB API 2.0提供了两种方法之一 commit or rollback 交易。
你已经知道如何实现事务。这又是类似的例子:
# Prepare SQL query to DELETE required records sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20) try: # Execute the SQL command cursor.execute(sql) # Commit your changes in the database db.commit() except: # Rollback in case there is any error db.rollback()
提交操作
Commit是一项操作,它向数据库发出绿色信号以完成更改,并且在执行此操作后,将无法还原任何更改。
这是一个简单的例子 commit method.
db.commit()
回滚操作
如果你对一项或多项更改不满意,并且想要完全还原这些更改,请使用 rollback() method.
这是一个简单的例子 rollback() method.
db.rollback()
断开数据库
若要断开数据库连接,请使用close()方法。
db.close()
如果用户使用close()方法关闭了与数据库的连接,则所有未完成的事务都将由数据库回滚。但是,最好不要显式地调用commit或rollback,而不是依赖于任何数据库较低级别的实现细节。
处理错误
错误的来源很多。一些示例是已执行的SQL语句中的语法错误,连接失败或为已取消或完成的语句句柄调用fetch方法。
DB API定义了每个数据库模块中必须存在的许多错误。下表列出了这些例外。
序号 | 例外与说明 |
---|---|
1 |
Warning 用于非致命问题。必须将StandardError子类化。 |
2 |
Error 错误的基类。必须将StandardError子类化。 |
3 |
InterfaceError 用于数据库模块中的错误,而不是数据库本身。必须子类化Error。 |
4 |
数据库错误 用于数据库中的错误。必须子类化Error。 |
5 |
数据错误 DatabaseError的子类,它引用数据中的错误。 |
6 |
OperationalError DatabaseError的子类,它引用诸如与数据库的连接断开之类的错误。这些错误通常不在Python脚本编写者的控制范围内。 |
7 |
完整性错误 DatabaseError的子类,用于可能破坏关系完整性的情况,例如唯一性约束或外键。 |
8 |
内部错误 DatabaseError的子类,它引用数据库模块内部的错误,例如游标不再处于活动状态。 |
9 |
编程错误 DatabaseError的子类,它引用诸如错误的表名之类的错误以及可以安全地归咎于你的其他事物。 |
10 |
NotSupportedError DatabaseError的子类,该子类涉及尝试调用不受支持的功能。 |
你的Python脚本应该处理这些错误,但是在使用上述任何异常之前,请确保MySQLdb支持该异常。你可以通过阅读DB API 2.0规范来获取有关它们的更多信息。