为mysql编写一个api接口
时间: 2024-04-29 11:23:45 浏览: 15
以下是一个简单的MySQL API接口示例:
```python
import pymysql
from flask import Flask, jsonify, request
app = Flask(__name__)
# 连接MySQL数据库
conn = pymysql.connect(host='localhost', user='root', password='password', database='test')
# 获取数据库中的所有用户数据
@app.route('/users', methods=['GET'])
def get_users():
cursor = conn.cursor()
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
users = []
for row in rows:
user = {
'id': row[0],
'name': row[1],
'email': row[2]
}
users.append(user)
cursor.close()
return jsonify(users)
# 获取指定用户的数据
@app.route('/users/<int:user_id>', methods=['GET'])
def get_user(user_id):
cursor = conn.cursor()
cursor.execute('SELECT * FROM users WHERE id = %s', (user_id,))
row = cursor.fetchone()
if row is None:
return jsonify({'error': 'User not found'}), 404
user = {
'id': row[0],
'name': row[1],
'email': row[2]
}
cursor.close()
return jsonify(user)
# 添加新用户
@app.route('/users', methods=['POST'])
def add_user():
name = request.json['name']
email = request.json['email']
cursor = conn.cursor()
cursor.execute('INSERT INTO users (name, email) VALUES (%s, %s)', (name, email))
conn.commit()
user_id = cursor.lastrowid
cursor.close()
return jsonify({'id': user_id, 'name': name, 'email': email}), 201
# 更新指定用户的数据
@app.route('/users/<int:user_id>', methods=['PUT'])
def update_user(user_id):
name = request.json['name']
email = request.json['email']
cursor = conn.cursor()
cursor.execute('UPDATE users SET name = %s, email = %s WHERE id = %s', (name, email, user_id))
conn.commit()
cursor.close()
return jsonify({'id': user_id, 'name': name, 'email': email})
# 删除指定用户
@app.route('/users/<int:user_id>', methods=['DELETE'])
def delete_user(user_id):
cursor = conn.cursor()
cursor.execute('DELETE FROM users WHERE id = %s', (user_id,))
conn.commit()
cursor.close()
return '', 204
if __name__ == '__main__':
app.run(debug=True)
```
这个示例使用了Flask框架,通过GET、POST、PUT和DELETE请求来处理MySQL数据库中的数据。在每个API路由函数中,都使用了pymysql库来连接并操作数据库。例如,获取所有用户数据的函数使用了SELECT查询语句,添加新用户的函数使用了INSERT语句,更新用户数据的函数使用了UPDATE语句,删除用户的函数使用了DELETE语句。同时,每个函数都返回JSON格式的数据。