Python and Data : MySQL as a data source for Python applications


python-logo@2xThis post will show you how to use Python to connect to a MySQL 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:

If you missed them, here are the other posts in the series

What Python package should I use?

logo-mysql-170x170_400x400Connecting to MySQL requires installing a Python package in your code that supports connections to SQL Server. In this post we use pymssql

pymssql runs on runs  on  Python 3.x: 3.3 or newer, Python 2.x: 2.6 or newer

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 pymssql and Select OK.

image

Connecting to the database

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

pymssql.connect(server= 'serverName', user='userName', password='password', database='databaseName')

  • server - identifies the server where mySQL is running. If you are running MySQL 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 MySQL. I have created a database called quizdb.
  • user and password- are the MySQL username and password that has permissions to log into the database and perform the desired actions.

Here is what that call looks like in my code

import pymssql
 
connection = pymssql.connect(server='localhost',                                user='sa',                                password='P@ssw0rd',                                database='quizdb')
print('Connected')

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 questions with the columns QuestionName, Description, CorrectAnswer, CategoryId. I would use a SQL statement similar to the following to add a row to the table.

INSERT INTO questions

( QuestionName, Description, CorrectAnswer, CategoryId)

VALUES

(‘CapitalCanada,’What is the capital of Canada?’,’Ottawa’,1)

Here’s a code example that will insert that record into our customers table using Python. I have deliberately provided the values for the columns as a parameter to avoid SQL Injection attacks.

import pymssql
 
connection = pymssql.connect(server='localhost',                                user='sa',                                password='P@ssw0rd',                                database='quizdb')
 
cursor = connection.cursor()  SQLCommand = ("INSERT INTO questions "                  "(QuestionName, Description, CorrectAnswer, CategoryId) "                  "VALUES (%s,%s,%s,%s)")
values = ('CapitalCanada','What is the capital of Canada?','Ottawa',2)
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 description, and correct answer information from the quiz database for a specific question I would use the following SELECT statement

SELECT description, correctanswer

FROM questions

WHERE questionName = ‘CapitalCanada’

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 pymssql 
connection = pymssql.connect(server='localhost',                                user='sa',                                password='P@ssw0rd',                                database='quizdb')
selectCursor = connection.cursor()
selectCommand = ("SELECT description, correctanswer "                  "FROM Questions "                  "WHERE questionName = %s")
values = ('CapitalCanada')
selectCursor.execute(selectCommand,values)
results = selectCursor.fetchone()
print("Question: " + results[0])
print("Answer:" + results[1])
 
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 pymssql
 
connection = pymssql.connect(server='localhost',                                user='sa',                                password='P@ssw0rd',                                database='quizdb')
selectCursor = connection.cursor()
selectCommand = ("SELECT description, correctanswer "                  "FROM Questions "                  "WHERE categoryid = %s")
values = ('2')
selectCursor.execute(selectCommand,values)
 
results = selectCursor.fetchone()
while results:     print("Question: " + results[0])     print("Answer:" + results[1])     results = selectCursor.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

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

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

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

Python and Django

  • Introducing Django
  • Building Models
  • Querying Models and Managing Migrations
  • URLs and Routing
  • Layouts and Forms
  • Deploying your application

QuickStart Python and MongoLab

Comments (0)

Skip to main content