pymysql连接数据库



创建数据库
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)