Connecting an Access Database to Python

Access is not really a database, it is more of 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 a little unpractical for multi user environments.

The other nice thing about Access is its user interface. A novice to easily manipulate 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.

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 databas.

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.Move Previous()

table.Delete()

To see an example of what can be done go to my site www.usedirectorysite.com. The html files where created by python from an access database that contain a listing of all the chamber web sites.

Ted Pottel is the owner of a internet marketing company called finemeontheweb.biz He attended U-Mass at Amherst and gradated with a degree for computer systems engineer. You may contact him at tedpottel#gmail.com

Article Source: http://EzineArticles.com/?expert=Teddy_Pottel