i found bug access sql queries can't seem track down. have straightforward sql query use retrieve data access database that's "managed" in older application (ie data in database , have no real control on what's in there).
import pyodbc mdb = '******.mdb' drv = '{microsoft access driver (*.mdb)}' pwd = '' con = pyodbc.connect('driver={};dbq={};pwd={}'.format(drv, mdb, pwd)) sql = ('select estim.partno, estim.descrip, estim.custcode, estim.user_text1, estim.revision, ' + 'estim.comments, routing.partno rpartno, routing.stepno, routing.workcntr, routing.vendcode, ' + 'routing.descrip stepdescrip, routing.setuptime, routing.cycletime, ' + 'routing.workorvend, ' + 'materials.partno matpartno, materials.subpartno, materials.qty, ' + 'materials.unit, materials.totalqty, materials.itemno, materials.vendor ' + 'from (( estim ' + 'inner join routing on estim.partno = routing.partno ) ' + 'inner join materials on estim.partno = materials.partno )') if 'partno' in kwargs: key = kwargs['partno'] sql = sql + 'where estim.partno=?' cursor = con.cursor().execute(sql, key) # use debuging num = 0 row in cursor.fetchall(): num += 1 return num this works fine partno except when partno contains decimal point. curiously, when partno contains decimal point , hyphen, appropriate record(s).
kwargs['partno'] = "100.100-2" # returns 1 record kwargs['partno'] = "200.100" # returns 0 records both partnos exist when viewed in other application, know there should records returned both queries.
my first thought ensure kwargs['partno'] string key = str(kwargs['partno']) no change.
i tried places quotes around 'partno' value no success. key = '\'' + kwargs['partno'] + '\''
finally, tried escape . no success (i realize break queries, i'm trying track down issue single period) key = str(kwargs['partno']).replace('.', '"."')
i know using query parameters should handle escaping me, @ point, i'm trying figure out what's going on. thoughts on this?
so issue isn't query parameters - works should. problem sql statement. incorrectly assumed - , never checked - there record in materials table matched partno.
inner join materials on estim.partno = materials.partno will return record if partno found in both tables, in particular case not.
changing
left outer join materials on estim.partno = materials.partno produces expected results. see info on joins. https://msdn.microsoft.com/en-us/library/bb243855(v=office.12).aspx
as print (repr(key)) - flask handles kwarg type upstream properly
api.add_resource(partapi, '/api/v1.0/part/<string:partno>' so when ran in browser, got "full length" strings. when run in cmd line using python -c ....... not handling argument type gord pointed out, truncating trailing zeros. didn't think flask portion relevant, never added in original question.
