1,连接数据库
import pymysql
#连接数据库
#参数1:MySQL服务所在的主机ip
#参数2:用户名
#参数3:密码
#参数4: 要连接的数据库名
#参数5: 服务的端口号
#db = pymysql.connect("localhost","root","123.xxx","yichen",3308)
db = pymysql.connect("192.168.1.132","root","123.xxx","yichen",3308)
#创建一个cursor对象
cursor = db.cursor()
sql = "select version()"
#执行sql语句
cursor.execute(sql)
#获取返回的信息
data =cursor.fetchone()
print(data)
#断开
cursor.close()
db.cursor()
2,创建数据库表
import pymysql
db = pymysql.connect("192.168.1.132","root","123.xxx","yichen",3308)
cursor = db.cursor()
#检查表是否存在,如果存在则删除
cursor.execute("drop table if exists bandcard")
#建表
sql = "create table bandcard(id int auto_increment primary key, money int not null)"
cursor.execute(sql)
#断开
cursor.close()
db.cursor()
3,数据库插入数据
import pymysql
db = pymysql.connect("192.168.1.132","root","123.xxx","yichen",3308)
cursor = db.cursor()
#插入数据
sql = "insert into bandcard values(0,100)"
try:
cursor.execute(sql)
db.commit()
except:
#如果提交失败,回滚到上一次数据
db.rollback()
#断开
cursor.close()
db.cursor()
4,数据库更新操作
import pymysql
db = pymysql.connect("192.168.1.132","root","123.xxx","yichen",3308)
cursor = db.cursor()
#插入数据
sql = "update bandcard set money=1000 where id=1"
try:
cursor.execute(sql)
db.commit()
except:
#如果提交失败,回滚到上一次数据
db.rollback()
#断开
cursor.close()
db.cursor()
5,数据库删除操作
import pymysql
db = pymysql.connect("192.168.1.132","root","123.xxx","yichen",3308)
cursor = db.cursor()
#插入数据
sql = "delete from bandcard where money = 1000"
try:
cursor.execute(sql)
db.commit()
except:
#如果提交失败,回滚到上一次数据
db.rollback()
#断开
cursor.close()
db.cursor()
6,数据库查询操作
import pymysql
'''
fetchone()
功能:获取下一个查询结果集,结果集是一个对象
fetchall()
功能:接收全部的返回的行
rowcount: 是一个只读属性,返回execute()方法影响的行数
'''
db = pymysql.connect("192.168.1.132","root","123.xxx","yichen",3308)
cursor = db.cursor()
sql = "select * from bandcard where money>400"
try:
cursor.execute(sql)
reslist =cursor.fetchall()
for row in reslist:
print("%d---%d"%(row[0],row[1]))
except:
db.rollback()
#断开
cursor.close()
db.cursor()
7,封装yichenSql
import pymysql
class YichenSql():
def __init__(self,host,user,passwd,dbName,port):
self.host = host
self.user = user
self.passwd = passwd
self.dbName =dbName
self.port =port
def connet(self):
self.db= pymysql.connect(self.host,self.user,self.passwd,self.dbName,self.port)
self.cursor = self.db.cursor()
def close(self):
self.cursor.close()
self.db.close()
def get_one(self,sql):
res = None
try:
self.connet()
self.cursor.execute(sql)
res = self.cursor.fetchone()
self.close()
except:
print("查询失败")
return res
def get_all(self,sql):
res = None
try:
self.connet()
self.cursor.execute(sql)
res = self.cursor.fetchall()
self.close()
except:
print("查询失败")
return res
def insert(self,sql):
return self.__edit(sql)
def update(self,sql):
return self.__edit(sql)
def delete(self):
return self.__edit(sql)
def __edit(self,sql):
count = 0
try:
self.connet()
count = self.cursor.execute(sql)
self.db.commit()
self.close()
except:
print("事物提交失败")
self.db.rollback()
8,测试封装的库
from yichenSql import YichenSql
#创建对象
s = YichenSql("192.168.1.132","root","123.xxx","yichen",3308)
res = s.get_all("select * from bandcard where money>400")
for row in res:
print("%d---%d" % (row[0], row[1]))