Welcome, guest | Sign In | My Account | Store | Cart
NOTE: Recipes have moved! Please visit GitHub.com/activestate/code for the current versions.

This DBI implements the Cursor and Connection objects. It is functional: you can create connections, cursors, do fetchone, fetchall, get rowcount, etc. It uses osql or sqlcmd instead of ODBC or ADO. There is a good sized section with examples to get you started.

Python, 239 lines
  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
#dblib.py                                                                                                                                          
#created by Jorge Besada
#Last updated: 5/24/2010 - suggestion by Kosta Welke implemented
                                                                                                                                                   
import os,sys                                                                                                                                      
                                                                                                                                                   
class Connection:                                                                                                                                  
    def __init__(self,sname,uname='',password='',db='', version=''):                                                                               
        self.version = version                                                                                                                     
        self.servername = sname                                                                                                                    
        self.username = uname                                                                                                                      
        self.password = password                                                                                                                   
        self.defdb = db                                                                                                                            
        self.constr = ''                                                                                                                           
        if db == '':                                                                                                                               
            self.defdb = 'master'                                                                                                                  
        self.connected = 0                                                                                                                         
        if self.version == None or self.version == "":                                                                                             
            print "Need to pass sql version argument"                                                                                              
            return self                                                                                                                            
        if self.version == "sql2000" or self.version == "sql7":                                                                                    
            execsql = "osql"                                                                                                                       
        if self.version == "sql2005" or self.version == "sql2008":
            execsql = "sqlcmd"                                                                                                                     
        if self.version == "sybase":                                                                                                               
            execsql = "isql"                                                                                                                       
            print "Sorry, Sybase has not been implemented yet!"                                                                                    
            return self                                                                                                                            
        if uname == '':                                                                                                                            
            self.constr = execsql + " -E -S" + self.servername + " -d" + self.defdb + " /w 8192 "                                                  
        else:                                                                                                                                      
            self.constr = execsql + " -U" + self.username + " -P" + self.password + " -S" + self.servername + " -d" + self.defdb + " /w 8192 "     
                                                                                                                                                   
        #test connection:                                                                                                                          
        s = "set nocount on select name from master..syslogins where name = 'sa'"                                                                  
        lst = os.popen(self.constr + ' -Q' + '"' + s + '"').readlines()                                                                            
                                                                                                                                                   
        try:                                                                                                                                       
            if lst[2].strip() == 'sa':                                                                                                             
                self.connected = 1                                                                                                                 
            else:                                                                                                                                  
                self.connected = 0                                                                                                                 
            c = Cursor()                                                                                                                           
            c.servername = sname                                                                                                                   
            c.username = uname                                                                                                                     
            c.password = password                                                                                                                  
            c.defdb = db                                                                                                                           
            c.constr = self.constr                                                                                                                 
            self.cursor = c                                                                                                                        
        except IndexError:                                                                                                                         
            print "Could not connect"                                                                                                              
                                                                                                                                                   
    def commit(self):                                                                                                                              
        "this is here for compatibility"                                                                                                           
        pass                                                                                                                                       
                                                                                                                                                   
    def close(self):                                                                                                                               
        self = None                                                                                                                                
        return self                                                                                                                                
                                                                                                                                                   
                                                                                                                                                   
class Cursor:                                                                                                                                      
    def __init__(self):                                                                                                                            
        self.defdb = ''                                                                                                                            
        self.servername = ''                                                                                                                       
        self.username = ''                                                                                                                         
        self.password = ''                                                                                                                         
        self.constr = ''                                                                                                                           
        self.rowcount = -1                                                                                                                         
        self.records = []                                                                                                                          
        self.rowid = 0                                                                                                                             
        self.sqlfile = "-Q"                                                                                                                        
        self.colseparator = chr(1) #default column separator                                                                                       
        #this is going to be a list of lists, each one with:                                                                                       
        #name, type_code, display_size, internal_size, precision, scale, null_ok                                                                   
        self.description = []                                                                                                                      
        self.fieldnames = []                                                                                                                       
        self.fieldvalues = []                                                                                                                      
        self.fieldvalue = []                                                                                                                       
        #one dictionary by column                                                                                                                  
        self.dictfield = {'name':'', 'type_code':0,'display_size':0,'internal_size':0,'precision':0, 'scale':0, 'null_ok':0}                       
        #list of lists                                                                                                                             
        self.dictfields = []                                                                                                                       
                                                                                                                                                   
    #this is for compatibility to allow both types of calls:                                                                                       
    #cursor = connection.cursor() or using cursor = connection.cursor                                                                              
    def __call__(self):                                                                                                                            
        c = Cursor()                                                                                                                               
        return c                                                                                                                                   
                                                                                                                                                   
    def execute(self, s):                                                                                                                          
        self.records = []                                                                                                                          
        lst = os.popen(self.constr + ' -s' + self.colseparator + " " + self.sqlfile + '"' + s + '"').readlines()                                   
        if len(lst) == 0:                                                                                                                          
            return self.rowcount                                                                                                                   
                                                                                                                                                   
        #If we get here we have results                                                                                                            
        #rowcount maybe in last line, in this form: (4 rows affected)                                                                              
        tmplastline = lst[-1]                                                                                                                      
        if tmplastline[0] == "(":  #there is a rowcount                                                                                            
            lastline = lst[-1]                                                                                                                     
            spacepos = lastline.index(" ")                                                                                                         
            cnt = lastline[1:spacepos]                                                                                                           
            self.rowcount = int(cnt)                                                                                                             
        else:                                                                                                                                      
            #last line has no recordcount, so reset it to 0                                                                                        
            self.records = lst[:]                                                                                                                  
            self.rowcount = 0                                                                                                                      
            return self.rowcount                                                                                                                   
                                                                                                                                                   
        #if we got here we may have a rowcount and the list with results                                                                           
        i = 0                                                                                                                                      
        #process metadata if we have it:                                                                                                           
        firstline = lst[0]                                                                                                                         
        lst1 = lst[0].split(self.colseparator)                                                                                                     
        self.fieldnames = []                                                                                                                       
        for x in lst1:                                                                                                                             
            x1 = x.strip()                                                                                                                         
            self.fieldnames.append(x1)  #add column name                                                                                           
        #need to make a list for each column name                                                                                                  
        self.description = []                                                                                                                      
        for x in self.fieldnames:                                                                                                                  
            l = []                                                                                                                                 
            l.append(x)                                                                                                                            
            for m in range(len(self.dictfield) - 1):                                                                                               
                l.append(0)                                                                                                                        
            l2 = tuple(l)                                                                                                                          
            self.description.append(l2)                                                                                                            
        self.description = tuple(self.description)                                                                                                 
                                                                                                                                                   
        #Data section: lst[0] is row with column names,skip                                                                                        
        #If the resulting string starts and ends with '-', discard                                                                                 
                                                                                                                                                   
        for x in lst[1:-1]:                                                                                                                        
            x0 = ''.join(x)                                                                                                                        
            x1 = x0.strip()                                                                                                                        
            #if x1 > '' and x1[0] > '-' and x1[-1] > '-':
            if x1 <> '' and x1[0] <> '-' and x1[-1] <> '-':
                self.records.append(x1)                                                                                                            
        #reset for each execution                                                                                                                  
        self.rowid = 0                                                                                                                             
        return self.rowcount                                                                                                                       
                                                                                                                                                   
    #returns one row of the result set, keeps track of the position                                                                                
    def fetchone(self):                                                                                                                            
        i = self.rowid                                                                                                                             
        j = i + 1                                                                                                                                  
        self.rowid = j                                                                                                                             
        try:                                                                                                                                       
            return tuple(self.records[i].split(self.colseparator))                                                                                 
        except IndexError:                                                                                                                         
            pass                                                                                                                                   
                                                                                                                                                   
    #returns whole recordset                                                                                                                       
    def fetchall(self):                                                                                                                            
        lst = []                                                                                                                                   
        try:                                                                                                                                       
            for x in range(self.rowid, self.rowcount):                                                                                             
                x1 = tuple(self.records[x].split(self.colseparator))                                                                               
                lst.append(x1)                                                                                                                     
        except IndexError:                                                                                                                         
            pass                                                                                                                                   
        return lst                                                                                                                                 
                                                                                                                                                   
    def close(self):                                                                                                                               
        self.records = None                                                                                                                        
        self = None                                                                                                                                
        return self                                                                                                                                
                                                                                                                                                   
#-----------------------------------------                                                                                                         
                                                                                                                                                   
#Testing harness: we create and drop logins and databases                                                                                          
#Edit connection for desired server name and security options:                                                                                     
#Sample: for local server default instance SQL2000, integrated security                                                                                                             
#   c = Connection('(local)',db='pubs', version='sql2000')                                                                                         
#For local server, SQL security                                                                                                                    
#   c = Connection('(local)','sa','sa password',db='pubs', version='sql2000')                                                                      
#These tests use a restored pubs database                                                                                          
#in a SQL2008 instance (local)\sql2008                                                                     

              
if __name__ == '__main__':                                                                                                                         
    c = Connection('(local)\sql2008',db='pubs', version='sql2008')                                                                                    
    print "Connection string: " + c.constr                                                                                                         
    if c.connected == 1:                                                                                                                           
        print "Connected OK"                                                                                                                       
    cu = c.cursor                                                                                                                                  
    lst = cu.execute('select * from authors')                                                                                                      
    print 'rowcount=' + str(cu.rowcount)                                                                                                           
    rows = cu.fetchall()                                                                                                                           
    for x in rows:                                                                                                                                 
        print x                                                                                                                                    
    c.close()                                                                                                                                      
                                                                                                                                                   
    #Several SQL statements test                                                                                                                   
    lst = cu.execute("sp_addlogin 'test2', 'test2'")                                                                                               
    print 'rowcount=' + str(cu.rowcount)                                                                                                           
    lst = cu.execute("select name from master..syslogins where name = 'test2'")                                                                    
    print 'rowcount=' + str(cu.rowcount)                                                                                                           
    rows = cu.fetchall()                                                                                                                           
    for x in rows:                                                                                                                                 
        print x                                                                                                                                    
    c.close()                                                                                                                                      
                                                                                                                                                   
    lst = cu.execute("EXEC sp_droplogin 'test2'")                                                                                                  
    print 'rowcount=' + str(cu.rowcount)                                                                                                           
    lst = cu.execute("select name from master..syslogins where name = 'test2'")                                                                    
    print 'rowcount=' + str(cu.rowcount)                                                                                                           
    rows = cu.fetchall()                                                                                                                           
    for x in rows:                                                                                                                                 
        print x                                                                                                                                    
    c.close()                                                                                                                                      
                                                                                                                                                   
    lst = cu.execute("CREATE DATABASE test")                                                                                                       
    print 'rowcount=' + str(cu.rowcount)                                                                                                           
    lst = cu.execute("select name from master..sysdatabases where name = 'test'")                                                                  
    print 'rowcount=' + str(cu.rowcount)                                                                                                           
    rows = cu.fetchall()                                                                                                                           
    for x in rows:                                                                                                                                 
        print x                                                                                                                                    
    c.close()                                                                                                                                      
                                                                                                                                                   
    lst = cu.execute("DROP DATABASE test")                                                                                                         
    print 'rowcount=' + str(cu.rowcount)                                                                                                           
    lst = cu.execute("select name from master..sysdatabases where name = 'test'")                                                                  
    print 'rowcount=' + str(cu.rowcount)                                                                                                           
    rows = cu.fetchall()                                                                                                                           
    for x in rows:                                                                                                                                 
        print x                                                                                                                                    
    c.close()

    lst = cu.execute("update authors set au_lname = 'Whitty' where au_id = '172-32-1176'")                                                                                                         
    print 'rowcount=' + str(cu.rowcount)                                                                                                           
    lst = cu.execute("select au_lname from authors  where au_id = '172-32-1176'")                                                                  
    print 'rowcount=' + str(cu.rowcount)                                                                                                           
    rows = cu.fetchall()                                                                                                                           
    for x in rows:                                                                                                                                 
        print x                                                                                                                                    
    c.close()      
                                                                                                                                                   

This is a library I created to manage SQL server databases. It works with SQL2005 and SQL2008's sqlcmd, in addition to the SQL2000 and SQL7's osql.exe. The previous revision included the option for integrated security, and the test section on the library itself; I also cleaned some old code and comments and removed the references to the string module. Later I will add support for Sybase (or maybe someone else will do it!). If you are a system engineer or database administrator and find yourself doing a lot of scripts and batch files doing calls to osql.exe you will find this library useful.

25 comments

AGIER Bertrand 14 years, 4 months ago  # | flag

CallProc. I can't execute procedures like EXEC sp_addlogin ... Is there a special way to do that ?

Thanks You

khawaja butt 13 years, 6 months ago  # | flag

what's dbcp, Hi, when i run your script i get erros, dbcp is not defined. python can't find this module. where can i get it. please reply. thanks

Jorge Besada (author) 13 years, 4 months ago  # | flag

dbcp and Pretty Printer. Hi Khawaja

dbcp is the module from another colaborator (Steve Holden). Do a search on the Cookbook for "Pretty Printer" and you will find it. Save it as a module named dbcp and your dblib will work ok. I will be updating dblib soon, thanks for trying it

Jorge Besada (author) 13 years, 4 months ago  # | flag

EXEC ok with sp_addllogin. Hi Bertrand

Thanks for testing dblib!

Sorry for the late reply

I tested using the sp_addlogin with EXEC (and without it)

and it worked fine.

Just make sure you configure the sql statement with a combination of double quotes and single quotes:

lst = cu.execute("EXEC sp_addlogin 'test3', 'test3'")

And it should work ok.

I will be working more on this from now on.

Best regards

Jorge

Updated test program follows

dblib_test.py

test program to test dblib.py

from dblib import *

from dbcp import pp #Pretty Printer imported here

c = Connection('SERVERNAME', 'sa', 'password','pubs')

print c.constr #print the connection string

print c.connected #prints 1 if connected OK

cu = c.cursor #create the cursor

lst = cu.execute('select * from authors')

print 'rowcount=' + str(cu.rowcount) #test print of record count

rows = cu.fetchall()

print pp(cu, rows, rowlens=1)

new test using sp_addlogin, no EXEC

lst = cu.execute("sp_addlogin 'test2', 'test2'")

print 'rowcount=' + str(cu.rowcount) #test print of record count

rows = cu.fetchall()

print pp(cu, rows, rowlens=1)

c.close()

new test using EXEC

lst = cu.execute("EXEC sp_addlogin 'test3', 'test3'")

print 'rowcount=' + str(cu.rowcount) #test print of record count

rows = cu.fetchall()

print pp(cu, rows, rowlens=1)

checking the logins were created:

lst = cu.execute("select name from master..syslogins")

print 'rowcount=' + str(cu.rowcount) #test print of record count

rows = cu.fetchall()

print pp(cu, rows, rowlens=1)

c.close()

-----------------------------------------------------

sasa sasa 11 years, 10 months ago  # | flag

Not getting all fields. Hi there:

I'm not getting all of the fields from my query:

sql="""select contact1.,contact2. from contact1 left outer join contact2 on contact1.accountno=contact2.accountno"""

c = Connection(...)

cu = c.cursor #create the cursor

lst = cu.execute(sql)

print cu.fieldnames

I'm getting 31 field names but the two combined tables should have 194.

thanks

Greg

Jorge Besada (author) 11 years, 9 months ago  # | flag

Please try this. Hi Greg - thanks for using dblib!

I made a copy of the authors table (named it authors2) from SQL server pubs database and run this version of your query

sql="select authors.,authors2. from authors left outer join authors2 on authors.au_id=authors2.au_id"

c = Connection('(local)',db='pubs')

cu = c.cursor #create the cursor

lst = cu.execute(sql)

print cu.fieldnames

And got the complete set of columns

['au_id', 'au_lname', 'au_fname', 'phone', 'address', 'city', 'state', 'zip', 'contract', 'au_id', 'au_lname', 'au_fname', 'phone', 'address', 'city', 'state', 'zip', 'contract']

Regards - Jorge

Brian Ironside 11 years, 7 months ago  # | flag

Memory Usage. After downloading and kicking around your osql module, I'm quite happy with its performance. The only difficulty I'm having with it concerns memory usage.

When running the following query with cursor.execute()

SELECT TOP 2000000 * FROM DATA

I end up using about 1.5 gigs of memory, which is rather what I expected. However, after I have closed and deleted all of the connection and cursor objects associated with the query, and all variables which took data from the query, the memory isn't released.

Even after deleting all the variables created in the scrpit, the memory remains allocated, only to be released when I exit the python session. Is there something I can do to recover this memory without needing to exit python altogether?

Thanks,

Brian

Jorge Besada (author) 11 years, 6 months ago  # | flag

Known issue with Python interpreter. Hi Brian, thanks for using dblib

I did some tests and could verify the fact: memory not being released. I did a little fix that improved a little: from 22MB went down to 16MB when closing the cursor, and before exiting Python; did not notice improvement when closing the connection

Doing some Google I found that this will be fixed in version 2.5

http://sourceforge.net/tracker/?func=detail&aid=1123430&group_id=5470&atid=305470

The original link that took me to the previous one was this:

http://evanjones.ca/python-memory-part2.html

For the time being, please test my partial fix (please fix indentation):

changed the close function in the cursor:

def close(self):

self.records=None #NEW LINE ADDED

self=None

return self

And here is the test section, I used the AdventureWorks2000 database with a cross join to get a good sized recordset

if __name__ == '__main__':

m = raw_input('Read memory 1 (Python Only) (approx 3,300KB), Hit Enter ')

c = Connection('(local)',db='AdventureWorks2000')

print c.constr #print the connection string

print c.connected   #prints 1 if connected OK

cu = c.cursor       #create the cursor

cu.execute('select top 10000 a.*, b.* from Address a, Address b')

print 'rowcount=' + str(cu.rowcount) #test print of record count

rows = cu.fetchall()

m = raw_input('Read memory 2 , (Cursor opened) Approx 22,000KB, Hit Enter')

cu.close()

m = raw_input('Read memory 3 (Cursor closed) Approx 16,000KB, Hit Enter')

c.close()

m = raw_input('Read memory 4 (Connection closed) Approx 16,000KB, Hit Enter')
Nicolas Lehuen 11 years, 6 months ago  # | flag

WTF ? Hi,

Why on earth should one use this recipe and start an osql process from within Python to execute SQL requests, when there are sane and proven alternatives like ODBC and ADO ? What are the possible advantages ? There is no way this can be faster, more compatible, more maintainable than the already existing alternatives... Don't touch this recipe without a 10-foot pole !

Except as a toy example of the power of os.popen, do yourself a favor and forget about this.

Sorry to be so harsh but this gives me the creeps. Really.

FYI, there is a Daily WTF article about this here :

http://thedailywtf.com/forums/62973/ShowPost.aspx

Kip Lehman 11 years, 6 months ago  # | flag

Counterpoint. Depending on your circumstances, this module could be a better choice than ODBC or ADO. ODBC (or the more popular mxODBC) requires that you have the win32all package installed (usually not a problem but your situation may be different). Use of mxODBC in a commercial environment requires a license, available at a very reasonable cost. The ADO approach requires the ctypes package for the ADO related modules I've seen. Docs and useful examples are fairly thin (and approaching being woefully out of date) for the ODBC and ADO modules. This module has at least some useful example usage demonstrated.

As far as performance goes, one should refer back to the original discussion segment where the author mentions his intended use, which is to aid in managing databases, ie., adding tables, adding users, simple queries to count rows, etc. I'm skeptical that the user of this module would suffer any meaningful performance penalty for that sort of use. But, if you're Amazon, you shouldn't use this module for your website transaction processing. Nor should you use this if you are NOAA crunching terabytes of sea-surface, air temps and humidity to issue hurricane forecasts.

There isn't any way that this module will ever be compatible with Oracle, DB2, MySQL, PostGreSQL, SQLite, etc. But, if you're in a MS-SQL Server environment, who cares? If and when you transition to SQLServer 2005, you'll probably have to substitute for the planned deprecation of osql. This is certainly manageable.

I also don't see a problem with maintainability if your use falls within the spectrum of use envisioned by the author. Don't use this to control nuclear power plants and don't think you can use this in a high availability mission-critical near real-time OLTP environment.

There's so many other Python modules out there that make use of os.popen to great advantage, that I don't understand why one would object to this one. Why re-invent the wheel or bother with increased complexity if using popen meets your needs? It's not like the hardware you're likely to be operating on will collapse under the "burden".

Do yourself a favor and skip the Daily WTF link. The postings there have so little relevance to this module. Unless you're in to gratuitous self-reference that's wholly devoid of useful content, you've seen all the critique specificity the prior respondent could muster.

While I might quibble about the name of the module (IMO, it should be named msdblib), some non-idiomatic Python syntax, the extraneous HTML line-break tags on the ASPN page and other minor issues, this module can be useful.

Depending on your environment and circumstances, the fact that it has no external dependencies (other than osql) may be reason enough to use this module.

The 3 star rating is justified.

Jorge Besada (author) 11 years, 6 months ago  # | flag

Some performance tests by author - not bad. I agree with Kip that this dblib may not be suitable for some applications, but it is very useful for others. If you find yourself using in your system administration job a lot of calls to osql from scripts, it really simplifies your life. And the performance for this kind of use is not bad: the speed is comparable to ADO. I do not have an equivalent version of the dblib using ADO (one that conforms the output cursor like the dblib does), but I did some speed tests with this code, just to compare the plain speed of both (put it after the line (if __name__ == '__main__'):

from time import time

import win32com.client



server = '(local)'

database = 'AdventureWorks2000'

ssql = 'select top 20000 a.*, b.* from Address a, Address b'



print "Execute using dblib"

c = Connection(server,db = database )

cu = c.cursor

t1 = time()

cu.execute(ssql)

t2 = time()

t3 = t2 - t1

print "Elapsed time:" + `t3`

print ""



print 'Execute using ADO, no processing'

t4 = time()

con=win32com.client.Dispatch('ADODB.Connection')

rs=win32com.client.Dispatch('ADODB.recordset')

cstr = "Provider=SQLOLEDB.1;Data Source=" + server

cstr = cstr + ";Initial Catalog=" + database + ";Integrated Security=SSPI;"

con.Open(cstr)

rs=con.Execute(ssql)

con.Close

t5 = time()

t6 = t5 - t4

print "Elapsed time:" + `t6`

print ""



print "Execute using plain osql, no processing"

t7 = time()

constr = "osql -E -S" + server + " -s" + chr(1) + " -Q" + '"' + ssql + '"'

lst = os.popen(constr).readlines()

t8 = time()

t9 = t8 - t7

print "Elapsed time:" + `t9`

I run it several times, for different sized recordsets, and got results similar to this:

Execute using dblib

Elapsed time:11.719000101089478

Execute using ADO, no processing

Elapsed time:0.125

Execute using plain osql, no processing

Elapsed time:0.21799993515014648

It is to be expected in this test scenario for dblib to be the slowest, but this is due to the amount of text processing being done to configure the cursor to make it compatible with the Python cursor standard. I suspect that if anybody creates a compatible cursor like this dblib based in ADO, it will perform with a speed approximate to the one of dblib. Another plus is that with minor code changes it could be used with Sybase. I have not had time to test it extensively with SQL 2005's sqlcmd.exe, but it seems to work well with it (with better speed). And in reference to Nicolas Lehuen comment about the 10-foot pole, I can vouch for the safety of this program: I have never been electrocuted by it, not even mild shocks :-)

Nicolas Lehuen 11 years, 5 months ago  # | flag

OK for the no-dependency part. You have a point : this recipe may be interesting if you need a simple DBAPI implementation in a non-production context, for example to perform administrative tasks.

Of course this should not be used in a high concurrency context, like in a Web application, but I guess this was not what the author intended.

Sorry if I've been too harsh, but I've read this recipe a few hours after reading the Daily WTF article, and the similarity in concepts made me go ping.

Regards,

Nicolas

Katya K 9 years, 11 months ago  # | flag

'Error: input query is too long\n' i'm using the module above and getting error message when trying to execute the proc. Any ideas I should change? The string sql="Execute [MyDB].[dbo].[MyProc] @var1=1, @var2=2.2, ...@var57=''"

What do I do wrong and what should I change to avoid this error? I print this message in class Cursor def execute(self, s), basically self.records = [] lst = os.popen(self.constr + ' -s' + self.colseparator + " " + self.sqlfile + '"' + s + '"').readlines() print "lst",lst # prints the error message in the title if len(lst) == 0: return self.rowcount

The rest of the code I use simple: c=Connection(...) if c.connected==1: sql="Execute [MyDB].[dbo].[MyProc] @var1=1, @var2=2.2, ...@var57=''" db = c.cursor ret = db.execute(sql)

it doesn't fail but also doesn't execute by printing ['Error: input query is too long\n'] from the my inserted statement :(

Any ideas or any other methods I could use? Many thanks!!! Katya

giuseppe procino 9 years, 11 months ago  # | flag

SQL Server 2005 problem with simple test. Hi Jorge, when i run your script i get the following erros:

>>> sqlcmd -Usa -Psa -Sfrododb01 -dpkt110_IMCA /w 8192
1
Traceback (most recent call last):
  File "C:\Python21\Pythonwin\pywin\framework\scriptutils.py", line 310, in RunScript
    exec codeObject in __main__.__dict__
  File "C:\Documents and Settings\gprocino\Desktop\etl\dblib_test.py", line 10, in ?
    lst = cu.execute('select * from comuni')
  File "C:\Documents and Settings\gprocino\Desktop\etl\dblib.py", line 104, in execute
    self.rowcount = int(count)
ValueError: invalid literal for int(): Righe
>>>

Source Script code is:

from dblib import *

from dbcp import pp #Pretty Printer imported here

c = Connection('frododb01', 'sa', 'xxxx','pkt110_IMCA','sql2005') #My micorsoft sql server 2005 parameters

print c.constr #print the connection string

print c.connected #prints 1 if connected OK

cu = c.cursor #create the cursor

lst = cu.execute('select * from comuni')

print 'rowcount=' + str(cu.rowcount) #test print of record count

rows = cu.fetchall()

print pp(cu, rows, rowlens=1)

c.close()

where can i get it. please reply. thanks Giuseppe P.

Jorge Besada (author) 9 years, 10 months ago  # | flag

Try this please. Hi Giuseppe: Problem may be due to the use of the reserved word count in the lines count = lastline[1:spacepos]

self.rowcount = int(count)

Replace count by cnt:

cnt = lastline[1:spacepos]

self.rowcount = int(cnt)

I will update the source code later. Thanks for using dblib!

Jorge Besada (author) 9 years, 10 months ago  # | flag

Probable cause. Hi Katya:

The list of arguments for your proc seems very long. A workaround would be to pass a smaller number of arguments (or a single one) and inside the prod split it. But there is a good possibility that the problem is caused by single quotes inside some passed parameter values. See if you can pass the parameters in smaller groups. Let me know if this helped. Thanks for using dblib.

Jorge Besada (author) 9 years, 10 months ago  # | flag

Probable cause. Hi Katya:

The list of arguments for your proc seems very long. A workaround would be to pass a smaller number of arguments (or a single one) and inside the prod split it. But there is a good possibility that the problem is caused by single quotes inside some passed parameter values. See if you can pass the parameters in smaller groups. Let me know if this helped. Thanks for using dblib.

Marco Ferreira 8 years, 7 months ago  # | flag

Is the sybase implemented already? :)

Jorge Besada (author) 8 years, 7 months ago  # | flag

Hi Marco Not yet ready for Sybase

Kosta Welke 8 years ago  # | flag

Hi!

I think line 137 should be

if x1 <> '' and x1[0] <> '-' and x1[-1] <> '-':

instead of

if x1 > '' and x1[0] > '-' and x1[-1] > '-':

Otherwise, valid lines are rejected... I had the problem that cursor.execute returned 66, then cursor.rowcount returned 66, but cursor.fetchall returned an array with len() 61 (oh and of course osql returned 66 rows). Above fix fixed that problem.

Jorge Besada (author) 7 years, 4 months ago  # | flag

Hi Kosta

Thanks for your input, I implemented it Sorry for the delay, it's been a long time since I look into this area

Roger 7 years, 3 months ago  # | flag

I send a big SQL like this : http://paste.pocoo.org/show/226546/ execute() does not do anything. Any idea ?

thanks

atrant 7 years ago  # | flag

it is easy to use and easy to start using ms sql inside python utilities in seconds. thank you VERY much!

Kobus 5 years, 2 months ago  # | flag

to echo everybody else, thanks, i will have a look for you at the Sybase if no one had a look at it yet. I use to script for Sybase with bash, and was looking for the same simplicity. And at last i found your posting, after searching the web on and off for months. Thanks again Jorge.

Jorge Besada (author) 5 years, 2 months ago  # | flag

Hi Kobus

Thanks for volunteering :-)

Looking forward to the Sybase support, I am sure it will be useful to many