Python and Data : SQL Server as a data source for Python applications

  • Article

python-logo@2xThis post will show you how to use Python to connect to a SQL Server database, save and retrieve data.

I ( @HockeyGeekGirl ) recently recorded some courses with Christopher Harrison ( @GeekTrainer ) on Microsoft Virtual Academy about coding with Python. During that series of courses we explored several different data sources. Sometimes it was difficult to find good code examples and documentation on how to connect to those data SQLServerNoVersionsources with Python. So I have put together this series on Python and Data to help others who may be trying to work with different data sources using Python

This blog post will explain

  • What Python package should I use?
  • Connecting to the database
  • Inserting a row
  • Retrieving a single row
  • Retrieving multiple rows
  • Additional Python resources

The examples in this post are written using CPython 3.4 in Visual Studio and Python Tools for Visual Studio. If you want to use the same tools:

Python: Fill in the Gaps

What Python package should I use?

Connecting to SQL Server requires installing a Python package in your code that supports connections to SQL Server. In this post we use pypyodbc.

pypyodbc runs on runs  on PyPy / CPython / Iron Python , Python 3.4 / 3.3 / 3.2 / 2.4 / 2.5 / 2.6 / 2.7 , Win / Linux / Mac , 32 / 64 bit

To install this package in a Visual Studio Python project, create a virtual environment in your solution in Solution Explorer for one of the supported versions of Python

image

Right click your Virtual Environment and select Install Python Package

image

Enter the package name pypyodbc and Select OK.

image

Connecting to the database

In order to connect to the database you use the connect method of the Connection object.

pypyodbc.connect(‘Driver = {drivername};Server=servername; Database=databaseName; uid=username;pwd=password)

  • Driver - identifies the driver you wish to use to connect to the database, the correct driver to use will depend on which database product you are using. Since we are using SQL Server, our driver should be SQL Server
  • Server - identifies the server where SQL Server is running. If you are running SQL Server on the same PC where you are running your Python code the server name will be localhost
  • Database - is the name of your database in SQL Server. I have created a database called testdb.
  • uid and pwd - are the SQL Server username and password that has permissions to log into the database and perform the desired actions. In this example I am logging in with the default sys admin password sa.

In this example we assume you are using Mixed Mode authentication on your SQL Server database instead of Windows authentication/Integrated security. If you are not sure what form of authentication your SQL Server installation is using, check out the MSDN article Change Server Authentication Mode

Here is what that call looks like in my code

 import pypyodbc

 connection = pypyodbc.connect('Driver={SQL Server};' 

                                  'Server=localhost;' 

                                  'Database=testdb;' 

                                  'uid=sa;pwd=P@ssw0rd') 

 connection.close()

Inserting a record

In order to insert a record you need to

  • declare a cursor.
  • pass the SQL Statement you wish to execute to the cursor using the execute method.
  • save your changes using the commit method of the connection or cursor

If you need to pass any values to your SQL statement, you can represent those in your SQL statement using a ? then pass in an array containing the values to use for the parameters when you call the execute method of your cursor

In SQL we insert a row into a database with the INSERT statement

INSERT INTO tablename

(columnName1, columndName2, columndName3, …)

VALUES

(value1, value2, value3, …)

For example. If I have a table called customers with the columns customerid, firstname, lastname, city. On my customers table customerid is an IDENTITY column that assigns an id to any new record inserted automatically. Therefore, when I insert a new customer record I don’t need to specify a value for customerid.

INSERT INTO customers

( lastname, firstname, city)

VALUES

(‘Susan’,’Ibach’,’Toronto’)

Here’s a code example that will insert that record into our customers table using Python

 import pypyodbc connection = pypyodbc.connect('Driver={SQL Server};' 

                                  'Server=localhost;' 

                                  'Database=testdb;' 

                                  'uid=sa;pwd=P@ssw0rd')  

 cursor = connection.cursor() SQLCommand = ("INSERT INTO Customers " 

                   "(firstName, lastName, city) " 

                   "VALUES (?,?,?)") 

 Values = ['Susan','Ibach','Toronto'] cursor.execute(SQLCommand,Values) connection.commit() connection.close()

Retrieving a single row

If you want to retrieve a single row from a database table you use the SQL SELECT command.

SELECT columnname1, columnname2, columndname3, …

FROM tablename

WHERE columnnamex = specifiedvalue

for example if I want to retrieve the firstname, lastname and city information for the customer with a customer id of 2 you would use the following SELECT statement

SELECT firstname, lastname, city

FROM customers

WHERE customerid = 2

To execute that command with Python I use a cursor and the execute statement the same way I executed the insert command.  After I execute the command I need to call the fetchone() method of the cursor to populate an array with the values returned by the SELECT statement. The first row of the array will contain the first column specified in the select statement. The second row of the array will contain the second column specified in the select statement and so on.

 import pypyodbc connection = pypyodbc.connect('Driver={SQL Server};' 

                                  'Server=localhost;' 

                                  'Database=testdb;' 

                                  'uid=sa;pwd=P@ssw0rd') cursor = connection.cursor() SQLCommand = ("SELECT firstname, lastname, city " 

                 "FROM customers " 

                 "WHERE customerid = ?") Values = [2] cursor.execute(SQLCommand,Values) results = cursor.fetchone() print("Your customer " + results[0] + " " + results[1] + " lives in " + results[2]) connection.close()

Retrieving multiple rows

If your select statement will retrieve multiple rows, you can simply move your fetchone() method call into a loop to retrieve all the rows from the command.

 import pypyodbc connection = pypyodbc.connect('Driver={SQL Server};' 

                                  'Server=localhost;' 

                                  'Database=testdb;' 

                                  'uid=sa;pwd=P@ssw0rd') cursor = connection.cursor()  

 SQLCommand = ("SELECT customerid, firstname, lastname, city " 

                 "FROM customers") cursor.execute(SQLCommand) results = cursor.fetchone() while results: 

      print ("Your customer " +  str(results[0]) + " " + results[1] + " lives in " + results[2]) 

      results = cursor.fetchone() connection.close()

Additional Python resources

If you want to learn more about Python check out the learning to code with Python series on Microsoft Virtual AcademyPart 1 - Introduction to Coding with Python

  • Displaying Text
  • String Variables
  • Storing Numbers
  • Working with Dates and Times
  • Making Decisions with Code
  • Complex Decisions with Code
  • Repeating Events
  • Remembering Lists
  • How to Save Information in Files
  • Functions
  • Handling Errors

Part 2 – Introduction to Creating Websites Using Python and Flask

  • Introduction to Flask
  • Creating a Web Interface
  • Data Storage Locations
  • Using Redis
  • Using Redis and Flask on Azure

Part 3 - Python, SQL and Flask

  • Design of a Flask Application
  • Designing Python Classes
  • Introduction to Relational Databases
  • Connecting to Relational Databases
  • Layouts Using Jinja
  • Introduction to Bootstrap

QuickStart Python and MongoLab

Coming June 17th, 2015 live at Microsoft Virtual Academy: Python and Django available on demand approximately two weeks after the live broadcast