Re: Python/SQLite best practices
On Wed, Aug 7, 2019 at 2:28 AM Dennis Lee Bieber wrote: > > On Mon, 5 Aug 2019 20:12:27 +0200, Karsten Hilbert > declaimed the following: > > > >Transactions involving several commands may require passing > >around of connections and/or cursors, however. > > > > Probably both -- as I recall, DB-API spec is that .commit() is done on > the connection, not the cursor. Though I'd prefer to put transaction > control at a single higher level > > create connection > create initial cursor > create transaction (though DB-API makes this tricky -- typically this > occurs on the first DML request that modifies data, but not for mere > SELECTs) > do stuff with cursor, maybe passing connection if "stuff" > needs > secondary cursors > COMMIT or ROLLBACK based on return from "do stuff" > That would only be useful if the underlying database is capable of multiple independent transactions on a single connection, and would just get in the way otherwise. Some databases support a form of "nested transactions" where you set a savepoint and then have the option to either release the savepoint ("commit") or rollback to the savepoint; but releasing a savepoint doesn't actually commit anything, and the overall transaction still controls everything. My usual idiom with databasing code is to create a single connection (or maybe a pool if I need concurrency) and then use "with conn, conn.cursor() as cur:" to create a cursor and set up a transaction, all at once. At the end of that block, the cursor is disposed of, and the transaction committed/rolled back. ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: Python/SQLite best practices
* To be reliably INSERTed Byte data should be first converted to sqlite3.Binary(my_data) explicitly Interesting. Is that Python 2 specific, or also in Python 3. Because the latter would surprise me (not saying it isn't the case). Only tried on Python 3. I'm inserting raw byte versions of web-pages. I forget the exact details but I have a comment in my code to the above effect so I must have ended up bashing my head against it at some point. A zip/lzma compressed byte output doesn't need this wrapper though. On a UNIX system the command "lsof -p pid-of-running-python-process" should show the path of the sqlite library that is linked to the Python executable, which should let you learn this. Thanks, I'll have to add that to the collection. I know when I was googling around for it on *nix I came across a bunch of different answers none of which seemed to work. Windows is a simple matter of replacing the right DLL (though again not really documented). I still have no idea how to do it on *nix but that's another thread. -- https://mail.python.org/mailman/listinfo/python-list
Re: Python/SQLite best practices
On 06Aug2019 00:01, Jonathan Moules wrote: Some gotcha tips from using SQLite with Python that I've encountered. [...] * To be reliably INSERTed Byte data should be first converted to sqlite3.Binary(my_data) explicitly Interesting. Is that Python 2 specific, or also in Python 3. Because the latter would surprise me (not saying it isn't the case). * It's typically opaque as to where the install of SQLite is that the library is using and it's very hard and not-documented as to how to update the SQLite version that Python is using. On a UNIX system the command "lsof -p pid-of-running-python-process" should show the path of the sqlite library that is linked to the Python executable, which should let you learn this. If you want an even thinner wrapper around SQLite there's APSW ( https://rogerbinns.github.io/apsw/index.html ) - I've never used it myself but it's useful to know about. There's a page with differences - https://rogerbinns.github.io/apsw/pysqlite.html#pysqlitediffs And for a thicker wrapper, I've been extremely happy using SQLAlchemy for database access. It has an expression syntax where real Python expressions (containing "column" objects) evaluate to safe SQL, letting you write safe queries in nice Pythonic form, and it also has an ORM for more sophisticated use. It provided context manager for transactions and sessions for various work. Finally, it knows about a lot of backends, so you could switch backends later (eg from SQLite to PostgreSQL) if that becomes a thing. Cheers, Cameron Simpson -- https://mail.python.org/mailman/listinfo/python-list
Re: Python/SQLite best practices
Some gotcha tips from using SQLite with Python that I've encountered. You may already know some/all of these: * SQLite doesn't have a "Truncate" function - simply delete the file if possible for larger datasets. * Explicitly committing is good because the default python sqlite3 library does it randomly and implicitly. I found that doing it only when the database is dettaching or closing speeds things up a lot. * SQLite 3 only considers up to 64bits an INTEGER. So if you want to insert a 128bit string you have to use Python string substitution (i.e. "Hello %s") rather than the SQLite variable substitution "insert into tab values (?)" * To be reliably INSERTed Byte data should be first converted to sqlite3.Binary(my_data) explicitly * By default Foreign Keys are not enforced. Enable them at connection time if you care about referential integrity! * It's typically opaque as to where the install of SQLite is that the library is using and it's very hard and not-documented as to how to update the SQLite version that Python is using. If you want an even thinner wrapper around SQLite there's APSW ( https://rogerbinns.github.io/apsw/index.html ) - I've never used it myself but it's useful to know about. There's a page with differences - https://rogerbinns.github.io/apsw/pysqlite.html#pysqlitediffs On 2019-08-05 22:43, David Raymond wrote: "What's the advantage of this over letting the connection object do that for you? As the context manager exits, it will automatically either commit or roll back. If you want to guarantee closing _as well_, then you can do that, but you can at least use what already exists." After review I guess I should have phrased it more as a "here's what I've found for reference" rather than a "here's what _you_ should do" Part of it is large use of the Command Line Interface for SQLite, and similar command line tools for other db's, which all work in autocommit mode by default, so that's how my brain is now wired to think about executing things. The context manager transaction feature I can see using, and might actually start switching to it as it's explicit enough. Though oddly, __enter__ doesn't seem to actually begin a transaction, not even a deferred one. It's only __exit__ that either commits or rolls back. (Eh, it'd "probably" be simple enough to subclass Connection so that __enter__ and __exit__ work properly no matter the isolation_level. Famous last words) The implicit stuff I hated because it never seemed straightforward enough. Especially since there used to be implicit commits as well as implicit begins ("Changed in version 3.6: sqlite3 used to implicitly commit an open transaction before DDL statements. This is no longer the case.") Maybe because I was new to both Python and SQLite at the time, but there was a lot of "stop doing hidden stuff I didn't tell you do" getting muttered, along with others like "why do I need to commit when I never did a begin?" The documentation on it is all of 1 sentence, so there was a lot of trial an error going on. "The Python sqlite3 module by default issues a BEGIN statement implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE)." "(Also, I'd definitely use conn.commit() rather than cur.execute("commit"), in case there's extra functionality in the commit method.)" True. I know for example that if you try to rollback when not in a transaction that cur.execute("rollback;") will raise an exception whereas conn.rollback() will quietly suppress it for you. So there might be similarly useful stuff in .commit() sqlite3 is (almost) all C though, so there'd be noticeably more digging and decyphering required to check. (For me anyway) -- https://mail.python.org/mailman/listinfo/python-list
Re: Python/SQLite best practices
On Tue, Aug 6, 2019 at 7:45 AM David Raymond wrote: > The context manager transaction feature I can see using, and might actually > start switching to it as it's explicit enough. Though oddly, __enter__ > doesn't seem to actually begin a transaction, not even a deferred one. It's > only __exit__ that either commits or rolls back. > (Eh, it'd "probably" be simple enough to subclass Connection so that > __enter__ and __exit__ work properly no matter the isolation_level. Famous > last words) > Easier just to leave the isolation level and let it automatically begin. (This is another reason to use the commit and rollback methods, as they may flag the connection as "hey, remember to begin before the next query".) > The implicit stuff I hated because it never seemed straightforward enough. > Especially since there used to be implicit commits as well as implicit begins > ("Changed in version 3.6: sqlite3 used to implicitly commit an open > transaction before DDL statements. This is no longer the case.") Maybe > because I was new to both Python and SQLite at the time, but there was a lot > of "stop doing hidden stuff I didn't tell you do" getting muttered, along > with others like "why do I need to commit when I never did a begin?" The > documentation on it is all of 1 sentence, so there was a lot of trial an > error going on. > I grew up on DB2 5.0 (after working in PC File and dbase), and you never did a BEGIN TRANSACTION unless you wanted to set specific parameters, but always had to COMMIT/ROLLBACK. The database itself would automatically open a transaction as soon as you do any query, and leave it open till you're done. So to me, that was never a problem. ChrisA -- https://mail.python.org/mailman/listinfo/python-list
RE: Python/SQLite best practices
"What's the advantage of this over letting the connection object do that for you? As the context manager exits, it will automatically either commit or roll back. If you want to guarantee closing _as well_, then you can do that, but you can at least use what already exists." After review I guess I should have phrased it more as a "here's what I've found for reference" rather than a "here's what _you_ should do" Part of it is large use of the Command Line Interface for SQLite, and similar command line tools for other db's, which all work in autocommit mode by default, so that's how my brain is now wired to think about executing things. The context manager transaction feature I can see using, and might actually start switching to it as it's explicit enough. Though oddly, __enter__ doesn't seem to actually begin a transaction, not even a deferred one. It's only __exit__ that either commits or rolls back. (Eh, it'd "probably" be simple enough to subclass Connection so that __enter__ and __exit__ work properly no matter the isolation_level. Famous last words) The implicit stuff I hated because it never seemed straightforward enough. Especially since there used to be implicit commits as well as implicit begins ("Changed in version 3.6: sqlite3 used to implicitly commit an open transaction before DDL statements. This is no longer the case.") Maybe because I was new to both Python and SQLite at the time, but there was a lot of "stop doing hidden stuff I didn't tell you do" getting muttered, along with others like "why do I need to commit when I never did a begin?" The documentation on it is all of 1 sentence, so there was a lot of trial an error going on. "The Python sqlite3 module by default issues a BEGIN statement implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE)." "(Also, I'd definitely use conn.commit() rather than cur.execute("commit"), in case there's extra functionality in the commit method.)" True. I know for example that if you try to rollback when not in a transaction that cur.execute("rollback;") will raise an exception whereas conn.rollback() will quietly suppress it for you. So there might be similarly useful stuff in .commit() sqlite3 is (almost) all C though, so there'd be noticeably more digging and decyphering required to check. (For me anyway) -- https://mail.python.org/mailman/listinfo/python-list
Re: Python/SQLite best practices
On Tue, Aug 6, 2019 at 5:05 AM David Raymond wrote: > I believe the default Connection context manager is set up for the context to > be a single transaction, with a commit on success or a rollback on a failure. > As far as I know it does NOT close the connection on exiting the context > manager. That only happens automatically when it's getting garbage > collected/going out of scope/correct terminology that I can't seem to > remember. > > > For transactions and general use I vastly prefer using "isolation_level = > None" when creating my connections, and then explicitly issuing all begin, > commit, and rollback commands with cur.execute("begin;"), conn.commit(), > conn.rollback() etc. > > > contextlib.closing() can be used to wrap cursors for use with with > (and also connections if they are created with isolation_level = None) > > with contextlib.closing(sqlite3.connect(fi, isolation_level = None)) as conn: > conn.row_factory = sqlite3.Row > with contextlib.closing(conn.cursor()) as cur: > cur.execute("begin;") > stuff > conn.commit() > > > > Normally though my stuff tends to look like the below (for better or for > worse): > > conn = sqlite3.connect(fi, isolation_level = None) > try: > conn.row_factory = sqlite3.Row > with contextlib.closing(conn.cursor()) as cur: > cur.execute("standalone query not needing an explicit transaction;") > stuff > cur.execute("begin;") > multiple queries that needed the explicit transaction > stuff > cur.execute("commit;") > except something bad: > blah > finally: > conn.rollback() > conn.close() > What's the advantage of this over letting the connection object do that for you? As the context manager exits, it will automatically either commit or roll back. If you want to guarantee closing _as well_, then you can do that, but you can at least use what already exists. (Also, I'd definitely use conn.commit() rather than cur.execute("commit"), in case there's extra functionality in the commit method.) ChrisA -- https://mail.python.org/mailman/listinfo/python-list
RE: Python/SQLite best practices
Not a full expert, but some notes: I believe the default Connection context manager is set up for the context to be a single transaction, with a commit on success or a rollback on a failure. As far as I know it does NOT close the connection on exiting the context manager. That only happens automatically when it's getting garbage collected/going out of scope/correct terminology that I can't seem to remember. For transactions and general use I vastly prefer using "isolation_level = None" when creating my connections, and then explicitly issuing all begin, commit, and rollback commands with cur.execute("begin;"), conn.commit(), conn.rollback() etc. contextlib.closing() can be used to wrap cursors for use with with (and also connections if they are created with isolation_level = None) with contextlib.closing(sqlite3.connect(fi, isolation_level = None)) as conn: conn.row_factory = sqlite3.Row with contextlib.closing(conn.cursor()) as cur: cur.execute("begin;") stuff conn.commit() Normally though my stuff tends to look like the below (for better or for worse): conn = sqlite3.connect(fi, isolation_level = None) try: conn.row_factory = sqlite3.Row with contextlib.closing(conn.cursor()) as cur: cur.execute("standalone query not needing an explicit transaction;") stuff cur.execute("begin;") multiple queries that needed the explicit transaction stuff cur.execute("commit;") except something bad: blah finally: conn.rollback() conn.close() -Original Message- From: Python-list On Behalf Of Dave via Python-list Sent: Monday, August 05, 2019 1:49 PM To: python-list@python.org Subject: Python/SQLite best practices I'm looking for some tips from experienced hands on on this subject. Some of the areas of interest are (feel free to add more): * Passing connections and cursors - good, bad indifferent? I try to avoid passing file handles unless necessary, so I view connections and cursors the same. Though that said, I'm not aware of any specific problems in doing so. For designs with multiple tables: * Better to pass an sql string to functions that create/add data/update/delete data and pass them to create, insert, update, delete functions; or have those functions for each table? Taking table creation for example, if there are five tables, and the sql string is passed, there would need to be six functions to do it, though the complexity of each function may be reduced a little. [table1create with sql and establishing a cursor, to table5create and then a function that executes the sql]. Best way to establish the connection and cursor, as well as close them? I have seen many ways to do this, and know that the with block can be used to create a connection and close it automatically, but the same is not true of the cursor. Also, using only a with block does not handle any errors as well as a try/with. For example: | try: | # Use with block to create connection – it will close self. | with sqlite3.connect(path) as conn: | cur = conn.cursor() | cur.execute(sql_ProjectsTable) | cur.close() | except Error as e: | print(e) What else? Dave, -- https://mail.python.org/mailman/listinfo/python-list -- https://mail.python.org/mailman/listinfo/python-list
Re: Python/SQLite best practices
On Mon, Aug 05, 2019 at 08:12:27PM +0200, Karsten Hilbert wrote: > Transactions involving several commands may require passing > around of connections and/or cursors, however. Among chains of python code, that is. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B -- https://mail.python.org/mailman/listinfo/python-list
Re: Python/SQLite best practices
On Mon, Aug 05, 2019 at 01:49:24PM -0400, Dave via Python-list wrote: > * Passing connections and cursors - good, bad indifferent? I try to avoid > passing file handles unless necessary, so I view connections and cursors the > same. Connections may be more long-lived, per thread perhaps. Cursors would generally be throw-away. Transactions involving several commands may require passing around of connections and/or cursors, however. > Best way to establish the connection and cursor, as well as close them? I > have seen many ways to do this, and know that the with block can be used to > create a connection and close it automatically, but the same is not true of > the cursor. Also, using only a with block does not handle any errors as > well as a try/with. For example: > > | try: > | # Use with block to create connection – it will close self. > | with sqlite3.connect(path) as conn: > | cur = conn.cursor() > | cur.execute(sql_ProjectsTable) > | cur.close() > | except Error as e: > | print(e) Use of try: except: finally: may come in handy for clean closure. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B -- https://mail.python.org/mailman/listinfo/python-list
Python/SQLite best practices
I'm looking for some tips from experienced hands on on this subject. Some of the areas of interest are (feel free to add more): * Passing connections and cursors - good, bad indifferent? I try to avoid passing file handles unless necessary, so I view connections and cursors the same. Though that said, I'm not aware of any specific problems in doing so. For designs with multiple tables: * Better to pass an sql string to functions that create/add data/update/delete data and pass them to create, insert, update, delete functions; or have those functions for each table? Taking table creation for example, if there are five tables, and the sql string is passed, there would need to be six functions to do it, though the complexity of each function may be reduced a little. [table1create with sql and establishing a cursor, to table5create and then a function that executes the sql]. Best way to establish the connection and cursor, as well as close them? I have seen many ways to do this, and know that the with block can be used to create a connection and close it automatically, but the same is not true of the cursor. Also, using only a with block does not handle any errors as well as a try/with. For example: | try: | # Use with block to create connection – it will close self. | with sqlite3.connect(path) as conn: | cur = conn.cursor() | cur.execute(sql_ProjectsTable) | cur.close() | except Error as e: | print(e) What else? Dave, -- https://mail.python.org/mailman/listinfo/python-list