python2.x版本使用mysqldb模块
python3.x版本使用pymysql模块
1.数据库常用操作:
show databases; 查看所有数据库
use [databasename]; 进入指定数据库
create database [name]; 创建数据库
创建一个表 进行mysqldb模块操作
#查看表
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 "-"
);
#插入一条
insert into students(name,sex,age,tel) values('yao','man',21,'1771090953x)
#删除一条
delete from students where id =2;
#更新一条
update students set name = 'sb' where id =1;
#查看一条
select * from students
2.python调用Mysqldb操作数据
1.插入一条数据:
#导入模块
import MySQLdb
#创建链接
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='python')
#创建游标
cur = conn.cursor()
#插入一条数据
reCount = cur.execute('insert into UserInfo(Name,Address) values(%s,%s)',('yao','usa'))
#提交
conn.commit()
#关闭指针对象
cur.close()
#关闭连接对象
conn.close()
#打印结果
print (reCount)
2.插入多条数据
#导入模块
import MySQLdb
#创建链接
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
#创建游标
cur = conn.cursor()
li =[
('liuyao','hk'),
('sb','beijing'),
]
#执行操作
reCount = cur.executemany('insert into UserInfo(Name,Address) values(%s,%s)',li)
#提交
conn.commit()
#关闭
cur.close()
#关闭
conn.close()
print (reCount)
3.删除数据
import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
cur = conn.cursor()
reCount = cur.execute('delete from UserInfo')
conn.commit()
cur.close()
conn.close()
print (reCount)
4.修改数据
import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
cur = conn.cursor()
reCount = cur.execute('update UserInfo set Name = %s',('alin',))
conn.commit()
cur.close()
conn.close()
print (reCount)
5.查询数据
#!/usr/bin/python
# encoding: utf-8
import MySQLdb
db = MySQLdb.connect("localhost","root","361way","test" ) # 打开数据库连接
cursor = db.cursor() # 使用cursor()方法获取操作游标
sql = "SELECT * FROM EMPLOYEE WHERE INCOME > '%d'" % (1000)try: # SQL 查询语句
cursor.execute(sql) # 获取所有记录列表
results = cursor.fetchall() for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4] # 打印结果
print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % (fname, lname, age, sex, income )except: print "Error: unable to fecth data"
db.close()# 关闭数据库连接
发表评论