Python - MS ADO - Revision history

PeterHarding at 04:47, 9 November 2007

2007-11-09T04:47:33Z

← Older revision		Revision as of 14:47, 9 November 2007
Line 1:		Line 1:
	= Python and ActiveX Data Objects (ADO) =		= Python and ActiveX Data Objects (ADO) =

			Article extracted from http://www.ecp.cc/pyado.html

	This is the 'sister' page to Bill Wilkinson's page on using Python with Microsoft's Data Access Objects (DAO). It is intended to give the basics on using Python to connect to an MS Access 2000 database via Microsoft's ActiveX Data Objects (ADO). There are several reasons you might want to do this. ADO is lighter and it tends to be a bit and faster than DAO, unlike DAO it can easily be used with other databases (SQL Server, Oracle, MySQL, etc.), it can be used with XML and text files and pretty much any data at all, and Microsoft will be supporting it for a longer time than DAO.		This is the 'sister' page to Bill Wilkinson's page on using Python with Microsoft's Data Access Objects (DAO). It is intended to give the basics on using Python to connect to an MS Access 2000 database via Microsoft's ActiveX Data Objects (ADO). There are several reasons you might want to do this. ADO is lighter and it tends to be a bit and faster than DAO, unlike DAO it can easily be used with other databases (SQL Server, Oracle, MySQL, etc.), it can be used with XML and text files and pretty much any data at all, and Microsoft will be supporting it for a longer time than DAO.

PeterHarding: /* 9. Getting the RecordCount */

2007-11-09T04:46:32Z

9. Getting the RecordCount

← Older revision		Revision as of 14:46, 9 November 2007
Line 169:		Line 169:

	This really just scratches the surface of ADO, but it should help getting connected from Python. For anything more than just simple database scripting it is worth looking into the object model. Here are some links that might be helpful.		This really just scratches the surface of ADO, but it should help getting connected from Python. For anything more than just simple database scripting it is worth looking into the object model. Here are some links that might be helpful.
	~~http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadoobjmod.asp~~
	~~http://www.activeserverpages.ru/ADO/dadidx01_1.htm~~

	Please contact me with questions or comments. I have placed a number of old questions and replies online. Feel free to contact me with questions, but I may use your question publicly. I will, of course, remove any identifying information.		* http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadoobjmod.asp
			* http://www.activeserverpages.ru/ADO/dadidx01_1.htm

			Please contact me with questions or comments. I have placed a number of old questions and replies online. Feel free to contact me with questions, but I may use your question publicly. I will, of course, remove any identifying information.

	= References =		= References =

PeterHarding: /* References */

2007-11-09T04:45:55Z

References

← Older revision		Revision as of 14:45, 9 November 2007
Line 176:		Line 176:
	= References =		= References =

	* [http://www.ecp.cc/pyado.html]		* [http://www.ecp.cc/pyado.html Python and ADO]

	= Examples =		= Examples =

PeterHarding at 04:45, 9 November 2007

2007-11-09T04:45:21Z

← Older revision		Revision as of 14:45, 9 November 2007
Line 17:		Line 17:
	10. Common Questions and Answers about Python and ADO.		10. Common Questions and Answers about Python and ADO.

	~~# The first thing to do is run the makepy utility~~. ~~It isn't necessary, but it improves speed and makes life in the PythonWin IDE that much easier. To do this select COM~~ Makepy ~~Utility from the PythonWin tools menu, and then select Microsoft ActiveX Data Objects 2.5 Library.~~		== 1. Running Makepy ==

	# Next you will need a Data Source Name [DSN] and a connection object. [I tend to use DSN-Less connection strings (as opposed to system DSNs as it improves performance and makes code portable)]		The first thing to do is run the makepy utility. It isn't necessary, but it improves speed and makes life in the PythonWin IDE that much easier. To do this select COM Makepy Utility from the PythonWin tools menu, and then select Microsoft ActiveX Data Objects 2.5 Library.

			== 2. DSN ==

			Next you will need a Data Source Name [DSN] and a connection object. [I tend to use DSN-Less connection strings (as opposed to system DSNs as it improves performance and makes code portable)]

	For MS Access you can just copy the DSN below. For other databases, or for more advanced options like passwords and the like go to [Control Panel \| Administrative Tools \| Data Sources (ODBC)]. From there you can set up a system DSN. You can then either use it as a system DSN, or copy it (it is just a text file) into a string and make a DSN-Less connection string. You might also do a search for DSN-Less connection strings on the web. Better yet, here are some samples for SQL Server, Access, FoxPro, Oracle , Oracle, Access, SQL Server, and finally MySQL.		For MS Access you can just copy the DSN below. For other databases, or for more advanced options like passwords and the like go to [Control Panel \| Administrative Tools \| Data Sources (ODBC)]. From there you can set up a system DSN. You can then either use it as a system DSN, or copy it (it is just a text file) into a string and make a DSN-Less connection string. You might also do a search for DSN-Less connection strings on the web. Better yet, here are some samples for SQL Server, Access, FoxPro, Oracle , Oracle, Access, SQL Server, and finally MySQL.
Line 33:		Line 37:
	With these set up access to the database is fairly straight forward.		With these set up access to the database is fairly straight forward.

	# The next task is to open a Recordset. For fancy stuff like joins and whatnot, you can use SQL SELECT * FROM table_name type statements, or just use the table name surrounded by brackets []		== 3. Opening a Recordset ==

			The next task is to open a Recordset. For fancy stuff like joins and whatnot, you can use SQL SELECT * FROM table_name type statements, or just use the table name surrounded by brackets []

	<pre>		<pre>
Line 44:		Line 50:
	[The 1 and the 3 are constants for adOpenKeyset and adLockOptimistic and are well beyond the scope of this tutorial. I typically use these settings as defaults, but your mileage may vary. Pick up a book on ADO for details.]		[The 1 and the 3 are constants for adOpenKeyset and adLockOptimistic and are well beyond the scope of this tutorial. I typically use these settings as defaults, but your mileage may vary. Pick up a book on ADO for details.]

	# With the recordset open you can examine the field names/indices like ...		== 4. Looking at the Recordset ==

			With the recordset open you can examine the field names/indices like ...

	<pre>		<pre>
Line 63:		Line 71:


	# Actual work can be done as well. Adding new records to recordsets can be done both with INSERT INTO (see below) statements, or directly using the AddNew() and Update() methods.		== 5. Adding new Records ==

			Actual work can be done as well. Adding new records to recordsets can be done both with INSERT INTO (see below) statements, or directly using the AddNew() and Update() methods.

	<pre>		<pre>
Line 92:		Line 102:


	# You can get a list of the Tables in a Database using ADO.		== 6. Database Table Information ==

			You can get a list of the Tables in a Database using ADO.

	<pre>		<pre>
Line 104:		Line 116:


	# Close the connection. Notice that to close this connection the 'C' is upper case, whereas to close a file opened with python the 'c' is lower case.		== 7. Closing Connections ==

			Close the connection. Notice that to close this connection the 'C' is upper case, whereas to close a file opened with python the 'c' is lower case.

	<pre>		<pre>
Line 110:		Line 124:
	</pre>		</pre>

			== 8. Using SQL with ADO ==

	# To use SQL to INSERT or UPDATE data, use a Connection object directly.		To use SQL to INSERT or UPDATE data, use a Connection object directly.

	<pre>		<pre>
Line 124:		Line 139:


	# Here is a last example that often seems to be a sticking point with ADO. Generally, if one wants to get the RecordCount of a table, one must move through all of the records counting them along the way like ...		== 9. Getting the RecordCount ==

			Here is a last example that often seems to be a sticking point with ADO. Generally, if one wants to get the RecordCount of a table, one must move through all of the records counting them along the way like ...

	<pre>		<pre>

PeterHarding at 04:40, 9 November 2007

2007-11-09T04:40:38Z

← Older revision		Revision as of 14:40, 9 November 2007
Line 17:		Line 17:
	10. Common Questions and Answers about Python and ADO.		10. Common Questions and Answers about Python and ADO.

	1. The first thing to do is run the makepy utility. It isn't necessary, but it improves speed and makes life in the PythonWin IDE that much easier. To do this select COM Makepy Utility from the PythonWin tools menu, and then select Microsoft ActiveX Data Objects 2.5 Library.		# The first thing to do is run the makepy utility. It isn't necessary, but it improves speed and makes life in the PythonWin IDE that much easier. To do this select COM Makepy Utility from the PythonWin tools menu, and then select Microsoft ActiveX Data Objects 2.5 Library.

	2. Next you will need a Data Source Name [DSN] and a connection object. [I tend to use DSN-Less connection strings (as opposed to system DSNs as it improves performance and makes code portable)]		# Next you will need a Data Source Name [DSN] and a connection object. [I tend to use DSN-Less connection strings (as opposed to system DSNs as it improves performance and makes code portable)]
	For MS Access you can just copy the DSN below. For other databases, or for more advanced options like passwords and the like go to [Control Panel \| Administrative Tools \| Data Sources (ODBC)]. From there you can set up a system DSN. You can then either use it as a system DSN, or copy it (it is just a text file) into a string and make a DSN-Less connection string. You might also do a search for DSN-Less connection strings on the web. Better yet, here are some samples for SQL Server, Access, FoxPro, Oracle , Oracle, Access, SQL Server, and finally MySQL.
			For MS Access you can just copy the DSN below. For other databases, or for more advanced options like passwords and the like go to [Control Panel \| Administrative Tools \| Data Sources (ODBC)]. From there you can set up a system DSN. You can then either use it as a system DSN, or copy it (it is just a text file) into a string and make a DSN-Less connection string. You might also do a search for DSN-Less connection strings on the web. Better yet, here are some samples for SQL Server, Access, FoxPro, Oracle , Oracle, Access, SQL Server, and finally MySQL.

	<pre>		<pre>
Line 30:		Line 31:


	With these set up access to the database is fairly straight forward.		With these set up access to the database is fairly straight forward.

	3. The next task is to open a Recordset. For fancy stuff like joins and whatnot, you can use SQL SELECT * FROM table_name type statements, or just use the table name surrounded by brackets []		# The next task is to open a Recordset. For fancy stuff like joins and whatnot, you can use SQL SELECT * FROM table_name type statements, or just use the table name surrounded by brackets []

	<pre>		<pre>
Line 41:		Line 42:


	[The 1 and the 3 are constants for adOpenKeyset and adLockOptimistic and are well beyond the scope of this tutorial. I typically use these settings as defaults, but your mileage may vary. Pick up a book on ADO for details.]		[The 1 and the 3 are constants for adOpenKeyset and adLockOptimistic and are well beyond the scope of this tutorial. I typically use these settings as defaults, but your mileage may vary. Pick up a book on ADO for details.]

	4. With the recordset open you can examine the field names/indices like ...		# With the recordset open you can examine the field names/indices like ...

	<pre>		<pre>
Line 52:		Line 53:


	A field's type and size are returned by ...		A field's type and size are returned by ...

	<pre>		<pre>
Line 62:		Line 63:


	5. Actual work can be done as well. Adding new records to recordsets can be done both with INSERT INTO (see below) statements, or directly using the AddNew() and Update() methods.		# Actual work can be done as well. Adding new records to recordsets can be done both with INSERT INTO (see below) statements, or directly using the AddNew() and Update() methods.

	<pre>		<pre>
Line 71:		Line 72:


	These values can be also be returned.		These values can be also be returned.

	<pre>		<pre>
Line 80:		Line 81:


	So, if one wants to create a new Record, and know what number an AutoNumber field has generated for it without having to query the database ...		So, if one wants to create a new Record, and know what number an AutoNumber field has generated for it without having to query the database ...

	<pre>		<pre>
Line 91:		Line 92:


	6. You can get a list of the Tables in a Database using ADO.		# You can get a list of the Tables in a Database using ADO.

	<pre>		<pre>
Line 103:		Line 104:


	7. Close the connection. Notice that to close this connection the 'C' is upper case, whereas to close a file opened with python the 'c' is lower case.		# Close the connection. Notice that to close this connection the 'C' is upper case, whereas to close a file opened with python the 'c' is lower case.

	<pre>		<pre>
Line 110:		Line 111:


	8. To use SQL to INSERT or UPDATE data, use a Connection object directly.		# To use SQL to INSERT or UPDATE data, use a Connection object directly.

	<pre>		<pre>
Line 123:		Line 124:


	9. Here is a last example that often seems to be a sticking point with ADO. Generally, if one wants to get the RecordCount of a table, one must move through all of the records counting them along the way like ...		# Here is a last example that often seems to be a sticking point with ADO. Generally, if one wants to get the RecordCount of a table, one must move through all of the records counting them along the way like ...

	<pre>		<pre>
Line 138:		Line 139:


	Aside from being horribly inefficient, if the recordset is empty, moving to the first record will generate an error. ADO provides a way to correct this. Before opening the recordset, set the CursorLocation to 3. After opening the recordset, the recordcount will be available.		Aside from being horribly inefficient, if the recordset is empty, moving to the first record will generate an error. ADO provides a way to correct this. Before opening the recordset, set the CursorLocation to 3. After opening the recordset, the recordcount will be available.

	<pre>		<pre>
Line 148:		Line 149:


	[Again, the 3 is a constant.]		Again, the 3 is a constant.

	This really just scratches the surface of ADO, but it should help getting connected from Python. For anything more than just simple database scripting it is worth looking into the object model. Here are some links that might be helpful.		This really just scratches the surface of ADO, but it should help getting connected from Python. For anything more than just simple database scripting it is worth looking into the object model. Here are some links that might be helpful.

PeterHarding at 04:38, 9 November 2007

2007-11-09T04:38:19Z

Show changes

PeterHarding at 04:35, 9 November 2007

2007-11-09T04:35:22Z

← Older revision		Revision as of 14:35, 9 November 2007
Line 1:		Line 1:
	= ado.py =		= Python and ActiveX Data Objects (ADO) =

			= References =

			* [http://www.ecp.cc/pyado.html]

			= Examples =

			== ado.py ==

	This script was used to access a LoadRunner Access database repository.		This script was used to access a LoadRunner Access database repository.

PeterHarding: New page: = ado.py = This script was used to access a LoadRunner Access database repository.

 import time import win32com.client  engine = engine = win32com.client.Dispatch("DAO.DBEngine.36")...

2007-11-09T04:07:30Z

New page: = ado.py = This script was used to access a LoadRunner Access database repository. <pre> import time import win32com.client engine = engine = win32com.client.Dispatch("DAO.DBEngine.36")...

New page

= ado.py =

This script was used to access a LoadRunner Access database repository.

<pre>
import time
import win32com.client

engine = engine = win32com.client.Dispatch("DAO.DBEngine.36")
db = engine.OpenDatabase(r"c:\lr\py\analysis.mdb")
rs = db.OpenRecordset("Result")

while not rs.EOF:
scenario = rs.Fields("Scenario Name").Value
name = rs.Fields("Result Name").Value
tz = rs.Fields("Time Zone").Value / 3600
startTime = rs.Fields("Start Time").Value
endTime = rs.Fields("Result End Time").Value
rs.MoveNext()

print
print " Scenario: %s " % scenario
print " Run ID: %s " % name
print " TZ: %s " % tz
print "Start Time: %s " % time.asctime(time.localtime(startTime))
print " End Time: %s " % time.asctime(time.localtime(endTime))
</pre>