How to Implement database programming in Python

How to Implement database programming in Python

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:

?

1 2 3 4 5

# Instantiate a Database Engine Import win32com. client Engine = win32com. client. Dispatch ("DAO. DBEngine.35 ") # Instantiate a database object and establish a connection to the database Db = engine. 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:

?

1 2 3

Rs = db. OpenRecordset ("customers ") # Dataset manipulation using SQL Rs = db. OpenRecordset ("select * from MERs where state = 'oh '")

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

?

1 2	Db. Execute ("delete * from MERs where balancetype = 'overdue' and name = 'bill '") # Note: The deleted data cannot be recovered.

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

?

1 2 3

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:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

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.exe cute (# execute the SQL language """ SELECT country_id, name, insert_change_date FROM country Order by name """ ) Print 'column descriptions: '# display the row description For col in crsr. description: Print '', col Result = crsr. fetchall () # retrieve all results at a time Print '/nFirst result row:/N', result [0] # display the first row of the result Print '/nDate conversions:' # How is the dbiDate object? Date = result [0] [-1] Fmt = '%-25 s %-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 ))

The result is as follows:

Output)

?

1 2 3 4 5 6 7 8 9 10 11

Column descriptions: ('Country _ id', 'number', 12, 10, 10, 0, 0) ('Name', 'string', 45, 45, 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:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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

?

1 2 3 4 5 6 7

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 ', '2017-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.

?

1 2 3 4

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

?

1 2 3

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

?

1 2 3

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

?

1 2 3 4

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

?

1 2 3

>>> Rs. AddNew () >>> Rs. Fields. Item (1). Value = 'data' >>> Rs. Update ()

These values can also be returned:

?

1 2 3

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

?

1 2 3 4 5

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

?

1 2 3 4 5 6

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

?

1 2 3 4 5 6 7

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

?

1 2 3 4 5 6 7 8 9

>>> # See example 3 abve 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.

?

1 2 3 4

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