利用Python 连接数据库及其查询

==================================================

Connect and run SQL queries to an Oracle database from Python
==================================================

从 Python 连接到 Oracle 数据库提供了运行基本可以利用的 SQL 查询中地理处理任务的能力。提供说明描述了如何连接到 Oracle 数据库并从 Python 脚本运行的 SQL 查询。

下载和安装合适的cx_Oracle模块

http://cx-oracle.sourceforge.net/ 

根据用户自己的python版本以及Oracle版本下载对应的信息，而且如果是安装ArcGIS Desktop自带的Python，应该下载32位程序

Download 5.1.2 released July 6, 2012

• Windows x86 Installer (Oracle 10g, Python 2.6)
• Windows x86 Installer (Oracle 10g, Python 2.7)
• Windows x86 Installer (Oracle 10g, Python 3.2)
• Windows x86 Installer (Oracle 10g, Python 3.3)
• Windows x86 Installer (Oracle 11g, Python 2.6)
• Windows x86 Installer (Oracle 11g, Python 2.7)
• Windows x86 Installer (Oracle 11g, Python 3.2)
• Windows x86 Installer (Oracle 11g, Python 3.3)

• Windows amd64 Installer (Oracle 10g, Python 2.6)
• Windows amd64 Installer (Oracle 10g, Python 2.7)
• Windows amd64 Installer (Oracle 10g, Python 3.2)
• Windows amd64 Installer (Oracle 10g, Python 3.3)
• Windows amd64 Installer (Oracle 11g, Python 2.6)
• Windows amd64 Installer (Oracle 11g, Python 2.7)
• Windows amd64 Installer (Oracle 11g, Python 3.2)
• Windows amd64 Installer (Oracle 11g, Python 3.3)

相关例子参考

//引用cx_Oracle
>>> import cx_Oracle;
//Oracle连接，输入用户名密码和网络服务名
>>> conn=cx_Oracle.connect('sde/sde@orcl_165')
//定义参数获得游标
>>> cursor=conn.cursor();
//建立一个查询语句
>>> sql="select * from owner where objectid<3"
//获得游标对象
>>> cursor.execute(sql)
<__builtin__.OracleCursor on <cx_Oracle.Connection to sde@orcl_165>>
//获得游标指向的rows
>>> rows =cursor.fetchall()
//循环row
>>> for row in rows:
//输出row信息
...     print row
...     
(1, 100, 1, u'jobs', u'luly')
(2, 100, 2, u'jim', u'bob')

#==========================
# cx-oracle的教程    ,
#==========================     
    [简单] http://www.orafaq.com/wiki/Python
    [简单] http://codingtutorials.co.uk/blog/?p=31
    [全面]Sidekick - cx_Oracle (code paterns)系列 http://www.dbaportal.eu/?q=node/125
    [全面] http://www.oracle.com/technetwork/articles/dsl/python-091105.html
    [示例] http://code.google.com/p/cx-oracle-demos 
    [介绍]Python cx_Oracle 5.0新特性 http://www.oszx.net/archives/718
    
    
如何执行Oracle的存储过程, 并取到存储过程的out游标
http://stackoverflow.com/questions/6821372/python-oracle-passing-in-a-cursor-out-parameter

=============================================================================

Connect and run SQL queries to an SQL Server database from Python
=============================================================================

相关组件下载

相关代码参考

//Make a connection to the SQL Server database using database authentication or Windows authentication by passing in the //appropriate parameters such as the server name, user ID (UID) and password (PWD): 
//Database authentication string: 
con = pyodbc.connect('DRIVER={SQL Server};SERVER=Prod1\SQL2008R2;DATABASE=SDE;UID=sa;PWD=sa')
//Windows authentication string: 
con = pyodbc.connect('Trusted_Connection=yes', driver = '{SQL Server}',server = ‘Prod1\SQL2008R2 ‘, database = ‘SDE')
//Define a parameter to access the cursor method: 
cur = con.cursor()
//Create a query string: 
querystring = "select * into ParcelsA from ParcelsB"
//Pass the query string into the cursor method: 
cur.execute(querystring)
con.commit()

==============================================================================

Connect and run SQL queries to an PostgreSQL database from Python
===============================================================================

相关组件下载

Psycopg Version	Python Version	PostgreSQL version built against	Release Build	Debug Build (--define PSYCOPG_DEBUG)
2.4.5 (For Python 2.6)	2.6	9.1.3	psycopg2-2.4.5.win32-py2.6-pg9.1.3-release.exe
(For Python 2.6 amd64) (64bit Windows)	2.6	9.1.3	psycopg2-2.4.5.win-amd64-py2.6-pg9.1.3-release.exe
2.4.5 (For Python 2.7)	2.7	9.1.3	psycopg2-2.4.5.win32-py2.7-pg9.1.3-release.exe
(For Python 2.7 amd64) (64bit Windows)	2.7	9.1.3	psycopg2-2.4.5.win-amd64-py2.7-pg9.1.3-release.exe

相关参考

//Import the module in the Python script: 
import psycopg2
//Make a connection to a PostgreSQL database by passing in the appropriate user/password to the following connection string: 
connection = psycopg2.connect(host='prod', database='sde', user='sde', password='sde')
//Define a parameter to access the cursor method: 
cursor = connection.cursor()
//Create a query string and pass to cursor method: 
cursor.execute('select * from PARCELS WHERE OBJECTID < 70000')
//Create a for loop and print results 
for query in cursor:
    print str(query)

-------------------------------------------------------------------------------------------------------
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
------------------------------------------------------------------------------------------------------