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
Download
Copy to clipboard1 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.
Tags: database
CallProc. I can't execute procedures like EXEC sp_addlogin ... Is there a special way to do that ?
Thanks You
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
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
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()
-----------------------------------------------------
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
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
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
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__':
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
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.
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
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 :-)
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
'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
SQL Server 2005 problem with simple test. Hi Jorge, when i run your script i get the following erros:
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.
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!
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.
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.
Is the sybase implemented already? :)
Hi Marco Not yet ready for Sybase
Hi!
I think line 137 should be
instead of
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.
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
I send a big SQL like this : http://paste.pocoo.org/show/226546/ execute() does not do anything. Any idea ?
thanks
it is easy to use and easy to start using ms sql inside python utilities in seconds. thank you VERY much!
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.
Hi Kobus
Thanks for volunteering :-)
Looking forward to the Sybase support, I am sure it will be useful to many