Implementing database Programming with Python

There are at least six ways to use the Python language for database programming. I use in the actual project, not only powerful, but also convenient and quick. Here are my experiences in work and study.

Method one: Use DAO (Data Access Objects)

This first method may be more outdated. But it's still very useful. Suppose you have installed the Pythonwin, now start with me ...

Find the toolsàcom makepy utilities on the toolbar and you'll see a dialog box that pops up a select library and select ' Microsoft DAO 3.6 Object Library ' (or all of your versions) in the list.

Access to data is now implemented:

#实例化数据库引擎

Import Win32com.client

Engine = Win32com.client.Dispatch ("DAO". dbengine.35 ")

#实例化数据库对象, establish a connection to the database

db = engine. OpenDatabase (r "C:/temp/mydb.mdb")

Now that you have a connection to the database engine, you have an instance of the database object. You can now open a recordset. Suppose there is already a table in the database called ' customers '. To open the table and process the data, we use the following syntax:

rs = db. OpenRecordset ("Customers")

#可以采用SQL语言对数据集进行操纵

rs = db. OpenRecordset ("SELECT * FROM Customers where State = ' OH '")

You can also use the DAO's Execute method. Like this:

Db. Execute ("Delete * from customers where Balancetype = ' overdue ' and name = ' Bill '")

#注意, the deleted data cannot be recovered. J

Attributes such as EOF are also accessible, so you can write a statement like this:

While not Rs. Eof:

Print Rs. Fields ("state"). Value

Rs. MoveNext ()

I started with this approach and it felt good.

Method Two: Use the Python DB Api,python ODBC modules (you can using ODBC API directly, but maybe it is difficult for most.)

In order to have a common database interface in Python, Db-sig provides us with a Python database.   (For more information, visit Db-sig's website, http://www.python.org/sigs/db-sig/). Mark

Hammond's Win32 Extension Pythonwin contains an application-odbc.pyd for these APIs. This database API only opens up the functionality of some limited ODBC functions (that's not its purpose), but it's easy to use. And it's free inside the Win32.

The steps for installing Odbc.pyd are as follows:

1. Install the Python package:

http://www.python.org/download/

2. Install Mark Hammond's latest version of the Python Win32 extension-Pythonwin:

http://starship.python.net/crew/mhammond/

3. Install the necessary ODBC driver and use ODBC Manager to configure the data source parameters for your database

Your application will need to import two modules in advance:

Dbi.dll-supports a wide variety of SQL data types, such as: Date-dates

odbc.pyd– compiler-generated ODBC interface

Here's an example:

Import DBI, ODBC # importing ODBC module

Import times # Standard Time module

DBC = ODBC.ODBC (# Open a database connection

' Sample/monty/spam ' # ' data source/username/password '

)

CRSR = Dbc.cursor () # produces a cursor

Crsr.execute (# Execute SQL language

"""

SELECT country_id, name, insert_change_date

From country

Order BY name

"""

)

print ' Column descriptions: ' # Display row description

For Col in Crsr.description:

print ', col

result = Crsr.fetchall () # Take out all the results at once

print '/nfirst result row:/n ', result[0] # shows the first line of results

print '/ndate conversions: ' # What about Dbidate objects?

Date = Result[0][-1]

FMT = '%-25s%-20s '

Print FMT% (' Standard string: ', str (date))

Print Fmt% (' seconds since epoch: ', float (date))

Timetuple = time.localtime (date)

Print Fmt% (' time tuple: ', timetuple)

Print Fmt% (' User defined: ', time.strftime ('%d%B%Y ', timetuple))

Here is the result:

-------------------------------outputs (output)----------------------------

Column Descriptions:

(' country_id ', ' number ', 12, 10, 10, 0, 0)

(' name ', ' STRING ', 45, 45, 0, 0, 0)

(' Insert_change_date ', ' Date ', 19, 19, 0, 0, 1)

The A-result row:

(24L, ' Argentina ', <dbidate object at 7f1c80>)

Date Conversions:

Standard String:fri Dec 19 01:51:53 1997

seconds since epoch:882517913.0

Time Tuple: (1997, 12, 19, 1, 51, 53, 4, 353, 0)

User defined:19 December 1997

You can also go to http://www.python.org/windows/win32/odbc.html to see, there are two Hirendra Hindocha written examples, it is not bad.

Note that in this example, the resulting value is converted to a Python object. Time is transformed into a Dbidate object. There is a little bit of a limit, because dbidate can only represent Unix time (1 hours 1970 00:00:00 GMT) After the time. If you want to get an earlier time, there may be garbled or even cause system crashes. *_*

Method Three: Using the CallDll module

(Using This module, the can use ODBC API directly.) But now the Python version is 2.1, and I don ' t know if or other version are compatible with it. Old Witch:-)

Sam Rushing's CallDll module allows Python to invoke any function in any dynamic connection library, huh? In fact, you can provide a wrapper module odbc.py by directly calling the function operation inside the Odbc32.dll Odbc.sam. Just to do it. There are also code to manage the data source, install ODBC, and implement and maintain the database engine (Microsoft Access). In those demos and example code, there are some nice things to do, like cbdemo.py, There is a python function of the information loop and the window process!

[You can go to Sam's Python software to find the CallDll connection, there are a lot of other interesting things]

The following steps are to install the CallDll package:

1. Install the Python software package (up to 2.1 versions up to now)

2. Download Calldll-2001-05-20.zip:

Ftp://squirl.nightmare.com/pub/python/python-ext/calldll-2001-05-20.zip

3. Create a new path under the Lib path, for example:

C:/Program files/python/lib/caldll/

4. Extract Calldll.zip under the original directory

5. Move all files in calldll/lib/to a parent directory above (calldll), delete subdirectories (Lib)

6. Generate a file __init__.py in the call directory, like this:

# File to allow this directory-treated as a Python 1.5

Package.

7. Editor calldll/odbc.py:

In "Get_info_word" and "Get_info_long", change "calldll.membuf" to "Windll.membuf"

Here is an example of how to use CallDll:

From CallDll Import ODBC

DBC = Odbc.environment (). Connection () # Create connection

Dbc.connect (' Sample ', ' Monty ', ' spam ') # Connect to DB

# Alternatively, use the full connect string:

# dbc.driver_connect (' Dsn=sample; Uid=monty; Pwd=spam ')

print ' DBMS:%s%s/n '% (# show DB information

Dbc.get_info (ODBC. Sql_dbms_name),

Dbc.get_info (ODBC. Sql_dbms_ver)

)

result = Dbc.query (# Execute Query & return results

"""

SELECT country_id, name, insert_change_date

From country

Order BY name

"""

)

print ' Column descriptions: ' # show Column descriptions

For Col in Result[0]:

print ', col

print '/nfirst result row:/n ', result[1] # Show-A-result row

-------------------------------output (outputs)--------------------------------

Dbms:oracle 07.30.0000

Column Descriptions:

(' country_id ', 3, 10, 0, 0)

(' NAME ', 12, 45, 0, 0)

(' Insert_change_date ', 11, 19, 0, 1)

The A-result row:

[', ' Argentina ', ' 1997-12-19 01:51:53 ']

Method Four: Use ActiveX Data Object (ADO)

Now you have an instance of connecting to a MS Access 2000 database through Microsoft's ActiveX Data Objects (ADO). There are several benefits to using ADO: First, it connects to the database faster than DAO, and secondly, for various other databases (SQL Server, Oracle, MySQL, etc.) , ADO is very efficient and convenient, and it can be used for XML and text files and almost all other data, so Microsoft will also support it longer than DAO.

The first thing is to run the makepy. Although this is not necessary, it is helpful in improving speed. And running it in Pythonwin is very simple: find the toolsàcom makepy utilities on the toolbar and you'll see a pop-up select Library dialog box, select ' Microsoft ActiveX Data Objects 2.5 library ' (or all of your versions) in the list.

Then you will need a data source name of "DataSource" (DSN) and a Connection object. [I prefer to use a dsn-less connection string (which improves performance and optimizes code compared to the system data source name)]
In the case of MS Access, you just need to copy the following DSN. For other databases, or for these advanced features like password settings, you need to go to the Control Panel | management Tools Administrative tool | Data source Sources (ODBC)]. There, you can set up a system data source DSN. You can use it as a system data source name, or copy it into a string to produce a dsn-less connection string. You can search the Internet for information about dsn-less connection strings. OK, here are some examples of dsn-less connection strings for different databases: SQL Server, Access, FoxPro, Oracle, Oracle, Access, SQL Server, and finally MySQL.

>>> Import Win32com.client

>>> conn = Win32com.client.Dispatch (R ' ADODB. Connection ')

>>> DSN = ' Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/mydb.mdb; '

>>> Conn. Open (DSN)

After the above settings, you can directly connect to the database:

The first task is to open a dataset/datasheet

>>> rs = win32com.client.Dispatch (R ' ADODB. Recordset ')

>>> rs_name = ' myrecordset '

>>> Rs. Open (' [' + Rs_name + '] ', conn, 1, 3)

[1 and 3 are constants. On behalf of adOpenKeyset and adLockOptimistic. I use it as the default, and if your situation is different, maybe you should change it. For further topics, please refer to the ADO related Materials.

After opening the datasheet, you can check the domain name and field name and so on

>>> flds_dict = {}

>>> for x in range (Rs. Fields.Count):

... flds_dict[x] = Rs. Fields.item (x). Name

The field type and length are returned as a:

>>> Print Rs. Fields.item (1). Type

The # is a text field

>>> Print Rs. Fields.item (1). DefinedSize

# Characters

You are now starting to manipulate the dataset. You can insert into or AddNew () and update () with SQL statements

>>> Rs. AddNew ()

>>> Rs. Fields.item (1). Value = ' data '

>>> Rs. Update ()

These values can also be returned:

>>> x = Rs. Fields.item (1). Value

>>> Print X

' Data '

So if you want to add a new record, you don't have to look at the database to know what number and AutoNumber fields have been generated

>>> Rs. AddNew ()

>>> x = Rs. Fields.item (' Auto_number_field_name '). Value

# x contains the AutoNumber

>>> Rs. Fields.item (' Field_name '). Value = ' data '

>>> Rs. Update ()

With ADO, you can also get a list of all the table names in the database:

>>> OCat = Win32com.client.Dispatch (R ' ADOX. Catalog ')

>>> ocat.activeconnection = conn

>>> Otab = Ocat.tables

>>> for x in Otab:

... if x.type = = ' TABLE ':

... print x.name

Closes the connection. Note that C is uppercase here, but closing the file connection is lowercase c.

>>> Conn. Close ()

As mentioned earlier, you can use SQL statements to insert or update data, when we use a connection object directly.

>>> conn = Win32com.client.Dispatch (R ' ADODB. Connection ')

>>> DSN = ' Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/mydb.mdb; '

>>> sql_statement = "INSERT into [table_name]

([Field_1], [field_2]) VALUES (' data1 ', ' data2 ') "

>>> Conn. Open (DSN)

>>> Conn. Execute (sql_statement)

>>> Conn. Close ()

The last example is often seen as a difficulty in ADO. Generally speaking, to know the RecordCount of a table, one must compute them like this:

>>> # example 3 above for the set-up to this

>>> Rs. MoveFirst ()

>>> count = 0

>>> while 1:

.. if Rs. Eof:

.. break

. else:

... count = count + 1

... Rs. MoveNext ()

If you have a program like the one above, it's very inefficient to say that if the dataset is empty, moving the first record will produce an error. ADO provides a way to correct it. Set CursorLocation to 3 before opening the dataset. Once you've opened the dataset, you'll know RecordCount.

>>> Rs. CursorLocation = 3 # don ' t use parenthesis here

>>> Rs. Open (' SELECT * FROM [table_name] ', conn) # are sure Conn is open

>>> Rs. RecordCount # no parenthesis here either

186

[Again: 3 is constant]

These are only for ADO, but they should be helpful for connecting to a database from Python.

To learn more, it is recommended to drill down into the object model. Here are some connections:
Http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadoobjmod.asp
Http://www.activeserverpages.ru/ADO/dadidx01_1.htm

(Single step is also OK, why write as script does not work.) Old witch Doubts)

Method Five: Use the MXODBC module (available under Windows and UNIX, but it is commercial software that pays.) The following are related connections:

Http://thor.prohosting.com/~pboddie/Python/mxODBC.html

Http://www.egenix.com/files/python/mxODBC.html

Method VI: Use a specific Python module for a specific database

MySQL database àmysqldb module, download address is:

Http://sourceforge.net/projects/mysql-python

Postgressql Database ÀPSYCOPG Module

Postgressql's homepage is: http://www.postgresql.org

Python/postgressql module Download Address: HTTP://INITD.ORG/SOFTWARE/PSYCOPG

Oracle database àdcoracle module Download address: http://www.zope.org/Products/DCOracle

Àcx_oracle module Download Address: http://freshmeat.net/projects/cx_oracle/?topic_id=809%2C66