This 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:
- 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 MySQL Workbench to use as a client tool to access your MySQL database.
If you missed them, here are the other posts in the series
What Python package should I use?
Connecting 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
Right click your Virtual Environment and select Install Python Package
Enter the package name pymssql and Select OK.
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, …)
(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)
(‘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, …
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
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.
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')
results = selectCursor.fetchone() print("Question: " + results) print("Answer:" + results)
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) print("Answer:" + results) 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
- 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
- Handling Errors
- Introduction to Flask
- Creating a Web Interface
- Data Storage Locations
- Using Redis
- Using Redis and Flask on Azure
- Design of a Flask Application
- Designing Python Classes
- Introduction to Relational Databases
- Connecting to Relational Databases
- Layouts Using Jinja
- Introduction to Bootstrap
- Introducing Django
- Building Models
- Querying Models and Managing Migrations
- URLs and Routing
- Layouts and Forms
- Deploying your application