| Connecting Access to Python |
A quick guide to using the Access database with your python scraps. The python language allows far more sophisticated data manipulation then VBA, but does not offer a built in Database. By combing Accesses and python enables you to quickly produce powerful data manipulation tools
Access is not really a database, it is a file format. Thus, programs that access it, access the file directly. This has several benefits for small projects, being file size and speed. Most databases act as a gate keeper between your application and the data files. This also makes it unpractical for multi user environments.
The other nice thing about access is its user interface. OIt alyour a novice to easly mnapult and populate the data. The data entered can be used by python. An example project of mine was to make a chamber directory site. The chamber information is kept in an access database. A python program reads the database and constructs html files from it. Python can then act as an ftp client and download the files directly to the web server.
The first part is to install win32 on your computer. This will allow python to talk to macros COM interface, which talks to Access. You can find this at http://sourceforge.net/projects/pywin32
In this example we will use the DAO interface. In order to talk to access, we will be using the following objects
DBEngine, (connects to a database file)
DbeDatabase – provides database functionality
DbeRecordset, manipulates databases tables, this is what we will used.
To start we will create a DBEngind object, which will create a DBEDatabase object from which we create our recordset object.
engine = win32com.client.Dispatch("DAO.DBEngine.36")
Next we will construct the database object
db=engine.OpenDatabase(r"your databases file name")
Now we will construct an object for a table within the database
table = db.OpenRecordset("select * from nameofyourtable”)
The table, curtly points to the first record. We may display one of its text fields as follows, given the field name is first_name
Print table.Fields("first_name").Value.encode('utf-8')
We may display a number value by
Print table.Fields("street_number").Value
Now to get the values of the next record, we move the record pointer up by calling MoveNext
Db.MoveNext().
We may print out all the names in our table by
while not table.EOF
print table.Fields(“first_name”).encode(‘utf8’)
table.MoveNext()
To add a new record we use the Add and Update methods of the record set object
table.Add()
table.Firlds(‘firstname’).value=”Ted’
table.Update.
Now to change the current record we use the Edit
table.edit()
table.Fields(‘firstname’).value=’fred’
table.Update()
A useful property I find is Bookmark; it saves or sets a record position.
To boot mark the current record position
MyBookMark=table.Bookmark
Later on we may return to this record by
Table.Bookmark=MyBookMark.
Sometimes is may be useful to know how may records our object has. This is done by the RecordCount property. To display how many records our table has,
Print table.RecordCount
Now we may want to delete one of the records in the table, this is accomplish by
table.Delete()
Let’s say we want to delete the record we just crested with Add.
table.MovePrevious ()
table.Delete()
To see an example of what can be done go to my site  www.usedirectorysite. The html files where created by python from an access database that contain a listing of all the chamber web sites. You may also visit my company’s website at http://www.activetechconsulting.com/
Article Source: UnArchived Articles
|
|
|
|
