Connecting to SQL in your Python script


pypyodbc python package

I had been wondering for such a long time on how to connect my python scripts with our MS SQL database. So, when I had a little break from my workload today, I decided to finally solve this problem.

I have tried searching for solutions before too, but it seems today was my lucky day. I found the pypyodbc module.  It’s written in python, and helps execute SQL in your database.

Here’s an example of the module’s usage, and a getting started guide.

Some other article’s  withe useful information are – article on the various methods available in moduleUsing stored procedures

I needed to execute an SP in the database through my automated test script to check the data displayed on the front-end. So, I included the  following function in my code.

It’s great that I finally know how to integrate python and database because I can do so much more.  🙂

import pypyodbc

connection_string = "driver={SQL Server Native Client 10.0};
 server=127.0.0.1; database=databaseName; 
  uid=user; pwd=pwd"

cnxn = pypyodbc.connect(connection_string)
cursor = cnxn.cursor()

# SET NOCOUNT ON should be added to prevent errors thrown by cursor

sql = """
SET NOCOUNT ON
DECLARE @var1 INT = 0
DECLARE @var2Out NVARCHAR(MAX)
EXEC [dbo].[storedProc] 1,5,'',0,0,2,@var1 OUTPUT,@var2Out OUTPUT
SELECT @var1, @var2Out 
"""
result = cursor.execute(sql)

print result.fetchall()

result.nextset()
print result.fetchall()
result.nextset()
print result.fetchall()
Advertisements

SQL Queries – DateTime should be passed in single quotes


I learnt a new thing today.. I learnt that a DateTime value should always be passed within single quotes in a query.

Found this out when we were debugging a Query, used in our code, to find the cause of a bug I raised. The issue caused was due to DateTime value being passed in the SELECT query without being enclosed in single quotes.

On further research, I found that DateTime should always be enclosed within single quotes in all queries – whether it is a SELECT query, an UPDATE query, or an INSERT query. Otherwise, the DateTime value is not read/written correctly by the SQL Server.

For example, for the below given query,

SELECT * FROM TABLENAME 
 WHERE DateTime >= 12/04/2011

No error will be raised by the SQL server, but it will divide the date value given (12/04/2011) and get zero, and then take the minimum possible DateTime value as the (default date + 0)

Making the effective SQL query into:

SELECT * FROM TABLENAME 
 WHERE DateTime >= '1900-01-01 00:00:00.000'

Hence, though no error/exception is raised, and the query does appear to work, the results that you get from the query are incorrect.

A similar problem occurs when you pass DateTime values without quotes in INSERT and UPDATE queries.

(Microsoft Support: a value without quotes that is used as a datetime value is treated as the default date plus the number of days equal to the computed amount of the assigned value)

 

Other Reference:

* The bug I found today 🙂

* http://stackoverflow.com/questions/6119369/simple-datetime-sql-query

Search in SQL for a value containing a ‘ character


So, you are stuck with a problem…

You need to find User names that are stored in the DB Users table, and that contain the apostrophe character. (eg. Susan D’Souza)

How do you go about it?

You know the LIKE keyword has to be used somewhere. But, when you add the apostrophe character in the LIKE criteria, the query gives an error (as displayed below) on execution.
SQL LIKE query with apostrophe

Error for SQL LIKE query with apostrophe

It took a while for my colleague and I to find the resolution to this, but thanks to the world wide web, we did find it. 😀

And now I am writing it down here for the benefit of any one else who stumbles across this entry while searching online for the solution. (and of course, for my future reference)

And it’s a very simple solution –
Instead of just one apostrophe character, write 2 of them consecutively in the LIKE criteria.

An Example is given below-

>> SELECT * FROM Users where UserName like ‘%D”Souza%’

The above query will give you the list of all the users that have “D’Souza” in their name.

Happy Querying and see you soon… 🙂