关于pymysql的一些总结

# -*- coding: utf-8 -*-
# File  : 总结性的数据库.py
# Author: HuXianyong
# Date  : 2018-08-16 11:18

import pymysql,re

#这个函数给的是一个表名
def chooseTable(cursor):
    TableName = input(‘请输入你要操作的表名: ‘)
    sql = ‘show tables‘
    cursor.execute(‘show tables‘)
    tables = [cursor.fetchall()]
    tabList = re.findall(‘(\‘.*?\‘)‘, str(tables))
    tabList = [re.sub("‘", ‘‘, each) for each in tabList]
    if TableName not in tabList:
        msg = ‘‘‘
        你输入的表名不存在,是否创建这个表?
        创建请输入y
        不创建请任意输入
        ‘‘‘
        choose = input(msg)
        if choose == ‘y‘:
            print(‘这个功能暂时技术还不够先到这里!‘)
            pass
            # sql = ‘create table %s()‘
        else:
            print(‘没有表就不能操作,咱么拜拜了啊!‘)
            exit()
    return TableName

class operationTab():
    ‘‘‘
    alter和update都是修改,但是二者是有区别的!
    alter用来修改基本表,是对表的结构进行操作，比如对字段增加，删除，修改类型
    update用来修改表中的数据，修改某一行某一列的值
    ‘‘‘
    #增
    def insertDB(self,cursor,TabName):
        print(‘增‘)
        name = input(‘请输入你要插入的名字: ‘)
        age = input(‘请输入你要插入的岁数了: ‘)
        sql = ‘insert into %s(name,age)‘%TabName+‘ value (%s,%s)‘
        cursor.execute(sql,[name,age])

    #删
    def deleteDB(self,cursor,TabName):
        print(‘删‘)

        name = input(‘请输入你需要删除的名字: ‘)
        sql = ‘insert into %s(name,age)‘ %TabName + ‘ value (%s)‘
        cursor.execute(sql, [name])

    #改
    def updateDB(self,cursor,TabName):
        print(‘改‘)
        name = input(‘请输入你要修改的名字: ‘)
        age = input(‘请输入你要修改的岁数了: ‘)
        sql = ‘insert into %s(name,age)‘ % TabName + ‘ value (%s,%s)‘
        cursor.execute(sql, [name, age])
    #查
    def seleteDB(self,cursor,TabName):
        print(‘查‘)
        # name = input(‘请输入你需要查询的名字: ‘)
        sql = ‘select * from  %s‘%TabName
        cursor.execute(sql)

        info = cursor.fetchall()
        for i in info:
            print(i)

def chooseOperation(cursor,db):
    TabName = chooseTable(cursor)
    msg = ‘‘‘
    1 : 增
    2 : 删
    3 : 改
    4 : 查
    q : 退出表操作
    ‘‘‘
    ch = operationTab()
    while True:
        chooseNum = input(msg)
        try:

            if chooseNum == ‘1‘:
                ch.insertDB(cursor,TabName)
            elif chooseNum == ‘2‘:
                ch.deleteDB(cursor,TabName)
            elif chooseNum == ‘3‘:
                ch.updateDB(cursor,TabName)
            elif chooseNum == ‘4‘:
                ch.seleteDB(cursor,TabName)
            elif chooseNum == ‘q‘:
                break
                pass
            else:
                print(‘你的输入有误,请重新输入!‘)
        except:
            db.rollback()

        print(TabName)

def connectDB():
    dbName = input(‘请输入你需要连的库的名字: ‘)

    # 连接数据库,用户名,密码,主机,端口,库,字符集等
    db = pymysql.connect(
        user=‘root‘,
        password=‘mghxy123‘,
        host=‘localhost‘,
        port=3306,
        db=dbName,
        charset=‘utf8‘
    )
    # 使用游标,创建一个游标对象
    cursor = db.cursor()
    chooseTable(cursor)
    chooseOperation(cursor,db)

    #提交sql操作,也相当于flush privleges;
    db.commit()

    #关闭游标
    cursor.close()
    #关闭数据库链接
    db.close()

try:
    # chooseOperation(cursor)
    connectDB()
except Exception :
    print(‘你输入的库名不存在,谢谢!‘)

#请输入你需要操作的表名table_name
#如果不存在选择是否需要创建表,
#创建表一次输入属性,给个例子,列出属性的,字样

#用while循环
#1.询问是否要继续操作
#请选择你的操作,1.增,2.删,3.改,4.查,
#执行sql

# sqlSelect = ‘select * from student‘
# cursor.execute(sqlSelect)
# info = cursor.fetchall()
# #打印查询的数据库的数据
# for i in info:
#     print(i)

原文：http://blog.51cto.com/853056088/2160854