python_MySQL

原文章连接:http://www.runoob.com/python/python-mysql.html

配置数据库

conn = mysql.connector.connect(user='root', password='数据库密码', database='数据库名')
cursor = conn.cursor()
#如果表存，执行如下操作
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

创建表

sql = """ create table if not exists myUserInfo (
           id varchar(99),
           author varchar (20),
           title varchar (20),
           content varchar (9999)
           )"""

数据库的插入操作:

#插入一条数据
sql = """insert into myUserInfo(id, author, title, content) values ('2', 'sqz', '文章的标题2', '文章的内容2')"""

try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   conn.commit()
except:
   # 如果发生错误则回滚
   conn.rollback()
#关闭数据库
conn.close()

接口带参数插入数据

@app.route('/register/', methods=['GET', "POST"])
def register():
    uid = 0
    userName = request.values.get('userName')
    passWord = request.values.get('passWord')
    tel = request.values.get('tel')
    print(uid, userName, passWord)

    lin1 = cursor.rowcount
    cursor.execute('insert into myUserInfo values ("%d", "%s", "%s", "%s")' % (uid, userName, passWord, tel))
     # 执行sql语句
    conn.commit()

接口带参数查询数据

@app.route('/login/', methods=['GET', 'POST'])
def login():
    _userName = request.values.get('userName')
    _passWord = request.values.get('passWord')
    sql = "select * from myUserInfo where userName= '%s' and passWord='%s'" % (_userName, _passWord)
    cursor.execute(sql)
    results = cursor.fetchall()
    if results:
        return json.dumps({'resCode':'0'})
    else:
        return json.dumps({'resCode':'1', 'errorCode':'无此用户'})
    conn.close()

接口带参更新数据

@app.route('/update_passWord/', methods=['GET', 'POST'])
def modify_password():
    _userName = request.values.get('userName')
    _passWord = request.values.get('passWord')
    _newPassword =  request.values.get('newPassword')
    sql = "select * from myUserInfo where userName= '%s' and passWord='%s'" % (_userName, _passWord)
    cursor.execute(sql)
    results = cursor.fetchall()
    if results:
        #更新此用户的信息
        sql = "update myUserInfo set passWord='%s' where userName='%s'" % (_newPassword, _userName)
        cursor.execute(sql)
        conn.commit()
    else:
        return json.dumps({'resCode':'1', 'errorCode':'请重新输入'})
    conn.close()

删除操作我觉得危险，最好不要使用，后面在更新吧