X Tutup
Skip to content

Commit 0afcae0

Browse files
gregturngregturn
committed
SESPRINGPYTHONPY-103: Applied patches that have code, test cases, and documentation to support connecting to SQLServer using pyodbc.
git-svn-id: https://src.springframework.org/svn/se-springpython-py/trunk/springpython@510 ce8fead1-4192-4296-8608-a705134b927f
1 parent e0ca373 commit 0afcae0

File tree

6 files changed

+274
-1
lines changed

6 files changed

+274
-1
lines changed

‎docs/reference/src/dao.xml‎

Lines changed: 87 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -247,7 +247,93 @@ results = dt.query("select title, air_date, episode_number, writer from tv_shows
247247

248248
<para><emphasis>Inserts</emphasis> are implemented through the execute() function, just
249249
like in JdbcTemplate.</para>
250-
</section>
250+
</section>
251+
252+
<section id="dao-databasetemplate-sqlserver">
253+
<title>Notes on using SQLServerConnectionFactory</title>
254+
255+
<para>
256+
<classname>SQLServerConnectionFactory</classname> uses ODBC for connecting
257+
to SQL Server instances and it expects you to pass the ODBC parameters
258+
when creating connection factories or when injecting factory
259+
settings through IoC. The ODBC parameters you provide are directly
260+
translated into an ODBC connection string.
261+
</para>
262+
<para>
263+
That means that you use the exact ODBC parameters your ODBC provider
264+
understands and not the standard <emphasis>username</emphasis>,
265+
<emphasis>password</emphasis>, <emphasis>hostname</emphasis>
266+
and <emphasis>db</emphasis> parameters as with other connection
267+
factories.
268+
</para>
269+
<para>
270+
A simple example will demonstrate this. Here's how you would create
271+
a <classname>DatabaseTemplate</classname> on Windows
272+
for running queries against an SQL Server instance.
273+
</para>
274+
275+
<programlisting><![CDATA[
276+
from springpython.database.core import DatabaseTemplate
277+
from springpython.database.factory import SQLServerConnectionFactory
278+
279+
driver = "{SQL Server}"
280+
server = "localhost"
281+
database = "springpython"
282+
uid = "springpython"
283+
pwd = "cdZS*RQRBdc9a"
284+
285+
factory = SQLServerConnectionFactory(DRIVER=driver, SERVER=server, DATABASE=database, UID=uid, PWD=pwd)
286+
dt = DatabaseTemplate(factory)
287+
]]></programlisting>
288+
289+
<note>
290+
<title><classname>SQLServerConnectionFactory</classname> is dictionary driven</title>
291+
292+
<para>Due to <classname>SQLServerConnectionFactory</classname>'s pass-through nature, it is coded to accept a dictionary.
293+
For pure python, this means you MUST name the arguments and NOT rely on argument position.</para>
294+
295+
<para>For an XML-based application context, you must populate the argument <methodname>odbc_info</methodname>
296+
with a dictionary. See the following example.</para>
297+
</note>
298+
299+
<programlisting><![CDATA[
300+
<?xml version="1.0" encoding="UTF-8"?>
301+
<objects xmlns="http://www.springframework.org/springpython/schema/objects"
302+
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
303+
xsi:schemaLocation="http://www.springframework.org/springpython/schema/objects
304+
http://springpython.webfactional.com/schema/context/spring-python-context-1.0.xsd">
305+
306+
<object id="connection_factory" class="springpython.database.factory.SQLServerConnectionFactory">
307+
<property name="odbc_info">
308+
<dict>
309+
<entry>
310+
<key><value>DRIVER</value></key>
311+
<value>{SQL Server}</value>
312+
</entry>
313+
<entry>
314+
<key><value>SERVER</value></key>
315+
<value>localhost</value>
316+
</entry>
317+
<entry>
318+
<key><value>DATABASE</value></key>
319+
<value>springpython</value>
320+
</entry>
321+
<entry>
322+
<key><value>UID</value></key>
323+
<value>springpython</value>
324+
</entry>
325+
<entry>
326+
<key><value>PWD</value></key>
327+
<value>cdZS*RQRBdc9a</value>
328+
</entry>
329+
</dict>
330+
</property>
331+
</object>
332+
333+
</objects>
334+
]]></programlisting>
335+
336+
</section>
251337

252338
</section>
253339
</chapter>

‎src/springpython/database/factory.py‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -133,3 +133,24 @@ def connect(self):
133133
"""The import statement is delayed so the library is loaded ONLY if this factory is really used."""
134134
import cx_Oracle
135135
return cx_Oracle.connect(self.username, self.password, self.db)
136+
137+
class SQLServerConnectionFactory(ConnectionFactory):
138+
def __init__(self, **odbc_info):
139+
ConnectionFactory.__init__(self, [types.TupleType])
140+
self.odbc_info = odbc_info
141+
142+
def connect(self):
143+
"""The import statement is delayed so the library is loaded ONLY if this factory is really used."""
144+
import pyodbc
145+
odbc_info = ";".join(["%s=%s" % (key, value) for key, value in self.odbc_info.items()])
146+
return pyodbc.connect(odbc_info)
147+
148+
def in_transaction(self):
149+
return True
150+
151+
def count_type(self):
152+
return types.IntType
153+
154+
def convert_sql_binding(self, sql_query):
155+
"""SQL Server expects parameters to be passed as question marks."""
156+
return re.sub(pattern="%s", repl="?", string=sql_query)

‎test/springpythontest/checkin.py‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3,10 +3,12 @@
33
#from springpythontest.databaseCoreTestCases import MySQLDatabaseTemplateTestCase
44
#from springpythontest.databaseCoreTestCases import PostGreSQLDatabaseTemplateTestCase
55
from springpythontest.databaseCoreTestCases import SqliteDatabaseTemplateTestCase
6+
#from springpythontest.databaseCoreTestCases import SQLServerDatabaseTemplateTestCase
67
from springpythontest.databaseCoreTestCases import DatabaseTemplateMockTestCase
78
#from springpythontest.databaseTransactionTestCases import MySQLTransactionTestCase
89
#from springpythontest.databaseTransactionTestCases import PostGreSQLTransactionTestCase
910
from springpythontest.databaseTransactionTestCases import SqliteTransactionTestCase
11+
#from springpythontest.databaseTransactionTestCases import SQLServerTransactionTestCase
1012
#from springpythontest.remotingTestCases import PyroRemotingTestCase
1113
#from springpythontest.remotingTestCases import HessianRemotingTestCase
1214
from springpythontest.securityEncodingTestCases import *

‎test/springpythontest/databaseCoreTestCases.py‎

Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -783,3 +783,66 @@ def testIoCGeneralQueryWithDictionaryRowMapper(self):
783783
results = databaseTemplate.query("select * from animal", rowhandler=DictionaryRowMapper())
784784

785785

786+
class SQLServerDatabaseTemplateTestCase(AbstractDatabaseTemplateTestCase):
787+
def __init__(self, methodName='runTest'):
788+
AbstractDatabaseTemplateTestCase.__init__(self, methodName)
789+
790+
def createTables(self):
791+
self.createdTables = True
792+
try:
793+
self.factory = factory.SQLServerConnectionFactory(DRIVER="{SQL Server}",
794+
SERVER="localhost", DATABASE="springpython", UID="springpython", PWD="cdZS*RQRBdc9a")
795+
dt = DatabaseTemplate(self.factory)
796+
dt.execute("""IF EXISTS(SELECT 1 FROM sys.tables WHERE name='animal')
797+
DROP TABLE animal""")
798+
799+
dt.execute("""
800+
CREATE TABLE animal (
801+
id INTEGER IDENTITY(1,1) PRIMARY KEY,
802+
name VARCHAR(11),
803+
category VARCHAR(20),
804+
population INTEGER
805+
)
806+
""")
807+
808+
self.factory.commit()
809+
810+
except Exception, e:
811+
print("""
812+
!!! Can't run SQLServerDatabaseTemplateTestCase !!!
813+
814+
This assumes you have installed pyodbc (http://code.google.com/p/pyodbc/).
815+
816+
And then created an SQL Server database for the 'springpython'
817+
login and user.
818+
819+
USE master;
820+
821+
IF EXISTS(SELECT 1 FROM sys.databases WHERE name='springpython')
822+
DROP DATABASE springpython;
823+
824+
IF EXISTS(SELECT 1 FROM sys.syslogins WHERE name='springpython')
825+
DROP LOGIN springpython;
826+
827+
IF EXISTS(SELECT 1 FROM sys.sysusers WHERE name='springpython')
828+
DROP USER springpython;
829+
830+
CREATE DATABASE springpython;
831+
CREATE LOGIN springpython WITH PASSWORD='cdZS*RQRBdc9a', DEFAULT_DATABASE=springpython;
832+
833+
USE springpython;
834+
835+
CREATE USER springpython FOR LOGIN springpython;
836+
EXEC sp_addrolemember 'db_owner', 'springpython';
837+
838+
From here on, you should be able to connect into SQL Server and run SQL scripts.
839+
""")
840+
raise e
841+
842+
def testIoCGeneralQuery(self):
843+
appContext = ApplicationContext(XMLConfig("support/databaseTestSQLServerApplicationContext.xml"))
844+
factory = appContext.get_object("connection_factory")
845+
846+
databaseTemplate = DatabaseTemplate(factory)
847+
results = databaseTemplate.query("select * from animal", rowhandler=testSupportClasses.SampleRowMapper())
848+

‎test/springpythontest/databaseTransactionTestCases.py‎

Lines changed: 67 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -527,4 +527,71 @@ def createTables(self):
527527
""")
528528
raise e
529529

530+
class SQLServerTransactionTestCase(AbstractTransactionTestCase):
530531

532+
def __init__(self, methodName='runTest'):
533+
AbstractTransactionTestCase.__init__(self, methodName)
534+
535+
def createTables(self):
536+
self.createdTables = True
537+
try:
538+
self.factory = factory.SQLServerConnectionFactory(DRIVER="{SQL Server}",
539+
SERVER="localhost", DATABASE="springpython", UID="springpython", PWD="cdZS*RQRBdc9a")
540+
dt = DatabaseTemplate(self.factory)
541+
542+
dt.execute("""IF EXISTS(SELECT 1 FROM sys.tables WHERE name='animal')
543+
DROP TABLE animal""")
544+
545+
dt.execute("""
546+
CREATE TABLE animal (
547+
id INTEGER IDENTITY(1,1) PRIMARY KEY,
548+
name VARCHAR(11),
549+
category VARCHAR(20),
550+
population INTEGER
551+
)
552+
""")
553+
554+
dt.execute("""IF EXISTS(SELECT 1 FROM sys.tables WHERE name='account')
555+
DROP TABLE account""")
556+
557+
dt.execute("""
558+
CREATE TABLE account (
559+
id INTEGER IDENTITY(1,1) PRIMARY KEY,
560+
account_num VARCHAR(11),
561+
balance FLOAT(10)
562+
)
563+
""")
564+
565+
self.factory.commit()
566+
567+
except Exception, e:
568+
print("""
569+
!!! Can't run SQLServerDatabaseTemplateTestCase !!!
570+
571+
This assumes you have installed pyodbc (http://code.google.com/p/pyodbc/).
572+
573+
And then created an SQL Server database for the 'springpython'
574+
login and user.
575+
576+
USE master;
577+
578+
IF EXISTS(SELECT 1 FROM sys.databases WHERE name='springpython')
579+
DROP DATABASE springpython;
580+
581+
IF EXISTS(SELECT 1 FROM sys.syslogins WHERE name='springpython')
582+
DROP LOGIN springpython;
583+
584+
IF EXISTS(SELECT 1 FROM sys.sysusers WHERE name='springpython')
585+
DROP USER springpython;
586+
587+
CREATE DATABASE springpython;
588+
CREATE LOGIN springpython WITH PASSWORD='cdZS*RQRBdc9a', DEFAULT_DATABASE=springpython;
589+
590+
USE springpython;
591+
592+
CREATE USER springpython FOR LOGIN springpython;
593+
EXEC sp_addrolemember 'db_owner', 'springpython';
594+
595+
From here on, you should be able to connect into SQL Server and run SQL scripts.
596+
""")
597+
raise e

‎test/springpythontest/support/databaseTestSQLServerApplicationContext.xml‎

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,34 @@
1+
<?xml version="1.0" encoding="UTF-8"?>
2+
<objects xmlns="http://www.springframework.org/springpython/schema/objects"
3+
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4+
xsi:schemaLocation="http://www.springframework.org/springpython/schema/objects
5+
http://springpython.webfactional.com/schema/context/spring-python-context-1.0.xsd">
6+
7+
<object id="connection_factory" class="springpython.database.factory.SQLServerConnectionFactory">
8+
<property name="odbc_info">
9+
<dict>
10+
<entry>
11+
<key><value>DRIVER</value></key>
12+
<value>{SQL Server}</value>
13+
</entry>
14+
<entry>
15+
<key><value>SERVER</value></key>
16+
<value>localhost</value>
17+
</entry>
18+
<entry>
19+
<key><value>DATABASE</value></key>
20+
<value>springpython</value>
21+
</entry>
22+
<entry>
23+
<key><value>UID</value></key>
24+
<value>springpython</value>
25+
</entry>
26+
<entry>
27+
<key><value>PWD</value></key>
28+
<value>cdZS*RQRBdc9a</value>
29+
</entry>
30+
</dict>
31+
</property>
32+
</object>
33+
34+
</objects>

0 commit comments

Comments
 (0)
X Tutup