Keywords: Python Database ODBC ADO Python is indeed a good scripting language, simple and strong. Unfortunately, the document is lacking. Recently, I learned Python, the only "Python Technical Reference Daquan" in my hand has been out of time. I want to use Python-ASP to be a database-driven website, but the Chinese information used by Python Connect the database is difficult to find, there is no way, I have to explore myself. Below is some of my learning experience. Preparation Tool: <1> Download and install ActivePython (http://activestate.com/products/dow...id=activepython) There is a MSI installation package under Windows, install it directly, the installer is associated .py ,. PYW, .pyc, .pyw and other file types, and have set a variety of Win32 extension toolkits. I use version 2.2.3, installed in C: / Python22. <2> Install the CJK Code Decoding Base (http://cjkpython.i18n.org/). Including GB2312, GBK, GB18030, BIG5, etc. Encodings, according to your Python version, directly download the corresponding EXE installation package, and the version corresponding to Python 2.2.3 is CJKCODECS-1.1.Win32-Py2.2.exe. Download, run directly, automatically identify the installed Python, do not need any settings. <3> Install Egenix MXBASE (http://www.egenix.com/files/python/...extensions.html). Contains some useful tools for data processing, such as DateTime can be used to process time date fields in the database. The current version corresponding to Python 2.2.3 is eGenix-mx-base-2.0.5.win32-py2.2.exe, which is also automatically installed. <4> Install AdodBapi (http://sourceforge.net/projects/adodbapi). This is a Python-ADO interface module that fully implements Python DB API 2.0 specification, which is very convenient to use. Download Adodbapi-2.0.1.zip to complete setup.bat after decompression.
Write code: The following code uses an Access database C: /APPS/misc.mdb, the settings of the ODBC data source name is MISC, including a data table testdb, a total of 4 fields, long integer ID, character TX, logic BL, time date type DT <1> Using ActivePython Access Database Code: Import Sys, Time, Locale From ODBC IMPORT * TRY: ODBCCONN = ODBC ("DSN = Misc") # Establish Connection Except: Print "Unable to connect to the database!" Sys.exit () locale.setlocale (locale.lc_all, "") # Set the default area (for displaying Chinese Week) Try: odbccur = odbcconn.cursor () # 取 游 o o ob o o ("SELECT ID, TX, BL, DT from TestDB") # 执行 s 查 查 = INT (THEROW [0]) # 取 行 = = INT (THEROW [0]) # = = int For integer print "id =", id, tx = theow [1] Print "TX =", TX, BL = Int (TheRow [2]) # logic value converted to an integer (0 = false 1 = true) Print "BL = ", BL, DT = Time.localtime (TheROW [3]) # Convert to represent local time tupe type print" dt = ", time.strftime ("% y year% M month% D days,% A, % H time% m min% s second ", DT) # format output time theROW = ODBCCUR.FETCHONE () # Remove a line Finally: ODBCCCUR.CLOSE () odbcconn.close () Run Results: ID = 1 TX = Monster BL = 1 dt = December 10, 2003, Wednesday, 09:12 32 seconds id = 2 TX = Magic BL = 0 DT = December 11, 2002, Wednesday, 08:43 43 second ID = 3 TX = Stone BL = 1 DT = 06:00, 2004, 21:34 06 seconds: The function of this code is to obtain all the contents of the database. In the first step, import the ODBC module import ODBC or from ODBC IMPORT * Step 2, establish a connection, use connection = ODBC (Connection_String) Step 3, create a cursor, and perform SQL statement cursor = connection.cursor () int = through the cursor Cursor.execute (SQL, [VAR, ...]) Fourth step, get and resolve the result set.
Data = CURSOR.FETCHONE () # Take a row [data, ...] = cursor.Fetchmany () # Take a multi-line [data, ...] = Cursor.FetChall () # Take all rows and then from the DATA group Remove each field, convert to the correct data type Finally, close the cursor and connect Cursor.close () connection.close () <2> Using Adodbapi Access Database Code: Import Sys, Locale, Adodbapi, Time Try: conn = adodbapi. Connect (R "provider = microsoft.jet.oledb.4.0; data source = c: /apps/appdata/access/misc.mdb;") Except: print "Unable to connect to the database!" sys.exit () locale.setlocale (Locale.lc_all, "") # Set the default area (for displaying Chinese Week) TRY: CUR = Conn.cursor () # gets the cursor Cur.Execute ("SELECT ID, TX, BL, DT from TestDB") # Execute SQL Query THEROW = CUR.FETCHONE () # Take a line While TheroW <> None: # 是 = = Int (THEROW [0]) # Convert to Integer Print "ID =", ID, TX = therow [1 ] Print "TX =", TX.Encode ("GB2312"), BL = INT (TheRoW [2]) # logic value converted to an integer (0 = false 1 = true) Print "BL =", BL, DT = THEROW [3] # Due to the installation of MXBase, TheRow [3] is automatically converted to the DateTime type Print "DT =", time.strftime ("% y year% M month% D days,% a,% h,% m% M%) S second ", dt.Tuple ()) TheroW = cur.Fetchone () # Remove a line Finally: cur.close () Conn.close () Run Result: That is the same as the ODBC version: Basically, the same as the ODBC version, just achieved Connected statements become Connection = adodbapi.connect (connection_string), AD O The connection string has a variety of ways, where Microsoft Jet OLEDB Provider 4.0 is used directly. Adodbapi is written in pure Python, which is more powerful than the ODBC module, and the scope of application is also wide, recommended. In addition, the TX obtained here is a Unicode string, which cannot be directly Print, requiring encoding to GB2312. The above is a general way to access the database through ODBC and ADO, in fact for Access database, directly using Jet Engine access, nor is it complicated. Inscribed 1: The above code is running in Python 2.2, but it is necessary to make some modifications when porting to Python 2.3. <1> Python 2.3 requires a specified source file code, such as GB2312, UTF_8, GBK, GB18030, BIG5, etc.
