A detailed description of how Python implements database programming

In this paper, we explain how to implement database programming in Python. Share to everyone for your reference. The specific analysis is as follows:

There are at least six ways to use the Python language for database programming. I use in practical projects, not only powerful, but also convenient and quick. Here are my experiences in working and learning.

Method one: Use DAO (Data Access Objects)

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

Find the toolsàcom makepy utilities on the toolbar and you will 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.

Now implement access to the data:

#实例化数据库引擎import win32com.clientengine = 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 also have an instance of the database object. You can open a recordset now. Suppose that there is already a table in the database called ' customers '. To open this table and manipulate 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 Execute method. such as this:

Db. Execute ("Delete * from customers where Balancetype = ' overdue ' and name = ' Bill ') #注意, deleted data cannot be restored J

Properties 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 method and felt good.

Method Two: Using the Python DB Api,python ODBC modules (you can use the ODBC API directly, but maybe it's difficult for most beginner.)

In order to have a common database interface in Python, Db-sig provided 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 simple to use, And it's free in the Win32.

The steps to install Odbc.pyd are as follows:

1. Install the Python package:

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

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

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

3. Install the necessary ODBC drivers and configure parameters such as data sources for your database with the ODBC Administrator

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 interface generated by odbc.pyd– compilation

Here's an example:

Import DBI, ODBC   # Importing ODBC module import      time # standard timing Module DBC = ODBC.ODBC (   # Open a database connection    ' Sample/monty/spam '  # ' Data source/username/password '    ) CRSR = Dbc.cursor ()  # generates a Cursorcrsr.execute (     # Execute SQL Language "" "Select    country_id, name Insert_change_date from    country    ORDER by name    "") print ' Column descriptions: '  # Display line description for Col in Crsr.description:print ', Colresult = Crsr.fetchall ()    # Remove all results at once print '/nfirst result row:/n ', result[0]  # Display the first line of the result print '/ndate conversions: '  # Look at the Dbidate object? 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's the result:

Outputs (output)

Column descriptions:  (' country_id ', ' number ', 0, 0, 0)  (' name ', ' STRING ', ' GB, ') ', '  insert_c ', ' 0, ' 0 ' Hange_date ', ' Date ', 0, 0, 1) First result row:  (24L, ' ARGENTINA ', 
 
  
   
  ) Date conversions:  Standard string:   Fri Dec 01:51:53 1997  seconds since epoch:  882517913.0 time  tuple:    (1997, 12 , 1, Wuyi, 4, 353, 0)  User defined:    December 1997
 
  

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

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

Method Three: Using the CallDll module

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

Sam Rushing's CallDll module allows Python to invoke any function in any dynamically connected library, huh? Actually, you can make a wrapper module odbc.py by directly invoking the function operation inside Odbc32.dll Odbc.sam Just to do it. There is also code to manage the data source, install ODBC, implement and maintain the database engine (Microsoft Access). In those demos and example code, there are some good things, such as cbdemo.py, There is a Python function for information loops and window procedures!

[You can go to Sam's Python software to find links to CallDll, there are lots of other interesting things]

Here are the steps to install the CallDll package:

1. Install Python packages (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 below the Lib path for example, say:

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

4. Unzip the Calldll.zip in the original directory

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

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

# File to allow this directory to be treated as a Python 1.5
Package.

7. Edit 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 odbcdbc = Odbc.environment (). Connection () # Create Connectiondbc.connect (' Sample ', ' Monty ', ' Spam ') # Connect to db# alternatively with use 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_c Hange_date from  country  ORDER by name  ""  ) print ' column descriptions: ' # show Column descriptionsfor Col in result[0]:  print ', colprint '/nfirst result row:/n ', result[1] # show first result row

Output (outputs)

Dbms:oracle 07.30.0000Column Descriptions:  (' country_id ', 3, 0, 0) (' NAME ', ' A ', ' 0 ', '  0 ') (  ' Insert_chang E_date ', one, 0, 1) First result row:  [' ['] ', ' ARGENTINA ', ' 1997-12-19 01:51:53 ']

Method Four: Use ActiveX Data Object (ADO)

Now give an instance of connecting an MS Access 2000 database through Microsoft's ActiveX Data Objects (ado). There are several benefits of using ADO: First, it can connect to the database faster than DAO, and second, for various other databases (SQL Server, Oracle, MySQL, etc.) , ADO is very effective and convenient, and again, 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 to do is run makepy. Although this is not necessary, it is helpful for speed improvement. And running it in Pythonwin is very simple: find the toolsàcom makepy utilities on the toolbar and you'll see a Select Library dialog box, select ' Microsoft ActiveX Data Objects 2.5 Library ' (or all of your versions) in the list.

Then you'll need a data source name, a datasource name [DSN], and a connection object. [I prefer to use the Dsn-less connection string (it improves performance and optimizes code compared to the system data source name)]
In the case of MS Access, you only need to copy the DSN below. For other databases, or like password settings for these advanced features, you need to go to the Control Panel Dashboard | management tools Administrative Tools | 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 dsn-less connection string information. Well, 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 data set/data table

>>> rs = win32com.client.Dispatch (R ' ADODB. Recordset ') >>> rs_name = ' myrecordset ' >>> Rs. Open (' [' + Rs_name + '] ', conn, 1, 3)

[1 and 3 are constants. For 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 ADO related Materials.

After you open the datasheet, you can check the domain name and field name, etc.

>>> 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). TYPE202 # 202 is a text field>>> print Rs. Fields.item (1). DefinedSize50 # Characters

Now start working on the dataset. SQL statements can be used to insert into or AddNew () and update ()

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

Close the connection. Note Here c is uppercase, but the close 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 difficult point for ADO. In general, if you want to know the RecordCount of a table, you must calculate them as one by one:

>>> # See 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. If the dataset is empty, moving the first record will produce an error. ADO provides a way to correct it. Before opening the dataset, set CursorLocation to 3. 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 either186

[Again: 3 is constant]

This only uses ADO's fur, but it should be helpful for connecting to a database from Python.

If you want to learn more, it is recommended to delve 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 execution is OK, why not write as script?) The old Witch doubts)

Method Five: Use the MXODBC module (can be used under Windows and UNIX, but it is commercial software, to pay for.) Here are the related connections:

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

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

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

MySQL database àmysqldb module, download address:

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

Hopefully this article will help you with Python programming.

