This article basically demonstrates how one may use Python's pyodbc module with MS SQL Server. Yet, as this task becomes much easier (and, may I say, enjoyable) when Microsoft Visual Studio 2012 (Ultimate) and its SQL Server Data Tools (SSDT) are used,  these tools will be referred here and there. It includes full instructions and a demonstration of how to create, deploy, and use a scalar-valued .NET function in our database work with Python's pyodbc module.

Why did I prefer pyodbc over the other modules that Python offers for working with data bases? That's because pyodbc offers a minimal interface that mainly serves as a pipe to operate SQL commands and get the desired data. In other words, pyodbc may work with any database, so all you have to do in order to port your code from one kind of database to another is adjust the SQL commands to the new database.

Now, in order to work with a target database, a connection string (i.e., a string that describes how to connect that database) must be first constructed. A connection string consists of several elements like Driver, Server, and Database. Here's how you find the values to fill in for these elements in case of a server application that runs on the same Windows station where the database is located:

  • Driver: Run "Administrative Tools > Data Sources (ODBC)", select the "Drivers" tab, and take the name of the desired (newest) ODBC driver.
  • Server: Run "Control Panel > System", and take the computer name. Alternatively, simply type "localhost".
  • Database: Run Microsoft's "SQL Server Import and Export Wizard", choose the data source that suits the desired driver (see above), enter the same server name (see above), choose "Use Windows Authentication", and select the name of one of the databases listed by the designated combo box. In case the connection string is created in order to create a new database, use "master" instead.

Here are some relevant links that will prove helpful with different aspects of this article:

Before we proceed, it will be in place to add some rather important notes:

  • In case you have to use username and password in the connection string, do not specify them literally, as for security reasons they should not be part of the code. Instead, they should be either supplied by the caller, or fetched from some secure database.
  • The pyodbc cursor's "execute" method let's its caller interlace question marks ('?') where parameters should be specified, and then add their values as explicit parameters. This apparatus should be used in order to pass the caller's supplied parameters into method execute, in a way that no explicitly typed parameters values appear in the code, neither as part of the SQL command string, nor as separately following parameters, so that they won't appear in the code unconcealed. This recommendation wasn't kept here only because this code merely serves for demonstration purposes, and has no callers.
  • A connection shouldn't basically be kept open in the periods between series of SQL commands. That's in order to release resources at both the client and the server sides, and prevent their unrequired retention in case the user forgets to close the application before it leaves for his/hers annual vacation... It is just because this is a mere demo that I didn't bother adding code to close the connection at the end of each operation (and code to reopen the connection at the beginning of the next opeartion) in this article.

And also some less important, but still useful, remarks:

  • In SQL statements the name of the database may include the name of the schema (e.g., "dbo.Person"), and may not (e.g., "Person").
  • There's no need to call pyodbc connection's "commit" method after the excution of each SQL command, and it is usually done here just for the sake of methodic programming, and in order for the user to be able to immediately refresh the SQL Server Object Explorer and see the changes in all cases. Also in regard with "commit", in case a SQL command causes an exception, calling "commit" is really unnecessary.
  • One must be aware to the possibility that there are cases when the SQL Server rejects an SQL command with absolutely unrelated, non-descriptive, and misleading error messages (a most notorious example is "Cannot find the object *** because it does not exist or you do not have permissions" in response to, say, illegal table creation SQL command). Pyodbc will raise an exception and report this problematic message, what may make you loose hours in blind debugging...
  • In order to get table info (e.g., list of fields) for a target table in a Microsoft SQL Server database:
    • Open Microsoft SQL Server Object Explorer.
    • Click "Add SQL Server" to add the one that contains the target table.
    • Use the database information tree to view the desired data.
    • Optionally right-click any desired nodes and select "Properties".

Finally, it is worth to make some remarks about the code samples in this article; these samples:

  • Are written in Python 3.2.
  • Are using, of course, module pyodbc, and also module sys. The following imports are, therefore, assumed:

1
2
import sys
import pyodbc

  • Handle exceptions in a non-complete manner. If you like your production code to be better written, you'll have to relate the variables of each exception in your code. To learn how to do this, consult the "Errors and Exceptions" page in Python's documentation.

Here's a link to the sample code in this article: demo_db_works.py

 

After this somewhat long preface, we can begin doing the real work, that is, perform a list of SQL commands that cover a list of common database operations.


First, we have to create a new database, what's performed only once, if the target database doesn't exist, and isn't part of our fluent tasks. Here's how.

Creating a new database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
connection_str =    """
Driver={SQL Server Native Client 11.0};
Server=REPLACE_THIS_WITH_THE_TARGET_COMPUTER_NAME;
Database=master;
Trusted_Connection=yes;
"""
db_connection = pyodbc.connect(connection_str)
db_connection.autocommit = True
db_cursor = db_connection.cursor()
sql_command =   """
CREATE DATABASE PythonExperimentsDb
"""
try:
db_cursor.execute(sql_command)
except pyodbc.ProgrammingError:
print("Database 'PythonExperimentsDb' already exists.")
db_connection.autocommit = False
db_cursor.close()
del db_cursor
db_connection.close()

Once we have a database to work with, we can create applications to manipulate it. Such applications should start by connecting that database.

Connecting a database 

(Note in the preface the remark on opening and closing connections.)

1
2
3
4
5
6
7
8
9
10
connection_str =    """
Driver={SQL Server Native Client 11.0};
Server=REPLACE_THIS_WITH_THE_TARGET_COMPUTER_NAME;
Database=PythonExperimentsDb;
Trusted_Connection=yes;
"""
db_connection = pyodbc.connect(connection_str)
db_connection.autocommit = True
db_connection.autocommit = False
db_cursor = db_connection.cursor()

Having a database connection and a cursor at hand, we can safely continue with our regular database tasks, samplaes of which will be demonstrated in the following paragraphs, one by one.


Changing the name of an existing database

Warning: This command removes all the tables in the renamed database, together with all their data!

1
2
3
4
5
6
sql_command =   """
ALTER DATABASE PythonExperimentsDb
MODIFY NAME = PlayingPythonModulePyodbc 
"""
db_cursor.execute(sql_command)
db_connection.commit()

Creating a .NET (C#) scalar-valued function to use in SQL commands

That must be explained before the demo code is displayed: The scalar-valued .NET (C#) function that we'll use in the following code, RegExMatch (aliased RegExMatchFunc), is exported by a DLL that has been developed using Microsoft Visual Studio in a separate SQL Server Database project. This simple function, which tests any given input string against any given regular expression string, is required as SQL Server does not offer a native option for such a check constraint. As anyone who's familar with .NET/C# may understand, the capability to use .NET lets you harness the huge amount of powerful classes and functions available with .NET to your database works, and that great.

  • That understood, in order to create such a DLL follow the explanation here. You may, of course, add as many functions as you like to SQL server database DLLs. In our demo DLL, however, there's a module that defines just the single above described helper scalar-valued function. This module contains the following code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class SqlRegExFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static bool RegExMatch(string strValue, string strRegEx)
{
string strTrimmed = strValue.TrimEnd(null);
string strRegExTrimmed = strRegEx.TrimEnd(null);
if (string.IsNullOrEmpty(strTrimmed) || string.IsNullOrEmpty(strRegExTrimmed))
return false;
Regex reTrimmed = new Regex(strRegExTrimmed);
return reTrimmed.Match(strTrimmed).Success;
}
}

  • Next make SQL Server link the two databases, that is, the database where the the new scalar-valued function was stored to the database where you'd like to use this function.

I know, these steps aren't trivial, but at this point all the required configurations and arrangements are behind us, and the rest is simply a matter of sending SQL commands for execution, as would be explained next.


Creating an alias to a .NET (C#) scalar-valued function in order to use it in SQL commands with the current database

  • Create in the current database an assembly using the suitable SQL Server Database DLL. Below SQL command assumes that the DLL we created above is named "DbRegEx.dll", and located at "C:\\".

1
2
3
4
5
6
7
8
9
sql_command =   """
CREATE ASSEMBLY DbRegExAsm
FROM 'REPLACE_THIS_WITH_THE_REAL_PATH\\DbRegEx.dll'
"""
try:
db_cursor.execute(sql_command)
db_connection.commit()
except pyodbc.ProgrammingError:
print("Assembly 'DbRegExAsm' already exists.")

If you'd use the SQL Data Tools in order to get the SQL command for the assembly so created, you'd find out that it is a somewhat different command (abbreviated here for the sake of shortness):

 

1
2
3
CREATE ASSEMBLY [DbRegExAsm]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B8000000000000004000...

Though, as you can figure out, these two SQL commands are equivalent: the reference to a DLL file is replaced with its contents at the moment when the assembly is created. These contents are serialized into a long hexadecimal string. 

  • Create in the currently connected database a scalar-valued function that actually calls a corresponding scalar-valued function. Recall that "SqlRegExFunctions" is the name of the class in which our scalar-valued function is defined.

1
2
3
4
5
6
7
8
9
10
11
12
sql_command =   """
CREATE FUNCTION [dbo].[RegExMatchFunc]
(@strValue NVARCHAR (4000), @strRegEx NVARCHAR (4000))
RETURNS BIT
AS
EXTERNAL NAME [DbRegExAsm].[SqlRegExFunctions].[RegExMatch]
"""
try:
db_cursor.execute(sql_command)
db_connection.commit()
except pyodbc.ProgrammingError:
print("Function 'RegExMatchFunc' already exists.")

Creating a table

Note: in below defined table there's a "Id" column, which is the table's primary key, and whose values are checked using a scalar-valued function by the name RegExMatchFunc, which is exactly the scalar-valued function we created above.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
sql_command =   """
CREATE TABLE Person
(
Id char(9) PRIMARY KEY CHECK (dbo.RegExMatchFunc(Id, '^[0-9]{9}$') = 1),
LastName VARCHAR(20) NOT NULL,
FirstName VARCHAR(20) NOT NULL,
City VARCHAR(20),
BirthDate DATE CHECK (BirthDate > '1900-01-01'),
HeightMeters DECIMAL (4, 2) DEFAULT 1.70 NOT NULL CHECK (HeightMeters BETWEEN 1.00 AND 2.50)
)
"""
try:
db_cursor.execute(sql_command)
db_connection.commit()
except pyodbc.ProgrammingError:
print("Table 'Person' already exists.")

If you'd now use the SQL Data Tools in order to get the SQL command for the table so created, you'd find out that it is an equivalent, though, again, somewhat different command, pronounced in Microsoft's uniform manner:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE [dbo].[Person]
(
[Id] CHAR (9) NOT NULL,
[LastName] VARCHAR (20) NOT NULL,
[FirstName] VARCHAR (20) NOT NULL,
[City] VARCHAR (20) NULL,
[BirthDate] DATE NULL,
[HeightMeters] DECIMAL (4,2) DEFAULT ((1.70)) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC),
CHECK ([dbo].[RegExMatchFunc]([Id],'^[0-9]{9}$')=(1)),
CHECK ([BirthDate]>'1900-01-01'),
CHECK ([HeightMeters]>=(1.00) AND [HeightMeters]<=(2.50))
)

Yes, you could use either SQL command, and get the same results. It's just a matter of taste.

 


Deleting a table
 
Warning: do this with caution, as all the table's data will be lost!

1
2
3
4
5
6
7
8
sql_command =   """
DROP TABLE Person
"""
try:
db_cursor.execute(sql_command)
db_connection.commit()
except pyodbc.ProgrammingError:
print("Table 'Person' does not exists.")

Changing the name of an existing database

Warning: This command removes the tables in the renamed database!

1
2
3
4
5
6
sql_command =   """
ALTER DATABASE PythonExperimentsDb
MODIFY NAME = PlayingPythonModulePyodbc 
"""
db_cursor.execute(sql_command)
db_connection.commit()


Altering table columns

  • Add a column to a table.

1
2
3
4
5
6
7
8
sql_command =   """
ALTER TABLE Person ADD Foo INT
"""
try:
db_cursor.execute(sql_command)
db_connection.commit()
except pyodbc.ProgrammingError:
print("Field 'Foo' already exists.")

  • Change the data type of a column in a table.

1
2
3
4
5
sql_command =   """
ALTER TABLE Person ALTER COLUMN Foo REAL
"""
db_cursor.execute(sql_command)
db_connection.commit()

  • Delete a column from a table.

1
2
3
4
5
sql_command =   """
ALTER TABLE Person DROP COLUMN Foo
"""
db_cursor.execute(sql_command)
db_connection.commit()

  • Add a constraint to a table.

1
2
3
4
5
6
7
8
9
sql_command =   """
ALTER TABLE Person WITH NOCHECK 
ADD CONSTRAINT CK_Person_Id_NonZero CHECK (Id > 0) ;
"""
try:
db_cursor.execute(sql_command)
db_connection.commit()
except pyodbc.ProgrammingError:
print("Constraint 'CK_Person_Id_NonZero' already exists.")

Updating table rows
  • Add a row to a table.

A rude manner to perform this is as follows:

1
2
3
4
5
6
7
8
sql_command =   """
INSERT INTO Person (Id, LastName, FirstName, City, BirthDate, HeightMeters) VALUES ('000000001', 'Kohen', 'Aharon', 'Jerusalem', '2001-01-01', 1.80)
"""
try:
db_cursor.execute(sql_command, '000000001', 'Kohen', 'Aharon', 'Jerusalem', '2001-01-01', 1.80)
db_connection.commit()
except pyodbc.IntegrityError:
print("Row Id='000000001' already exists.")

The main disadvantage in this code is that "sql_command" is row-specific and must be recreated with each added row. (I ignore the fact that the arguments are explicitly typed, in spite of the relevant remark in the preface of this article, as this is only for the purpose of this demo.) But don't worry, Python's pyodbc library offers a better way that solves this problem:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
sql_command =   """
INSERT INTO Person VALUES (?, ?, ?, ?, ?, ?)
"""
try:
db_cursor.execute(sql_command, '000000001', 'Kohen', 'Aharon', 'Jerusalem', '2001-01-01', 1.80)
db_connection.commit()
except pyodbc.IntegrityError:
print("Row Id='000000001' already exists.")
try:
db_cursor.execute(sql_command, '000000002', 'Elboher', 'Ofer', 'End Of The World', '2002-02-02', 2.22)
db_connection.commit()
except pyodbc.IntegrityError:
print("Row Id='000000002' already exists.")
try:
db_cursor.execute(sql_command, '000000003', 'Aaa', 'Bbb', 'Ccc', '2003-03-03', 1.70)
db_connection.commit()
except pyodbc.IntegrityError:
print("Row Id='000000003' already exists.")

  • Update a row in a table.
  • 1
    2
    3
    4
    5
    
    sql_command =   """
    UPDATE Person SET FirstName=?, HeightMeters=? WHERE Id=?
    """
    db_cursor.execute(sql_command, 'Bb', 1.77, '000000003')
    db_connection.commit()

    • Delete a row from a table.

    1
    2
    3
    4
    5
    6
    7
    8
    
    sql_command =   """
    DELETE FROM Person WHERE FirstName=? AND HeightMeters=?
    """
    try:
    db_cursor.execute(sql_command, 'Bb', 1.77)
    db_connection.commit()
    except pyodbc.ProgrammingError as pe:
    print("Record intended for deletion is missing.")


    Selecting rows from a table

    • Select all columns of all rows: 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    
    sql_command =   """
    SELECT * FROM Person
    """
    db_cursor.execute(sql_command)
    rows = db_cursor.()
    print("---")
    for row in rows:
    for index, field in enumerate(row):
    title = ("Field #%u" % (index))
    sys.stdout.write("%s = %s | " % (title, field))
    print()
    print("---")

    Here's what's printed to the console by above code:

    1
    2
    3
    4
    
    ---
    Field #0 = 000000001 | Field #1 = Kohen | Field #2 = Aharon | Field #3 = Jerusalem | Field #4 = 2001-01-01 | Field #5 = 1.80 |
    Field #0 = 000000002 | Field #1 = Elboher | Field #2 = Ofer | Field #3 = End Of The World | Field #4 = 2002-02-02 | Field #5 = 2.22 |
    ---

    • Select some columns of all rows:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    
    sql_command =   """
    SELECT Id, LastName, City FROM Person
    """
    db_cursor.execute(sql_command)
    rows = db_cursor.()
    print("---")
    for row in rows:
    for index, field in enumerate(row):
    title = { 0: 'Id', 1: 'LastName', 2: 'City' }[index]
    sys.stdout.write("%s = %s | " % (title, field))
    print()
    print("---")

    Here's what's printed to the console by above code:

    1
    2
    3
    4
    
    ---
    Id = 000000001 | LastName = Kohen | City = Jerusalem |
    Id = 000000002 | LastName = Elboher | City = End Of The World |
    ---

    • Select and count some columns of some rows:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    
    sql_command =   """
    SELECT Id, LastName, City FROM Person WHERE City > ? AND HeightMeters > ?
    """
    db_cursor.execute(sql_command, 'End', 2.00)
    rows = db_cursor.()
    print("---")
    for row in rows:
    for index, field in enumerate(row):
    title = { 0: 'Id', 1: 'LastName', 2: 'City' }[index]
    sys.stdout.write("%s = %s | " % (title, field))
    print()
    print("---")

    1
    2
    3
    
    ---
    Id = 000000002 | LastName = Elboher | City = End Of The World |
    ---

    • Select and count some rows:

    1
    2
    3
    4
    5
    6
    7
    8
    
    sql_command =   """
    SELECT COUNT(*) AS person_count FROM Person WHERE City > ? AND HeightMeters > ?
    """
    db_cursor.execute(sql_command, 'End', 2.00)
    row = db_cursor.fetchone()
    print("---")
    print("Count of selected table rows: %u" % row.person_count)
    print("---")

    Here's what's printed to the console by above code:

    1
    2
    3
    
    ---
    Count of selected table rows: 1
    ---


    Closing the connection with the database

    1
    2
    3
    
    db_cursor.close()
    del db_cursor
    db_connection.close()

     


    You may email me, if you wish, to: ofer.elboher@gmail.com.

    Good luck.