[DAO Database in win32] What is more efficient

[Greg Jorgensen]

"Arturo Pérez Mulas" <arturo_perez at wanadoo.es> wrote:

> I am accessing an Access database under Windows NT from Python. I am
> using Mark's python windows extensions to access through the DAO
> objects, and I have a question and a performance comment:

> a) When I open a connection and create a recordset executing an arbitrary
> SQL instruction, as follows:

> db = dbengine.OpenDatabase("filename")
> SQL_query = 'SELECT Table1.Column1, Table2.Colum2  FROM Table1 INNER JOIN
Table2 ON (Table1.Num =
> Table2.Num)  WHERE (((Table1.ID)="name"));'
> myrecordset = self.db.OpenRecordset(SQL_BCSet_query)
>
> Is there any way to check if the resulting recordset is empty?

If you aren't using ADO you should be. DAO was replaced by ADO some time
ago.

The recordset's EOF property will be true right after the OpenRecordset() if
the recordset is empty.

> b) I need to add a register to a database but being sure that one
particular field is not repeated;
> I have no access to the predefined structure of the database, so I need to
check manually if
> such a register exists before adding it; which of the following is more
efficient?:

I don't know what you mean by "register" in this context. Your examples are
simply checking a table to see if any row has the desired value in a column.
If you don't know the database structure you will have trouble writing
queries; SQL statements can't refer to columns that aren't defined. You'll
generate an error, not an empty recordset.

The simplest and fastest way to see if any row has a desired value in a
column is:

  rs = db.OpenRecordset("select count(*) as 'nrows' from table where field =
value")

The resulting recordset will have exactly one row, and one column named
'nrows.' The value of that column is how many rows were found where field =
value. If the number is 0 there were no matches.

Using the database engine to scan the table will be a lot faster than
looping over a cursor as your second example does.

--
Greg Jorgensen
Deschooling Society
Portland, Oregon, USA
gregj at pobox.com