Pymysql

PyMySQL 安装：

在python目录下的Programs文件路径下执行下面的命令

 pip install PyMySQL

pymysql创建表:

import pymysql  #导入pymysql模块
def createtable():
    #1 建立数据库
    conn=pymysql.connect('127.0.0.1','root','123456','student',charset='utf8')
   #2 获得cursor对象
    mycursor=conn.cursor()
    #执行sql语句
    sqlstr='''
        create table test1(
        id int PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(20) NOT NULL,
        sex CHAR(4)
        )
    '''
    mystr=mycursor.execute(sqlstr)
    #  获取执行结果
    result=mycursor.fetchone()
    #关闭
    conn.close()
createtable()

公共的连接方法：

def getconn():
    conn = pymysql.connect("127.0.0.1", "root", "123456", "student",charset='utf8')
    mycursor=conn.cursor()
    return [conn,mycursor]

插入数据：

def inserttable():
    conn=pymysql.connect("localhost","root","123456","house",charset='utf8')
    mycursor=conn.cursor()
    sqlstr='''insert into `hos_district`(did,dName) values('11','朝阳')'''.encode("utf-8")
    try:
        result=mycursor.execute(sqlstr)
        print(result)
        conn.commit()#提交到数据库执行
    except:
        conn.rollback()#如果发生错误则回滚
inserttable()

查询数据：

def querytable():
    conn=getconn()
    sqlstr="select studentNo,studentName,sex,phone from student"
    conn[1].execute(sqlstr)
    rs=conn[1].fetchall()
    for row in rs:
        print("studentNo:{0},studentName:{1},sex:{2},phone:{3}".format(row[0],row[1],row[2],row[3]))
    conn[0].close()
querytable()

删除数据：

def deltable():
    conn=getconn()
    sqlstr='''delete from test1 where id=1'''
    try:
        print(conn[1].execute(sqlstr))
        conn[0].commit()
    except:
        conn[0].rollback()
    conn[0].close()
deltable()

简洁的方法

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 test1 where id=1'''
更新
sqlstr='''update test1 set name='李四' where id=2'''
增
sqlstr='''insert into test1(name,sex) VALUES ('张三','男')'''
operationtable(sqlstr)