python操作mysql

標籤：

Python 操作 Mysql 模組的安裝

linux：

     yum install MySQL - python
　　安裝  python-mysqldb　　

tar zxf MySQL-python-1.2.3c1.tar.gzcd MySQL-python-1.2.3c1python setup.py install  

SQL常用操作：

1、資料庫操作

show databases;use [databasename];create database  [name];

2、資料表操作

show tables; 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 "-"    );

3、資料操作　　

insert into students(name,sex,age,tel) values(‘alex‘,‘man‘,18,‘151515151‘) delete from students where id =2; update students set name = ‘sb‘ where id =1; select * from students

##############查詢select######################

fetchall

import MySQLdb      #匯入串連資料的模組conn = MySQLdb.connect(host=‘10.10.50.30‘,user=‘root‘,passwd=‘boyojoy.com‘,db=‘yangmv‘)#cur = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)    #以字典的格式返回結果,預設是元組cur = conn.cursor()reCount = cur.execute(‘select * from user‘) #execute得到執行命令結果的行數data = cur.fetchall()                       #fetchall()擷取資料庫中所有資料cur.close()conn.close()print reCountprint data

　　

fetchone,fetchmany

import MySQLdbconn = MySQLdb.connect(host=‘127.0.0.1‘,user=‘root‘,passwd=‘1234‘,db=‘mydb‘)cur = conn.cursor()reCount = cur.execute(‘select * from user‘)print cur.fetchone()    #執行一次，返回第一條資料print cur.fetchone()    #每次二次，返回第二條資料print cur.fetchmany(4)  #擷取前4條資料#指標定位cur.scroll(0,mode=‘absolute‘)   #絕對位置，0座標cur.scroll(-1,mode=‘relative‘)  #相對位置，當前位置左移動1print cur.fetchone()cur.close()conn.close()

　　

 ############## 插入insert#######################

import MySQLdbconn = MySQLdb.connect(host=‘10.10.50.30‘,user=‘root‘,passwd=‘boyojoy.com‘,db=‘yangmv‘)cur = conn.cursor()sql = "insert into user (name,address) value(%s,%s)"    #sql語句params = (‘jay‘,‘taiwan‘)reCount = cur.execute(sql,params)      #插入一條資料conn.commit()                          #提交至mysql生效#cur.lastrowid                         #擷取插入資料的自增IDcur.close()conn.close()print reCount   #查看成功執行了幾條命令

　　

批量插入資料

import MySQLdbconn = MySQLdb.connect(host=‘10.10.50.30‘,user=‘root‘,passwd=‘boyojoy.com‘,db=‘yangmv‘)cur = conn.cursor()li =[     (‘alex‘,‘usa‘),(‘sb‘,‘japan‘),]reCount = cur.executemany(‘insert into user(name,address) values(%s,%s)‘,li)conn.commit()cur.close()conn.close()print reCount

　　

  ############## 刪除delete#######################

import MySQLdbconn = MySQLdb.connect(host=‘10.10.50.30‘,user=‘root‘,passwd=‘boyojoy.com‘,db=‘yangmv‘)cur = conn.cursor()sql = "delete from user where id = %s"    #sql語句params = (1,)                           #刪除id為1的資料reCount = cur.execute(sql,params)      #刪除一條資料conn.commit()cur.close()conn.close()print reCount

　　

 ############## 更改update#######################

import MySQLdbconn = MySQLdb.connect(host=‘10.10.50.30‘,user=‘root‘,passwd=‘boyojoy.com‘,db=‘yangmv‘)cur = conn.cursor()sql = "update user set name = %s where name = ‘wj‘" #把wj更改為yangmvparams = (‘yangmv‘,)reCount = cur.execute(sql,params)      #修改資料conn.commit()cur.close()conn.close()print reCount

　　

 

 

python操作mysql