[zt]PYTHON MYSQL 数据库交互

beike

浏览: 362006 次

最近访客更多访客>>

malongzl

a1111b2222

去香山看枫叶

mari1224

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

python

Python MySQL SQL Linux GCC

http://www.cnblogs.com/ceniy/archive/2010/08/31/1814066.html

1.确认已经安装了 MySQLdb module


Python 1.5.2 (#1, Feb 1 2000, 16:32:16) [GCC egcs-2.91.66
19990314/Linux
(egcs- on linux-i386
Copyright 1991-1995 Stichting Mathematisch Centrum, Amsterdam
>>> import MySQLdb
Traceback (innermost last):
File "<stdin>", line 1, in ?
ImportError: No module named MySQLdb
>>>

这样的消息说明没有安装MySQLdb, 从http://sourceforge.net/projects/mysql-python下载安装。

$ tar -xzvf MySQL-python-0.9.2.tar.gz
$ cd MySQL-python-0.9.2
$ python setup.py build
$ python setup.py install

再检查：
Python 1.5.2 (#1, Feb 1 2000, 16:32:16) [GCC egcs-2.91.66
19990314/Linux
(egcs- on linux-i386
Copyright 1991-1995 Stichting Mathematisch Centrum, Amsterdam
>>> import MySQLdb
>>> 


然后我们用以下这个数据表：


mysql> SELECT * FROM animals;
+---------+----------+
| name | species |
+---------+----------+
| Wallace | Walrus |
| Polly | Parrot |
| Freddie | Frog |
| Tusker | Elephant |
| Sammy | Skunk |
+---------+----------+
5 rows in set (0.01 sec)

然后用这个短小的python 代码，检索并打印出每条记录


#!/usr/bin/python
# import MySQL module
import MySQLdb
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",
db="db56a")
# create a cursor
cursor = db.cursor()
# execute SQL statement
cursor.execute("SELECT * FROM animals")
# get the resultset as a tuple
result = cursor.fetchall()
# iterate through resultset
for record in result:
print record[0] , "-->", record[1]

然后我们一步一步认识下这个过程，先是 python 导入 MySQLdb module


# import MySQL module
import MySQLdb

之后打开一个数据库连接，需要参数 服务器，用户名，密码，数据库名


# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",
db="db56a")

成功连接后可以得到一个db对象，可以生成游标


# create a cursor
cursor = db.cursor()

这个游标用于执行SQL，并返回得到的结果


# execute SQL statement
cursor.execute("SELECT * FROM animals")
# get the resultset as a tuple
result = cursor.fetchall()

有很多种方法去获得这个SQL 结果，这里使用的是fetchall(),他会返回一个元祖集,它里面的每一个元祖表示一行数据，可以用for进行循环显示每一个数据。

# iterate through resultset
for record in result:
print record[0] , "-->", record[1]

也可以用fetchone()每次去获取一条数据，看下面的例子：


#!/usr/bin/python
# import MySQL module
import MySQLdb
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",
db="db56a")
# create a cursor
cursor = db.cursor()
# execute SQL statement
cursor.execute("SELECT * FROM animals")
# get the number of rows in the resultset
numrows = int(cursor.rowcount)
# get and display one row at a time
for x in range(0,numrows):
row = cursor.fetchone()
print row[0], "-->", row[1]

这里，我们先用游标的rowcount ()方法去获取结果集的条数。然后用在for循环中，去用fetchone（）方法去获取每行数据。
我们还可以用fetchmany()方法去指定获取的条数。


#!/usr/bin/python
# import MySQL module
import MySQLdb
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",
db="db56a")
# create a cursor
cursor = db.cursor()
# execute SQL statement
cursor.execute("SELECT * FROM animals")
# limit the resultset to 3 items
result = cursor.fetchmany(3)
# iterate through resultset
for record in result:
print record[0] , "-->", record[1]

很明显的，你可以执行插入，更新和删除通过 MySQLdb module

#!/usr/bin/python
# import MySQL module
import MySQLdb
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",
db="db56a")
# create a cursor
cursor = db.cursor()
# execute SQL statement
cursor.execute("""INSERT INTO animals (name, species) VALUES ("Harry",
"Hamster")""")

修改一下，我们让用户输入信息，保存到数据库


#!/usr/bin/python
# import MySQL module
import MySQLdb
# get user input
name = raw_input("Please enter a name: ")
species = raw_input("Please enter a species: ")
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",
db="db56a")
# create a cursor
cursor = db.cursor()
# execute SQL statement
cursor.execute("INSERT INTO animals (name, species) VALUES (%s, %s)",
(name, species))

这次运行的时候，你需要输入信息：


Please enter a name: Rollo
Please enter a species: Rat

注意%s位置会被 (name,species)元组的值替换。

如果你有自动增长的字段，你可以使用insert_id()去获取最后一条插入的数据的ID。


#!/usr/bin/python
# import MySQL module
import MySQLdb
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",
db="db56a")
# create a cursor
cursor = db.cursor()
# execute SQL statement
cursor.execute("""INSERT INTO test (field1, field2) VALUES ("val1",
"val2")""")
# get ID of last inserted record
print "ID of inserted record is ", int(cursor.insert_id())

许多数据库脚本涉及到多次的数据操作（比如插入），每次插入大量的不同的值，MySQLdb有一个executemany()方法，可以简化这种操作。

看下面的例子：

#!/usr/bin/python
# import SQL module
import MySQLdb
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",
db="db56a")
# create a cursor
cursor = db.cursor()
# dynamically generate SQL statements from  list
cursor.executemany("INSERT INTO animals (name, species) VALUES (%s,
%s)", [  ('Rollo', 'Rat'),  ('Dudley', 'Dolphin'),  ('Mark', 'Marmoset')
])

这里，同样的操作被执行了很多遍（不同的值），通过传递给executemany()一个Python list，每个元素是一个元组。

用这种方式，我们可以让用户一次输入大量的数据，然后一次性保存。

#!/usr/bin/python
# import MySQL module
import MySQLdb
# initialize some variables
name = ""
data = []
# loop and ask for user input
while (1):
name = raw_input("Please enter a name (EOF to end): ")
if name == "EOF":
break
species = raw_input("Please enter a species: ")
# put user input into a tuple
tuple = (name, species)
# and append to data[] list
data.append(tuple)
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",
db="db56a")
# create a cursor
cursor = db.cursor()
# dynamically generate SQL statements from data[] list
cursor.executemany("INSERT INTO animals (name, species) VALUES (%s,
%s)",
data)

这里我们用while 循环让用户输入信息，保存到values 列表，当用户输入完成的时候，执行executemany（）

MySQLdb 类还有很多其他有用的方法：

connection.begin() - 开始一个事务

connection.apilevel() - returns the current DB APIlevel

connection.conv() - set type conversion options between MySQL and Python

connection.commit() - commit a transaction

connection.rollback() - roll back a transaction

有用的链接：

The MySQLdb project page on SourceForge, athttp://sourceforge.net/projects/mysql-python

The Python home page, at http://www.python.org/

The MySQL home page, at http://www.mysql.com/

Note: All examples in this article have been tested on Linux/i586 with Python 1.5.2, MySQL 3.23 and MySQLdb 0.9.2. Examples are illustrative only, and are not meant for a production environment. Melonfire provides no warranties or support for the source code described in this article. YMMV!