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