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
