In Python development, using a database is a common thing. Today's NoSQL is also very popular, but it is not involved. This article mainly records the problem of connecting common relational database in Python. Today, when you configure a SQL Server connection, you encounter a different module, which organizes the operation of the entire database module. (Environment: windows,python2.7) First summarize the following common relational database: SQLite, MySQL, Poesgresql, Oracle, SQL Server, Excel
- Sqlite:sqlite3. (python2.5+ built-in)
- Mysql:mysqldb
- POESGRESQL:POSTGRESQL_PSYCOPG2 ()
- Oracle:cx_oracle
- SQL Server:pymssql, PYODBC, Adodbapi
- Excel:pyexcelertor
1.1 SQL server:pymssql Installation pymssql,cmd execute pip install pymssql, and then on the interactive page or idle import pymmsql The specified module cannot be found. Pip installs the latest pymssql2.1.2, but then we need to install it ourselves
FreeTDS andOpenssl.
| Python version |
VS |
| 2.7 |
vs2008 |
| 3.3 or 3.4 |
vs2010 |
| 3.5 |
vs2015 |
After the download is extracted, locate a folder containing DLL files, add this directory to the system or user's
PATHVariable inside.
Then download the precompiled OpenSSL and select the corresponding VS version of the 7z file according to your Python version (see the table above). After extracting the same folder that contains the DLL files (64-bit system can choose the folder name with 64), and then add this directory into the PATH variable.
My two directories are:
C:\freetds-v0.95.81-win-x86_64-vs2008\lib;C:\openssl-1.0.1q-vs2008\bin64;Use: Import pymssqlconn=pymssql.connect (server= "127.0.0.1", port= "1433", user= "sa", password= "123", database= "MyBlog" , charset= "UTF-8")
cursor=conn.cursor()Conn.close () 1.2 SQL server:pyodbc install pyodbc,cmd pip install PYODBC, using the following: Import Pyodbc
Connecting to a database
1) Connect directly to the database and create a cursor (the cursor)
?
| 12 |
cnxn= pyodbc.connect(‘DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass‘)cursor =cnxn.cursor() |
2) Connect using DSN. The DSN connection usually does not require a password, or a PSW keyword is required.
?
| 12 |
cnxn= pyodbc.connect(‘DSN=test;PWD=password‘)cursor =cnxn.cursor() |
1.3 SQL Server:adodbapi Install Adodbapi, cmd under pip install Adodbapi or install Pywin32 (Since pywin32 release 211, Adodbapi is Included) use: Import adodbapi conn={' server ': ' 192.168.29.86\\eclexpress ', ' Password ': ' xxxx ', ' db ': ' pscitemp '} constr = R "Provider=SQLOLEDB.1; Initial catalog=%s; Data source=%s; User id=%s; password=%s; " \
% (conn[' db '), conn[' server '], ' sa ', conn[' password ']) conn=adodbapi.connect (CONSTR) 2, sqlite:sqlite3 use Import sqlite3# Open the DB file to get the connection
conn = sqlite3.connect (' Data file name ')
#获得游标
c = Conn.cursor ()
#执行SQL
C.execute (' SQL fragment ')
#如果有对数据的修改操作, then you need a commit.
Conn.commit ()
#关闭游标
C.close ()
#关闭连接conn. Close () 3, MYSQL:MYSQLDB Connection import mysqldbconn=mysqldb.connect (host= "localhost", user= "root", passwd= "", DB = "Test", charset= "UTF8") cursor = Conn.cursor () 4, POESGRESQL:POSTGRESQL_PSYCOPG2 import PSYCOPG2 conn = Psycopg2.conn ECT (database= "TestDB", user= "Postgres", password= "pass123", host= "127.0.0.1", port= "5432") print "opened database Successfully "cur = conn.cursor () 5, oracle:cx_oracle import cx_oracle #引用模块cx _oracle
Conn=cx_oracle.connect (' Load/[email protected]/ora11g ') #连接数据库
C=conn.cursor () #获取cursor 6, excel:pyexcelertor from pyexcelerator import *shee Ts=pyexcelerator.parse_xls (' Xxx.xls ') #读取文件内容
Python database module
