# MySQL中的数据
root@localhost : menu 06:16:42> select * from fish;
+----+---------------+-------+
| ID | NAME          | PRICE |
+----+---------------+-------+
|  1 | catfish       |  8.50 |
|  2 | catfish       |  8.50 |
|  3 | tuna          |  8.00 |
|  4 | catfish       |  5.00 |
|  5 | bass          |  6.75 |
|  6 | haddock       |  6.50 |
|  7 | salmon        |  9.50 |
|  8 | trout         |  6.00 |
|  9 | tuna          |  7.50 |
| 10 | yellowfintuna | 12.00 |
| 11 | yellowfintuna | 13.00 |
| 12 | tuna          |  7.50 |
+----+---------------+-------+
12 rows in set (0.26 sec)

# python中的操作
[root@localhost python]# python
Python 2.7.5 (default, Jun 17 2014, 18:11:42) 
[GCC 4.8.2 20140120 (Red Hat 4.8.2-16)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import tab
>>> import MySQLdb

# python与MySQL建立连接
>>> mydb = MySQLdb.connect(user='root',host='127.0.0.1',port=3306,db='menu')
>>> cur=mydb.cursor()

# 在python中执行sql语句
>>> command = cur.execute('select * from fish')

# 获取sql语句执行的结果
>>> results = cur.fetchall()
>>> print results
((1L, 'catfish', Decimal('8.50')), (2L, 'catfish', Decimal('8.50')), (3L, 'tuna', Decimal('8.00')), (4L, 'catfish', Decimal('5.00')), (5L, 'bass', Decimal('6.75')), (6L, 'haddock', Decimal('6.50')), (7L, 'salmon', Decimal('9.50')), (8L, 'trout', Decimal('6.00')), (9L, 'tuna', Decimal('7.50')), (10L, 'yellowfintuna', Decimal('12.00')), (11L, 'yellowfintuna', Decimal('13.00')), (12L, 'tuna', Decimal('7.50')))

# 对sql语句的结果进行友好式的处理
>>> for record in results:                                  
...     print record[0],'-->',record[1],'@',record[2],'each'
... 
1 --> catfish @ 8.50 each
2 --> catfish @ 8.50 each
3 --> tuna @ 8.00 each
4 --> catfish @ 5.00 each
5 --> bass @ 6.75 each
6 --> haddock @ 6.50 each
7 --> salmon @ 9.50 each
8 --> trout @ 6.00 each
9 --> tuna @ 7.50 each
10 --> yellowfintuna @ 12.00 each
11 --> yellowfintuna @ 13.00 each
12 --> tuna @ 7.50 each

# 在sql语句中添加变量
>>> values=7.50  
>>> command = cur.execute('select * from fish where price = %f'%(values))
>>> results = cur.fetchall()
>>> print results
((9L, 'tuna', Decimal('7.50')), (12L, 'tuna', Decimal('7.50')))
>>> for record in results:                               
...     print record[0],'-->',record[1],'(',record[2],')'
... 
9 --> tuna ( 7.50 )
12 --> tuna ( 7.50 )

# 交互式执行sql语句
>>> operation = raw_input('operation:')
operation:=
>>> values = float(raw_input('values:'))
values:7.50
>>> command = cur.execute('select * from fish where price %s %f'%(operation,values))
>>> results = cur.fetchall()
>>> print results
((9L, 'tuna', Decimal('7.50')), (12L, 'tuna', Decimal('7.50')))
>>> for record in results:
...     print record[0],'-->',record[1],'(',record[2],')'
... 
9 --> tuna ( 7.50 )
12 --> tuna ( 7.50 )

# 提交sql操作
>>> mydb.commit()

# 关闭MySQL连接
>>> mydb.close()

# 在python建立连接后，在MySQL执行show processlist可以看到的连接变化，多增加了id=6的连接
root@localhost : (none) 06:28:09> show processlist;
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+
| Id | User            | Host            | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+
|  1 | event_scheduler | localhost       | NULL | Daemon  |  762 | Waiting on empty queue | NULL             |
|  3 | root            | localhost       | menu | Sleep   |  721 |                        | NULL             |
|  5 | root            | localhost       | NULL | Query   |    0 | init                   | show processlist |
|  6 | root            | 127.0.0.1:33085 | menu | Sleep   |    1 |                        | NULL             |
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+
4 rows in set (0.00 sec)

# 在python中执行mydb.close()后，在MySQL执行show processlist可以看到的连接变化，id=6的连接消失了
root@localhost : (none) 06:28:47> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
|  1 | event_scheduler | localhost | NULL | Daemon  | 2544 | Waiting on empty queue | NULL             |
|  3 | root            | localhost | menu | Sleep   | 2503 |                        | NULL             |
|  5 | root            | localhost | NULL | Query   |    0 | init                   | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
3 rows in set (0.00 sec)