pyodbcis an open source Python module that provides access to ODBC databases.pyodbc implements the Python DB API 2.0 specification.
The PythonDB API defines a database-neutral interface to data stored inrelational databases. Python DB was designed to allow conformantmodules to provide a consistent interface to different databaseproducts. This helps developers to write Python applications that areportable across databases.
pyodbc is a Python DB conformantmodule for ODBC databases. This tutorial shows how to use pyodbc withan ODBC driver, which you can download from this site.You can then connect Python on Linux and Unix to remote database suchas Microsoft SQL Server, Oracle, Microsoft Access, Sybase ASE andInterBase.
Easysoft ODBC drivers havebeen tested with pyodbc 2.0+ on RedHat, Ubuntu (Edgy Eft, Feisty Fawn,Gutsy Gibbon and Hardy Heron) and Debian, but should work with anyrecent Linux distribution (CentOS, Fedora, Mandrake, SUSE and so on).
The pyodbc module requires Python 2.4 or greater (see README.txt, which is included with the pyodbc distribution).
To build pyodbc, you need the Python libraries and header files, and a C++ compiler.
Whentesting on RedHat, we used Python 2.5.1, the python-devel package andthe gcc-c++ package. On Ubuntu, we used Python 2.5.1, the python-devpackage and the g++ package.
To use pyodbc, you need to install an ODBC driver on the machine Python where is installed:
For example, if want to access SQL Server from Python, download the Easysoft ODBC-SQL Server Driver for your Python platform. If the SQL Server ODBC driver is not currently available for your platform, check the list of ODBC-ODBC Bridge client platforms. The ODBC-ODBC Bridge is an alternative SQL Server solution from Easysoft, which you can download from this site.
For installation instructions, see the ODBC driver documentation. Refer to the documentation to see which environment variables you need to set (LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH, SHLIB_PATH or ORACLE_HOME depending on the driver, platform and linker).
[MSSQL-PYTHON] Driver = Easysoft ODBC-SQL Server Driver Server = my_machine\SQLEXPRESS User = my_domain\my_user Password = my_password # If the database you want to connect to is the default # for the SQL Server login, omit this attribute Database = Northwind
cd /usr/local/easysoft/unixODBC/bin ./isql -v MSSQL-PYTHON
At the prompt, type "help" to display a list of tables. To exit, press return in an empty prompt line.
OnUnix and Linux platforms, you need to download the pyodbc sourcedistribution and build it against an ODBC driver manager. Theseinstructions show how to build pyodbc against the unixODBC drivermanager supplied with an Easysoft ODBC driver. We recommend that youuse the driver manager distributed with the driver because this is theversion of unixODBC that we test the driver with.
$ unzip pyodbc-2.0.52.zip $ cd pyodbc-2.0.52
extra_compile_args = None extra_link_args = None
Edit the lines so that they look like this:
extra_compile_args = ['-I/usr/local/easysoft/unixODBC/include'] extra_link_args = ['-L/usr/local/easysoft/unixODBC/lib']
Note In pyodbc versions earlier than 2.0.52, setup.py was named setup.PY.
$ python setup.py build
Note If you need to rebuild pyodbc, first remove the build directory tree by using rm -r build rather than python setup.py clean. When testing with pyodbc 2.0.52, we found that the clean command failed to remove pyodbc.so. As a consequence, running python setup.py build failed to rebuild pyodbc.so.
# python setup.py install
The pyodbc distribution includes two test suites:
ThePython DB API 2.0 test suite was written to allow Python DB developersto verify their driver’s DB API conformance. As the tests access andmanipulate database tables, they provide another way to test pyodbcagainst your ODBC driver and database. We therefore recommend that yourun them. To do this:
[apitest] section, specify your ODBC data source in the connection-string value. For example: [apitest] connection-string=DSN=MSSQL-PYTHON
$ python setup.py -v apitest
The pyodbc tests allow you to ensure that an ODBC driver is compatible with pyodbc.
NoteSome tests use data types and SQL syntax that are supported by SQLServer but not other databases. The test suite is most relevant to SQLServer therefore. The test suite does skip some tests based on datatype information reported by the ODBC driver. However, some tests arestill run even though the driver has reported that the prerequisitedata type is not available. (To see which tests pyodbctest skips,include the -d option when running the tests.)
Whenwe tested pyodbc, Easysoft ODBC drivers passed all tests that thetarget database was capable of passing. For example, when we ran thepyodbc test suite against Oracle Database XE, test_sqlserver_callprocfailed because it uses SQL Server specific syntax to create and executea stored procedure. If the test is modified to use SQL syntax thatOracle supports, the test succeeds. For example:
# Recreate existing procedure using syntax that Oracle supports.self.cursor.execute(""" create or replace procedure pyodbctest (var1 IN OUT VARCHAR2) is begin select s into var1 from t1; end; """)self.cnxn.commit()# Call the procedure, using the more portable ODBC escape sequence.# The ODBC driver for the target database will replaces this escape# sequence with the appropriate DBMS-specific syntax for calling# procedures. This method for calling procedures will therefore work# for both Oracle and SQL Server. Note that pyodbc does not# currently support the DB method callproc().self.cursor.execute("{call pyodbctest(?)}", ('testing'))
In addition to these issues, please note the following before running the tests:
| Test | Notes | Solution |
|---|---|---|
| test_image test_long_binary | Before running these tests, make sure that you have pyodbc 2.0.52 or later. When testing pyodbc with the SQL Server ODBC driver, we submitted a pyodbc patch, which fixes a problem that affects these tests. (See bug report [ 1872275 ] data at exec problem.) The patch was merged into the 2.0.52 release. | If necessary, upgrade pyodbc to 2.0.52 or later. |
To run the tests, cd into the directory created by unzipping the pyodbc distribution file and type:
$ python pyodbctests.py DSN=data_source
where data_source is the name of your ODBCdata source. The pyodbc tests attempt to create tables and proceduresand insert and retrieve data. Your data source therefore needs toconnect to a database in which these actions are permitted.
To run an individual test rather than all tests, include -t test_name before the DSN setting. (Type python pyodbctests.py --help to display all test suite options.)
In the example session shown here, we used pyodbc with the SQL Server ODBC driverto connect Python to a SQL Server Express database. The driver can alsobe used to access other SQL Server 2005 editions from Python on Linuxand Unix. (As well as earlier versions of the database such as SQLServer 2000 and SQL Server 7.0.)
In the pyodbc.connect() call, replace MSSQL-PYTHON with the name of your SQL Server ODBC driver data source.
$ pythonPython 2.5.1 (r251:54863, Jan 25 2008, 16:14:49)[GCC 3.2.2 20030222 (Red Hat Linux 3.2.2-5)] on linux2Type "help", "copyright", "credits" or "license" for more information.>>> import pyodbc>>> cnxn = pyodbc.connect("DSN=MSSQL-PYTHON")>>> cursor = cnxn.cursor()>>> cursor.tables()>>> rows = cursor.fetchall()>>> for row in rows:... print row.table_name...CategoriesCustomerCustomerDemoCustomerDemographicsCustomersEmployeesEmployeeTerritories...>>> exit()
To connect to adifferent DBMS, the only change to the Python code (shown in theprevious section) that you need to make is the data source name. Forexample:
>>> cnxn = pyodbc.connect("DSN=ORACLE-PYTHON")
ORACLE-PYTHON is an Oracle ODBC driver data source that we used with pyodbc to connect Python to an Oracle database.
聯(lián)系客服
