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=; 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 = """
DECLARE @var1 INT = 0
EXEC [dbo].[storedProc] 1,5,'',0,0,2,@var1 OUTPUT,@var2Out OUTPUT
SELECT @var1, @var2Out 
result = cursor.execute(sql)

print result.fetchall()

print result.fetchall()
print result.fetchall()

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,

 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:

 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 🙂


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… 🙂

Ordering of Strings according to numbers contained in them

This is an error that is more common than you would think.

Take an instance – There is a column in the Database table such as a Profile ID, and the developer thinks of sorting the records according to the Profile ID in either an ascending, or a descending order. eg. Sorting records by descending Profile ID to show the latest added records first.

What happens is that the Profile Id is alphanumeric, and default sorting of such records is not exactly as one would expect.

Below is the description of a recent bug I raised. You will understand what I’ve been trying to say by reading it.

Bug Description – “The ‘Top Deals in your City’ section of Home page is required to show the latest entered bike listings.
However, Latest Listings were not being displayed as expected. Issue is as  explained below:

Listings were being Ordered by ProfileID, and not by EntryDate.
Hence, a listing with ProfileID ‘S27’ that was entered on ‘2012-08-27 20:24:26.067’ was considered newer than a listing with ProfileID ‘S237’ that was entered on ‘2013-02-05 11:13:27.637’

Strings are ordered as ascending/descending in a way different from the ordering of integers.
Hence, while ordering, ‘S27’ was considered to be greater than ‘S237’ ”

Have I succeeded in explaining this to you yet? Let me try again..

You have the following values:







You want to order them in the ascending order. What would you expect the result to be? I’m sure everyone would expect it to be as below;







But, the default sort functions in almost every programming language would return the below result. The records are sorted in ASCII order, and not in Alphabetical order.







What can we do to resolve this problem? Let’s see…

1. The first and most simple solution would be to not use the alpha-numeric column for sorting the records.

Instead, one could use a Date column to sort records (as mentioned in my bug description above), or one could use an ID column that contains only numbers and is meant for sorting the records.

Proper planning of Database architecture could help prevent this problem from arising.

2. If the only way to sort records is to use the alpha-numeric column, then one can use “Natural Sorting”. Following are some good links that would help you understand this subject more.

The Alphanum Algorithm

Sorting for Humans : Natural Sort Order

Natural Sorting in C#

Compact Python Human Sort

Importance of Boundary Value analysis while testing in Database

A few days back, we found a critical issue in production.

The issue that occurred was as follows:

We were given a functionality to test for uploading images for a selected car. There was only a Browse button, and an Upload button in the page.

We had to test if the image name is getting saved in the format

(make year-car make-car model-car version-unique ID.jpg)

We tested it for a number of different cars and functionality was working fine.

But, we missed out one thing.

There was a stored procedure created for saving these image URLs to the database (it was not shared with us. We normally do black box testing.)

Database column being used to save was of size 100 characters, but the stored procedure was storing image URLs in a variable of length 50 characters

Now, the Image names and URLs were getting saved properly for most cars.

But, for cars where Image URLs got greater than 50 chars in length, data got truncated while saving in the database. And this resulted in the images not getting displayed in production.

I was left baffled at first about how I could have caught this bug before release, without having had a look at the SP. How could I have found that variable size was less than what it was supposed to be?

Then it struck me. This is how i am going to work on preventing such defects in the future-

When given a page to test, I will also list out the DB tables that are affected.

For all the columns affected, I will note the size of the column and try to insert values that tend towards the maximum length allowed in the column.

If I had tried this earlier, by selecting longer car names in my effort to reach a 100 char length, I would have found that the maximum length of a possible input into the column was around 76. And in that case I would have found the bug.

I urge you to put this test case as a top priority test case as data truncation is a serious issue. The final goal is to confirm that both the below given conditions are always true:

1. The maximum length of data that a user can input <= The size of the variable in stored procedure that is used to store the value

2.  The size of the variable in stored procedure <= The size of the related Database column