添加链接 注册    登录
link管理
链接快照平台
  • 输入网页链接，自动生成快照
  • 标签化管理网页链接
相关文章推荐
留胡子的热带鱼  ·  锁定文件 - PDM·  1 月前    · 
谦虚好学的花生  ·  第 4 章 Web 表单 | Flask ...·  3 周前    · 
霸气的蚂蚁  ·  Flask request.data ...·  3 周前    · 
从未表白的棒棒糖  ·  python - Flask - ...·  3 周前    · 
年轻有为的领带  ·  Python编程：Flask表单扩展Flas ...·  3 周前    · 
老实的弓箭  ·  死宅天使之家吧-百度贴吧·  4 月前    · 
冷冷的枕头  ·  零售百科网（www.6als.com） - ...·  5 月前    · 
千杯不醉的甘蔗  ·  Get Started with ...·  5 月前    · 
酷酷的卡布奇诺  ·  中联重科5140价格_中联重科ZLJ5140 ...·  6 月前    · 
健壮的李子  ·  国家知识产权局 通知公告 ...·  8 月前    · 
link管理  ›  How to execute raw SQL in Flask-SQLAlchemy app - GeeksforGeeks
django python sqlalchemy flask
https://www.geeksforgeeks.org/how-to-execute-raw-sql-in-flask-sqlalchemy-app/
刚分手的茶叶
11 月前

In this article, we are going to see how to execute raw SQL in Flask-SQLAlchemy using Python.

Installing requirements

Install the Flask and Flask-SQLAlchemy libraries using pip 

pip install Flask
pip install flask_sqlalchemy

Syntax

To run raw SQL queries, we first create a flask-SQLAlchemy engine object using which we can connect to the database and execute the SQL queries. The syntax is –

flask_sqlalchemy.SQLAlchemy.engine.execute(statement)

Executes a SQL expression construct or string statement within the current transaction.

Parameters:

  • statement: SQL expression

    • Returns:

  • sqlalchemy.engine.result.ResultProxy

    • Example 1

    Python

    # IMPORT REQUIRED LIBRARIES
    from flask import Flask, request
    from flask_sqlalchemy import SQLAlchemy
    # CREATE THE FLASK APP
    app = Flask(__name__)
    # ADD THE DATABASE CONNECTION TO THE FLASK APP
    db = SQLAlchemy(app)
    db_cred = {
    'user' : 'root' , # DATABASE USER
    'pass' : 'password' , # DATABASE PASSWORD
    'host' : '127.0.0.1' , # DATABASE HOSTNAME
    'name' : 'Geeks4Geeks' # DATABASE NAME
    app.config[ 'SQLALCHEMY_DATABASE_URI' ] = f"mysql + pymysql: / / \
    {db_cred[ 'user' ]}:{db_cred[ 'pass' ]}@{db_cred[ 'host' ]} / \
    {db_cred[ 'name' ]}"
    app.config[ 'SQLALCHEMY_TRACK_MODIFICATIONS' ] = False
    # CREATE A users TABLE USING RAW SQL QUERY
    db.engine.execute(
    CREATE TABLE users (
    email VARCHAR(50),
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    passwd VARCHAR(50)
    # INSERT TEMP VALUES IN THE users TABLE USING RAW SQL QUERY
    db.engine.execute(
    INSERT INTO users(email, first_name, last_name, passwd) VALUES
    ('john.doe@zmail.com', 'John', 'Doe', 'john@123');
    INSERT INTO users(email, first_name, last_name, passwd) VALUES
    ('john.doe@zmail.com', 'John', 'Doe', 'johndoe@777');
    INSERT INTO users(email, first_name, last_name, passwd) VALUES
    ('noah.emma@wmail.com', 'Emma', 'Noah', 'emaaa!00');
    INSERT INTO users(email, first_name, last_name, passwd) VALUES
    ('emma@tmail.com', 'Emma', 'Noah', 'whrfc2bfh904');
    INSERT INTO users(email, first_name, last_name, passwd) VALUES
    ('noah.emma@wmail.com', 'Emma', 'Noah', 'emaaa!00');
    INSERT INTO users(email, first_name, last_name, passwd) VALUES
    ('liam.olivia@wmail.com', 'Liam', 'Olivia', 'lolivia#900');
    INSERT INTO users(email, first_name, last_name, passwd) VALUES
    ('liam.olivia@wmail.com', 'Liam', 'Olivia', 'lolivia$345');
    # VIEW THE RECORDS INSERTED
    for record in db.engine.execute( 'SELECT * FROM users;' ):
    print (record)
    # RUN THE APP
    if __name__ = = '__main__' :
    app.run()

    Output:

    In this example, we created a simple flask app that does not have any route but instead runs raw SQL queries. We have created the SQLAlchemy connection and then executed 3 different raw SQL queries. The first query creates the user’s table. The second query inserts some sample records in the table. The third query fetches all the records and displays them in the terminal.

    In all three cases, we have used the db.engine.execute() method. The db.engine provides an SQLAlchemy engine connection and the execute method takes in a SQL query to execute the request.

    Example 2

    In this example, we have created 2 different routes to work with. These routes will act as an API where we can send a POST request with a query key in the body. The value for this query key will be the raw SQL query that we need to execute. The get_results API will be used to fetch the records that we get from the SELECT query. The execute_query API is used to execute raw SQL queries and will return the response message if the query is successfully executed or not.

    Python

    # IMPORT REQUIRED LIBRARIES
    from flask import Flask, request
    from flask_sqlalchemy import SQLAlchemy
    # CREATE THE FLASK APP
    app = Flask(__name__)
    # ADD THE DATABASE CONNECTION TO THE FLASK APP
    db = SQLAlchemy(app)
    db_cred = {
    'user' : 'root' , # DATABASE USER
    'pass' : 'password' , # DATABASE PASSWORD
    'host' : '127.0.0.1' , # DATABASE HOSTNAME
    'name' : 'Geeks4Geeks' # DATABASE NAME
    app.config[ 'SQLALCHEMY_DATABASE_URI' ] = f"mysql + pymysql: / / \
    {db_cred[ 'user' ]}:{db_cred[ 'pass' ]}@{db_cred[ 'host' ]} / \
    {db_cred[ 'name' ]}"
    app.config[ 'SQLALCHEMY_TRACK_MODIFICATIONS' ] = False
    # APP ROUTE TO GET RESULTS FOR SELECT QUERY
    @app .route( '/get_results' , methods = [ 'POST' ])
    def get_results():
    # GET THE SQLALCHEMY RESULTPROXY OBJECT
    result = db.engine.execute(request.get_json()[ 'query' ])
    response = {}
    i = 1
    # ITERATE OVER EACH RECORD IN RESULT AND ADD IT
    # IN A PYTHON DICT OBJECT
    for each in result:
    response.update({f 'Record {i}' : list (each)})
    i + = 1
    return response
    # APP ROUTE TO RUN RAW SQL QUERIES
    @app .route( '/execute_query' , methods = [ 'POST' ])
    def execute_query():
    try :
    db.engine.execute(request.get_json()[ 'query' ])
    except :
    return { "message" : "Request could not be completed." }
    return { "message" : "Query executed successfully." }
    # RUN THE APP
    if __name__ = = '__main__' :
    app.run()

    Output:

    We will test the routes through POSTMAN. Following are the 3 cases that are tested using POSTMAN.

    1. Running a SELECT query to fetch all the records through the get_results API

    2. Next, we will test the execute_query API for a valid INSERT query

    3. Lastly, we will put any random query and see if we get any error message

    We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy Got It !
    Please go through our recently updated Improvement Guidelines before submitting any improvements.
    This article is being improved by another user right now. You can suggest the changes for now and it will be under the article's discussion tab.
    You will be notified via email once the article is available for improvement. Thank you for your valuable feedback!
    Please go through our recently updated Improvement Guidelines before submitting any improvements.
    Suggest Changes
    Help us improve. Share your suggestions to enhance the article. Contribute your expertise and make a difference in the GeeksforGeeks portal.
     
    推荐文章
    留胡子的热带鱼  ·  锁定文件 - PDM
    1 月前
    谦虚好学的花生  ·  第 4 章 Web 表单 | Flask Web开发：基于Python的Web应用开发实战
    3 周前
    霸气的蚂蚁  ·  Flask request.data empty if Content-type is application/x-www-form-urlencoded · Issue #92 · logandk/
    3 周前
    从未表白的棒棒糖  ·  python - Flask - 当内容类型为“application/x-www-form-urlencoded”时，如何读取 POST 请求中的原始正文 -
    3 周前
    年轻有为的领带  ·  Python编程：Flask表单扩展Flask-WTF-阿里云开发者社区
    3 周前
    老实的弓箭  ·  死宅天使之家吧-百度贴吧
    4 月前
    冷冷的枕头  ·  零售百科网（www.6als.com） - Welcome!
    5 月前
    千杯不醉的甘蔗  ·  Get Started with WinForms PDF Viewer | WinForms Controls | DevExpress Documentation
    5 月前
    酷酷的卡布奇诺  ·  中联重科5140价格_中联重科ZLJ5140THBJE-10022R车载泵价格_参数_口碑_图片-第一工程机械网
    6 月前
    健壮的李子  ·  国家知识产权局 通知公告 2022年6月审批设立的专利代理机构名录
    8 月前
    Link管理   ·   51好读   ·   Sov5搜索   ·   小百科
    link管理 - 链接快照平台