Friday, May 17, 2024

I am starting a brief series of posts on manipulating a SQL Server database with Python. This is a beginner level tutorial for students wanting to get started with this activity. Python provides access to a large ecosystem of data science functions above the normal SQL Query results. We will eventually work our way up to these. But first we will start with the basics: creating an ODBC connection to a SQL Server database followed by a series of posts on the standard CRUD (Create, Read, Update, Delete) functions.

These examples were done using Windows 10. To follow along you will need to have Python installed (we are using Python 3) as well as SQL Server (it should not matter too much which version so long as it is fairly recent. We are using v16). The tutorial uses a build script which we have supplied on our Google drive. This creates a sample database called bookstore. Install this and then create an ODBC data source using the Windows ODBC data sources applet. If need be, download and install the odbc driver 17 from Microsoft first.

To connect your python scripts to SQL Server you need one more component, the Python package to connect to an ODBC data source: pyodbc. You may find you already have it installed but if not you can just install pyodbc using pip in the command window.

>pip install pyodbc

With the above in place you can adjust and run the following Python script to connect to the database. Modify the server to equal <your computer name>\<SQL Server instance name>. 

import pyodbc
# assumes bookstore database has been created in sql server
def open_database():
    """ Open connection to bookstore database using ODBC"""
    cnxn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
                      'Server=HP10ALL-IN-1\SQLEXPRAS2016A1;'
                      'Database=bookstore;'
                      'Trusted_Connection=yes;')
    print('\nConnected to SQL Server with ODBC');
    return cnxn  
# main program    
cnxn = open_database()
cnxn.close()
print("Database connection closed")

I have used Windows Authentication but you can use SQL Server authentication if you prefer. You just need to supply the user name and password in lieu of Trusted_Conection=yes.

Once this is working you can proceed to the next steps which we will provide in following posts: 1) read data, 2) add records, 3) update data, 4) delete records. After that we will extend the series to include DDL - creating and dropping databases and tables, etc. We will also cover creating and using a stored procedure.

Saturday, July 25, 2020

DAT/210 Wk2 - JDoodle





A very handy online code editor. As you can see it handles many languages. I find it great for those  short teaching scripts.

Thursday, November 2, 2017

We recently created a short course in using Visio 2016 to create ERD's. Visio 2016 does not have a lot of traditional database design tool capabilities such as forward engineering to create scripts but it is simple and rather elegant. And it is already widely installed. Below is the slide deck for the class.



Wednesday, October 11, 2017

We finished out Project Libre course. The first will run Saturday, November 18, in San Juan Capistrano. We were able to pretty much replicate our standard Microsoft Project Basic course. There were some features that Project Libre lacked but for the most part it does, in fact, provide the expected capabilities. It also uses the ribbon so the interface is familiar. You can see the slide deck for the course below. It is based on our similar Microsoft Project course. Note we have since renumbered the course as PRJ111XL.

Tuesday, October 10, 2017

Microsoft Project Accelerated

We have found many of our clients for Microsoft Project training that are in construction make little use of the features Microsoft makes available for resource management. So we created an accelerated course that does not go into resources in depth. And shortens a few other topics as well. This compresses the standard two day course sequence into a single day. We taught the first class a couple weeks ago in San Juan Capistrano and found it a good fit for these students. Below is the slide deck from SlideShare.


Wednesday, July 26, 2017

Database Glossary

Here is a link to a database glossary we compiled a while ago. You may find it a useful reference.

Database Glossary

---Dan