Sqlite clean up statements within code and add to file

Question

I'd like to clean up raw statements within the code and add them to seperate files in another directory as sql files.

So would like to run the code below:

read_cursor.execute('INSERT OR IGNORE INTO quantity VALUES (?,?,?)', row)

but change it to something like:

read_cursor.execute('sqlstatements/quantity.sql', row)

Is this possible how would I go about doing this?

Um, something like `read_cursor.execute(open('sqlstatements/quantity.sql').read(), row)`? Though this is a dirty solution and you should follow [this answer](https://stackoverflow.com/questions/7409780/reading-entire-file-in-python) on how to read a file instead. — metatoaster, Jan 19 '18 at 15:53
Possible duplicate of [reading external sql script in python](https://stackoverflow.com/questions/19472922/reading-external-sql-script-in-python) — metatoaster, Jan 19 '18 at 15:53

score 0 · Accepted Answer · answered Jan 19 '18 at 16:18

I don't think that's a good idea actually - it will make your code much harder to read and maintain for no good reason (and let's not talk about the cost of opening/reading files for each and any query).

If your goal is to decouple data access (how you get/store data) from business logic (how you use data), a better solution would be to write a data acces module with the proper functions and call those functions from your main code, ie:

# datalayer.py
import atexit

_conn = <open your connection here>

def close_connection():
    if _conn:
        _conn.close()
        _conn = None

atexit.register(close_connection)

# FIXME: proper arg names
def update_quantity(x, y, z, commit=True):
    c = _conn.cursor()       
    try:
        r = c.execute('INSERT OR IGNORE INTO quantity VALUES (?,?,?)', (x, y, z))
    except Exception as e:
        c.close()
        raise

    if commit:
        _conn.commit()

    return r

Then in your code:

import datalayer

def func():
   # do things here
   datalayer.update_quantities(a, b, c)
   # etc

This is of course a very dumbed down example - depending on your app's complexity you may want to have distinct classes for different sets of operations (depending on which datasets you're working etc), and in all cases you'll probably want to make sure the connection is always properly closed, that it try and reopens when it's been closed by the server etc, but you get the main idea.

Sqlite clean up statements within code and add to file

1 Answers1