3 ms·
I need a very good reason before using any external library in an attempt to keep the total code base as clean as possible. It is just too easy to be rushed a
by acutesoftware 8y ago
I need a very good reason before using any external library in an attempt to
keep the total code base as clean as possible.
It is just too easy to be rushed and bring in a heap of code, so
I prefer to use SQL instead of ORM's.
All access to the database is done in a single module and they are
wrapped in functions like below
def get_table_as_list(user_id, cols, tbl, where_clause, params_as_list, conn_str, order_by="1", maxrows='2000'):
"""
This should be the ONLY place that selects from the database
"""
db = get_db_conn(conn_str)
cur = db.cursor()
where_clause += ' AND user_id = %s'
sql = "SELECT " + cols + " FROM " + tbl + " WHERE " + where_clause + " ORDER BY " + order_by + " LIMIT " + maxrows
params_as_list.append(str(user_id))
cur.execute(sql, params_as_list)
res = list(cur.fetchall())
cur.close()
db.close()
return res
The database is designed and built first, then in the application the
definitions are done like below
all_tables = [
{'tbl':'as_note',
'cols':['id','title','pinned', 'important','content','folder'],
'col_types':['id','Text','Checkbox','Checkbox', 'Note','Text'],
},
{'tbl':'as_task',
'cols':['id','Title','Pinned', 'Important','Notes','folder','Done'],
'col_types':['id','Text','Checkbox','Checkbox','Note','Text','Checkbox'],
}]
So far it is working well, and it is very simple to add new tables to
the schema and have them working in the application.
- blattimwind 8y agoIt's nice to see that people are still able to write 2002-era PHP in Python today.
- tasuki 8y agoWhat exactly is the problem you're attempting to solve with this? Bonus questions: What about maintenance or admin queries which aren't tied to a specific user_id? What about sql injection?
- acutesoftware 8y ago> What exactly is the problem you're attempting to solve with this? Keeping all database access in one place to avoid having selects around the codebase. > What about maintenance or admin queries which aren't tied to a specific user_id? This is the web interface for users, all admin stuff is done elsewhere > What about sql injection? The selects are passed as parametised queries, so the where clause would be 'title = %s AND folder = %s'
- diek 8y agoUnless you're performing some magic elsewhere in the codebase, this will leak connections if an exception is thrown since you're not closing the connection in a 'finally' block. Alternatively, depending on your version of Python, you could use a 'with' context to ensure the connection is closed.
- acutesoftware 8y agoGood point, thanks for that - there is a lot error handling I haven't shown but hadn't taken into account memory leaks.