從python中建立客戶訊息,並且存入mysql中

def State():
    import pymysql
    tableoff = False
    tablename =input('輸入欲創建表名or欲插入,查詢,更改的表名')
    # spider=欲使用的數據庫
    us = pymysql.connect('127.0.0.1', 'root', '111', 'spider')
    print(us)
    cursor = us.cursor()
    return cursor ,tablename,tableoff,us

---

def CreateTable(cursor,tablename,tableoff):
    # 當前庫下所有表
    print(tablename)
    cursor.execute('SHOW TABLES;')
    tables = cursor.fetchall()
    #判斷是否存在
    for table in tables:
        print(table[0])
        if table[0] == tablename:
            print('列表',table[0],'已存在')
            tableoff = True
        #如果不存在,創建
    if not tableoff:
        #PRIMARY KEY AUTO_INCREMENT =主健自增長
        cursor.execute('CREATE TABLE '+tablename+'(id INT PRIMARY KEY AUTO_INCREMENT, firstname VARCHAR(32),lastname VARCHAR(32),age INT,'
                                                 'sex ENUM("M","F"),assets DOUBLE);')
        print('創建成功')

#對表插入值
def InsertValues(cursor,tablename,us):
    while True:
        ent=input('添加值請輸入Y,不添加請輸入任意值:')
        if ent=='Y' or ent =='y':
            firstname='"'+input('輸入姓')+'"'
            lastname ='"'+input('輸入名')+'"'
            age = input('輸入年齡')
            sex = input('輸入M代表男,輸入F代表女,隨意填寫代表男')
            if sex == 'm' or sex =='M':
                sex='M'
                print('男女判定正確')
            elif sex =='f' or sex=='F':
                sex='F'
            else :
                sex='M'
            sex ='"'+sex+'"'
            assets = input('輸入資產')
            print('INSERT INTO '+tablename+"(firstname,lastname,age,sex,assets) VALUSE"+"("+firstname+","+lastname+","+age+","+sex+","+assets+");")
            cursor.execute('INSERT INTO '+tablename+"(firstname,lastname,age,sex,assets) VALUES"+"("+firstname+","+lastname+","+age+","+sex+","+assets+");")
            print('持續添加')
        else :
            print('exit')
            cursor.execute('SELECT * FROM '+tablename+";")
            nowselecttable=cursor.fetchall();
            #顯示表內容
            print('表:',nowselecttable)
            us.commit()
            NextOrExit()
def Updata(cursor,tablename,us):
    while True:
        user =input('輸入0查詢當前數據,輸入1更新當前數據,任意輸入表示離開:')
        if user=='0':
            cursor.execute('SELECT * FROM ' + tablename + ";")
            nowselecttable = cursor.fetchall();
            print('表:',nowselecttable)
        elif user=='1':
            cursor.execute('SELECT * FROM ' + tablename + ";")
            nowselecttable = cursor.fetchall();
            print('表:', nowselecttable)
            idselect = input('輸入欲更改對象的id:')
            cursor.execute('SELECT *FROM '+tablename+" "+"WHERE id ="+idselect)
            datas=cursor.fetchall()
            print(datas)
            if len(datas)>0:
                while True:
                    off= input('繼續修改請輸入1,任意輸入離開')
                    if off=='1':
                        modify=input('輸入1修改(姓),2(名),3(年齡)4(性別)5(資產)')
                        if modify=='1':
                            firstname = '"'+input('欲替換姓')+'"'
                            cursor.execute('UPDATE '+tablename+" SET firstname="+firstname+' where id='+idselect+';')
                            print('修改成功')
                        if modify=='2':
                            lastname = '"'+input('欲替換名')+'"'
                            cursor.execute('UPDATE '+tablename+" SET lastname="+lastname+' where id='+idselect+';')
                            print('修改成功')
                        if modify == '3':
                            age = input('欲替換年齡')
                            cursor.execute(
                                'UPDATE ' + tablename + " SET age=" + age + ' where id=' + idselect + ';')
                            print('修改成功')
                        if modify == '4':
                            sex = input('欲替換性別M或F')
                            if sex != 'M' and sex!='F':
                                print('輸入錯誤')
                                continue
                            sex='"'+sex+'"'
                            print(sex)
                            cursor.execute(
                                'UPDATE ' + tablename + " SET sex=" + sex + ' where id=' + idselect + ';')
                            print('修改成功')
                        if modify=='5':
                            assets = input('欲替資產')
                            cursor.execute('UPDATE '+tablename+" SET assets="+assets+' where id='+idselect+';')
                            print('修改成功')
                        else:
                            print('輸入錯誤,請輸入欲修改對象的代號')
                            pass
                    else:
                        print('離開')
                        break
                cursor.execute('SELECT *FROM ' + tablename + " " + "WHERE id =" + idselect)
                datas = cursor.fetchall()
                print('更改後:',datas)
            elif len(datas)==0:
                print('不存在此id請重新輸入')
        else:
            us.commit()
            print('離開')
            NextOrExit()

---

def NextOrExit():
    while True:
        chooise=input('輸入1新增資料,輸入2更改資料,任意輸入離開')
        if chooise=='1' or chooise=='2':
            state = State()
            CreateTable(state[0], state[1], state[2])
            if chooise=='1':
                InsertValues(state[0], state[1],state[3])
            elif chooise=='2':
                Updata(state[0], state[1],state[3])
            else:
                print('離開')
                state[3].commit()
                # 關閉游摽然後關閉系統
                state[0].close()
                state[3].close()
                NextOrExit()
        else:
            print('離開')
            os._exit(0)

---

if __name__ == '__main__':
    import os
    NextOrExit()