1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>
Microsoft SQL Server
—
SQLAlchemy 0.9 Documentation
</title>
<!-- begin iterate through SQLA + sphinx environment css_files -->
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
<link rel="stylesheet" href="../_static/docs.css" type="text/css" />
<link rel="stylesheet" href="../_static/sphinx_paramlinks.css" type="text/css" />
<link rel="stylesheet" href="../_static/changelog.css" type="text/css" />
<!-- end iterate through SQLA + sphinx environment css_files -->
<!-- begin layout.mako headers -->
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '0.9.8',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html'
};
</script>
<!-- begin iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/jquery.js"></script>
<script type="text/javascript" src="../_static/underscore.js"></script>
<script type="text/javascript" src="../_static/doctools.js"></script>
<!-- end iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/detectmobile.js"></script>
<script type="text/javascript" src="../_static/init.js"></script>
<link rel="index" title="Index" href="../genindex.html" />
<link rel="search" title="Search" href="../search.html" />
<link rel="copyright" title="Copyright" href="../copyright.html" />
<link rel="top" title="SQLAlchemy 0.9 Documentation" href="../index.html" />
<link rel="up" title="Dialects" href="index.html" />
<link rel="next" title="MySQL" href="mysql.html" />
<link rel="prev" title="Firebird" href="firebird.html" />
<!-- end layout.mako headers -->
</head>
<body>
<div id="docs-container">
<div id="docs-top-navigation-container" class="body-background">
<div id="docs-header">
<div id="docs-version-header">
Release: <span class="version-num">0.9.8</span> | Release Date: October 13, 2014
</div>
<h1>SQLAlchemy 0.9 Documentation</h1>
</div>
</div>
<div id="docs-body-container">
<div id="fixed-sidebar" class="withsidebar">
<div id="docs-sidebar-popout">
<h3><a href="../index.html">SQLAlchemy 0.9 Documentation</a></h3>
<p id="sidebar-paginate">
<a href="index.html" title="Dialects">Up</a> |
<a href="firebird.html" title="Firebird">Prev</a> |
<a href="mysql.html" title="MySQL">Next</a>
</p>
<p id="sidebar-topnav">
<a href="../index.html">Contents</a> |
<a href="../genindex.html">Index</a>
</p>
<div id="sidebar-search">
<form class="search" action="../search.html" method="get">
<input type="text" name="q" size="12" /> <input type="submit" value="Search" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
</div>
<div id="docs-sidebar">
<h3><a href="#">
Microsoft SQL Server
</a></h3>
<ul>
<li><a class="reference internal" href="#">Microsoft SQL Server</a><ul>
<li><a class="reference internal" href="#dialect-mssql">Support for the Microsoft SQL Server database.</a></li>
<li><a class="reference internal" href="#auto-increment-behavior">Auto Increment Behavior</a><ul>
<li><a class="reference internal" href="#controlling-start-and-increment">Controlling “Start” and “Increment”</a></li>
<li><a class="reference internal" href="#insert-behavior">INSERT behavior</a></li>
</ul>
</li>
<li><a class="reference internal" href="#collation-support">Collation Support</a></li>
<li><a class="reference internal" href="#limit-offset-support">LIMIT/OFFSET Support</a></li>
<li><a class="reference internal" href="#nullability">Nullability</a></li>
<li><a class="reference internal" href="#date-time-handling">Date / Time Handling</a></li>
<li><a class="reference internal" href="#clustered-index-support">Clustered Index Support</a></li>
<li><a class="reference internal" href="#mssql-specific-index-options">MSSQL-Specific Index Options</a><ul>
<li><a class="reference internal" href="#include">INCLUDE</a></li>
<li><a class="reference internal" href="#index-ordering">Index ordering</a></li>
</ul>
</li>
<li><a class="reference internal" href="#compatibility-levels">Compatibility Levels</a></li>
<li><a class="reference internal" href="#triggers">Triggers</a></li>
<li><a class="reference internal" href="#enabling-snapshot-isolation">Enabling Snapshot Isolation</a></li>
<li><a class="reference internal" href="#known-issues">Known Issues</a></li>
<li><a class="reference internal" href="#sql-server-data-types">SQL Server Data Types</a></li>
<li><a class="reference internal" href="#module-sqlalchemy.dialects.mssql.pyodbc">PyODBC</a><ul>
<li><a class="reference internal" href="#dialect-mssql-pyodbc-url">DBAPI</a></li>
<li><a class="reference internal" href="#dialect-mssql-pyodbc-connect">Connecting</a></li>
<li><a class="reference internal" href="#additional-connection-examples">Additional Connection Examples</a></li>
<li><a class="reference internal" href="#unicode-binds">Unicode Binds</a></li>
</ul>
</li>
<li><a class="reference internal" href="#module-sqlalchemy.dialects.mssql.mxodbc">mxODBC</a><ul>
<li><a class="reference internal" href="#dialect-mssql-mxodbc-url">DBAPI</a></li>
<li><a class="reference internal" href="#dialect-mssql-mxodbc-connect">Connecting</a></li>
<li><a class="reference internal" href="#execution-modes">Execution Modes</a></li>
</ul>
</li>
<li><a class="reference internal" href="#module-sqlalchemy.dialects.mssql.pymssql">pymssql</a><ul>
<li><a class="reference internal" href="#dialect-mssql-pymssql-url">DBAPI</a></li>
<li><a class="reference internal" href="#dialect-mssql-pymssql-connect">Connecting</a></li>
</ul>
</li>
<li><a class="reference internal" href="#module-sqlalchemy.dialects.mssql.zxjdbc">zxjdbc</a><ul>
<li><a class="reference internal" href="#dialect-mssql-zxjdbc-url">DBAPI</a></li>
<li><a class="reference internal" href="#dialect-mssql-zxjdbc-connect">Connecting</a></li>
</ul>
</li>
<li><a class="reference internal" href="#module-sqlalchemy.dialects.mssql.adodbapi">AdoDBAPI</a><ul>
<li><a class="reference internal" href="#dialect-mssql-adodbapi-url">DBAPI</a></li>
<li><a class="reference internal" href="#dialect-mssql-adodbapi-connect">Connecting</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</div>
<div id="docs-body" class="withsidebar" >
<div class="section" id="module-sqlalchemy.dialects.mssql.base">
<span id="microsoft-sql-server"></span><span id="mssql-toplevel"></span><h1>Microsoft SQL Server<a class="headerlink" href="#module-sqlalchemy.dialects.mssql.base" title="Permalink to this headline">¶</a></h1>
<div class="section" id="dialect-mssql">
<p>Support for the Microsoft SQL Server database.</p>
<h2>DBAPI Support<a class="headerlink" href="#dialect-mssql" title="Permalink to this headline">¶</a></h2>
<p>The following dialect/DBAPI options are available. Please refer to individual DBAPI sections for connect information.<ul class="simple">
<li><a class="reference external" href="#module-sqlalchemy.dialects.mssql.pyodbc">PyODBC</a></li>
<li><a class="reference external" href="#module-sqlalchemy.dialects.mssql.mxodbc">mxODBC</a></li>
<li><a class="reference external" href="#module-sqlalchemy.dialects.mssql.pymssql">pymssql</a></li>
<li><a class="reference external" href="#module-sqlalchemy.dialects.mssql.zxjdbc">zxJDBC for Jython</a></li>
<li><a class="reference external" href="#module-sqlalchemy.dialects.mssql.adodbapi">adodbapi</a></li>
</ul>
</p>
</div>
<div class="section" id="auto-increment-behavior">
<h2>Auto Increment Behavior<a class="headerlink" href="#auto-increment-behavior" title="Permalink to this headline">¶</a></h2>
<p>SQL Server provides so-called “auto incrementing” behavior using the
<tt class="docutils literal"><span class="pre">IDENTITY</span></tt> construct, which can be placed on an integer primary key.
SQLAlchemy considers <tt class="docutils literal"><span class="pre">IDENTITY</span></tt> within its default “autoincrement” behavior,
described at <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column.params.autoincrement" title="sqlalchemy.schema.Column"><tt class="xref py py-paramref docutils literal"><span class="pre">Column.autoincrement</span></tt></a>; this means
that by default, the first integer primary key column in a <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a>
will be considered to be the identity column and will generate DDL as such:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Table</span><span class="p">,</span> <span class="n">MetaData</span><span class="p">,</span> <span class="n">Column</span><span class="p">,</span> <span class="n">Integer</span>
<span class="n">m</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="n">t</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'t'</span><span class="p">,</span> <span class="n">m</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'x'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">))</span>
<span class="n">m</span><span class="o">.</span><span class="n">create_all</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span></pre></div>
</div>
<p>The above example will generate DDL as:</p>
<div class="highlight-sql"><div class="highlight"><pre><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">t</span> <span class="p">(</span>
<span class="n">id</span> <span class="nb">INTEGER</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="k">IDENTITY</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">),</span>
<span class="n">x</span> <span class="nb">INTEGER</span> <span class="k">NULL</span><span class="p">,</span>
<span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">id</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<p>For the case where this default generation of <tt class="docutils literal"><span class="pre">IDENTITY</span></tt> is not desired,
specify <tt class="docutils literal"><span class="pre">autoincrement=False</span></tt> on all integer primary key columns:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">m</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="n">t</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'t'</span><span class="p">,</span> <span class="n">m</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span> <span class="n">autoincrement</span><span class="o">=</span><span class="bp">False</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'x'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">))</span>
<span class="n">m</span><span class="o">.</span><span class="n">create_all</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span></pre></div>
</div>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">An INSERT statement which refers to an explicit value for such
a column is prohibited by SQL Server, however SQLAlchemy will detect this
and modify the <tt class="docutils literal"><span class="pre">IDENTITY_INSERT</span></tt> flag accordingly at statement execution
time. As this is not a high performing process, care should be taken to
set the <tt class="docutils literal"><span class="pre">autoincrement</span></tt> flag appropriately for columns that will not
actually require IDENTITY behavior.</p>
</div>
<div class="section" id="controlling-start-and-increment">
<h3>Controlling “Start” and “Increment”<a class="headerlink" href="#controlling-start-and-increment" title="Permalink to this headline">¶</a></h3>
<p>Specific control over the parameters of the <tt class="docutils literal"><span class="pre">IDENTITY</span></tt> value is supported
using the <a class="reference internal" href="../core/defaults.html#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal"><span class="pre">schema.Sequence</span></tt></a> object. While this object normally
represents an explicit “sequence” for supporting backends, on SQL Server it is
re-purposed to specify behavior regarding the identity column, including
support of the “start” and “increment” values:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Table</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">Sequence</span><span class="p">,</span> <span class="n">Column</span>
<span class="n">Table</span><span class="p">(</span><span class="s">'test'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span>
<span class="n">Sequence</span><span class="p">(</span><span class="s">'blah'</span><span class="p">,</span> <span class="n">start</span><span class="o">=</span><span class="mi">100</span><span class="p">,</span> <span class="n">increment</span><span class="o">=</span><span class="mi">10</span><span class="p">),</span>
<span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">20</span><span class="p">))</span>
<span class="p">)</span><span class="o">.</span><span class="n">create</span><span class="p">(</span><span class="n">some_engine</span><span class="p">)</span></pre></div>
</div>
<p>would yield:</p>
<div class="highlight-sql"><div class="highlight"><pre><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">test</span> <span class="p">(</span>
<span class="n">id</span> <span class="nb">INTEGER</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="k">IDENTITY</span><span class="p">(</span><span class="mi">100</span><span class="p">,</span><span class="mi">10</span><span class="p">)</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">,</span>
<span class="n">name</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">20</span><span class="p">)</span> <span class="k">NULL</span><span class="p">,</span>
<span class="p">)</span></pre></div>
</div>
<p>Note that the <tt class="docutils literal"><span class="pre">start</span></tt> and <tt class="docutils literal"><span class="pre">increment</span></tt> values for sequences are
optional and will default to 1,1.</p>
</div>
<div class="section" id="insert-behavior">
<h3>INSERT behavior<a class="headerlink" href="#insert-behavior" title="Permalink to this headline">¶</a></h3>
<p>Handling of the <tt class="docutils literal"><span class="pre">IDENTITY</span></tt> column at INSERT time involves two key
techniques. The most common is being able to fetch the “last inserted value”
for a given <tt class="docutils literal"><span class="pre">IDENTITY</span></tt> column, a process which SQLAlchemy performs
implicitly in many cases, most importantly within the ORM.</p>
<p>The process for fetching this value has several variants:</p>
<ul>
<li><p class="first">In the vast majority of cases, RETURNING is used in conjunction with INSERT
statements on SQL Server in order to get newly generated primary key values:</p>
<div class="highlight-sql"><div class="highlight"><pre><span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">t</span> <span class="p">(</span><span class="n">x</span><span class="p">)</span> <span class="k">OUTPUT</span> <span class="n">inserted</span><span class="p">.</span><span class="n">id</span> <span class="k">VALUES</span> <span class="p">(</span><span class="o">?</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first">When RETURNING is not available or has been disabled via
<tt class="docutils literal"><span class="pre">implicit_returning=False</span></tt>, either the <tt class="docutils literal"><span class="pre">scope_identity()</span></tt> function or
the <tt class="docutils literal"><span class="pre">@@identity</span></tt> variable is used; behavior varies by backend:</p>
<ul>
<li><p class="first">when using PyODBC, the phrase <tt class="docutils literal"><span class="pre">;</span> <span class="pre">select</span> <span class="pre">scope_identity()</span></tt> will be
appended to the end of the INSERT statement; a second result set will be
fetched in order to receive the value. Given a table as:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">t</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'t'</span><span class="p">,</span> <span class="n">m</span><span class="p">,</span> <span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'x'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">),</span>
<span class="n">implicit_returning</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span></pre></div>
</div>
<p>an INSERT will look like:</p>
<div class="highlight-sql"><div class="highlight"><pre><span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">t</span> <span class="p">(</span><span class="n">x</span><span class="p">)</span> <span class="k">VALUES</span> <span class="p">(</span><span class="o">?</span><span class="p">);</span> <span class="k">select</span> <span class="n">scope_identity</span><span class="p">()</span></pre></div>
</div>
</li>
<li><p class="first">Other dialects such as pymssql will call upon
<tt class="docutils literal"><span class="pre">SELECT</span> <span class="pre">scope_identity()</span> <span class="pre">AS</span> <span class="pre">lastrowid</span></tt> subsequent to an INSERT
statement. If the flag <tt class="docutils literal"><span class="pre">use_scope_identity=False</span></tt> is passed to
<a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>, the statement <tt class="docutils literal"><span class="pre">SELECT</span> <span class="pre">@@identity</span> <span class="pre">AS</span> <span class="pre">lastrowid</span></tt>
is used instead.</p>
</li>
</ul>
</li>
</ul>
<p>A table that contains an <tt class="docutils literal"><span class="pre">IDENTITY</span></tt> column will prohibit an INSERT statement
that refers to the identity column explicitly. The SQLAlchemy dialect will
detect when an INSERT construct, created using a core <a class="reference internal" href="../core/dml.html#sqlalchemy.sql.expression.insert" title="sqlalchemy.sql.expression.insert"><tt class="xref py py-func docutils literal"><span class="pre">insert()</span></tt></a>
construct (not a plain string SQL), refers to the identity column, and
in this case will emit <tt class="docutils literal"><span class="pre">SET</span> <span class="pre">IDENTITY_INSERT</span> <span class="pre">ON</span></tt> prior to the insert
statement proceeding, and <tt class="docutils literal"><span class="pre">SET</span> <span class="pre">IDENTITY_INSERT</span> <span class="pre">OFF</span></tt> subsequent to the
execution. Given this example:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">m</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="n">t</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'t'</span><span class="p">,</span> <span class="n">m</span><span class="p">,</span> <span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'x'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">))</span>
<span class="n">m</span><span class="o">.</span><span class="n">create_all</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<span class="n">engine</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">t</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span> <span class="p">{</span><span class="s">'id'</span><span class="p">:</span> <span class="mi">1</span><span class="p">,</span> <span class="s">'x'</span><span class="p">:</span><span class="mi">1</span><span class="p">},</span> <span class="p">{</span><span class="s">'id'</span><span class="p">:</span><span class="mi">2</span><span class="p">,</span> <span class="s">'x'</span><span class="p">:</span><span class="mi">2</span><span class="p">})</span></pre></div>
</div>
<p>The above column will be created with IDENTITY, however the INSERT statement
we emit is specifying explicit values. In the echo output we can see
how SQLAlchemy handles this:</p>
<div class="highlight-sql"><div class="highlight"><pre><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">t</span> <span class="p">(</span>
<span class="n">id</span> <span class="nb">INTEGER</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="k">IDENTITY</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">),</span>
<span class="n">x</span> <span class="nb">INTEGER</span> <span class="k">NULL</span><span class="p">,</span>
<span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">id</span><span class="p">)</span>
<span class="p">)</span>
<span class="k">COMMIT</span>
<span class="k">SET</span> <span class="n">IDENTITY_INSERT</span> <span class="n">t</span> <span class="k">ON</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">t</span> <span class="p">(</span><span class="n">id</span><span class="p">,</span> <span class="n">x</span><span class="p">)</span> <span class="k">VALUES</span> <span class="p">(</span><span class="o">?</span><span class="p">,</span> <span class="o">?</span><span class="p">)</span>
<span class="p">((</span><span class="mi">1</span><span class="p">,</span> <span class="mi">1</span><span class="p">),</span> <span class="p">(</span><span class="mi">2</span><span class="p">,</span> <span class="mi">2</span><span class="p">))</span>
<span class="k">SET</span> <span class="n">IDENTITY_INSERT</span> <span class="n">t</span> <span class="k">OFF</span>
<span class="k">COMMIT</span></pre></div>
</div>
<p>This
is an auxilliary use case suitable for testing and bulk insert scenarios.</p>
</div>
</div>
<div class="section" id="collation-support">
<h2>Collation Support<a class="headerlink" href="#collation-support" title="Permalink to this headline">¶</a></h2>
<p>Character collations are supported by the base string types,
specified by the string argument “collation”:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">VARCHAR</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'login'</span><span class="p">,</span> <span class="n">VARCHAR</span><span class="p">(</span><span class="mi">32</span><span class="p">,</span> <span class="n">collation</span><span class="o">=</span><span class="s">'Latin1_General_CI_AS'</span><span class="p">))</span></pre></div>
</div>
<p>When such a column is associated with a <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a>, the
CREATE TABLE statement for this column will yield:</p>
<div class="highlight-python"><pre>login VARCHAR(32) COLLATE Latin1_General_CI_AS NULL</pre>
</div>
<div class="versionadded">
<p><span>New in version 0.8: </span>Character collations are now part of the base string
types.</p>
</div>
</div>
<div class="section" id="limit-offset-support">
<h2>LIMIT/OFFSET Support<a class="headerlink" href="#limit-offset-support" title="Permalink to this headline">¶</a></h2>
<p>MSSQL has no support for the LIMIT or OFFSET keysowrds. LIMIT is
supported directly through the <tt class="docutils literal"><span class="pre">TOP</span></tt> Transact SQL keyword:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">select</span><span class="o">.</span><span class="n">limit</span></pre></div>
</div>
<p>will yield:</p>
<div class="highlight-python"><pre>SELECT TOP n</pre>
</div>
<p>If using SQL Server 2005 or above, LIMIT with OFFSET
support is available through the <tt class="docutils literal"><span class="pre">ROW_NUMBER</span> <span class="pre">OVER</span></tt> construct.
For versions below 2005, LIMIT with OFFSET usage will fail.</p>
</div>
<div class="section" id="nullability">
<h2>Nullability<a class="headerlink" href="#nullability" title="Permalink to this headline">¶</a></h2>
<p>MSSQL has support for three levels of column nullability. The default
nullability allows nulls and is explicit in the CREATE TABLE
construct:</p>
<div class="highlight-python"><pre>name VARCHAR(20) NULL</pre>
</div>
<p>If <tt class="docutils literal"><span class="pre">nullable=None</span></tt> is specified then no specification is made. In
other words the database’s configured default is used. This will
render:</p>
<div class="highlight-python"><pre>name VARCHAR(20)</pre>
</div>
<p>If <tt class="docutils literal"><span class="pre">nullable</span></tt> is <tt class="docutils literal"><span class="pre">True</span></tt> or <tt class="docutils literal"><span class="pre">False</span></tt> then the column will be
<tt class="docutils literal"><span class="pre">NULL`</span> <span class="pre">or</span> <span class="pre">``NOT</span> <span class="pre">NULL</span></tt> respectively.</p>
</div>
<div class="section" id="date-time-handling">
<h2>Date / Time Handling<a class="headerlink" href="#date-time-handling" title="Permalink to this headline">¶</a></h2>
<p>DATE and TIME are supported. Bind parameters are converted
to datetime.datetime() objects as required by most MSSQL drivers,
and results are processed from strings if needed.
The DATE and TIME types are not available for MSSQL 2005 and
previous - if a server version below 2008 is detected, DDL
for these types will be issued as DATETIME.</p>
</div>
<div class="section" id="clustered-index-support">
<span id="mssql-indexes"></span><h2>Clustered Index Support<a class="headerlink" href="#clustered-index-support" title="Permalink to this headline">¶</a></h2>
<p>The MSSQL dialect supports clustered indexes (and primary keys) via the
<tt class="docutils literal"><span class="pre">mssql_clustered</span></tt> option. This option is available to <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal"><span class="pre">Index</span></tt></a>,
<a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.UniqueConstraint" title="sqlalchemy.schema.UniqueConstraint"><tt class="xref py py-class docutils literal"><span class="pre">UniqueConstraint</span></tt></a>. and <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.PrimaryKeyConstraint" title="sqlalchemy.schema.PrimaryKeyConstraint"><tt class="xref py py-class docutils literal"><span class="pre">PrimaryKeyConstraint</span></tt></a>.</p>
<p>To generate a clustered index:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Index</span><span class="p">(</span><span class="s">"my_index"</span><span class="p">,</span> <span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x</span><span class="p">,</span> <span class="n">mssql_clustered</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span></pre></div>
</div>
<p>which renders the index as <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">CLUSTERED</span> <span class="pre">INDEX</span> <span class="pre">my_index</span> <span class="pre">ON</span> <span class="pre">table</span> <span class="pre">(x)</span></tt>.</p>
<div class="versionadded">
<p><span>New in version 0.8.</span></p>
</div>
<p>To generate a clustered primary key use:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Table</span><span class="p">(</span><span class="s">'my_table'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'x'</span><span class="p">,</span> <span class="o">...</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'y'</span><span class="p">,</span> <span class="o">...</span><span class="p">),</span>
<span class="n">PrimaryKeyConstraint</span><span class="p">(</span><span class="s">"x"</span><span class="p">,</span> <span class="s">"y"</span><span class="p">,</span> <span class="n">mssql_clustered</span><span class="o">=</span><span class="bp">True</span><span class="p">))</span></pre></div>
</div>
<p>which will render the table, for example, as:</p>
<div class="highlight-python"><pre>CREATE TABLE my_table (x INTEGER NOT NULL, y INTEGER NOT NULL,
PRIMARY KEY CLUSTERED (x, y))</pre>
</div>
<p>Similarly, we can generate a clustered unique constraint using:</p>
<div class="highlight-python"><pre> Table('my_table', metadata,
Column('x', ...),
Column('y', ...),
PrimaryKeyConstraint("x"),
UniqueConstraint("y", mssql_clustered=True),
)
.. versionadded:: 0.9.2</pre>
</div>
</div>
<div class="section" id="mssql-specific-index-options">
<h2>MSSQL-Specific Index Options<a class="headerlink" href="#mssql-specific-index-options" title="Permalink to this headline">¶</a></h2>
<p>In addition to clustering, the MSSQL dialect supports other special options
for <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal"><span class="pre">Index</span></tt></a>.</p>
<div class="section" id="include">
<h3>INCLUDE<a class="headerlink" href="#include" title="Permalink to this headline">¶</a></h3>
<p>The <tt class="docutils literal"><span class="pre">mssql_include</span></tt> option renders INCLUDE(colname) for the given string
names:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Index</span><span class="p">(</span><span class="s">"my_index"</span><span class="p">,</span> <span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x</span><span class="p">,</span> <span class="n">mssql_include</span><span class="o">=</span><span class="p">[</span><span class="s">'y'</span><span class="p">])</span></pre></div>
</div>
<p>would render the index as <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">INDEX</span> <span class="pre">my_index</span> <span class="pre">ON</span> <span class="pre">table</span> <span class="pre">(x)</span> <span class="pre">INCLUDE</span> <span class="pre">(y)</span></tt></p>
<div class="versionadded">
<p><span>New in version 0.8.</span></p>
</div>
</div>
<div class="section" id="index-ordering">
<h3>Index ordering<a class="headerlink" href="#index-ordering" title="Permalink to this headline">¶</a></h3>
<p>Index ordering is available via functional expressions, such as:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Index</span><span class="p">(</span><span class="s">"my_index"</span><span class="p">,</span> <span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x</span><span class="o">.</span><span class="n">desc</span><span class="p">())</span></pre></div>
</div>
<p>would render the index as <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">INDEX</span> <span class="pre">my_index</span> <span class="pre">ON</span> <span class="pre">table</span> <span class="pre">(x</span> <span class="pre">DESC)</span></tt></p>
<div class="versionadded">
<p><span>New in version 0.8.</span></p>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="../core/constraints.html#schema-indexes-functional"><em>Functional Indexes</em></a></p>
</div>
</div>
</div>
<div class="section" id="compatibility-levels">
<h2>Compatibility Levels<a class="headerlink" href="#compatibility-levels" title="Permalink to this headline">¶</a></h2>
<p>MSSQL supports the notion of setting compatibility levels at the
database level. This allows, for instance, to run a database that
is compatible with SQL2000 while running on a SQL2005 database
server. <tt class="docutils literal"><span class="pre">server_version_info</span></tt> will always return the database
server version information (in this case SQL2005) and not the
compatibility level information. Because of this, if running under
a backwards compatibility mode SQAlchemy may attempt to use T-SQL
statements that are unable to be parsed by the database server.</p>
</div>
<div class="section" id="triggers">
<h2>Triggers<a class="headerlink" href="#triggers" title="Permalink to this headline">¶</a></h2>
<p>SQLAlchemy by default uses OUTPUT INSERTED to get at newly
generated primary key values via IDENTITY columns or other
server side defaults. MS-SQL does not
allow the usage of OUTPUT INSERTED on tables that have triggers.
To disable the usage of OUTPUT INSERTED on a per-table basis,
specify <tt class="docutils literal"><span class="pre">implicit_returning=False</span></tt> for each <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a>
which has triggers:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Table</span><span class="p">(</span><span class="s">'mytable'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="c"># ...,</span>
<span class="n">implicit_returning</span><span class="o">=</span><span class="bp">False</span>
<span class="p">)</span></pre></div>
</div>
<p>Declarative form:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">MyClass</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="c"># ...</span>
<span class="n">__table_args__</span> <span class="o">=</span> <span class="p">{</span><span class="s">'implicit_returning'</span><span class="p">:</span><span class="bp">False</span><span class="p">}</span></pre></div>
</div>
<p>This option can also be specified engine-wide using the
<tt class="docutils literal"><span class="pre">implicit_returning=False</span></tt> argument on <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>.</p>
</div>
<div class="section" id="enabling-snapshot-isolation">
<h2>Enabling Snapshot Isolation<a class="headerlink" href="#enabling-snapshot-isolation" title="Permalink to this headline">¶</a></h2>
<p>Not necessarily specific to SQLAlchemy, SQL Server has a default transaction
isolation mode that locks entire tables, and causes even mildly concurrent
applications to have long held locks and frequent deadlocks.
Enabling snapshot isolation for the database as a whole is recommended
for modern levels of concurrency support. This is accomplished via the
following ALTER DATABASE commands executed at the SQL prompt:</p>
<div class="highlight-python"><pre>ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON</pre>
</div>
<p>Background on SQL Server snapshot isolation is available at
<a class="reference external" href="http://msdn.microsoft.com/en-us/library/ms175095.aspx">http://msdn.microsoft.com/en-us/library/ms175095.aspx</a>.</p>
</div>
<div class="section" id="known-issues">
<h2>Known Issues<a class="headerlink" href="#known-issues" title="Permalink to this headline">¶</a></h2>
<ul class="simple">
<li>No support for more than one <tt class="docutils literal"><span class="pre">IDENTITY</span></tt> column per table</li>
<li>reflection of indexes does not work with versions older than
SQL Server 2005</li>
</ul>
</div>
<div class="section" id="sql-server-data-types">
<h2>SQL Server Data Types<a class="headerlink" href="#sql-server-data-types" title="Permalink to this headline">¶</a></h2>
<p>As with all SQLAlchemy dialects, all UPPERCASE types that are known to be
valid with SQL server are importable from the top level dialect, whether
they originate from <a class="reference internal" href="../core/types.html#module-sqlalchemy.types" title="sqlalchemy.types"><tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.types</span></tt></a> or from the local dialect:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.mssql</span> <span class="kn">import</span> \
<span class="n">BIGINT</span><span class="p">,</span> <span class="n">BINARY</span><span class="p">,</span> <span class="n">BIT</span><span class="p">,</span> <span class="n">CHAR</span><span class="p">,</span> <span class="n">DATE</span><span class="p">,</span> <span class="n">DATETIME</span><span class="p">,</span> <span class="n">DATETIME2</span><span class="p">,</span> \
<span class="n">DATETIMEOFFSET</span><span class="p">,</span> <span class="n">DECIMAL</span><span class="p">,</span> <span class="n">FLOAT</span><span class="p">,</span> <span class="n">IMAGE</span><span class="p">,</span> <span class="n">INTEGER</span><span class="p">,</span> <span class="n">MONEY</span><span class="p">,</span> \
<span class="n">NCHAR</span><span class="p">,</span> <span class="n">NTEXT</span><span class="p">,</span> <span class="n">NUMERIC</span><span class="p">,</span> <span class="n">NVARCHAR</span><span class="p">,</span> <span class="n">REAL</span><span class="p">,</span> <span class="n">SMALLDATETIME</span><span class="p">,</span> \
<span class="n">SMALLINT</span><span class="p">,</span> <span class="n">SMALLMONEY</span><span class="p">,</span> <span class="n">SQL_VARIANT</span><span class="p">,</span> <span class="n">TEXT</span><span class="p">,</span> <span class="n">TIME</span><span class="p">,</span> \
<span class="n">TIMESTAMP</span><span class="p">,</span> <span class="n">TINYINT</span><span class="p">,</span> <span class="n">UNIQUEIDENTIFIER</span><span class="p">,</span> <span class="n">VARBINARY</span><span class="p">,</span> <span class="n">VARCHAR</span></pre></div>
</div>
<p>Types which are specific to SQL Server, or have SQL Server-specific
construction arguments, are as follows:</p>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.BIT">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.mssql.</tt><tt class="descname">BIT</tt><a class="headerlink" href="#sqlalchemy.dialects.mssql.BIT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.mssql.BIT.__init__">
<tt class="descname">__init__</tt><a class="headerlink" href="#sqlalchemy.dialects.mssql.BIT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>x.__init__(...) initializes x; see help(type(x)) for signature</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.CHAR">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.mssql.</tt><tt class="descname">CHAR</tt><big>(</big><em>length=None</em>, <em>collation=None</em>, <em>convert_unicode=False</em>, <em>unicode_error=None</em>, <em>_warn_on_bytestring=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.CHAR" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.String</span></tt></a></p>
<p>The SQL CHAR type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.CHAR.__init__">
<tt class="descname">__init__</tt><big>(</big><em>length=None</em>, <em>collation=None</em>, <em>convert_unicode=False</em>, <em>unicode_error=None</em>, <em>_warn_on_bytestring=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.CHAR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Create a string-holding type.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.mssql.CHAR.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.CHAR.params.length">¶</a> – optional, a length for the column for use in
DDL and CAST expressions. May be safely omitted if no <tt class="docutils literal"><span class="pre">CREATE</span>
<span class="pre">TABLE</span></tt> will be issued. Certain databases may require a
<tt class="docutils literal"><span class="pre">length</span></tt> for use in DDL, and will raise an exception when
the <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TABLE</span></tt> DDL is issued if a <tt class="docutils literal"><span class="pre">VARCHAR</span></tt>
with no length is included. Whether the value is
interpreted as bytes or characters is database specific.</li>
<li><span class="target" id="sqlalchemy.dialects.mssql.CHAR.params.collation"></span><strong>collation</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.CHAR.params.collation">¶</a> – <p>Optional, a column-level collation for
use in DDL and CAST expressions. Renders using the
COLLATE keyword supported by SQLite, MySQL, and Postgresql.
E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">cast</span><span class="p">,</span> <span class="n">select</span><span class="p">,</span> <span class="n">String</span>
<span class="gp">>>> </span><span class="k">print</span> <span class="n">select</span><span class="p">([</span><span class="n">cast</span><span class="p">(</span><span class="s">'some string'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="n">collation</span><span class="o">=</span><span class="s">'utf8'</span><span class="p">))])</span>
<span class="go">SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1</span></pre></div>
</div>
<div class="versionadded">
<p><span>New in version 0.8: </span>Added support for COLLATE to all
string types.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.dialects.mssql.CHAR.params.convert_unicode"></span><strong>convert_unicode</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.CHAR.params.convert_unicode">¶</a> – <p>When set to <tt class="docutils literal"><span class="pre">True</span></tt>, the
<a class="reference internal" href="../core/types.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><tt class="xref py py-class docutils literal"><span class="pre">String</span></tt></a> type will assume that
input is to be passed as Python <tt class="docutils literal"><span class="pre">unicode</span></tt> objects,
and results returned as Python <tt class="docutils literal"><span class="pre">unicode</span></tt> objects.
If the DBAPI in use does not support Python unicode
(which is fewer and fewer these days), SQLAlchemy
will encode/decode the value, using the
value of the <tt class="docutils literal"><span class="pre">encoding</span></tt> parameter passed to
<a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> as the encoding.</p>
<p>When using a DBAPI that natively supports Python
unicode objects, this flag generally does not
need to be set. For columns that are explicitly
intended to store non-ASCII data, the <a class="reference internal" href="../core/types.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><tt class="xref py py-class docutils literal"><span class="pre">Unicode</span></tt></a>
or <a class="reference internal" href="../core/types.html#sqlalchemy.types.UnicodeText" title="sqlalchemy.types.UnicodeText"><tt class="xref py py-class docutils literal"><span class="pre">UnicodeText</span></tt></a>
types should be used regardless, which feature
the same behavior of <tt class="docutils literal"><span class="pre">convert_unicode</span></tt> but
also indicate an underlying column type that
directly supports unicode, such as <tt class="docutils literal"><span class="pre">NVARCHAR</span></tt>.</p>
<p>For the extremely rare case that Python <tt class="docutils literal"><span class="pre">unicode</span></tt>
is to be encoded/decoded by SQLAlchemy on a backend
that does natively support Python <tt class="docutils literal"><span class="pre">unicode</span></tt>,
the value <tt class="docutils literal"><span class="pre">force</span></tt> can be passed here which will
cause SQLAlchemy’s encode/decode services to be
used unconditionally.</p>
</li>
<li><span class="target" id="sqlalchemy.dialects.mssql.CHAR.params.unicode_error"></span><strong>unicode_error</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.CHAR.params.unicode_error">¶</a> – Optional, a method to use to handle Unicode
conversion errors. Behaves like the <tt class="docutils literal"><span class="pre">errors</span></tt> keyword argument to
the standard library’s <tt class="docutils literal"><span class="pre">string.decode()</span></tt> functions. This flag
requires that <tt class="docutils literal"><span class="pre">convert_unicode</span></tt> is set to <tt class="docutils literal"><span class="pre">force</span></tt> - otherwise,
SQLAlchemy is not guaranteed to handle the task of unicode
conversion. Note that this flag adds significant performance
overhead to row-fetching operations for backends that already
return unicode objects natively (which most DBAPIs do). This
flag should only be used as a last resort for reading
strings from a column with varied or corrupted encodings.</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.DATETIME2">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.mssql.</tt><tt class="descname">DATETIME2</tt><big>(</big><em>precision=None</em>, <em>**kw</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.DATETIME2" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.mssql.base._DateTimeBase</span></tt>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.DateTime" title="sqlalchemy.types.DateTime"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.DateTime</span></tt></a></p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.DATETIMEOFFSET">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.mssql.</tt><tt class="descname">DATETIMEOFFSET</tt><big>(</big><em>precision=None</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.DATETIMEOFFSET" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.IMAGE">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.mssql.</tt><tt class="descname">IMAGE</tt><big>(</big><em>length=None</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.IMAGE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.LargeBinary" title="sqlalchemy.types.LargeBinary"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.LargeBinary</span></tt></a></p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.IMAGE.__init__">
<tt class="descname">__init__</tt><big>(</big><em>length=None</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.IMAGE.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a LargeBinary type.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><span class="target" id="sqlalchemy.dialects.mssql.IMAGE.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.IMAGE.params.length">¶</a> – optional, a length for the column for use in
DDL statements, for those BLOB types that accept a length
(i.e. MySQL). It does <em>not</em> produce a small BINARY/VARBINARY
type - use the BINARY/VARBINARY types specifically for those.
May be safely omitted if no <tt class="docutils literal"><span class="pre">CREATE</span>
<span class="pre">TABLE</span></tt> will be issued. Certain databases may require a
<em>length</em> for use in DDL, and will raise an exception when
the <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TABLE</span></tt> DDL is issued.</td>
</tr>
</tbody>
</table>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.MONEY">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.mssql.</tt><tt class="descname">MONEY</tt><a class="headerlink" href="#sqlalchemy.dialects.mssql.MONEY" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.mssql.MONEY.__init__">
<tt class="descname">__init__</tt><a class="headerlink" href="#sqlalchemy.dialects.mssql.MONEY.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>x.__init__(...) initializes x; see help(type(x)) for signature</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.NCHAR">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.mssql.</tt><tt class="descname">NCHAR</tt><big>(</big><em>length=None</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.NCHAR" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Unicode</span></tt></a></p>
<p>The SQL NCHAR type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.NCHAR.__init__">
<tt class="descname">__init__</tt><big>(</big><em>length=None</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.NCHAR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Create a <a class="reference internal" href="../core/types.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><tt class="xref py py-class docutils literal"><span class="pre">Unicode</span></tt></a> object.</p>
<p>Parameters are the same as that of <a class="reference internal" href="../core/types.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><tt class="xref py py-class docutils literal"><span class="pre">String</span></tt></a>,
with the exception that <tt class="docutils literal"><span class="pre">convert_unicode</span></tt>
defaults to <tt class="docutils literal"><span class="pre">True</span></tt>.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.NTEXT">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.mssql.</tt><tt class="descname">NTEXT</tt><big>(</big><em>length=None</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.NTEXT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.UnicodeText" title="sqlalchemy.types.UnicodeText"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.UnicodeText</span></tt></a></p>
<p>MSSQL NTEXT type, for variable-length unicode text up to 2^30
characters.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.NTEXT.__init__">
<tt class="descname">__init__</tt><big>(</big><em>length=None</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.NTEXT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Create a Unicode-converting Text type.</p>
<p>Parameters are the same as that of <a class="reference internal" href="../core/types.html#sqlalchemy.types.Text" title="sqlalchemy.types.Text"><tt class="xref py py-class docutils literal"><span class="pre">Text</span></tt></a>,
with the exception that <tt class="docutils literal"><span class="pre">convert_unicode</span></tt>
defaults to <tt class="docutils literal"><span class="pre">True</span></tt>.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.NVARCHAR">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.mssql.</tt><tt class="descname">NVARCHAR</tt><big>(</big><em>length=None</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.NVARCHAR" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Unicode</span></tt></a></p>
<p>The SQL NVARCHAR type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.NVARCHAR.__init__">
<tt class="descname">__init__</tt><big>(</big><em>length=None</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.NVARCHAR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Create a <a class="reference internal" href="../core/types.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><tt class="xref py py-class docutils literal"><span class="pre">Unicode</span></tt></a> object.</p>
<p>Parameters are the same as that of <a class="reference internal" href="../core/types.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><tt class="xref py py-class docutils literal"><span class="pre">String</span></tt></a>,
with the exception that <tt class="docutils literal"><span class="pre">convert_unicode</span></tt>
defaults to <tt class="docutils literal"><span class="pre">True</span></tt>.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.REAL">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.mssql.</tt><tt class="descname">REAL</tt><big>(</big><em>**kw</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.REAL" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.REAL" title="sqlalchemy.types.REAL"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.REAL</span></tt></a></p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.SMALLDATETIME">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.mssql.</tt><tt class="descname">SMALLDATETIME</tt><big>(</big><em>timezone=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.SMALLDATETIME" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.mssql.base._DateTimeBase</span></tt>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.DateTime" title="sqlalchemy.types.DateTime"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.DateTime</span></tt></a></p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.SMALLDATETIME.__init__">
<tt class="descname">__init__</tt><big>(</big><em>timezone=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.SMALLDATETIME.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a new <a class="reference internal" href="../core/types.html#sqlalchemy.types.DateTime" title="sqlalchemy.types.DateTime"><tt class="xref py py-class docutils literal"><span class="pre">DateTime</span></tt></a>.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><span class="target" id="sqlalchemy.dialects.mssql.SMALLDATETIME.params.timezone"></span><strong>timezone</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.SMALLDATETIME.params.timezone">¶</a> – boolean. If True, and supported by the
backend, will produce ‘TIMESTAMP WITH TIMEZONE’. For backends
that don’t support timezone aware timestamps, has no
effect.</td>
</tr>
</tbody>
</table>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.SMALLMONEY">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.mssql.</tt><tt class="descname">SMALLMONEY</tt><a class="headerlink" href="#sqlalchemy.dialects.mssql.SMALLMONEY" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.mssql.SMALLMONEY.__init__">
<tt class="descname">__init__</tt><a class="headerlink" href="#sqlalchemy.dialects.mssql.SMALLMONEY.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>x.__init__(...) initializes x; see help(type(x)) for signature</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.SQL_VARIANT">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.mssql.</tt><tt class="descname">SQL_VARIANT</tt><a class="headerlink" href="#sqlalchemy.dialects.mssql.SQL_VARIANT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.mssql.SQL_VARIANT.__init__">
<tt class="descname">__init__</tt><a class="headerlink" href="#sqlalchemy.dialects.mssql.SQL_VARIANT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>x.__init__(...) initializes x; see help(type(x)) for signature</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.TEXT">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.mssql.</tt><tt class="descname">TEXT</tt><big>(</big><em>length=None</em>, <em>collation=None</em>, <em>convert_unicode=False</em>, <em>unicode_error=None</em>, <em>_warn_on_bytestring=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.TEXT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.Text" title="sqlalchemy.types.Text"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Text</span></tt></a></p>
<p>The SQL TEXT type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.TEXT.__init__">
<tt class="descname">__init__</tt><big>(</big><em>length=None</em>, <em>collation=None</em>, <em>convert_unicode=False</em>, <em>unicode_error=None</em>, <em>_warn_on_bytestring=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.TEXT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Create a string-holding type.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.mssql.TEXT.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.TEXT.params.length">¶</a> – optional, a length for the column for use in
DDL and CAST expressions. May be safely omitted if no <tt class="docutils literal"><span class="pre">CREATE</span>
<span class="pre">TABLE</span></tt> will be issued. Certain databases may require a
<tt class="docutils literal"><span class="pre">length</span></tt> for use in DDL, and will raise an exception when
the <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TABLE</span></tt> DDL is issued if a <tt class="docutils literal"><span class="pre">VARCHAR</span></tt>
with no length is included. Whether the value is
interpreted as bytes or characters is database specific.</li>
<li><span class="target" id="sqlalchemy.dialects.mssql.TEXT.params.collation"></span><strong>collation</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.TEXT.params.collation">¶</a> – <p>Optional, a column-level collation for
use in DDL and CAST expressions. Renders using the
COLLATE keyword supported by SQLite, MySQL, and Postgresql.
E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">cast</span><span class="p">,</span> <span class="n">select</span><span class="p">,</span> <span class="n">String</span>
<span class="gp">>>> </span><span class="k">print</span> <span class="n">select</span><span class="p">([</span><span class="n">cast</span><span class="p">(</span><span class="s">'some string'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="n">collation</span><span class="o">=</span><span class="s">'utf8'</span><span class="p">))])</span>
<span class="go">SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1</span></pre></div>
</div>
<div class="versionadded">
<p><span>New in version 0.8: </span>Added support for COLLATE to all
string types.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.dialects.mssql.TEXT.params.convert_unicode"></span><strong>convert_unicode</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.TEXT.params.convert_unicode">¶</a> – <p>When set to <tt class="docutils literal"><span class="pre">True</span></tt>, the
<a class="reference internal" href="../core/types.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><tt class="xref py py-class docutils literal"><span class="pre">String</span></tt></a> type will assume that
input is to be passed as Python <tt class="docutils literal"><span class="pre">unicode</span></tt> objects,
and results returned as Python <tt class="docutils literal"><span class="pre">unicode</span></tt> objects.
If the DBAPI in use does not support Python unicode
(which is fewer and fewer these days), SQLAlchemy
will encode/decode the value, using the
value of the <tt class="docutils literal"><span class="pre">encoding</span></tt> parameter passed to
<a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> as the encoding.</p>
<p>When using a DBAPI that natively supports Python
unicode objects, this flag generally does not
need to be set. For columns that are explicitly
intended to store non-ASCII data, the <a class="reference internal" href="../core/types.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><tt class="xref py py-class docutils literal"><span class="pre">Unicode</span></tt></a>
or <a class="reference internal" href="../core/types.html#sqlalchemy.types.UnicodeText" title="sqlalchemy.types.UnicodeText"><tt class="xref py py-class docutils literal"><span class="pre">UnicodeText</span></tt></a>
types should be used regardless, which feature
the same behavior of <tt class="docutils literal"><span class="pre">convert_unicode</span></tt> but
also indicate an underlying column type that
directly supports unicode, such as <tt class="docutils literal"><span class="pre">NVARCHAR</span></tt>.</p>
<p>For the extremely rare case that Python <tt class="docutils literal"><span class="pre">unicode</span></tt>
is to be encoded/decoded by SQLAlchemy on a backend
that does natively support Python <tt class="docutils literal"><span class="pre">unicode</span></tt>,
the value <tt class="docutils literal"><span class="pre">force</span></tt> can be passed here which will
cause SQLAlchemy’s encode/decode services to be
used unconditionally.</p>
</li>
<li><span class="target" id="sqlalchemy.dialects.mssql.TEXT.params.unicode_error"></span><strong>unicode_error</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.TEXT.params.unicode_error">¶</a> – Optional, a method to use to handle Unicode
conversion errors. Behaves like the <tt class="docutils literal"><span class="pre">errors</span></tt> keyword argument to
the standard library’s <tt class="docutils literal"><span class="pre">string.decode()</span></tt> functions. This flag
requires that <tt class="docutils literal"><span class="pre">convert_unicode</span></tt> is set to <tt class="docutils literal"><span class="pre">force</span></tt> - otherwise,
SQLAlchemy is not guaranteed to handle the task of unicode
conversion. Note that this flag adds significant performance
overhead to row-fetching operations for backends that already
return unicode objects natively (which most DBAPIs do). This
flag should only be used as a last resort for reading
strings from a column with varied or corrupted encodings.</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.TIME">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.mssql.</tt><tt class="descname">TIME</tt><big>(</big><em>precision=None</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.TIME" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TIME" title="sqlalchemy.types.TIME"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TIME</span></tt></a></p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.TINYINT">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.mssql.</tt><tt class="descname">TINYINT</tt><a class="headerlink" href="#sqlalchemy.dialects.mssql.TINYINT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.Integer" title="sqlalchemy.types.Integer"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Integer</span></tt></a></p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.mssql.TINYINT.__init__">
<tt class="descname">__init__</tt><a class="headerlink" href="#sqlalchemy.dialects.mssql.TINYINT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>x.__init__(...) initializes x; see help(type(x)) for signature</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.UNIQUEIDENTIFIER">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.mssql.</tt><tt class="descname">UNIQUEIDENTIFIER</tt><a class="headerlink" href="#sqlalchemy.dialects.mssql.UNIQUEIDENTIFIER" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.TypeEngine</span></tt></a></p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.mssql.UNIQUEIDENTIFIER.__init__">
<tt class="descname">__init__</tt><a class="headerlink" href="#sqlalchemy.dialects.mssql.UNIQUEIDENTIFIER.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>x.__init__(...) initializes x; see help(type(x)) for signature</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.VARCHAR">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.mssql.</tt><tt class="descname">VARCHAR</tt><big>(</big><em>length=None</em>, <em>collation=None</em>, <em>convert_unicode=False</em>, <em>unicode_error=None</em>, <em>_warn_on_bytestring=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.VARCHAR" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/types.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.String</span></tt></a></p>
<p>The SQL VARCHAR type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.VARCHAR.__init__">
<tt class="descname">__init__</tt><big>(</big><em>length=None</em>, <em>collation=None</em>, <em>convert_unicode=False</em>, <em>unicode_error=None</em>, <em>_warn_on_bytestring=False</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.mssql.VARCHAR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Create a string-holding type.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.mssql.VARCHAR.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.VARCHAR.params.length">¶</a> – optional, a length for the column for use in
DDL and CAST expressions. May be safely omitted if no <tt class="docutils literal"><span class="pre">CREATE</span>
<span class="pre">TABLE</span></tt> will be issued. Certain databases may require a
<tt class="docutils literal"><span class="pre">length</span></tt> for use in DDL, and will raise an exception when
the <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TABLE</span></tt> DDL is issued if a <tt class="docutils literal"><span class="pre">VARCHAR</span></tt>
with no length is included. Whether the value is
interpreted as bytes or characters is database specific.</li>
<li><span class="target" id="sqlalchemy.dialects.mssql.VARCHAR.params.collation"></span><strong>collation</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.VARCHAR.params.collation">¶</a> – <p>Optional, a column-level collation for
use in DDL and CAST expressions. Renders using the
COLLATE keyword supported by SQLite, MySQL, and Postgresql.
E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">cast</span><span class="p">,</span> <span class="n">select</span><span class="p">,</span> <span class="n">String</span>
<span class="gp">>>> </span><span class="k">print</span> <span class="n">select</span><span class="p">([</span><span class="n">cast</span><span class="p">(</span><span class="s">'some string'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="n">collation</span><span class="o">=</span><span class="s">'utf8'</span><span class="p">))])</span>
<span class="go">SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1</span></pre></div>
</div>
<div class="versionadded">
<p><span>New in version 0.8: </span>Added support for COLLATE to all
string types.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.dialects.mssql.VARCHAR.params.convert_unicode"></span><strong>convert_unicode</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.VARCHAR.params.convert_unicode">¶</a> – <p>When set to <tt class="docutils literal"><span class="pre">True</span></tt>, the
<a class="reference internal" href="../core/types.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><tt class="xref py py-class docutils literal"><span class="pre">String</span></tt></a> type will assume that
input is to be passed as Python <tt class="docutils literal"><span class="pre">unicode</span></tt> objects,
and results returned as Python <tt class="docutils literal"><span class="pre">unicode</span></tt> objects.
If the DBAPI in use does not support Python unicode
(which is fewer and fewer these days), SQLAlchemy
will encode/decode the value, using the
value of the <tt class="docutils literal"><span class="pre">encoding</span></tt> parameter passed to
<a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> as the encoding.</p>
<p>When using a DBAPI that natively supports Python
unicode objects, this flag generally does not
need to be set. For columns that are explicitly
intended to store non-ASCII data, the <a class="reference internal" href="../core/types.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><tt class="xref py py-class docutils literal"><span class="pre">Unicode</span></tt></a>
or <a class="reference internal" href="../core/types.html#sqlalchemy.types.UnicodeText" title="sqlalchemy.types.UnicodeText"><tt class="xref py py-class docutils literal"><span class="pre">UnicodeText</span></tt></a>
types should be used regardless, which feature
the same behavior of <tt class="docutils literal"><span class="pre">convert_unicode</span></tt> but
also indicate an underlying column type that
directly supports unicode, such as <tt class="docutils literal"><span class="pre">NVARCHAR</span></tt>.</p>
<p>For the extremely rare case that Python <tt class="docutils literal"><span class="pre">unicode</span></tt>
is to be encoded/decoded by SQLAlchemy on a backend
that does natively support Python <tt class="docutils literal"><span class="pre">unicode</span></tt>,
the value <tt class="docutils literal"><span class="pre">force</span></tt> can be passed here which will
cause SQLAlchemy’s encode/decode services to be
used unconditionally.</p>
</li>
<li><span class="target" id="sqlalchemy.dialects.mssql.VARCHAR.params.unicode_error"></span><strong>unicode_error</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.VARCHAR.params.unicode_error">¶</a> – Optional, a method to use to handle Unicode
conversion errors. Behaves like the <tt class="docutils literal"><span class="pre">errors</span></tt> keyword argument to
the standard library’s <tt class="docutils literal"><span class="pre">string.decode()</span></tt> functions. This flag
requires that <tt class="docutils literal"><span class="pre">convert_unicode</span></tt> is set to <tt class="docutils literal"><span class="pre">force</span></tt> - otherwise,
SQLAlchemy is not guaranteed to handle the task of unicode
conversion. Note that this flag adds significant performance
overhead to row-fetching operations for backends that already
return unicode objects natively (which most DBAPIs do). This
flag should only be used as a last resort for reading
strings from a column with varied or corrupted encodings.</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
</dd></dl>
</div>
<div class="section" id="module-sqlalchemy.dialects.mssql.pyodbc">
<span id="pyodbc"></span><h2>PyODBC<a class="headerlink" href="#module-sqlalchemy.dialects.mssql.pyodbc" title="Permalink to this headline">¶</a></h2>
<p>Support for the Microsoft SQL Server database via the PyODBC driver.</p>
<div class="section" id="dialect-mssql-pyodbc-url">
<h3>DBAPI<a class="headerlink" href="#dialect-mssql-pyodbc-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for PyODBC is available at:
<a class="reference external" href="http://pypi.python.org/pypi/pyodbc/">http://pypi.python.org/pypi/pyodbc/</a></p>
</div>
<div class="section" id="dialect-mssql-pyodbc-connect">
<h3>Connecting<a class="headerlink" href="#dialect-mssql-pyodbc-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-python"><pre>mssql+pyodbc://<username>:<password>@<dsnname></pre>
</div>
</p>
</div>
<div class="section" id="additional-connection-examples">
<h3>Additional Connection Examples<a class="headerlink" href="#additional-connection-examples" title="Permalink to this headline">¶</a></h3>
<p>Examples of pyodbc connection string URLs:</p>
<ul>
<li><p class="first"><tt class="docutils literal"><span class="pre">mssql+pyodbc://mydsn</span></tt> - connects using the specified DSN named <tt class="docutils literal"><span class="pre">mydsn</span></tt>.
The connection string that is created will appear like:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">dsn</span><span class="o">=</span><span class="n">mydsn</span><span class="p">;</span><span class="n">Trusted_Connection</span><span class="o">=</span><span class="n">Yes</span></pre></div>
</div>
</li>
<li><p class="first"><tt class="docutils literal"><span class="pre">mssql+pyodbc://user:pass@mydsn</span></tt> - connects using the DSN named
<tt class="docutils literal"><span class="pre">mydsn</span></tt> passing in the <tt class="docutils literal"><span class="pre">UID</span></tt> and <tt class="docutils literal"><span class="pre">PWD</span></tt> information. The
connection string that is created will appear like:</p>
<div class="highlight-python"><pre>dsn=mydsn;UID=user;PWD=pass</pre>
</div>
</li>
<li><p class="first"><tt class="docutils literal"><span class="pre">mssql+pyodbc://user:pass@mydsn/?LANGUAGE=us_english</span></tt> - connects
using the DSN named <tt class="docutils literal"><span class="pre">mydsn</span></tt> passing in the <tt class="docutils literal"><span class="pre">UID</span></tt> and <tt class="docutils literal"><span class="pre">PWD</span></tt>
information, plus the additional connection configuration option
<tt class="docutils literal"><span class="pre">LANGUAGE</span></tt>. The connection string that is created will appear
like:</p>
<div class="highlight-python"><pre>dsn=mydsn;UID=user;PWD=pass;LANGUAGE=us_english</pre>
</div>
</li>
<li><p class="first"><tt class="docutils literal"><span class="pre">mssql+pyodbc://user:pass@host/db</span></tt> - connects using a connection
that would appear like:</p>
<div class="highlight-python"><pre>DRIVER={SQL Server};Server=host;Database=db;UID=user;PWD=pass</pre>
</div>
</li>
<li><p class="first"><tt class="docutils literal"><span class="pre">mssql+pyodbc://user:pass@host:123/db</span></tt> - connects using a connection
string which includes the port
information using the comma syntax. This will create the following
connection string:</p>
<div class="highlight-python"><pre>DRIVER={SQL Server};Server=host,123;Database=db;UID=user;PWD=pass</pre>
</div>
</li>
<li><p class="first"><tt class="docutils literal"><span class="pre">mssql+pyodbc://user:pass@host/db?port=123</span></tt> - connects using a connection
string that includes the port
information as a separate <tt class="docutils literal"><span class="pre">port</span></tt> keyword. This will create the
following connection string:</p>
<div class="highlight-python"><pre>DRIVER={SQL Server};Server=host;Database=db;UID=user;PWD=pass;port=123</pre>
</div>
</li>
<li><p class="first"><tt class="docutils literal"><span class="pre">mssql+pyodbc://user:pass@host/db?driver=MyDriver</span></tt> - connects using a
connection string that includes a custom ODBC driver name. This will create
the following connection string:</p>
<div class="highlight-python"><pre>DRIVER={MyDriver};Server=host;Database=db;UID=user;PWD=pass</pre>
</div>
</li>
</ul>
<p>If you require a connection string that is outside the options
presented above, use the <tt class="docutils literal"><span class="pre">odbc_connect</span></tt> keyword to pass in a
urlencoded connection string. What gets passed in will be urldecoded
and passed directly.</p>
<p>For example:</p>
<div class="highlight-python"><pre>mssql+pyodbc:///?odbc_connect=dsn%3Dmydsn%3BDatabase%3Ddb</pre>
</div>
<p>would create the following connection string:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">dsn</span><span class="o">=</span><span class="n">mydsn</span><span class="p">;</span><span class="n">Database</span><span class="o">=</span><span class="n">db</span></pre></div>
</div>
<p>Encoding your connection string can be easily accomplished through
the python shell. For example:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">import</span> <span class="nn">urllib</span>
<span class="gp">>>> </span><span class="n">urllib</span><span class="o">.</span><span class="n">quote_plus</span><span class="p">(</span><span class="s">'dsn=mydsn;Database=db'</span><span class="p">)</span>
<span class="go">'dsn%3Dmydsn%3BDatabase%3Ddb'</span></pre></div>
</div>
</div>
<div class="section" id="unicode-binds">
<h3>Unicode Binds<a class="headerlink" href="#unicode-binds" title="Permalink to this headline">¶</a></h3>
<p>The current state of PyODBC on a unix backend with FreeTDS and/or
EasySoft is poor regarding unicode; different OS platforms and versions of
UnixODBC versus IODBC versus FreeTDS/EasySoft versus PyODBC itself
dramatically alter how strings are received. The PyODBC dialect attempts to
use all the information it knows to determine whether or not a Python unicode
literal can be passed directly to the PyODBC driver or not; while SQLAlchemy
can encode these to bytestrings first, some users have reported that PyODBC
mis-handles bytestrings for certain encodings and requires a Python unicode
object, while the author has observed widespread cases where a Python unicode
is completely misinterpreted by PyODBC, particularly when dealing with
the information schema tables used in table reflection, and the value
must first be encoded to a bytestring.</p>
<p>It is for this reason that whether or not unicode literals for bound
parameters be sent to PyODBC can be controlled using the
<tt class="docutils literal"><span class="pre">supports_unicode_binds</span></tt> parameter to <tt class="docutils literal"><span class="pre">create_engine()</span></tt>. When
left at its default of <tt class="docutils literal"><span class="pre">None</span></tt>, the PyODBC dialect will use its
best guess as to whether or not the driver deals with unicode literals
well. When <tt class="docutils literal"><span class="pre">False</span></tt>, unicode literals will be encoded first, and when
<tt class="docutils literal"><span class="pre">True</span></tt> unicode literals will be passed straight through. This is an interim
flag that hopefully should not be needed when the unicode situation stabilizes
for unix + PyODBC.</p>
<div class="versionadded">
<p><span>New in version 0.7.7: </span><tt class="docutils literal"><span class="pre">supports_unicode_binds</span></tt> parameter to <tt class="docutils literal"><span class="pre">create_engine()</span></tt>.</p>
</div>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.mssql.mxodbc">
<span id="mxodbc"></span><h2>mxODBC<a class="headerlink" href="#module-sqlalchemy.dialects.mssql.mxodbc" title="Permalink to this headline">¶</a></h2>
<p>Support for the Microsoft SQL Server database via the mxODBC driver.</p>
<div class="section" id="dialect-mssql-mxodbc-url">
<h3>DBAPI<a class="headerlink" href="#dialect-mssql-mxodbc-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for mxODBC is available at:
<a class="reference external" href="http://www.egenix.com/">http://www.egenix.com/</a></p>
</div>
<div class="section" id="dialect-mssql-mxodbc-connect">
<h3>Connecting<a class="headerlink" href="#dialect-mssql-mxodbc-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-python"><pre>mssql+mxodbc://<username>:<password>@<dsnname></pre>
</div>
</p>
</div>
<div class="section" id="execution-modes">
<h3>Execution Modes<a class="headerlink" href="#execution-modes" title="Permalink to this headline">¶</a></h3>
<p>mxODBC features two styles of statement execution, using the
<tt class="docutils literal"><span class="pre">cursor.execute()</span></tt> and <tt class="docutils literal"><span class="pre">cursor.executedirect()</span></tt> methods (the second being
an extension to the DBAPI specification). The former makes use of a particular
API call specific to the SQL Server Native Client ODBC driver known
SQLDescribeParam, while the latter does not.</p>
<p>mxODBC apparently only makes repeated use of a single prepared statement
when SQLDescribeParam is used. The advantage to prepared statement reuse is
one of performance. The disadvantage is that SQLDescribeParam has a limited
set of scenarios in which bind parameters are understood, including that they
cannot be placed within the argument lists of function calls, anywhere outside
the FROM, or even within subqueries within the FROM clause - making the usage
of bind parameters within SELECT statements impossible for all but the most
simplistic statements.</p>
<p>For this reason, the mxODBC dialect uses the “native” mode by default only for
INSERT, UPDATE, and DELETE statements, and uses the escaped string mode for
all other statements.</p>
<p>This behavior can be controlled via
<a class="reference internal" href="../core/selectable.html#sqlalchemy.sql.expression.Executable.execution_options" title="sqlalchemy.sql.expression.Executable.execution_options"><tt class="xref py py-meth docutils literal"><span class="pre">execution_options()</span></tt></a> using the
<tt class="docutils literal"><span class="pre">native_odbc_execute</span></tt> flag with a value of <tt class="docutils literal"><span class="pre">True</span></tt> or <tt class="docutils literal"><span class="pre">False</span></tt>, where a
value of <tt class="docutils literal"><span class="pre">True</span></tt> will unconditionally use native bind parameters and a value
of <tt class="docutils literal"><span class="pre">False</span></tt> will unconditionally use string-escaped parameters.</p>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.mssql.pymssql">
<span id="pymssql"></span><h2>pymssql<a class="headerlink" href="#module-sqlalchemy.dialects.mssql.pymssql" title="Permalink to this headline">¶</a></h2>
<p>Support for the Microsoft SQL Server database via the pymssql driver.</p>
<div class="section" id="dialect-mssql-pymssql-url">
<h3>DBAPI<a class="headerlink" href="#dialect-mssql-pymssql-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for pymssql is available at:
<a class="reference external" href="http://pymssql.org/">http://pymssql.org/</a></p>
</div>
<div class="section" id="dialect-mssql-pymssql-connect">
<h3>Connecting<a class="headerlink" href="#dialect-mssql-pymssql-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-python"><pre>mssql+pymssql://<username>:<password>@<freetds_name>?charset=utf8</pre>
</div>
</p>
</div>
<p>pymssql is a Python module that provides a Python DBAPI interface around
<a class="reference external" href="http://www.freetds.org/">FreeTDS</a>. Compatible builds are available for
Linux, MacOSX and Windows platforms.</p>
</div>
<div class="section" id="module-sqlalchemy.dialects.mssql.zxjdbc">
<span id="zxjdbc"></span><h2>zxjdbc<a class="headerlink" href="#module-sqlalchemy.dialects.mssql.zxjdbc" title="Permalink to this headline">¶</a></h2>
<p>Support for the Microsoft SQL Server database via the zxJDBC for Jython driver.</p>
<div class="section" id="dialect-mssql-zxjdbc-url">
<h3>DBAPI<a class="headerlink" href="#dialect-mssql-zxjdbc-url" title="Permalink to this headline">¶</a></h3>
<p>Drivers for this database are available at:
<a class="reference external" href="http://jtds.sourceforge.net/">http://jtds.sourceforge.net/</a></p>
</div>
<div class="section" id="dialect-mssql-zxjdbc-connect">
<h3>Connecting<a class="headerlink" href="#dialect-mssql-zxjdbc-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-python"><pre>mssql+zxjdbc://user:pass@host:port/dbname[?key=value&key=value...]</pre>
</div>
</p>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.mssql.adodbapi">
<span id="adodbapi"></span><h2>AdoDBAPI<a class="headerlink" href="#module-sqlalchemy.dialects.mssql.adodbapi" title="Permalink to this headline">¶</a></h2>
<p>Support for the Microsoft SQL Server database via the adodbapi driver.</p>
<div class="section" id="dialect-mssql-adodbapi-url">
<h3>DBAPI<a class="headerlink" href="#dialect-mssql-adodbapi-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for adodbapi is available at:
<a class="reference external" href="http://adodbapi.sourceforge.net/">http://adodbapi.sourceforge.net/</a></p>
</div>
<div class="section" id="dialect-mssql-adodbapi-connect">
<h3>Connecting<a class="headerlink" href="#dialect-mssql-adodbapi-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-python"><pre>mssql+adodbapi://<username>:<password>@<dsnname></pre>
</div>
</p>
</div>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">The adodbapi dialect is not implemented SQLAlchemy versions 0.6 and
above at this time.</p>
</div>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links">
Previous:
<a href="firebird.html" title="previous chapter">Firebird</a>
Next:
<a href="mysql.html" title="next chapter">MySQL</a>
<div id="docs-copyright">
© <a href="../copyright.html">Copyright</a> 2007-2014, the SQLAlchemy authors and contributors.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.2b1.
</div>
</div>
</div>
</body>
</html>
|
