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

Artigo
03/11/2015

This 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 sources 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:

You can download Visual Studio Community for free.
Python Tools For Visual Studio (PTVS) is a free add on for Visual Studio
Instructions for how to install PTVS and a Python interpreter in Visual Studio so you can code Python in Visual Studio can be found here
You can get SQL Server Express for free

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

Right click your Virtual Environment and select Install Python Package

Enter the package name pypyodbc and Select OK.

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 Academy Part 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

Comments

Anonymous
March 28, 2016
The comment has been removed

Compartilhar via