How to develop a simple online investment management system using MySQL and Python

How to use MySQL and Python to develop a simple online investment management system

Introduction:
With the continuous development of the Internet, more and more people are starting to Invest assets in various investments. For investors, an easy-to-use online investment management system can help them better manage and track their investments. This article will introduce how to use MySQL and Python to develop a simple online investment management system, and provide specific code examples.

1. System requirements analysis
Before starting development, we need to clarify the functional requirements of the system to be developed. A simple online investment management system should include the following functions:

1. User registration and login: Users need to be able to register an account and log in to the system.
2. Project management: Users can create, modify and delete investment projects. Each project can include a name, start date and investment amount.
3. Investment record management: Users can record each investment behavior, including investment date, investment amount and invested projects.
4. Investment statistics and reports: Users can view their own investment statistics and generate reports.

2. Development environment preparation
Before starting development, we need to prepare the development environment. This article will use the MySQL database and Python programming language to build a development environment.

1. Install MySQL database and create a new database.
2. Install the Python programming environment and install the MySQL connection library (such as pymysql).

3. Database design
Next, we need to design the database table structure to store the system data. This article will design three data tables: user table, project table and investment record table.

1. The user table (users) includes fields such as user ID, user name and password.
2. The project table (projects) includes fields such as project ID, project name, start date, and investment amount.
3. The investment record table (investments) includes fields such as record ID, investment date, investment amount, invested project ID and user ID.

4. System Development
Before development, we need to import the required libraries and modules, such as pymysql, flask, etc.

1. Create a Python file and import the required libraries and modules.

2. Define database connection and cursor objects.

   import pymysql
   connection = pymysql.connect(host='localhost', user='root', password='password', database='investments')
   cursor = connection.cursor()

3. Create user registration and login functions.

   # 用户注册
   @app.route('/register', methods=['POST'])
   def register():
    username = request.form['username']
    password = request.form['password']
    cursor.execute("INSERT INTO users (username, password) VALUES (%s, %s)", (username, password))
    connection.commit()
    return redirect(url_for('login'))
   
   # 用户登录
   @app.route('/login', methods=['POST'])
   def login():
    username = request.form['username']
    password = request.form['password']
    cursor.execute("SELECT * FROM users WHERE username = %s AND password = %s", (username, password))
    if cursor.fetchone() is not None:
        session['username'] = username
        return redirect(url_for('home'))
    else:
        return redirect(url_for('login'))

4. Create project management functionality.

   # 创建项目
   @app.route('/create-project', methods=['POST'])
   def create_project():
    name = request.form['name']
    start_date = request.form['start_date']
    investment_amount = request.form['investment_amount']
    cursor.execute("INSERT INTO projects (name, start_date, investment_amount) VALUES (%s, %s, %s)", (name, start_date, investment_amount))
    connection.commit()
    return redirect(url_for('projects'))
   
   # 修改项目
   @app.route('/edit-project/<int:project_id>', methods=['POST'])
   def edit_project(project_id):
    name = request.form['name']
    start_date = request.form['start_date']
    investment_amount = request.form['investment_amount']
    cursor.execute("UPDATE projects SET name = %s, start_date = %s, investment_amount = %s WHERE id = %s", (name, start_date, investment_amount, project_id))
    connection.commit()
    return redirect(url_for('projects'))
   
   # 删除项目
   @app.route('/delete-project/<int:project_id>', methods=['POST'])
   def delete_project(project_id):
    cursor.execute("DELETE FROM projects WHERE id = %s", (project_id,))
    connection.commit()
    return redirect(url_for('projects'))

5. Create investment record management functions.

   # 创建投资记录
   @app.route('/create-investment', methods=['POST'])
   def create_investment():
    date = request.form['date']
    amount = request.form['amount']
    project_id = request.form['project_id']
    cursor.execute("INSERT INTO investments (date, amount, project_id, user_id) VALUES (%s, %s, %s, %s)", (date, amount, project_id, session['username']))
    connection.commit()
    return redirect(url_for('investments'))
   
   # 修改投资记录
   @app.route('/edit-investment/<int:investment_id>', methods=['POST'])
   def edit_investment(investment_id):
    date = request.form['date']
    amount = request.form['amount']
    project_id = request.form['project_id']
    cursor.execute("UPDATE investments SET date = %s, amount = %s, project_id = %s WHERE id = %s", (date, amount, project_id, investment_id))
    connection.commit()
    return redirect(url_for('investments'))
   
   # 删除投资记录
   @app.route('/delete-investment/<int:investment_id>', methods=['POST'])
   def delete_investment(investment_id):
    cursor.execute("DELETE FROM investments WHERE id = %s", (investment_id,))
    connection.commit()
    return redirect(url_for('investments'))

6. Create investment statistics and reporting functions.

   # 投资统计
   @app.route('/investment-stats')
   def investment_stats():
    cursor.execute("SELECT SUM(amount) FROM investments WHERE user_id = %s", (session['username'],))
    total_investment = cursor.fetchone()[0]
    return render_template('investment_stats.html', total_investment=total_investment)
   
   # 生成报表
   @app.route('/generate-report')
   def generate_report():
    cursor.execute("SELECT * FROM investments WHERE user_id = %s", (session['username'],))
    investments = cursor.fetchall()
    return render_template('report.html', investments=investments)

7. Run the system.

   if __name__ == '__main__':
    app.secret_key = 'secret_key'
    app.run(debug=True)

5. Summary
This article briefly introduces how to use MySQL and Python to develop a simple online investment management system, and provides relevant code examples. This system can help investors better manage and track their investments, including project management, investment record management, investment statistics and reports, etc. Developers can further improve the system according to their own needs and apply it to actual investment management.

The above is the detailed content of How to develop a simple online investment management system using MySQL and Python. For more information, please follow other related articles on the PHP Chinese website!