Python 数据库连接(sqlite, mysql, oracle, postgresql, sql server)

Python连接数据主要用SQLAlchemy。

SQLAlchemy是最有名的ORM框架。
首先通过pip或easy_install 安装 SQLAlchemy

pip install sqlalchemy

数据库连接

from sqlalchemy import create_engine# 连接 sqlite内存数据库engine = create_engine('sqlite:///:memory:')# 连接 sqlite文件数据库# where <path> is relative:engine = create_engine('sqlite:///foo.db')# or absolute, starting with a slash:engine = create_engine('sqlite:////absolute/path/to/foo.db')# 连接 mysqlengine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')#连接 oracleengine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')#连接SQL Serverengine = create_engine('mssql+pyodbc://mydsn')#orengine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')#连接postgresqlengine = create_engine('postgresql://scott:tiger@localhost:5432/mydatabase')

create_engine()用来初始化数据库连接。SQLAlchemy用一个字符串表示连接信息：

'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'

参考：[廖雪峰Python教程]

数据库驱动安装

cx_Oracle 安装

折腾了一天，在mac上安装好了cx_Oracle库。具体步骤如下：

1, 在http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html 上下载对应系统的包：

• Instant Client Package - Basic
• Instant Client Package - SDK
• Instant Client Package - SQL*Plus

2，解压到本地后，一起拷贝到/Applications/Utilities/instantclient_11_2目录下（instantclient_11_2为新建目录）

3，按照官网提示，运行下面的命令

Installation1. Download the desired Instant Client ZIP files. All installations require the Basic or Basic Lite package.2. Unzip the packages into a single directory such as "/opt/oracle/instantclient_11_2" that is accessible to your application.3. Create the appropriate libclntsh.dylib and libocci.dylib links for the version of Instant Client. For example:cd /opt/oracle/instantclient_11_2ln -s libclntsh.dylib.11.1 libclntsh.dylibln -s libocci.dylib.11.1 libocci.dylib4. Set the environment variable DYLD_LIBRARY_PATH to the directory created in Step 2, for example:export DYLD_LIBRARY_PATH=/opt/oracle/instantclient_11_2:$DYLD_LIBRARY_PATH5. To use supplied binaries such as SQL*Plus, update your PATH environment variable, for example:export PATH=/opt/oracle/instantclient_11_2:$PATH6. Start your application.

4,在/Applications/Utilities/instantclient_11_2/network/admin目录下，建立tnsnames.ora文件，内容如下：

# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\client_1\network\admin\tnsnames.ora# Generated by Oracle configuration tools.ORCL =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)

5，安装cx_Oracle库

$ sudo easy_install cx_Oracle

6，安装成功！

pymssql安装

安装freetds

$ brew install freetds

mysql安装 [win7 x64]

去官网下载mysql，我选的社区版。
win7 64位，MySQLdb直接用pip install 安装不上，出现这样的错误：

    _mysql.c    _mysql.c(42) : fatal error C1083: 无法打开包括文件:“config-win.h”: No such file or directory    error: command 'C:\\Program Files (x86)\\Microsoft Visual Studio 9.0\\VC\\BIN\\cl.exe' failed with exit status 2

百度搜索解决方案，在csdn下载MySQL-python-1.2.3.win32-py2.7 安装包就可行，http://download.csdn.net/download/five3/4488892。