failed to decode json object when tried to do insertion on flask

Question

I tried to register new user to the local database on my flask app, and I used psycopg2 to do database interaction. I learned that using mogrify would do data insertion on db, but I got following error at server endpoint:

{
  "message": "Failed to decode JSON object: Expecting value: line 1 column 1 (char 0)"
}

I could able to retrieve existing user from local database but I can't register a new users to same database table with psycopg2. can anyone point me how to make this work? anyway I could register/put the new user to a local database table? any thoughts? thanks

my attempt in flask:

Here is my attempt to register/put new user to local database table:

from flask_restplus import reqparse, fields, abort
from psycopg2.extensions import AsIs
from flask_restplus import Api, Namespace, Resource, fields, reqparse
from flask import Flask, request, jsonify
import psycopg2, json

app = Flask(__name__)
api = Api(app) 

credential_model = api.model('credential', {
    'username': fields.String(required=True),
    'password': fields.String(required=True)
})

credential_parser = reqparse.RequestParser()
credential_parser.add_argument('username', type=str)
credential_parser.add_argument('password', type=str)

@api.route('/token')
class Token(Resource):
    @api.expect(credential_parser, validate=True)
    def post(self):
        args = credential_parser.parse_args()

        username = args.get('username')
        password = args.get('password')
        user = {'_id':username,
                'password': password 
        }
        columns = user.keys()
        values = [user[column] for column in columns]
        insert_statement = 'insert into authorized_user_table (%s) values %s'
        try:
            cursor = db.cursor()
            cursor.mogrify(insert_statement, (AsIs(','.join(columns)), tuple(values)))
        except:
            return {"message" : "{} has already been signed".format(username)}, 400
        return { 
            "message" : "{} Register Successfully".format(username), 
            "prediction_id" : username
            }, 200

if __name__ == '__main__':
    db = psycopg2.connect(database='test_api', user='postgres', password='password', host='localhost', port="5432")
    app.run(debug=True)

update: database table:

 CREATE TABLE authorized_user_table (
   user_id bigint(20) NOT NULL AUTO_INCREMENT,
   user_name varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
   user_email varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
   user_password varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`user_id`)

insert  into authorized_user_table(user_id,username,email,password) values 
(4,'Jen hardy','jhardy@gmail.com','password4');

for inserting new entries to databse table, I used this post and second post, but still I can't register new user to local database table.

update:error persist:

I tried posted answer, but I still not able to insert new user to DB. how to make posted answer correct? any idea?

objective:

I just want to verify whether user exists in database if it is not, I want to register them in local db first, then set them authenticated users can access to flask app. how can I make this work? any possible thought? thanks

Answer 1

This code works correctly. In particular, it build the INSERT statement using the tools provided in psycopg2.sql and then executes the result. cursor.mogrify returns the statement as a bytestring, but does not execute it.

This code assumes that dictionaries preserve insertion order. This is true for CPython 3.6, and all implementations of Python 3.7. If you are using an earlier version you will need to be sure that the keys and values are in synch when generating columns and values.

import sys
import traceback

from flask_restplus import reqparse, fields
from psycopg2 import sql
from flask_restplus import Api, Resource
from flask import Flask
import psycopg2

app = Flask(__name__)
api = Api(app)

credential_model = api.model(
    "credential",
    {
        "username": fields.String(required=True),
        "password": fields.String(required=True),
    },
)

credential_parser = reqparse.RequestParser()
credential_parser.add_argument("username", type=str)
credential_parser.add_argument("password", type=str)


@api.route("/token")
class Token(Resource):
    @api.expect(credential_parser, validate=True)
    def post(self):
        args = credential_parser.parse_args()

        username = args.get("username")
        password = args.get("password")
        user = {"user_name": username, "user_password": password}

        insert_statement = "insert into authorized_user_table ({}) values ({})"
        cols = sql.SQL(", ").join([sql.Identifier(x) for x in user.keys()])
        vals = sql.SQL(", ").join([sql.Placeholder() for _ in user.values()])
        insert_statement = sql.SQL(insert_statement).format(cols, vals)

        try:
            cursor = db.cursor()
            cursor.execute(insert_statement, tuple(user.values()))
            # Commit/rollback added to make this work locally 
            # perhaps you are already doing this in your code
            db.commit()
        except: 
            # Print exception traceback if there's an error
            tb = sys.exc_info()
            traceback.print_exception(*tb)
            db.rollback()
            return {"message": "{} has already been signed".format(username)}, 400
        return (
            {
                "message": "{} Register Successfully".format(username),
                "prediction_id": username,
            },
            200,
        )


if __name__ == "__main__":
    db = psycopg2.connect(database="test")
    app.run(debug=True)