Detailed description of how to implement database programming in Python, and programming in python Database

Detailed description of how to implement database programming in Python, and programming in python Database

This article describes how to implement database programming in Python. Share it with you for your reference. The specific analysis is as follows:

There are at least six methods available for database programming in PYTHON. I used it in actual projects. It is not only powerful, but also convenient and fast. the following is my experience at work and study.

Method 1: use DAO (Data Access Objects)

This first method may be outdated, but it is still very useful. If you have installed PYTHONWIN, start with me now ......

Find Tools à COM MakePy utilities on the toolbar. The Select Library dialog box is displayed. In the list, Select 'Microsoft DAO 3.6 Object Library '(or all your versions ).

Access to data is now implemented:

Instantiate the database engine
The import win32com. Client
Engine = win32com. Client. Dispatch (" DAO. DBEngine. 35 ")
Instantiate the database object to establish a connection to the database
Db = engine. The OpenDatabase (r "c: / temp/mydb. MDB)

Now you have a database engine connection and a database object instance. now you can open a recordset. suppose there is already a table named 'customer' in the database '. to open the table and process the data, we use the following syntax:

Rs = db. OpenRecordset (" customers ")
Data sets can be manipulated using the SQL language
Rs = db.openrecordset ("select * from customers where state = 'OH'")

You can also use the execute method of DAO. For example:

Execute("delete * from customers where balancetype = '] and name = 'bill'")
Note that the deleted data cannot be restored J

EOF and other attributes are accessible, so you can write the following statement:

while not rs.EOF:
 print rs.Fields("State").Value
 rs.MoveNext()

I started using this method, and it feels good.

Method 2: use the Python db api and Python ODBC modules (you can use odbc api directly, but maybe it is difficult for most beginner .)

In order to have a common database interface in Python, DB-SIG provides us with a Python database. (For details, visit the DB-SIG site, http://www.python.org/sigs/db-sig/). Mark

Hammond's win32 extension PythonWin contains an application of these Apis-odbc. pyd. this database API only provides some limited ODBC functions (that is not its purpose), but it is easy to use and is free of charge in win32.

Follow these steps to install odbc. pyd:

1. Install the python software package:

Http://www.python.org/download/

2. Install python win32 extension of the latest version of Mark Hammond-PythonWin:

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

3. Install the necessary ODBC driver and use the ODBC manager to configure data sources and other parameters for your database.

Your application will need to import two modules in advance:

Dbi. dll-supports a variety of SQL data types, such as: Date-dates
Odbc. pyd-ODBC interface generated by compilation

The following is an example:

Import dbi, odbc
Import time # standard time module
DBC = odc.odbc (# opens a database connection
'sample/monty/spam' # 'data source/username/password'
)
CRSR = DBC. Cursor () # produces a cursor
Crsr.execute (#) executes the SQL language
"" "
The SELECT country_id, name, insert_change_date
FROM the country
The ORDER BY name
"" "
)
Print 'Column Column :' # shows the row description
For col in CRSR. Description:
Print ', col
Result = crsr.fetchall() # fetchall results at once
Print '/nFirst result row:/n ', result[0] # shows the first row of the result
Print '/nDate conversions:' # how about dbiDate objects?
The date = result [0] [1]
FMT = '% 25 s % - 20 s'
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))

The result is as follows:

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)
First 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. there are two examples written by hirendra Hindocha.

Note: In this example, the result value is converted to a Python object. time is converted into a dbiDate object. there is a limit here, because dbiDate can only represent the time after the UNIX time (1 Jan 1970 00:00:00 GMT. if you want to get an earlier time, garbled code may even cause a system crash. *_*

Method 3: Use the calldll Module

(Using this module, you can use odbc api directly. But now the python version is 2.1, and I don't know if other version is compatible with it. Old Wu :-)

Sam Rushing's calldll module allows Python to call any function in any dynamic Connection Library, isn't it amazing? Ha. in fact, you can directly call the functions in odbc32.dll to operate ODBC. sam provides a packaging module odbc. py is used to do this. you can also use code to manage data sources, install ODBC, and implement and maintain the database engine (Microsoft Access ). in those demos and sample code, there are some good examples, such as cbdemo. py, there is a Python function for information loop and window process!

[You can go to Sam's Python Software to find connections related to calldll. There are many other interesting things there]

Follow these steps to install the CALLDLL package:

1. Install the PYTHON Software Package (up to 2.1 is supported so far)

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. Decompress calldll.zip in the original directory

5. Move all files in calldll/lib/to the preceding parent directory (calldll) and delete the subdirectory (lib)

6. Generate a file _ init _. py file in the CALL Directory, as shown in the following figure:

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

The following 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 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 first result row


 

	
  


 

Output)

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)
First result row:
  ['24', 'ARGENTINA', '1997-12-19 01:51:53']


 

	
  


 

Method 4: Use ActiveX Data Object (ADO)

An instance connecting to the MS Access 2000 database through Microsoft's ActiveX Data Objects (ADO) is provided. using ADO has the following advantages: first, compared with DAO, it can connect to the database faster; second, for other databases (SQL Server, Oracle, MySQL, etc .) ADO is very effective and convenient; moreover, it can be used for XML and text files and almost all other data, so Microsoft will also support it a little longer than DAO.

The first thing is to run makepy. although this is not necessary, it helps increase the speed. in addition, it is very easy to run in PYTHONWIN: find Tools à COM MakePy utilities on the toolbar, and you will see a Select Library dialog box, select 'Microsoft ActiveX Data Objects 2.5 Library '(or all your versions) in the list ).

Then you need a Data Source Name [DSN] and a connection object. [I prefer to use a DSN-Less connection string (which improves performance and optimizes code better than the system data source name)]
For MS Access, you only need to copy the following DSN. for other databases or advanced features such as password settings, you need to go to [Control Panel | Administrative Tools | Data source Data Sources (ODBC)]. you can set a system data source DSN. you can use it as a system data source name, or copy it to a string to generate a DSN-Less connection string. you can search for information about the DSN-Less connection string on the Internet. well, here are some examples of DSN-Less connection strings for different databases: SQL Server, Access, FoxPro, Oracle, Oracle, Access, SQL Server, and 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/data table.

>>> rs = win32com.client.Dispatch(r'ADODB.Recordset')
>>> rs_name = 'MyRecordset'
>>> rs.Open('[' + rs_name + ']', conn, 1, 3)


 

	
  


 

[1 and 3 are constants. represents adOpenKeyset and adLockOptimistic. I use it as the default value. If your situation is different, maybe you should change it. for more information, see ADO.]

After opening the data table, you can check the domain name, field name, and so on.

>>> flds_dict = {}
>>> for x in range(rs.Fields.Count):
...  flds_dict[x] = rs.Fields.Item(x).Name


 

	
  


 

Field type and length are returned as follows:

>>> print rs.Fields.Item(1).Type
202 # 202 is a text field
>>> print rs.Fields.Item(1).DefinedSize
50 # 50 Characters


 

	
  


 

You can use SQL statements to INSERT INTO, 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'


 

	
  


 

Therefore, if you want to add a new record, you do not have to check 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 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 that C is in upper case, but close the file connection is in lower case.

>>> Conn. Close ()

As mentioned above, you can use SQL statements to insert or update data. In this case, we directly use a connection object.

>>> 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 the difficulty of ADO. Generally, to know the RecordCount of a table, you must calculate them one by one as follows:

>>> # 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 are using a program like the above, it is very effective. 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. after opening the dataset, you can know the recordcount.

>>> rs.Cursorlocation = 3 # don't use parenthesis here
>>> rs.Open('SELECT * FROM [Table_Name]', conn) # be sure conn is open
>>> rs.RecordCount # no parenthesis here either
186

[Again: 3 is a constant]

This only applies to ADO, but it should be helpful for connecting to the database from PYTHON.

To learn more, we recommend that you go deep into the object model. The following are some links:
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 acceptable. Why can't I write it as a script? Old Wu doubts)

Method 5: Use the mxODBC module (which can be used in both Windows and Unix, but is a commercial software, which requires money). The related connections are as follows:

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

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

Method 6: Use a specific PYTHON module for a specific database

MySQL database module named "MySQLdb:

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

PostgresSQL database à psycopg Module

PostgresSQL home page: http://www.postgresql.org

Python/PostgresSQL module: http://initd.org/software/psycopg

Oracle Database à DCOracle module: http://www.zope.org/Products/DCOracle

? Cx _ oracle module: http://freshmeat.net/projects/cx_oracle? Topic_id = 809% 2C66

I hope this article will help you with Python programming.