Re: Context manager for database connection
On 24/08/2023 06.11, dn via Python-list wrote:
On 24/08/2023 03.41, Jason Friedman via Python-list wrote:
with Database() as mydb:
conn = mydb.get_connection()
cursor = conn.get_cursor()
cursor.execute("update table1 set x = 1 where y = 2")
cursor.close()
cursor = conn.get_cursor()
cursor.execute("update table2 set a = 1 where b = 2")
cursor.close()
import jaydebeapi as jdbc
class Database:
database_connection = None
def __init__(self, auto_commit: bool = False):
self.database_connection = jdbc.connect(...)
self.database_connection.jconn.setAutoCommit(auto_commit)
def __enter__(self) -> jdbc.Connection:
return self
def __exit__(self, exception_type: Optional[Type[BaseException]],
exception_value: Optional[BaseException],
traceback: Optional[types.TracebackType]) -> bool:
if exception_type:
self.database_connection.rollback()
else:
self.database_connection.commit()
self.database_connection.close()
def get_connection(self) -> jdbc.Connection:
return self.database_connection
Using a context-manager is a good idea: it ensures clean-up with/without
an exception occurring. Accordingly, I (and may I presume, most) like
the idea when working with life-cycle type resources, eg I/O. Absolutely
nothing wrong with the idea!
However, the scope of a c-m is the with-block. If there are a number of
'nested operations' to be performed (which could conceivably involve
other c-ms, loops, or similar code-structures) the code could become
harder to read and the length of the scope unwieldy.
An ease of management tactic is being able to see the start and end of a
construct on the same page/screen. Such would 'limit' the length of a
c-m's scope.
Perhaps drawing an inappropriate parallel, but like a try-except block,
there seems virtue in keeping a c-m's scope short, eg releasing
resources such as a file opened for output, and some basic DBMS-es which
don't offer multi-access.
Accordingly, why I stopped using a c-m for database work. NB YMMV!
There were two other reasons:
1 multiple databases
2 principles (and my shining virtue (?) )
1 came across a (statistics) situation where the client was using two
DBMS-es. They'd used one for some time, but then preferred another. For
their own reasons, they didn't migrate old data to the new DBMS. Thus,
when performing certain analyses, the data-collection part of the script
might have to utilise different DB 'sources'. In at least one case,
comparing data through time, the routine needed to access both DBMS-es.
(oh what a tangled web we weave...)
2 another situation where the script may or may not actually have needed
to access the DB. Odd? In which case, the 'expense' of the 'entry' and
'exit' phases would never be rewarded.
Thus, 'inspired' to realise that had (long) been contravening SOLID's
DSP advice?rule (Dependency Inversion Principle).
Accordingly, these days adopt something like the following (explaining
it 'backwards' to aid comprehension in case you (gentle reader) have not
come-across such thinking before - remember that word, "inversion"!)
- in the mainline, prior to processing, instantiate a database object
database = Database( credentials )
- assume the mainline calls a function which is the substance of the script:
def just_do_it( database_instance, other_args, ):
while "there's work to be done":
database.query( query, data, etc, )
# could be SELECT or UPDATE in and amongst the 'work'
- a basic outline of query() might be:
def query( self, sql, data, etc, ):
cursor = self.get_cursor()
cursor.execute( sql, data, ) # according to DB/connector, etc
# return query results
- a query can't happen without a "cursor", so either use an existing
cursor, or create a fresh one:
def get_cursor( self ):
if not self._cursor:
connection = self.get_connection()
self._cursor = connection.cursor()
return self._cursor
NB assuming the DBMS has restrictions on cursors, I may have multiple
connections with one cursor each, but in some situations it may be
applicable to run multiple cursors through a single connection.
- a cursor can't exist without a "connection", so either ... :
def get_connection( self ):
if not self._connection:
self._connection = # connect to the DB
return self._connection
- to instantiate a DB-object in the first place, the class definition:
class Database:
def __init__(self):
self._connection = None
self._cursor = None
- and the one part of the exposition that's not 'backwards':
def close(self):
if self._connection:
self._connection.close()
It might be a plan to have several query() methods, according to
application. If each is a dedicated query, such avoids the
Re: Context manager for database connection
On 24/08/2023 03.41, Jason Friedman via Python-list wrote:
I want to be able to write code like this:
with Database() as mydb:
conn = mydb.get_connection()
cursor = conn.get_cursor()
cursor.execute("update table1 set x = 1 where y = 2")
cursor.close()
cursor = conn.get_cursor()
cursor.execute("update table2 set a = 1 where b = 2")
cursor.close()
I'd like for both statements to succeed and commit, or if either fails to
stop and for all to rollback.
Is what I have below correct?
import jaydebeapi as jdbc
class Database:
database_connection = None
def __init__(self, auto_commit: bool = False):
self.database_connection = jdbc.connect(...)
self.database_connection.jconn.setAutoCommit(auto_commit)
def __enter__(self) -> jdbc.Connection:
return self
def __exit__(self, exception_type: Optional[Type[BaseException]],
exception_value: Optional[BaseException],
traceback: Optional[types.TracebackType]) -> bool:
if exception_type:
self.database_connection.rollback()
else:
self.database_connection.commit()
self.database_connection.close()
def get_connection(self) -> jdbc.Connection:
return self.database_connection
Looking good!
Assuming this is the only DB-interaction, a context-manager seems
appropriate. If the real use-case calls for more interactions, the cost
of establishing and breaking DB-connections becomes a consideration.
Alternately, the 'length'?'life' of the context-manager *might*
complicate things.
Intriguing that given such a start, the code doesn't feature a
context-manager for a query.
That two cursors are established is also a 'cost'. Could both queries
utilise the same cursor?
(in which case, could consider adding to __init__() or __enter__(), and
close in __exit__() )
Because the context-manager has been implemented as a class, there is no
reason why one can't add more methods to that class (it doesn't need to
be limited to the functional __enter__() and __exit__() methods!
Indeed there is already get_connection(). Why not also a query( self,
sql-code ) method?
These might reduce the mainline-code to something like:
if __name__ == "__main__":
with Database() as mydb:
mydb.query( "update table1 set x = 1 where y = 2" )
mydb.query( "update table2 set a = 1 where b = 2" )
--
Regards,
=dn
--
https://mail.python.org/mailman/listinfo/python-list
Context manager for database connection
I want to be able to write code like this:
with Database() as mydb:
conn = mydb.get_connection()
cursor = conn.get_cursor()
cursor.execute("update table1 set x = 1 where y = 2")
cursor.close()
cursor = conn.get_cursor()
cursor.execute("update table2 set a = 1 where b = 2")
cursor.close()
I'd like for both statements to succeed and commit, or if either fails to
stop and for all to rollback.
Is what I have below correct?
import jaydebeapi as jdbc
class Database:
database_connection = None
def __init__(self, auto_commit: bool = False):
self.database_connection = jdbc.connect(...)
self.database_connection.jconn.setAutoCommit(auto_commit)
def __enter__(self) -> jdbc.Connection:
return self
def __exit__(self, exception_type: Optional[Type[BaseException]],
exception_value: Optional[BaseException],
traceback: Optional[types.TracebackType]) -> bool:
if exception_type:
self.database_connection.rollback()
else:
self.database_connection.commit()
self.database_connection.close()
def get_connection(self) -> jdbc.Connection:
return self.database_connection
--
https://mail.python.org/mailman/listinfo/python-list
Re: Best practice for database connection
On 01/06/2023 06.45, Thomas Passin wrote:
On 5/31/2023 2:10 PM, Jason Friedman wrote:
I'm trying to reconcile two best practices which seem to conflict.
1) Use a _with_ clause when connecting to a database so the connection is
closed in case of premature exit.
class_name = 'oracle.jdbc.OracleDriver'
url = f"jdbc:oracle:thin:@//{host_name}:{port_number}/{database_name}"
with jdbc.connect(class_name, url, [user_name, password],
jdbc_jar_file.as_posix()) as connection:
logger.info(f"Connected.")
2) Use self-made functions to streamline code. For example, there are
several places I need to know if the database object is a particular
type,
so I create a function like this:
foobar_set = set()
...
def is_foobar(connection: jdbc.Connection, name: str) -> bool:
"""
:param connection: connection object
:param name: owner.object
:return: True if this object is of type foobar
"""
global foobar_set
if not foobar_set:
query = f"""select stuff from stuff"""
cursor = connection.cursor()
cursor.execute(query)
for owner, object_name in cursor.fetchall():
foobar_set.add(f"{owner}.{object_name}")
cursor.close()
return name.upper() in foobar_set
But that requires that I call is_foobar() with a connection object.
Essentially I'd like a function that leverages the one connection I
create
at the beginning using a with clause.
If you need to have a connection object that persists outside of the
with block, then
1. you can just not use a "with" block:
connection = jdbc.connect(class_name, url, [user_name, password],
jdbc_jar_file.as_posix())
You will want to close the connection yourself when you are done with it.
2. do all the subsequent work within the "with" block.
As with many such questions, the answer is "it depends". Sadly that's no
help!
Please consider: is the database critical to this application? In other
words, if the script is unable to proceed without access, eg RDBMS is
down or credentials are not accepted, then must the logic simply stop?
Most of mine fit into that category. Accordingly, script-execution
starts with setting the environment, eg gathering credentials; followed
by establishing a connection to the RDBMS. An operational RDBMS is part
of the environment! Now (wait for many of our colleagues to hurriedly
suck in their breath) we can see that the connection is a global-value -
something which resides in a base "frame" of the Python stack, and is
accordingly available (R-O) 'everywhere'.
NB when I say "connection", it is part of a wider RDBMS-interface object.
If you wish to use a Context Manager, then its only content may be a
call to main() - or preferably a function which better describes what
the application will do. (per @Thomas' contribution)
PS I don't like using the global keyword/command, but prefer to pass the
connection as a parameter. A matter of style? A "contract":
no-connection, no-query? YMMV...
Another approach might be to enact the Dependency Inversion Principle
(one of 'Uncle Bob' Martin's SOLID Principles. In this case, proceed
with the application, and when it comes time to issue a query against
the database, ask the question: "does a connection exist?". In the
negative case, then call a routine which establishes the connector,
passes that to the query-routine, and proceeds.
Assuming multiple query-routines, the problem with this is that it takes
some sleight-of-hand to create a shared connection. Alternately, maybe
the application is prepared to assume the 'cost' of creating multiple
connections (over time) because the RDBMS-interactions are few-and-far
between, or only a small part of the total.
I don't use one, but others enthuse about ORMs, eg SQLAlchemy. This
suits those who combine RDBMS and OOP, and has its own persistence
methodology.
--
Regards,
=dn
--
https://mail.python.org/mailman/listinfo/python-list
Re: Best practice for database connection
On 5/31/2023 2:10 PM, Jason Friedman wrote:
I'm trying to reconcile two best practices which seem to conflict.
1) Use a _with_ clause when connecting to a database so the connection is
closed in case of premature exit.
class_name = 'oracle.jdbc.OracleDriver'
url = f"jdbc:oracle:thin:@//{host_name}:{port_number}/{database_name}"
with jdbc.connect(class_name, url, [user_name, password],
jdbc_jar_file.as_posix()) as connection:
logger.info(f"Connected.")
2) Use self-made functions to streamline code. For example, there are
several places I need to know if the database object is a particular type,
so I create a function like this:
foobar_set = set()
...
def is_foobar(connection: jdbc.Connection, name: str) -> bool:
"""
:param connection: connection object
:param name: owner.object
:return: True if this object is of type foobar
"""
global foobar_set
if not foobar_set:
query = f"""select stuff from stuff"""
cursor = connection.cursor()
cursor.execute(query)
for owner, object_name in cursor.fetchall():
foobar_set.add(f"{owner}.{object_name}")
cursor.close()
return name.upper() in foobar_set
But that requires that I call is_foobar() with a connection object.
Essentially I'd like a function that leverages the one connection I create
at the beginning using a with clause.
If you need to have a connection object that persists outside of the
with block, then
1. you can just not use a "with" block:
connection = jdbc.connect(class_name, url, [user_name, password],
jdbc_jar_file.as_posix())
You will want to close the connection yourself when you are done with it.
2. do all the subsequent work within the "with" block.
--
https://mail.python.org/mailman/listinfo/python-list
Best practice for database connection
I'm trying to reconcile two best practices which seem to conflict.
1) Use a _with_ clause when connecting to a database so the connection is
closed in case of premature exit.
class_name = 'oracle.jdbc.OracleDriver'
url = f"jdbc:oracle:thin:@//{host_name}:{port_number}/{database_name}"
with jdbc.connect(class_name, url, [user_name, password],
jdbc_jar_file.as_posix()) as connection:
logger.info(f"Connected.")
2) Use self-made functions to streamline code. For example, there are
several places I need to know if the database object is a particular type,
so I create a function like this:
foobar_set = set()
...
def is_foobar(connection: jdbc.Connection, name: str) -> bool:
"""
:param connection: connection object
:param name: owner.object
:return: True if this object is of type foobar
"""
global foobar_set
if not foobar_set:
query = f"""select stuff from stuff"""
cursor = connection.cursor()
cursor.execute(query)
for owner, object_name in cursor.fetchall():
foobar_set.add(f"{owner}.{object_name}")
cursor.close()
return name.upper() in foobar_set
But that requires that I call is_foobar() with a connection object.
Essentially I'd like a function that leverages the one connection I create
at the beginning using a with clause.
--
https://mail.python.org/mailman/listinfo/python-list
Re: try pattern for database connection with the close method
On 22/02/2015 18:41, Mario Figueiredo wrote:
On Sat, 21 Feb 2015 12:22:58 +, Mark Lawrence
breamore...@yahoo.co.uk wrote:
Use your context manager at the outer level.
import sqlite3 as lite
try:
with lite.connect('data.db') as db:
try:
db.execute(sql, parms)
except lite.IntegrityError:
raise ValueError('invalid data')
except lite.DatabaseError:
raise OSError('database file corrupt or not found.')
The sqlite context manager doesn't close a database connection on
exit. It only ensures, commits and rollbacks are performed.
Where in the documentation does it state that? If it does, it certainly
breaks my expectations, as I understood the whole point of Python
context managers is to do the tidying up for you. Or have you misread
what it says here
https://docs.python.org/3/library/sqlite3.html#using-the-connection-as-a-context-manager
?
import sqlite3
with
sqlite3.connect(r'C:\Users\Mark\Documents\Cash\Data\cash.sqlite') as db:
... db.execute('select count(*) from accounts')
...
sqlite3.Cursor object at 0x032C70A0
db.close()
Looks like you're correct. Knock me down with a feather, Clevor Trevor.
--
My fellow Pythonistas, ask not what our language can do for you, ask
what you can do for our language.
Mark Lawrence
--
https://mail.python.org/mailman/listinfo/python-list
Re: try pattern for database connection with the close method
On Sun, Feb 22, 2015 at 12:41 PM, Mario Figueiredo mar...@gmail.com wrote:
The sqlite context manager doesn't close a database connection on
exit. It only ensures, commits and rollbacks are performed.
Sorry, I haven't paid careful attention to this thread, so perhaps
this has already been suggested, however... Can't you write your own
class which delegates to the necessary sqlite3 bits and has a context
manager with the desired behavior? Thinking out loud, you could define
a ConnectionMgr class which accepts a sqlite3 connection as a
parameter:
class ConnectionMgr(object):
def __init__(self, conn):
self.conn = conn
def __enter__(self):
...
def __exit__(self, type, value, exception):
if self.conn is not None:
... close self.conn connection here ...
self.conn = None
def __getattr__(self, attr):
return getattr(self.conn, attr)
then...
try:
with MyConnection(lite.connect('data.db')) as db:
...
except lite.DatabaseError:
...
Might also have to __enter__ and __exit__ self.conn as appropriate.
Skip
--
https://mail.python.org/mailman/listinfo/python-list
Re: try pattern for database connection with the close method
On Sat, 21 Feb 2015 12:22:58 +, Mark Lawrence
breamore...@yahoo.co.uk wrote:
Use your context manager at the outer level.
import sqlite3 as lite
try:
with lite.connect('data.db') as db:
try:
db.execute(sql, parms)
except lite.IntegrityError:
raise ValueError('invalid data')
except lite.DatabaseError:
raise OSError('database file corrupt or not found.')
The sqlite context manager doesn't close a database connection on
exit. It only ensures, commits and rollbacks are performed.
--
https://mail.python.org/mailman/listinfo/python-list
Re: try pattern for database connection with the close method
On Sun, 22 Feb 2015 13:15:09 -0600, Skip Montanaro skip.montan...@gmail.com wrote: Sorry, I haven't paid careful attention to this thread, so perhaps this has already been suggested, however... Can't you write your own class which delegates to the necessary sqlite3 bits and has a context manager with the desired behavior? Thinking out loud, you could define a ConnectionMgr class which accepts a sqlite3 connection as a parameter Indeed I could. Thank you. -- https://mail.python.org/mailman/listinfo/python-list
Re: try pattern for database connection with the close method
On Sat, 21 Feb 2015 16:22:36 +0100, Peter Otten __pete...@web.de wrote: Why would you care about a few lines? You don't repeat them, do you? Put the code into a function or a context manager and invoke it with Thanks for the suggestions that followed. -- https://mail.python.org/mailman/listinfo/python-list
Re: try pattern for database connection with the close method
On Sun, 22 Feb 2015 19:07:03 +, Mark Lawrence breamore...@yahoo.co.uk wrote: Looks like you're correct. Knock me down with a feather, Clevor Trevor. It took me by surprise when I first encountered it too. The rationale apparently is that the context manager is strictly a transactional feature, allowing for multiple context managers within the same connection to properly perform commits and rollbacks on multiple transactions. -- https://mail.python.org/mailman/listinfo/python-list
Re: try pattern for database connection with the close method
Mario Figueiredo wrote:
Hello all,
I'm using the following pattern for db access that requires me to
close the connection as soon as it is not needed:
import sqlite3 as lite
try:
db = lite.connect('data.db')
except lite.DatabaseError:
raise OSError('database file corrupt or not found.')
else:
try:
with db:
db.execute(sql, parms)
except lite.IntegrityError:
raise ValueError('invalid data')
finally:
db.close()
Since it's a bit verbose,
Why would you care about a few lines? You don't repeat them, do you? Put the
code into a function or a context manager and invoke it with
my_execute(sql, parms)
or
with my_db() as db:
... db.execute(sql, parms)
is there a better way?
Note: The user of this API has the whole database functionality
abstracted away. Hence the exception channeling in the except clauses.
db.execute() may trigger other sqlite-related errors including
DatabaseError:
import sqlite3
db = sqlite3.connect(/dev/full)
db.execute(create table foo (bar, baz);)
Traceback (most recent call last):
File stdin, line 1, in module
sqlite3.OperationalError: unable to open database file
import os
with open(data.db, wb) as f:
... f.write(os.urandom(1024)) # put random bytes into data.db
... # chances of creating a valid db
... # left as an exercise ;)
...
1024
db = sqlite3.connect(data.db)
db.execute(create table foo (bar, baz);)
Traceback (most recent call last):
File stdin, line 1, in module
sqlite3.DatabaseError: file is encrypted or is not a database
If you want to catch these, too:
@contextlib.contextmanager
def my_db():
db = None
try:
db = sqlite3.connect(data.db)
with db:
yield db # db.execute() if you don't buy
# into the contextmanager idea
except sqlite3.IntegrityError:
raise ValueError
except sqlite3.DatabaseError:
raise OSError
except sqlite3.Error:
raise WhateverYouNeed
finally:
if db is not None:
db.close()
with my_db() as db:
db.execute(select * from sqlite_master;)
--
https://mail.python.org/mailman/listinfo/python-list
Re: try pattern for database connection with the close method
On Sat, Feb 21, 2015 at 5:22 AM, Mark Lawrence breamore...@yahoo.co.uk wrote:
On 21/02/2015 02:42, Mario Figueiredo wrote:
Hello all,
I'm using the following pattern for db access that requires me to
close the connection as soon as it is not needed:
import sqlite3 as lite
try:
db = lite.connect('data.db')
except lite.DatabaseError:
raise OSError('database file corrupt or not found.')
else:
try:
with db:
db.execute(sql, parms)
except lite.IntegrityError:
raise ValueError('invalid data')
finally:
db.close()
Since it's a bit verbose, is there a better way?
Note: The user of this API has the whole database functionality
abstracted away. Hence the exception channeling in the except clauses.
Use your context manager at the outer level.
import sqlite3 as lite
try:
with lite.connect('data.db') as db:
try:
db.execute(sql, parms)
except lite.IntegrityError:
raise ValueError('invalid data')
except lite.DatabaseError:
raise OSError('database file corrupt or not found.')
This could result in the OSError being misleadingly raised due to some
DatabaseError raised by the execute rather than the connect.
--
https://mail.python.org/mailman/listinfo/python-list
Re: try pattern for database connection with the close method
Ian Kelly wrote:
On Sat, Feb 21, 2015 at 5:22 AM, Mark Lawrence breamore...@yahoo.co.uk
wrote:
On 21/02/2015 02:42, Mario Figueiredo wrote:
Hello all,
I'm using the following pattern for db access that requires me to
close the connection as soon as it is not needed:
import sqlite3 as lite
try:
db = lite.connect('data.db')
except lite.DatabaseError:
raise OSError('database file corrupt or not found.')
else:
try:
with db:
db.execute(sql, parms)
except lite.IntegrityError:
raise ValueError('invalid data')
finally:
db.close()
Since it's a bit verbose, is there a better way?
Note: The user of this API has the whole database functionality
abstracted away. Hence the exception channeling in the except clauses.
Use your context manager at the outer level.
import sqlite3 as lite
try:
with lite.connect('data.db') as db:
try:
db.execute(sql, parms)
except lite.IntegrityError:
raise ValueError('invalid data')
except lite.DatabaseError:
raise OSError('database file corrupt or not found.')
This could result in the OSError being misleadingly raised due to some
DatabaseError raised by the execute rather than the connect.
The OP probably wants to catch these DatabaseErrors, too. Also, the chance
of a misleading traceback has been greatly reduced with the advent of
chained exceptions.
--
https://mail.python.org/mailman/listinfo/python-list
Re: try pattern for database connection with the close method
On Sat, Feb 21, 2015 at 8:27 AM, Peter Otten __pete...@web.de wrote:
Ian Kelly wrote:
On Sat, Feb 21, 2015 at 5:22 AM, Mark Lawrence breamore...@yahoo.co.uk
wrote:
try:
with lite.connect('data.db') as db:
try:
db.execute(sql, parms)
except lite.IntegrityError:
raise ValueError('invalid data')
except lite.DatabaseError:
raise OSError('database file corrupt or not found.')
This could result in the OSError being misleadingly raised due to some
DatabaseError raised by the execute rather than the connect.
The OP probably wants to catch these DatabaseErrors, too. Also, the chance
of a misleading traceback has been greatly reduced with the advent of
chained exceptions.
Yes, but the point is that OSError is probably inappropriate in that case.
--
https://mail.python.org/mailman/listinfo/python-list
Re: try pattern for database connection with the close method
Ian Kelly wrote:
On Sat, Feb 21, 2015 at 8:27 AM, Peter Otten __pete...@web.de wrote:
Ian Kelly wrote:
On Sat, Feb 21, 2015 at 5:22 AM, Mark Lawrence breamore...@yahoo.co.uk
wrote:
try:
with lite.connect('data.db') as db:
try:
db.execute(sql, parms)
except lite.IntegrityError:
raise ValueError('invalid data')
except lite.DatabaseError:
raise OSError('database file corrupt or not found.')
This could result in the OSError being misleadingly raised due to some
DatabaseError raised by the execute rather than the connect.
The OP probably wants to catch these DatabaseErrors, too. Also, the
chance of a misleading traceback has been greatly reduced with the advent
of chained exceptions.
Yes, but the point is that OSError is probably inappropriate in that case.
Perhaps, but the example I gave in my other post:
with open(data.db, wb) as f:
... f.write(os.urandom(1024)) # put random bytes into data.db
db = sqlite3.connect(data.db)
db.execute(create table foo (bar, baz);)
Traceback (most recent call last):
File stdin, line 1, in module
sqlite3.DatabaseError: file is encrypted or is not a database
matches the database file corrupt part of the error message provided by
the OP.
--
https://mail.python.org/mailman/listinfo/python-list
Re: try pattern for database connection with the close method
On 21/02/2015 02:42, Mario Figueiredo wrote:
Hello all,
I'm using the following pattern for db access that requires me to
close the connection as soon as it is not needed:
import sqlite3 as lite
try:
db = lite.connect('data.db')
except lite.DatabaseError:
raise OSError('database file corrupt or not found.')
else:
try:
with db:
db.execute(sql, parms)
except lite.IntegrityError:
raise ValueError('invalid data')
finally:
db.close()
Since it's a bit verbose, is there a better way?
Note: The user of this API has the whole database functionality
abstracted away. Hence the exception channeling in the except clauses.
Use your context manager at the outer level.
import sqlite3 as lite
try:
with lite.connect('data.db') as db:
try:
db.execute(sql, parms)
except lite.IntegrityError:
raise ValueError('invalid data')
except lite.DatabaseError:
raise OSError('database file corrupt or not found.')
--
My fellow Pythonistas, ask not what our language can do for you, ask
what you can do for our language.
Mark Lawrence
--
https://mail.python.org/mailman/listinfo/python-list
Re: try pattern for database connection with the close method
On Fri, Feb 20, 2015 at 6:42 PM, Mario Figueiredo mar...@gmail.com wrote:
import sqlite3 as lite
try:
db = lite.connect('data.db')
except lite.DatabaseError:
raise OSError('database file corrupt or not found.')
else:
try:
with db:
db.execute(sql, parms)
except lite.IntegrityError:
raise ValueError('invalid data')
finally:
db.close()
Two comments:
You could remove the else statement, as it will work exactly the same
with or without it. This will reduce the indentation of the bulk of the
code by 1 level.
You MIGHT be able to remove the finally...close as the with-statement
probably does the same thing. I do not know sqlite3, however, so it may do
something different, such as committing, but that would normally be on some
transition object you get from a call.
Basically, you could probably get the same result with (untested):
try:
db = lite.connect('data.db')
except lite.DatabaseError:
raise OSError('database file corrupt or not found.')
try:
with db:
db.execute(sql, parms)
except lite.IntegrityError:
raise ValueError('invalid data')
# You may still need the finally, depending on what the with statement does
in sqlite3 - you'd have to check the documentation.
Chris
--
https://mail.python.org/mailman/listinfo/python-list
try pattern for database connection with the close method
Hello all,
I'm using the following pattern for db access that requires me to
close the connection as soon as it is not needed:
import sqlite3 as lite
try:
db = lite.connect('data.db')
except lite.DatabaseError:
raise OSError('database file corrupt or not found.')
else:
try:
with db:
db.execute(sql, parms)
except lite.IntegrityError:
raise ValueError('invalid data')
finally:
db.close()
Since it's a bit verbose, is there a better way?
Note: The user of this API has the whole database functionality
abstracted away. Hence the exception channeling in the except clauses.
--
https://mail.python.org/mailman/listinfo/python-list
Re: database connection
En Fri, 07 Oct 2011 02:18:04 -0300, masood shaik masood@gmail.com escribió: can u please tell me how we can connect to database without changing the permission of db file using sqlite3 The OS user who executes the Python script must have read (and write, usually) access to the database file - *any* OS user who can read the database file can connect to it. sqlite does not have internal users, and does not implement GRANT/REVOKE statements. -- Gabriel Genellina -- http://mail.python.org/mailman/listinfo/python-list
database connection
Hi can u please tell me how we can connect to database without changing the permission of db file using sqlite3 -- http://mail.python.org/mailman/listinfo/python-list
database connection error - postgresql
Hi,
*code:* (only the class definiton and Database connection part)
import pg
__metaclass__=type
class addbook:
conn=pg.connect('secondbooks.db')
curs=conn.cursor()
*error:*
conn=pg.connect(secondbooks.db)
pg.InternalError: FATAL: database secondbooks.db does not exist
In fact i have a database called secondbooks in postgresql.
Pls help on this as i am new to python.
Thanks in advance
Bijoy
--
http://mail.python.org/mailman/listinfo/python-list
Re: database connection error - postgresql
hi,
I figured out these too.
Pls excuse me
Thanks
Bijoy
On Mon, Mar 30, 2009 at 1:30 PM, bijoy bijoy.fra...@gmail.com wrote:
Hi,
*code:* (only the class definiton and Database connection part)
import pg
__metaclass__=type
class addbook:
conn=pg.connect('secondbooks.db')
curs=conn.cursor()
*error:*
conn=pg.connect(secondbooks.db)
pg.InternalError: FATAL: database secondbooks.db does not exist
In fact i have a database called secondbooks in postgresql.
Pls help on this as i am new to python.
Thanks in advance
Bijoy
--
http://mail.python.org/mailman/listinfo/python-list
Re: database connection error - postgresql
On Mon, 30 Mar 2009 13:30:18 +0530 bijoy bijoy.fra...@gmail.com wrote: conn=pg.connect(secondbooks.db) pg.InternalError: FATAL: database secondbooks.db does not exist In fact i have a database called secondbooks in postgresql. If it is called secondbooks then why are you connecting to secondbooks.db in the code? -- D'Arcy J.M. Cain da...@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. -- http://mail.python.org/mailman/listinfo/python-list
Keeping a database connection with a Singleton?
I remember reading about the Singleton pattern in python and how it's an unpythonic pattern and all. At the time I did not need the Singleton anyways, so I just glanced over the document. But, setting this aside: I have an application where I have a connection to a database. At some point in the application I open up a new window. The new windows resides in a different module. So I have a directory structure like this: - mainapp.py - newwindow.py So I import newwindow in mainapp so I can instantiate and display it. Meanwhile, the main-app has an open connection to the database. What's the cleanest way to hand this connection to the new window? I can see several possibilities: 1) Simply pass the connection as paramtere to the constructor of new- window. 2) Use the Singleton deisign pattern to keep a reference to the connection 3) Open up a completely new connection to the database in the new window. Now, option 1) is clearly the easiest to implement, however, I somehow tend to use option 2 (the singleton) as it's more flexible. Option 3 looks ugly to me. This is a stuation I run into many times. And I am always faced with the same choice. And I never know which one to chose. And now that I am getting more and more comfortable with the basics of python, I would like to know if I am missing something more pythonic. So, what would you recommend? -- http://mail.python.org/mailman/listinfo/python-list
Re: Keeping a database connection with a Singleton?
On Sep 19, 7:26 am, exhuma.twn [EMAIL PROTECTED] wrote: I remember reading about the Singleton pattern in python and how it's an unpythonic pattern and all. At the time I did not need the Singleton anyways, so I just glanced over the document. But, setting this aside: I have an application where I have a connection to a database. At some point in the application I open up a new window. The new windows resides in a different module. So I have a directory structure like this: - mainapp.py - newwindow.py So I import newwindow in mainapp so I can instantiate and display it. Meanwhile, the main-app has an open connection to the database. What's the cleanest way to hand this connection to the new window? I can see several possibilities: 1) Simply pass the connection as paramtere to the constructor of new- window. 2) Use the Singleton deisign pattern to keep a reference to the connection 3) Open up a completely new connection to the database in the new window. Now, option 1) is clearly the easiest to implement, however, I somehow tend to use option 2 (the singleton) as it's more flexible. Option 3 looks ugly to me. This is a stuation I run into many times. And I am always faced with the same choice. And I never know which one to chose. And now that I am getting more and more comfortable with the basics of python, I would like to know if I am missing something more pythonic. So, what would you recommend? You don't need a way to make every instance the same. You need a way where every instance shares the same state. Aka, the Borg pattern, courtesy of Alex Martelli. It's in the Python Cookbook at http:// aspn.activestate.com/ASPN/Cookbook/Python/Recipe/66531. Good luck! --Jason -- http://mail.python.org/mailman/listinfo/python-list
Re: Keeping a database connection with a Singleton?
exhuma.twn wrote: I remember reading about the Singleton pattern in python and how it's an unpythonic pattern and all. At the time I did not need the Singleton anyways, so I just glanced over the document. But, setting this aside: I have an application where I have a connection to a database. At some point in the application I open up a new window. The new windows resides in a different module. So I have a directory structure like this: - mainapp.py - newwindow.py So I import newwindow in mainapp so I can instantiate and display it. Meanwhile, the main-app has an open connection to the database. What's the cleanest way to hand this connection to the new window? I can see several possibilities: 1) Simply pass the connection as paramtere to the constructor of new- window. 2) Use the Singleton deisign pattern to keep a reference to the connection 3) Open up a completely new connection to the database in the new window. Now, option 1) is clearly the easiest to implement, however, I somehow tend to use option 2 (the singleton) as it's more flexible. Option 3 looks ugly to me. This is a stuation I run into many times. And I am always faced with the same choice. And I never know which one to chose. And now that I am getting more and more comfortable with the basics of python, I would like to know if I am missing something more pythonic. So, what would you recommend? Passing the connection as a parameter is the best approach because it keeps the dependencies explicit. Also, it allows you to switch between singleton and one connection per window without ever touching the newwindow module. By the way, there is a pythonic (near) singleton: the module. So if you go with option 2, just move the connection setup into a separate module that you can import into client code. Peter -- http://mail.python.org/mailman/listinfo/python-list
Re: Keeping a database connection with a Singleton?
On Sep 19, 3:45 pm, Peter Otten [EMAIL PROTECTED] wrote: exhuma.twn wrote: [...] By the way, there is a pythonic (near) singleton: the module. So if you go with option 2, just move the connection setup into a separate module that you can import into client code. Peter You say (near) singleton. What's the difference then? -- http://mail.python.org/mailman/listinfo/python-list
Re: Keeping a database connection with a Singleton?
exhuma.twn wrote: On Sep 19, 3:45 pm, Peter Otten [EMAIL PROTECTED] wrote: exhuma.twn wrote: [...] By the way, there is a pythonic (near) singleton: the module. So if you go with option 2, just move the connection setup into a separate module that you can import into client code. Peter You say (near) singleton. What's the difference then? I was thinking of the main script of an application. If you import that by its name import main # assuming the file is main.py you end up with two instances sys.modules[main] and sys.modules[__main__]. Peter -- http://mail.python.org/mailman/listinfo/python-list
Re: Keeping a database connection with a Singleton?
On Sep 19, 4:03 pm, Peter Otten [EMAIL PROTECTED] wrote: exhuma.twn wrote: On Sep 19, 3:45 pm, Peter Otten [EMAIL PROTECTED] wrote: exhuma.twn wrote: [...] By the way, there is a pythonic (near) singleton: the module. So if you go with option 2, just move the connection setup into a separate module that you can import into client code. Peter You say (near) singleton. What's the difference then? I was thinking of the main script of an application. If you import that by its name import main # assuming the file is main.py you end up with two instances sys.modules[main] and sys.modules[__main__]. Peter I see. Thanks. I will give it a go. -- http://mail.python.org/mailman/listinfo/python-list
Re: MS SQL Database connection
Hitesh wrote:
Hi currently I am using DNS and ODBC to connect to MS SQL database.
Is there any other non-dns way to connect? If I want to run my script
from different server I first have to create the DNS in win2k3.
Here are several ways to connect to an MSSQL database w/o
having to create DNS or anything else in win2k3 ;)
There are other ways (the slightly stale MSSQL module
from Object Craft, for example, which still works fine
for Python = 2.3).
TJG
code
def adodbapi_connection (server, database, username, password):
#
# http://adodbapi.sf.net
#
import adodbapi
connectors = [Provider=SQLOLEDB]
connectors.append (Data Source=%s % server)
connectors.append (Initial Catalog=%s % database)
if username:
connectors.append (User Id=%s % username)
connectors.append (Password=%s % password)
else:
connectors.append(Integrated Security=SSPI)
return adodbapi.connect (;.join (connectors))
def pymssql_connection (server, database, username, password):
#
# http://pymssql.sf.net
#
import pymssql
if not username:
raise RuntimeError, Unable to use NT authentication for pymssql
return pymssql.connect (user=username, password=password,
host=server, database=database)
def pyodbc_connection (server, database, username, password):
#
# http://pyodbc.sf.net
#
import pyodbc
connectors = [Driver={SQL Server}]
connectors.append (Server=%s % server)
connectors.append (Database=%s % database)
if username:
connectors.append (UID=%s % username)
connectors.append (PWD=%s % password)
else:
connectors.append (TrustedConnection=Yes)
return pyodbc.connect (;.join (connectors))
/code
--
http://mail.python.org/mailman/listinfo/python-list
Re: MS SQL Database connection
On Mar 5, 4:44 am, Tim Golden [EMAIL PROTECTED] wrote:
Hitesh wrote:
Hi currently I am using DNS and ODBC to connect to MS SQL database.
Is there any other non-dns way to connect? If I want to run my script
from different server I first have to create the DNS in win2k3.
Here are several ways to connect to an MSSQL database w/o
having to create DNS or anything else in win2k3 ;)
There are other ways (the slightly stale MSSQL module
from Object Craft, for example, which still works fine
for Python = 2.3).
TJG
code
def adodbapi_connection (server, database, username, password):
#
#http://adodbapi.sf.net
#
import adodbapi
connectors = [Provider=SQLOLEDB]
connectors.append (Data Source=%s % server)
connectors.append (Initial Catalog=%s % database)
if username:
connectors.append (User Id=%s % username)
connectors.append (Password=%s % password)
else:
connectors.append(Integrated Security=SSPI)
return adodbapi.connect (;.join (connectors))
def pymssql_connection (server, database, username, password):
#
#http://pymssql.sf.net
#
import pymssql
if not username:
raise RuntimeError, Unable to use NT authentication for pymssql
return pymssql.connect (user=username, password=password,
host=server, database=database)
def pyodbc_connection (server, database, username, password):
#
#http://pyodbc.sf.net
#
import pyodbc
connectors = [Driver={SQL Server}]
connectors.append (Server=%s % server)
connectors.append (Database=%s % database)
if username:
connectors.append (UID=%s % username)
connectors.append (PWD=%s % password)
else:
connectors.append (TrustedConnection=Yes)
return pyodbc.connect (;.join (connectors))
/code
Thank you.
And I yes I meant DSN not DNS (my mistake, thank you for catching
it ;)
hj
--
http://mail.python.org/mailman/listinfo/python-list
MS SQL Database connection
Hi currently I am using DNS and ODBC to connect to MS SQL database. Is there any other non-dns way to connect? If I want to run my script from different server I first have to create the DNS in win2k3. Thank you, hj -- http://mail.python.org/mailman/listinfo/python-list
Re: Sharing database connection from C to Python
[EMAIL PROTECTED] wrote: I'm developing an application using the C language and Python for it's plugins. The C program connects to a MySQL database and keeps that connection active. Is it possible to 'share' this connection with the Python plugins? If so, is there a standard way to do that? There is no simple way as MySQL connections in Python are Python extension objects. You'll probably have to use a custom MySQL driver. Either create the session in C and add a constructor to the python to create a MySQL connection object from that handle or pointer Or add a method to the Python MySQL object to get the handle and pointer. Create all database connections in Python (precise: using the Python API from C), get the handle and pointer to use it from C, pass the original session to plugins. Daniel -- http://mail.python.org/mailman/listinfo/python-list
Sharing database connection from C to Python
Hi, I'm developing an application using the C language and Python for it's plugins. The C program connects to a MySQL database and keeps that connection active. Is it possible to 'share' this connection with the Python plugins? If so, is there a standard way to do that? Thank you. DG -- http://mail.python.org/mailman/listinfo/python-list
Pervasive Database Connection
What is the best way to access a Pervasive database on another machine? cheers, Alex. -- http://mail.python.org/mailman/listinfo/python-list
Re: Pervasive Database Connection
Alex Le Dain wrote, in entirety: What is the best way to access a Pervasive database on another machine? The best way, is, well ... Pervasively! Sorry. I can be kida a jerk like that. People often do get get hundreds, sometimes thousands of dollars worth of consultation from these groups, for free, by asking the right questions. Or by asking the questions right, which is the same thing but different. Focus. Be absolutely clear. What is it you need to know? -- --Bryan -- http://mail.python.org/mailman/listinfo/python-list
Re: Pervasive Database Connection
Alex Le Dain wrote: What is the best way to access a Pervasive database on another machine? Hi Alex, You can use ODBC / ADO to access Pervasive DBs. I found this page helpful: http://www.mayukhbose.com/python/ado/index.php -Dave -- http://mail.python.org/mailman/listinfo/python-list
Re: Pervasive Database Connection
http://www.pervasive.com/developerzone/access_methods/oledbado.asp -- http://mail.python.org/mailman/listinfo/python-list
Re: Pervasive Database Connection
On 6/9/05 08:52, Alex Le Dain wrote: What is the best way to access a Pervasive database on another machine? Assuming you mean the Pervasive.SQL DBMS... ...depends what you're trying to do and what you're comfortable with. Pervasive can be accessed through various access methods, e.g. low-level C (Btrieve) and Java APIs, ODBC/JDBC, .Net, etc. For quick-and-dirty utilities and queries I tend to use the old Python ODBC driver and I've dabbled with Jython+zxJDBC. For production code I have the luxury of a tried-and-tested C wrapper developed in-house as a DLL, so I can use that through c-types. -- Neil -- http://mail.python.org/mailman/listinfo/python-list
Re: Database connection caching
Thanks everybody for their replies. Lorenzo -- http://mail.python.org/mailman/listinfo/python-list
Re: Database connection caching
On 18 Mar 2005 04:52:03 -0800, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi all, is there an alternative way of: - create a connection object - open the connection - close the connection every time one has to run a query. Why not use cursor objects with a single connection object? You can have any number of cursor objects and can run with a single connection object A good introduction is at http://www.amk.ca/python/writing/DB-API.html Regards, -- Swaroop C H Blog: http://www.swaroopch.info Book: http://www.byteofpython.info -- http://mail.python.org/mailman/listinfo/python-list
Re: Database connection caching
Lorenzo is there an alternative way of: Lorenzo - create a connection object Lorenzo - open the connection Lorenzo - close the connection Lorenzo every time one has to run a query. Sure, create a Queue.Queue object and stuff a number of connections into it. When you want a connection call the queue's .get() method. When you're done with it .put() it back. This has the other nice feature that a program with a large number of threads can't overwhelm your database. Skip -- http://mail.python.org/mailman/listinfo/python-list
Re: Database connection caching
On 18 Mar 2005 04:52:03 -0800, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: is there an alternative way of: - create a connection object - open the connection - close the connection every time one has to run a query. It's actually morte like: create connection create cursor execute SQL process cursor close cursor close connection The bit that you can avoid it the creation of the connection - that's an intensive process. If your system is single threaded, or if it's a very small app, you can just create a single open connection and keep using that. I usually find that I need a pool of open connections, though. -- Cheers, Simon B, [EMAIL PROTECTED], http://www.brunningonline.net/simon/blog/ -- http://mail.python.org/mailman/listinfo/python-list
Re: Database connection caching
[EMAIL PROTECTED] wrote: Hi all, is there an alternative way of: - create a connection object - open the connection - close the connection psycopg, a Postgresql database adapter does connection pooling automatically http://initd.org/projects/psycopg1 Most Zope database adapters also have implicit connection pooling. Istvan. -- http://mail.python.org/mailman/listinfo/python-list
