操作mysql

操作mysql数据库之前要先安装pymysql模块，
安装命令：

pip install pymysql

下面是练习：

import pymysql

#IP     xxx.xxx.xxx.xxx
#port   3306
#user   xxx
#password   123456
#db xxx

# conn = pymysql.connect(host='xxx.xxx.xxx.xxx',
#                        port=3306,
#                        user='xxx',
#                        password='123456',
#                        db='xxx',
#                        charset='utf8',autocommit=True)   #要写utf8，port要写int类型   autocommit=True自动提交
# cur = conn.cursor() #建立游标
# sql = 'select * from app_myuser limit 5;'
# cur.execute(sql)    #执行sql语句
# resault = cur.fetchall()    #获取所有结果
#
# print(resault)
# cur.close()     #关闭游标
# conn.close()    #关闭连接
#
# sql = "insert into app_myuser (username,passwd,is_admin) values('meteor','123456','1');"
# cur.execute(sql)
# conn.commit()   #操作完要提交
#
# sql1 = 'select * from app_myuser limit 5;'
# cur.execute(sql1)    #执行sql语句
# resault = cur.fetchall()    #获取所有结果
#
# print(resault)
# cur.close()     #关闭游标
# conn.close()
#
# cur = conn.cursor(pymysql.cursors.DictCursor)
# # sql = "insert into app_myuser (username,passwd,is_admin) values('meteor','123456','1');"
# # cur.execute(sql)
# sql1 = "select * from app_myuser where username like 'meteor%';"
# cur.execute(sql1)
# # resault = cur.fetchall()  #获取所有结果
# #resault = cur.fetchone()    #获取一条结果
# resault1 = cur.fetchone()
# resault = cur. fetchall()    #返回字典{'username':'meteor','password':'123456'......}只需要指定游标类型cur = conn.cursor(pymysql.cursors.DictCursor)
# print(resault1)
# print(resault)
# #print(resault[0].get('username'))
# cur.close()
# conn.close()

练习

def op_mysql(info,sql:str):
    resault = '执行完成'
    conn = pymysql.connect(**info)
    cur = conn.cursor(pymysql.cursors.DictCursor)   #建立字典式游标
    cur.execute(sql)
    if sql.strip().lower().startswith('select'):
        resault = cur.fetchall()
    cur.close()
    conn.close()
    return resault

info = {'host':'xxx.xxx.xxx',
        'port':3306,
        'user':'xxx',
        'password':'123456',
        'db':'xxx',
        'charset':'utf8',
        'autocommit':True
       }
#sql = "select * from app_myuser limit 10;"

#print(op_mysql(info,sql))
# resault = op_mysql(info,"insert into app_myuser (username,passwd,is_admin) values('meteor3','123456','1');")
insert_sql = 'insert into app_myuser (username,passwd,is_admin) values("meteor3","123456","1");'
select_sql = "select * from app_myuser where username = 'meteor3';"
update_sql = 'update app_myuser set passwd="123123" where username = "meteor3";'
delete_sql = 'delete from app_myuser where username = "meteor3";'
print(op_mysql(info,select_sql))