Python SQLite insert with dynamic columns or use DEFAULT value-CodePudding

I am working with flask-restful and a user can post some required and some optional data, which i would like to make an SQLite insert statement with. The problem is that i dont want to butcher the sql statement by using variables for column names and checking which argument is None and which has some data. I was hoping i could just use all columns and when arg2 or arg3 are None, the database would use the colums default values. Sadly i get the message NOT NULL constraint failed: mytable.col1. Removing NOT NULL from the table creation with my method will just insert NULL when using my statement.

Is there an explicit way to tell SQLite to use its default value?

If not, is there a more graceful way to get the default value instead of executing some table schema query?

If not and no other solution exists, what would be the most efficient way to butcher the sql statement?

parser_post = reqparse.RequestParser()
parser_post.add_argument('arg1', type=str, required=True)
parser_post.add_argument('arg2', type=str)
parser_post.add_argument('arg3', type=str)
args = parser_post.parse_args()

# ... db init code

cur.execute("insert into mytable (col1, col2, col3) values (?, ?, ?)", 
(args['arg1'], args['arg2'], args['arg3']))

The table was created like this:

sql = """CREATE TABLE mytable (
    PK_id       INTEGER PRIMARY KEY AUTOINCREMENT,
    col1        INTEGER NOT NULL,
    col2        TEXT DEFAULT "global" NOT NULL,
    col3        TEXT DEFAULT "global" NOT NULL)"""
cursor.execute(sql)

CodePudding user response：

You can build the query dynamically, as long as you are careful with what you compose into the query string.

def prep_qry(args, colnames):
    """this query is secure as long as `colnames` contains trusted data
    standard parametrized query mechanism secures `args`"""

    binds,use = [],[]

    for colname, value in zip(colnames,args):
        if value is not None:
            use.extend([colname,","])
            binds.extend(["?",","])


    parts = ["insert into mytable ("]
    use = use[:-1]
    binds = binds[:-1]
    
    parts.extend(use)
    parts.append(") values(")
    parts.extend(binds)
    parts.append(")")

    qry = " ".join(parts)

    return qry, tuple([v for v in args if not v is None])

print(prep_qry([1,None,3], ["col1", "col2", "col3"]))
print(prep_qry([1,2,3], ["col1", "col2", "col3"]))

Output:

('insert into mytable ( col1 , col3 ) values( ? , ? )', (1, 3))
('insert into mytable ( col1 , col2 , col3 ) values( ? , ? , ? )', (1, 2, 3))