python-05

首先是安装工具

Linux

安装mysql：mysql-server
安装python-mysql模块： python-mysqldb

Windows

下载安装mysql
python操作mysql模块：MySQL-python-1.2.3.win32-py2.7.exe 或 MySQL-python-1.2.3.win-amd64-py2.7.exe
mysql图形界面：Navicat_for_MySQL

创建数据库

create table students
    (
        id int  not null auto_increment primary key,
        name char(8) not null,
        sex char(4) not null,
        age tinyint unsigned not null,
        tel char(13) null default "-"
    );

插入一条数据： insert into student  (name,sex,age,tel) values('test','man',19,'123456777')


MySQLdb的操作：
查询

 1 #!/usr/bin/env python
 2 #-*- encoding: utf-8 -*-
 3 
 4 import MySQLdb
 5 
 6 conn = MySQLdb.connect(host='127.0.0.1', user = 'root',passwd ='123456',db='yangshanlei')  #连接mysql
 7 cur = conn.cursor()   #创建游标
 8 
 9 reCount = cur.execute('select * from students')   #查询sql语句
10 data = cur.fetchall()     #把得到的数据都拿出来
11  
12 
13 cur.close()   #关闭游标
14 conn.close()  #关闭连接
15 
16 print reCount    #查询出影响行数
17 print data

查询

insert

 1 #!/usr/bin/env python
 2 #-*- encoding: utf-8 -*-
 3 
 4 import MySQLdb
 5 
 6 conn = MySQLdb.connect(host='127.0.0.1', user = 'root',passwd ='123456',db='yangshanlei')  #连接mysql
 7 cur = conn.cursor()   #创建游标
 8 
 9 sql = "insert into students  (name,sex,age,tel) values(%s,%s,%s,%s)"
10 params = ('yang','man',19,'2222222')    
11 
12 
13 reCount = cur.execute(sql,params)
14 conn.commit()        #insert update delete都需要加上commit
15 
16 cur.close()   #关闭游标
17 conn.close()  #关闭连接
18 
19 print reCount    #查询出影响行数

插入一行

 1 #!/usr/bin/env python
 2 #-*- encoding: utf-8 -*-
 3 
 4 import MySQLdb
 5 
 6 conn = MySQLdb.connect(host='127.0.0.1', user = 'root',passwd ='123456',db='yangshanlei')  #连接mysql
 7 cur = conn.cursor()   #创建游标
 8  
 9 li =[
10      ('www','usa',19,'555'),
11      ('sss','usa',19,'666')
12      ]
13 
14 reCount = cur.executemany("insert into students (name,sex,age,tel) values(%s,%s,%s,%s)",li)  
15 
16 conn.commit()        #insert update delete都需要加上commit
17 cur.close()   #关闭游标
18 conn.close()  #关闭连接
19 
20 print reCount    #查询出影响行数

插入多行

delete

 1 #!/usr/bin/env python
 2 #-*- encoding: utf-8 -*-
 3 
 4 import MySQLdb
 5 
 6 conn = MySQLdb.connect(host='127.0.0.1', user = 'root',passwd ='123456',db='yangshanlei')  #连接mysql
 7 cur = conn.cursor()   #创建游标
 8 
 9 sql = "delete from students where name =%s"
10 params = ('test1',)    
11 
12 
13 reCount = cur.execute(sql,params)
14 conn.commit()        #insert update delete都需要加上commit
15 
16 cur.close()   #关闭游标
17 conn.close()  #关闭连接
18 
19 print reCount    #查询出影响行数

delete

update

 1 #!/usr/bin/env python
 2 #-*- encoding: utf-8 -*-
 3 
 4 import MySQLdb
 5 
 6 conn = MySQLdb.connect(host='127.0.0.1', user = 'root',passwd ='123456',db='yangshanlei')  #连接mysql
 7 cur = conn.cursor()   #创建游标
 8 
 9 sql = "update students set name  = %s  where id = 1"
10 params = ('sb',)    
11 
12 
13 reCount = cur.execute(sql,params)
14 conn.commit()        #insert update delete都需要加上commit
15 
16 cur.close()   #关闭游标
17 conn.close()  #关闭连接
18 
19 print reCount    #查询出影响行数

update

 1 #!/usr/bin/env python
 2 #-*- encoding: utf-8 -*-
 3 import MySQLdb
 4 
 5 class helperall(object):
 6     def __init__(self):
 7         pass
 8 
 9     def Get_Dict(self,sql,params):
10         #conn = MySQLdb.connect('127.0.0.1','root','123456','yangshanlei')
11         conn = MySQLdb.connect(host='127.0.0.1', user = 'root',passwd ='123456',db='yangshanlei')    
12         cur = conn.cursor()
13 
14         reCount = cur.execute(sql,params)
15         date = cur.fetchall()
16 
17         cur.close()
18         conn.close()
19 
20         return date
21 
22     def Get_One(self,sql,params):
23         conn = MySQLdb.connect('127.0.0.1','root','123456','yangshanlei')
24         cur = conn.cursor() #打开游标
25 
26         reCount = cur.execute(sql,params) 
27         data = cur.fetchone()
28 
29         cur.close()
30         conn.close()
31         return data
32 
33     def Get_insertone(self,sql,params):
34         conn = MySQLdb.connect('127.0.0.1','root','123456','yangshanlei')
35         cur = conn.cursor()
36 
37         reCount = cur.execute(sql,params) 
38         conn.commit()
39 
40         cur.close()
41         conn.close()
42         return reCount
43 
44     def Get_insertall(self,sql,li):
45         conn = MySQLdb.connect('127.0.0.1','root','123456','yangshanlei')
46         cur = conn.cursor()      
47 
48         reCount = cur.executemany(sql,li)  
49 
50         conn.commit()        #insert update delete都需要加上commit
51         cur.close()
52         conn.close()
53 
54 '''
55 #插入多次
56 helper = helperall()
57 li =[
58          ('ww1','usa'),
59          ('ss1','usa')
60          ]
61 sql = "insert into students  (name,sex) values(%s,%s)"
62 dicct_insertall = helper.Get_insertall(sql,li)
63 print dicct_insertall
64 '''
65 '''
66 #插入一次
67 helper = helperall()
68 sql = "insert into students  (name,sex) values(%s,%s)"
69 params = ('yangshan','man')
70 dicct_insert = helper.Get_insertone(sql,params)
71 print dicct_insert
72 '''
73 '''
74 #查询
75 helper = helperall()
76 sql = "select *  from students   where id > %s"
77 params = (1,)
78 dict_data = helper.Get_Dict(sql,params)  #查询全部
79 dict_one = helper.Get_One(sql,params)     #查询1次
80 print dict_data
81 print dict_one  
82 '''