创建数据库
import pymysql
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute() 方法执行 SQL,如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 使用预处理语句创建表
sql = """CREATE TABLE EMPLOYEE (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )"""
cursor.execute(sql)
# 关闭数据库连接
db.close()
import pymysql
def createtable():
#创建链接
conn=pymysql.connect("localhost","root","123456","school")
#得到cursor
mycursor=conn.cursor()
#执行sql语句
sqlstr='''
create table person2(
id int PRIMARY key auto_increment,
name VARCHAR(20) not null,
sex char(2)
)
'''
mycursor.execute(sqlstr)
#获取执行的结果,如果创建表的语句则返回none
#关闭连接对象
conn.close()
#createtable()
#数据插入
def inserttable():
conn=pymysql.connect("localhost","root","123456","school")
mycursor=conn.cursor()
sqlstr='''insert into person2(name,sex) values('张三','男')'''.encode("utf-8")
try:
result=mycursor.execute(sqlstr)
print(result)
conn.commit()#提交到数据库执行
except:
conn.rollback()#如果发生错误则回滚
inserttable()
#公共连接
def getconn():
conn = pymysql.connect("localhost", "root", "123456", "school")
mycursor=conn.cursor()
return [conn,mycursor]
#删除
def deltable():
conn=getconn()
sqlstr='''delete from person2 where id=1'''
try:
print(conn[1].execute(sqlstr))
conn[0].commit()
except:
conn[0].rollback()
conn[0].close()
deltable()
#更新
def updatetable():
conn=getconn()
sqlstr='''update person2 set name='李四' where id=2'''
try:
print(conn[1].execute(sqlstr))
conn[0].commit()
except:
conn[0].rollback()
conn[0].close()
updatetable()
#查询
def querytable():
conn=getconn()
sqlstr="select studentNo,studentName,sex,phone from student1"
conn[1].execute(sqlstr)
rs=conn[1].fetchall()
for row in rs:
print("studentNo:%d,studentName:%s,sex:%s,phone:%s"%(row[0],row[1],row[2],row[3]))
conn[0].close()
querytable()
#查询
class Test():
def __init__(self,studentNo=None,studentName=None,sex=None,phone=None):
self.studentNo=studentNo
self.studentName = studentName
self.sex = sex
self.phone = phone
def __str__(self):
return "studentNo:"+str(self.studentNo)+",studentName:"+self.studentName+",sex:"+self.sex+",phone:"+self.phone
def querytable():
list=[]
conn=getconn()
sqlstr="select studentNo,studentName,sex,phone from student1"
conn[1].execute(sqlstr)
rs=conn[1].fetchall()
for row in rs:
test=Test(row[0],row[1],row[2],row[3])
list.append(test)
conn[0].close()
return list
testlist=querytable()
for row in testlist:
print(row)
简便的写法:
#公共连接
def getconn():
conn = pymysql.connect("localhost", "root", "123456", "school")
mycursor=conn.cursor()
return [conn,mycursor]
def operationtable(sqlstr):
conn = getconn()
try:
print(conn[1].execute(sqlstr))
conn[0].commit()
except:
conn[0].rollback()
conn[0].close()
#删除
# sqlstr = '''delete from person2 where id=1'''
#更新
# sqlstr='''update person2 set name='李四' where id=2'''
#增
sqlstr='''insert into person2(name,sex) VALUES ('张三','男')'''
operationtable(sqlstr)
import pymysql
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute() 方法执行 SQL,如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 使用预处理语句创建表
sql = """CREATE TABLE EMPLOYEE (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )"""
cursor.execute(sql)
# 关闭数据库连接
db.close()
import pymysql
def createtable():
#创建链接
conn=pymysql.connect("localhost","root","123456","school")
#得到cursor
mycursor=conn.cursor()
#执行sql语句
sqlstr='''
create table person2(
id int PRIMARY key auto_increment,
name VARCHAR(20) not null,
sex char(2)
)
'''
mycursor.execute(sqlstr)
#获取执行的结果,如果创建表的语句则返回none
#关闭连接对象
conn.close()
#createtable()
#数据插入
def inserttable():
conn=pymysql.connect("localhost","root","123456","school")
mycursor=conn.cursor()
sqlstr='''insert into person2(name,sex) values('张三','男')'''.encode("utf-8")
try:
result=mycursor.execute(sqlstr)
print(result)
conn.commit()#提交到数据库执行
except:
conn.rollback()#如果发生错误则回滚
inserttable()
#公共连接
def getconn():
conn = pymysql.connect("localhost", "root", "123456", "school")
mycursor=conn.cursor()
return [conn,mycursor]
#删除
def deltable():
conn=getconn()
sqlstr='''delete from person2 where id=1'''
try:
print(conn[1].execute(sqlstr))
conn[0].commit()
except:
conn[0].rollback()
conn[0].close()
deltable()
#更新
def updatetable():
conn=getconn()
sqlstr='''update person2 set name='李四' where id=2'''
try:
print(conn[1].execute(sqlstr))
conn[0].commit()
except:
conn[0].rollback()
conn[0].close()
updatetable()
#查询
def querytable():
conn=getconn()
sqlstr="select studentNo,studentName,sex,phone from student1"
conn[1].execute(sqlstr)
rs=conn[1].fetchall()
for row in rs:
print("studentNo:%d,studentName:%s,sex:%s,phone:%s"%(row[0],row[1],row[2],row[3]))
conn[0].close()
querytable()
#查询
class Test():
def __init__(self,studentNo=None,studentName=None,sex=None,phone=None):
self.studentNo=studentNo
self.studentName = studentName
self.sex = sex
self.phone = phone
def __str__(self):
return "studentNo:"+str(self.studentNo)+",studentName:"+self.studentName+",sex:"+self.sex+",phone:"+self.phone
def querytable():
list=[]
conn=getconn()
sqlstr="select studentNo,studentName,sex,phone from student1"
conn[1].execute(sqlstr)
rs=conn[1].fetchall()
for row in rs:
test=Test(row[0],row[1],row[2],row[3])
list.append(test)
conn[0].close()
return list
testlist=querytable()
for row in testlist:
print(row)
简便的写法:
#公共连接
def getconn():
conn = pymysql.connect("localhost", "root", "123456", "school")
mycursor=conn.cursor()
return [conn,mycursor]
def operationtable(sqlstr):
conn = getconn()
try:
print(conn[1].execute(sqlstr))
conn[0].commit()
except:
conn[0].rollback()
conn[0].close()
#删除
# sqlstr = '''delete from person2 where id=1'''
#更新
# sqlstr='''update person2 set name='李四' where id=2'''
#增
sqlstr='''insert into person2(name,sex) VALUES ('张三','男')'''
operationtable(sqlstr)