Ubuntu12.10 under Python (PYODBC) access to SQL Server solutions

first, the basic principlePlease check this URL, speak Gray often detailed: http://www.jeffkit.info/2010/01/476/ Ii. steps of Implementation 1 . Install drivers for SQL Server under Linuxinstall FreeTDS, do not use the Apt-get install, because the source version is not necessarily new, it is recommended to use the stable version,Download the source code to manually compile the installation, because there are a number of parameters that need to be specified in the compilation. Execute the following command:

tar zfvx freetds-stable.tgzcd freetds-*. /configure--prefix=/usr/local/freetds--with-tdsver=7.1 --enable-msdblibmakesudo  makeinstall(note is root)

FreeTDS has been installed successfully, and then installs a gadget freetds-bin to test the connection database:

Apt-get Freetds-bin

After the installation is complete, you can use the TSQL command to test the SQL Server database connection:

Tsql–h mssql-server-ip–u username–p Password

The 1> prompt appears to be successful. 2. Installing the ODBC Driver Management program under LinuxInstall UNIXODBC so that applications under Linux can access the data source through ODBC. We need to install Unixodbc,unixodbc-dev,unixodbc-bin separately (optional), TDSODBC

Install unixodbc unixodbc-dev  unixodbc-bin tdsodbc

The ODBC Driver Manager manages database drivers and data sources through profiles, with database-driven profiles and data source profiles Odbcinst.ini and Odbc.ini, where the location may vary depending on the operating system and installation, although you can use Odbcinst- J command to view. These two files are not content, and now all we have to do is configure the data driver and the data source. When we configure ODBC, we can add content directly to two configurations, but this method is not recommended, and it is recommended to use the ODBCINST command to install driver information and data source information. Configure data-drivenFirst, create a tds.driver.template file, enter the following and save:

[Tds]description            for Linux & MSSQL on win32driver          =/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.sosetup          =/ Usr/lib/x86_64-linux-gnu/odbc/libtdss.so

[TDS] This line means to start defining a database driver called TDS, the following is the relevant properties of this database, please check, driver and setup refers to the two LIB file is the existence or the path is correct, if not exist, to check this step of the installation work is not in place, Now to install the database driver defined in Tds.driver.template to the ODBC Driver Library of the system, execute the following command:

sudo odbcinst-i-d-f tds.driver.template

-I means install, the installation of-D means driver, the driver-F is installed to indicate file, specify the files to install Configure the data sourceNext, create a tds.datasource.template file, enter the following and save:

[My_data]description           = Connection to Windows virtual machinedriver          = tdstrace          = nodatabase          =  P10server          192.168. 88.200 Port           1433 tds_version           7.1

[My_data] One line means to start defining a data source called My_data. The name specified by the driver property is a pre-defined TDs driver, and the others are related properties of the database, such as server address, port, etc. Now install the data source defined in the Tds.datasource.template file into the ODBC data source of the system and execute the following command:

sudo odbcinst-i-s-l-F tds.datasource.template

-I indicates install, install-s means source, the installed data source-L means the data source is installed into the system's global data source configuration file, with-H is installed into the user's data source configuration file-F for file, specify the files to install the driver and the data source configuration is complete, Now, to test access to SQL Server via ODBC, use the UNIXODBC isql command, using the format: ISQL data source name username password

sudo isql my_data sa password+---------------------------------------+| connected! | | | |                                                  Sql-statement                                | | help [tablename]                           | | quit                                            | |                                                  | +---------------------------------------+SQL

If you see a similar picture above, it means that ODBC and SQL Server are communicating well now, so you can enter the SQL statement at the sql> prompt and try it, haha ~ 3. Python implementation with ODBC protocol installed –PYODBCIf your machine has successfully installed PIP, install it directly with the following command:

sudo Install PYODBC

Once the installation is successful, you can test it:

python>>import  pyodbc>> conn=pyodbc.connect ('dsn= data source name; uid= user name; pwd= password ')

More about the use of PYODBC can be seen at this URL: http://my.oschina.net/zhengyijie/blog/35587 If you want to specify which database to connect to, Can modify the configuration file Odbc.ini, the value of the database is left blank, the use can be specified when using the following:

Conn=pyodbc.connect ('dsn= data source name; database= database name; uid= user name; pwd= password ')

Iv. methods of Use 1, through the FreeTDS test the connection to SQL ServerUsing the FreeTDS gadget Freetds-bin, use the TSQL command, using the format: tsql–h mssql-server-ip–u username–p password-p Port

192.168. 88.146 1433  "zh_cn. UTF-8 ""UTF-8""UTF-8"  1>

If the above image appears, the connection is successful. 2. Accessing SQL Server through ODBCUsing the UNIXODBC isql command, use the format: ISQL data source name username password

sudo isql my_data sa password+---------------------------------------+| connected! | | | |                                                  Sql-statement                                | | help [tablename]                           | | quit                                            | |                                                  | +---------------------------------------+SQL>

If you see a similar picture above, it means that ODBC and SQL Server are communicating well now, so you can enter the SQL statement at the sql> prompt and try it, haha ~ 3. Connecting to SQL Server via PyodbcUse format: Conn=pyodbc.connect (' dsn= data source name; database= database name; uid= user name; pwd= password ')

python>>import  pyodbc>> conn=pyodbc.connect ('dsn= data source name; uid= user name; pwd= password ')

If you want to specify which database to connect to, you can modify the configuration file Odbc.ini, leave the database value blank, use to specify databases.

In Methods 2, 3, you can access different SQL Server data sources by modifying the contents of the/etc/odbc.ini file. v. Turn on SQL Server remote connectionAfter the above environment is configured, to successfully access SQL Server, you must open the remote connection for this SQL Server, see: http://blog.csdn.net/lzy_1515/article/details/6868143 References in this article include: http://www.blogwind.com/Wuvist/74789.shtmlhttp://blog.csdn.net/gzlaiyonghao/article/details/ 3838967http://sunnet.blog.sohu.com/203379478.htmlhttp://blog.csdn.net/dlutxie/article/details/6851429http:// www.jeffkit.info/2010/01/476/http://my.oschina.net/zhengyijie/blog/35587