0

I am currently fixing up a method that is used to take a query with user-submitted data, and perform a database operation (such as an update, insert, or delete) via SQLITE3.

The intention is to allow optional parameters to be accepted which would reference the user-submitted variables for the query. An example of how the method would be used:

incidentkey = request.args.get('incident') incident_rows = databaseInsert2("SELECT * FROM incident_history where incident_number=?", incidentkey)

I have had several issues with the code provided - however, the primary issue is that my query fails. Originally the query failed straight up due to syntax, however now it seems to be returning a None type object, and I have already confirmed this item can be accessed manually within Sqlite3.

Is there any better way to handle queries which may have an unknown number of parameters included? Any help would be greatly appreciated.

def databaseInsert2(query, *args): try: conn = sql.connect('db/ccstatus.db') c = conn.cursor() c.execute(query, (args)) conn.commit() c.close() print("Database Insert: Success") except sql.Error as e: print("You have encountered an error while attempting to connect to the database: ", query, args, e)

Update:

I was able to get my code working as expected using *args as a parameter. I was missing a key piece where I was not returning the assumed result - once this was returned, the code above worked fine.

Example of working code:

def dbLookup(query, *args): try: con = sql.connect('db/ccstatus.db') con.row_factory = sql.Row c = con.cursor() c.execute(query, (args)) con.commit() rows = c.fetchall() c.close() print("Connection Success") except sql.Error as e: print("You have encountered an error while attempting to connect to the database: ", query, args, e) return rows

Example of how you would call this method: dbAlter("INSERT INTO systems VALUES (NULL, ?, ?, ?, ?", name, description, urlname, url)

Steven
  • 51
  • 3

1 Answers1

1

You can use SQLAlchemy to build queries with Python.

As for your code, the second argument needs to be a tuple of params, even if there is only one param.

incident_rows = databaseInsert2("SELECT * FROM incident_history where incident_number=?", (incidentkey,))
davidism
  • 121,510
  • 29
  • 395
  • 339
Adelina
  • 10,915
  • 1
  • 38
  • 46
  • Inherited a legacy system, running on an out of date variant of Python (3.5.X), and no proper ORM setup. In order to use fstrings, I need to upgrade them to 3.6 - something I'm looking into right now. I should also add, that although this would be a solution for this single query, it doesn't entirely answer how I would handle a different query with the same method. For example, lets say the query provided may have an alternative version that uses several fields of data, which would be passed to *args. – Steven Jul 30 '18 at 19:49
  • 1
    There are bunch of ways to format string: https://pyformat.info don't need to use fstring – Adelina Jul 30 '18 at 19:55
  • I was able to get my code working as expected using *args as a parameter. I was missing a key piece where I was not returning the assumed result - once this was returned, the code above worked fine.Thank you for help though, as your comments led me to find the issue. @Nuts – Steven Jul 30 '18 at 22:02