python操作mysql
Python 操作 Mysql 模块的安装
linux:
yum install MySQL
-
python
安装 python-mysqldb
tar zxf MySQL-python-1.2.3c1.tar.gz cd MySQL-python-1.2.3c1 python 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 reCount print data
fetchone,fetchmany
import MySQLdb conn = 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') #相对位置,当前位置左移动1 print cur.fetchone() cur.close() conn.close()
##############插入insert#######################
import MySQLdb conn = 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 #获取插入数据的自增ID cur.close() conn.close() print reCount #查看成功执行了几条命令
批量插入数据
import MySQLdb conn = 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 MySQLdb conn = 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 MySQLdb conn = 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更改为yangmv params = ('yangmv',) reCount = cur.execute(sql,params) #修改数据 conn.commit() cur.close() conn.close() print reCount