CODEWITHSUNDEEP
pythondatabase
user1382520
user1382520

Reputation: 1

How to pass variables for sql statements in python?

I am a beginner in python. I am doing a query on access database. I was to pass a string variable to sql statement but can't figure it out for a double.

Here is my code:

qllname = 29221.0

llname = "01-01-05-W2"

sqlstatement = recordset.Open("SELECT * FROM LLD WHERE LLD = '%s'" % (llname),connection,1,3)

for x in range(recordset.Fields.Count):

    fields_dict[x] = recordset.Fields.Item(x).Name

    print fields_dict[x], recordset.Fields.Item(x).Value, x


sqlstatement2 = recordset2.Open("SELECT * FROM SWAWELLS WHERE WWDR_NO = '%d'" % (qllname),connection2,1,3)

for x2 in range(recordset2.Fields.Count):

    print recordset2.Fields.Item(x2).Value, type(recordset2.Fields.Item(x2).Value)

result:

LLD 01-01-05-W2 0
X 678817.81875 1
Y 5431052.45982 2
Traceback (most recent call last):
  File "L:/temporary/start of test/accessnew--.py", line 25, in <module>
    sqlstatement2 = recordset2.Open("SELECT * FROM SWAWELLS WHERE WWDR_NO = '%d'" % (qllname),connection2,1,3)
  File "<COMObject ADODB.Recordset>", line 4, in Open
com_error: (-2147352567, 'Exception occurred.', (0, u'Microsoft JET Database Engine', u'Data type mismatch in criteria expression.', None, 5003071, -2147217913), None)
>>>

Upvotes: 0

Views: 11534

Answers (2)

jaime
jaime

Reputation: 2344

Try:

sqlstatement2 = recordset2.Open("SELECT * FROM SWAWELLS WHERE WWDR_NO = '%d'" % (qllname),connection2,1,3)

You're using the %d string interpolation operator, which tells me you're passing a number in to the SQL statement. However, you use a SQL string, when I think you need an integer. I think you want a SQL statement that looks like -

SELECT * FROM SWAWELLS WHERE WWDR_NO = 29221

However, the code you have provided will generate this statement:

SELECT * FROM SWAWELLS WHERE WWDR_NO = '29221'

If WWDR_NO is an integer, than you will get the "Data type mismatch in criteria expression" you're receiving above.

Try this:

# Notice there is no ' around %d
sqlstatement2 = recordset2.Open("SELECT * FROM SWAWELLS "
                                "WHERE WWDR_NO = %d" % 
                                (qllname),connection2,1,3)

Also, as others have mentioned you should use a DBAPI wrapper. What's that? DB-API is the python "standard" for accessing databases. There is one available for ACCESS called adodbapi. I have no idea about the state of this library. The reason people say stay away from the interpolation you're doing above is because it can build invalid SQL and if you're taking data from an untrusted source it is a security risk.

The other BIG benefit of using dbapi is that it's a standard. And your code will be reasonably portable to other databases if you give it a whirl.

Want to check out DBAPI right now without downloading a library? If you're using py2.5 or greater you have it built in with sqlite. Check out the sqlite3 module here.

Upvotes: 2

akaRem
akaRem

Reputation: 7638

You shouldn't use % formatting operator. This is unsafe.

Here is Python Database API Specification v2.0 http://www.python.org/dev/peps/pep-0249/

Most of python DB wrappers supports it.

Upvotes: 2

Related Questions