西部数码主机 | 阿里云主机| 虚拟主机 | 服务器 | 返回乐道官网
当前位置: 主页 > 开发教程 > python教程 >

python操作MySQL方法流程

时间:2016-01-14 09:14来源:未知 作者:好模板 点击:
作为爱上python的MySQL DBA来说,可以使用python操作MySQL是在是一件太过瘾的事情,但是一段时间不哟个MySQLdb的库,就会忘记怎么使用python操作MySQL了,所以记忆力太差,我就只能把我的M

作为爱上python的MySQL DBA来说,可以使用python操作MySQL是在是一件太过瘾的事情,但是一段时间不哟个MySQLdb的库,就会忘记怎么使用python操作MySQL了,所以记忆力太差,我就只能把我的MySQLdb操作记录记载下来,便于以后的快速使用和学习啦

# 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)

(责任编辑:好模板)
顶一下
(0)
0%
踩一下
(0)
0%
------分隔线----------------------------
栏目列表
热点内容