19 September, 2003
Database programming with Python language, at least six methods are available. I use it in the actual project, not only powerful, but it is convenient and fast. The following is my experience in work and study.
Method 1: Use DAO (Data Access Objects)
This first method may be more time. But it is still very useful. Suppose you have installed Pythonwin, now start with me ...
Find the toolbar Toolsàcom makepy utilities, you will see a SELECT LIBRARY dialog box, select 'Microsoft Dao 3.6 Object Library' (or all your version) in the list.
Realize access to data now:
# Instantiate Database Engine
Import win32com.client
Engine = Win32com.client.dispatch ("DAO.DBENGINE.35")
# Instantiate the database object to establish a connection to the database
DB = Engine.Opendatabase (r "c: /temp/mydb.mdb")
Now you have a connection to the database engine, and there is an instance of the database object. You can open a recordset now. Assume that there is already a table called 'Customers' in the database. To open this table, the data is processed, we use The following syntax:
RS = DB.OpenRecordset ("Customers")
# You can use SQL language to manipulate data sets
RS = db.openrecordset ("Select * from customers where state = 'oh'")
You can also use DAO's Execute method. For example:
Db.execute ("delete * from customers where balancetype = 'overdue' and name = 'bill'")
# Note that deleted data cannot recover J
EOF and other properties are also accessible, so you can write such a statement:
While Not Rs.eof:
Print Rs.fields ("State"). Value
rs.movenext ()
I started using this method, I feel good.
Method 2: Using Python DB API, Python ODBC Modules (You CAN Use ODBC API Directly, But Maybe It Is Difficult for Most Beginner.)
In order to have a general database interface in Python, DB-SIG provides us with a Python database. (For more information, access DB-SIG website, 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 has a limited function of limited ODBC functions (that is not its purpose), but it is very simple, and in Win32 It is free.
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 Python Win32 extension in Mark Hammond - Pythonwin:
http://starship.python.net/crew/mhammond/
3. Install the necessary ODBC drivers, use the ODBC Manager to configure data sources for your database.
Your application will need to import two modules in advance:
DBI.dll - Supports a wide variety of SQL data types, for example: Date-Dates
ODBC.PYD - Compile the ODBC interface
Here's an example:
Import dbi, ODBC # Import ODBC Module Import Time # Standard Time Module
DBC = ODBC.ODBC (# Open a database connection
'Sample / Monty / Spam' # 'Data Source / User Name / Password'
)
CRSR = dbc.cursor () # Generate a CURSOR
Crsr.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 '', col
Result = Crsr.FetChall () # Take out all results at a time
Print '/ Nfirst Result Row: / n', Result [0] # Show the first line of the result
Print '/ NDATE CONVERSIONS:' # See how DBIDATE object?
Date = Result [0] [- 1]
FMT = '% -25s% -20s'
PRINT FMT% ('Standard String:', Str (Date)
Print FMT% ('Seconds Since:', Float (Date))
TimeEtuple = Time.localTime (Date)
Print FMT% ('Time Tuple:', TimeTuple)
Print FMT% ('User Defined:', Time.StrFTIME ('% D% B% Y', TIMETUETUE))
The following is the result:
------------------------------- 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
',
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
Everyone can go to http://www.python.org/windows/win32/odbc.html, there are two examples written by Hirendra Hindocha, not bad.
Note that in this example, the result value is converted to a Python object. Time is converted to a DBIDATE object. Here there is a limit, because DBIDate can only represent UNIX time (
1 Jan 1970
00:00:00
The time after GMT. If you want to get an earlier time, there may be garbled or even cause the system to crash. * _ *
Method 3: Using the CallDLL module (Using this module, you can use odbc api Directly. But now the python version is 2.1, and i do't know if other version is compatible with it. 老巫 :-)
SAM Rushing's CallDLL module allows Python to call any function in any dynamic connection library, is amazing? Ha. In fact, you can operate ODBC.SAM by calling the function in odbc32.dll to operate ODBC.SAM ODBC.PY, To do this. There is also code to manage data sources, install ODBC, implement and maintain database engines (Microsoft Access). In those demonstrations and examples, there are some good stuff, such as CBDemo.py, There is a python function for a message loop and window process!
You can find the relevant connection between Sam's Python Software to find the CallDll, there are still many interesting things]
Below is the step of installing a CallDLL package:
1. Install the Python package (to support up to version 2.1) 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, such as:
C: / program files / python / lib / caldll /
4. Unzip CallDll.zip in the original directory
5. Move all the files in CallDLL / LIB / in one parent directory (CallDLL), delete subdirectory (LIB)
6. Generate a file __init__.py file 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" as "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
# AlternativeLely, 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:' # s olumn descriptions
For col in result [0]:
Print '', col
Print '/ Nfirst Result Row: / N', Result [1] # SHOW First Result ROW -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -Output (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:
['twenty four', '
Argentina
',' 1997-12-19
01:51:53
']
Method 4: Using ActiveX Data Object (ADO)
Now give an instance of the MS Access 2000 database through Microsoft's ActiveX Data Objects (ADO). Use ADO has the following benefits: First, it can connect to the database faster than DAO; secondly, for other various Database (SQL Server, Oracle, Mysql, ETC.), ADO is very effective; there is another, it can be used for XML and text files and almost all other data, so Microsoft will also support it than DAO some.
The first thing is to run makepy. Although this is not necessary, it is helpful to improve speed. And run it in Pythonwin: Find the toolbar to Toolsàcom makepy Utilities, you will see a SELECT LIBRARY In the box, select 'Microsoft ActiveX Data Objects 2.5 Library' (or all your version).
Then you need a data source Data Source Name [DSN] and a connection object. [I prefer to use the DSN-LESS connection string (it more improves performance and optimized code)] For acceptings, you only need to copy the following DSN. For other databases, or like password settings these advanced features, you need to go [Control Panel Control Panel | Administrative Tools | Data Source Data Sources (ODBC) Where you can set up a system data source DSN. You can use it as a system data source, or copy it into a string to generate a DSN-LESS connection string. You can search online DSN-LESS connection string related information. Ok, there are examples of DSN-Less connection strings for different databases here: SQL Server, Access, FoxPro, Oracle, Oracle, Access, SQL Server, 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 the database:
The primary task is to open a data set / data sheet.
>>> RS = Win32com.client.dispatch (R'adodb.Recordset ')
>>> rs_name = 'myRecordset'
>>> r. o ('[' RS_NAME '], CONN, 1, 3) [1 and 3 are constants. Represents AdopenKeyset and AdlockOptimistic. I use it as the default, if your situation is different, maybe You should change. For further topics, please refer to ADO-related materials.
After opening the data sheet, you can check the domain name and field name, etc.
>>> FLDS_DICT = {}
>>> for x in → rs.fields.count:
... flds_dict [x] = rs.fields.Item (x) .name
Field type and length are returned to A:
>>> Print Rs.fields.Item (1) .Type
202 # 202 is A Text Field
>>> print .fields.Item (1) .definedsize
50 # 50 characters
Start with the data set now. You can use SQL statement INSERT INTO or AddNew () and Update ()
>>> >.addnew ()
>>> rs.fields.Item (1) .value = 'data'
>>> r.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, you know what Number and Autonumber fields have been generated.
>>> >.addnew ()
>>> x = rs.fields.item ('auto_number_field_name "). Value
# x Contains the autonumber
>>> rs.fields.Item ('Field_name'). Value = 'Data'
>>> r.update ()
Using 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
Turn off the connection. Note that C is uppercase, however off the file connection is lowercase C.
>>> CONN.CLOSE ()
As mentioned earlier, you can use the SQL statement to insert or update the data, then 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 of ADO. Generally speaking, if you want to know a table's RecordCount, you must calculate them like this:
>>> # See Example 3 Above for the set-up to THIS
>>> r.movefirst ()
>>> count = 0
>>> While 1:
... if xi: e:
... BREAK
... Else:
... count = count 1
... rs.movenext ()
If you are also like the above programs, very basic effect, if the data set is empty, the operation of moving the first record will generate an error. ADO provides a method to correct it. Before opening the data set, Set the CURSORLOCATION to 3. After opening the dataset, you can know that RecordCount is.
>>> r.cursorlocation = 3 # Don't use parenthesis here
>>> rs.open ('Select * from [Table_name]', conn) # Be Sure Conn IS Open
>>> rs.recordcount # no pieenthesis here either
186
[Again: 3 is constant]
These only the fur fur of ADO, but for connecting the database from Python, it should still be helpful.
If you want to learn more, it is recommended to in-depth object model. Below is some connections: http://msdn.microsoft.com/library/default.asp? Url = / library / en-us / ado270 / htm / mdmscadoobjMod.asphttp: / / www.activeserverpages.ru/ado/dadidx01_1.htm
(You can also write a single step, why don't you write to script? Old Witch is confused)
Method 5: Use the MXODBC module (you can use below Windows and UNIX, but is commercial software, you have to pay.) The following is the associated connection:
Http://thor.prohosting.com/~pBoddie/python/mxodbc.html
http://www.egenix.com/files/python/mxodbc.html
Method 6: Use specific Python modules for specific databases
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,66
