Tag Archives: SqlAlchemy

Python 101: Episode #34 – The SQLAlchemy Package

In this screencast, we learn about the popular SQLAlchemy package. SQLAlchemy is an Object Relational Mapper for Python that allows you to interface with databases in a “Pythonic” manner.

You can also read the chapter this video is based on here or get the book on Leanpub

Note: This video was recorded a couple of years ago, so there may be some minor API changes in SQLAlchemy.

Flask 101: Adding a Database

Last time we learned how to get Flask set up. In this article we will learn how to add a database to our music data website. As you might recall, Flask is a micro-web-framework. That means it doesn’t come with an Object Relational Mapper (ORM) like Django does. If you want to add database interactivity, then you need to add it yourself or install an extension. I personally like SQLAlchemy, so I thought it was nice that there is a ready-made extension for adding SQLAlchemy to Flask called Flask-SQLAlchemy.

To install Flask-SQLAlchemy, you just need to use pip. Make sure that you are in your activated virtual environment that we created in the first part of this series before you run the following or you’ll end up installing the extension to your base Python instead of your virtual environment:

pip install flask-sqlalchemy

Now that we have the Flask-SQLAlchemy installed along with its dependencies, we can get started creating a database! Continue reading Flask 101: Adding a Database

A Simple SqlAlchemy 0.7 / 0.8 Tutorial

A couple years ago I wrote a rather flawed tutorial about SQLAlchemy. I decided it was about time for me to re-do that tutorial from scratch and hopefully do a better job of it this time around. Since I’m a music nut, we’ll be creating a simple database to store album information. A database isn’t a database without some relationships, so we’ll create two tables and connect them. Here are a few other things we’ll be learning:

  • Adding data to each table
  • Modifying data
  • Deleting data
  • Basic queries

Continue reading A Simple SqlAlchemy 0.7 / 0.8 Tutorial

wxPython and SQLAlchemy: Loading Random SQLite Databases for Viewing

I was recently contacted about whether or not there were any wxPython applications out here that could present a SQLite database. As I understood it, they wanted to be able to introspect the database and view the tables, probably using the wx.grid.Grid widget. I find the Grid widget to be very powerful and also rather difficult to use. So I spent some time writing a super simple application that uses the ObjectListView widget instead. Continue reading wxPython and SQLAlchemy: Loading Random SQLite Databases for Viewing

Improving MediaLocker: wxPython, SQLAlchemy, and MVC

This blog ran an article about wxPython, SQLAlchemy, CRUD and MVC earlier this month. The program that we created in that post was dubbed “MediaLocker”, whether or not it was explicitly stated as such. Anyway, since then, I have received a couple comments about improving the program. One came from Michael Bayer, one of the creative minds behind SQLAlchemy itself and the other comments came from Werner Bruhin, a nice guy who haunts the wxPython mailing list, helping new users. So I went about creating an improved version of the code following their advice. Werner then improved it a bit more. So in this article, we will be looking at improving the code, first with my example and then with his. Enough talk though; let’s get to the meat of story! Continue reading Improving MediaLocker: wxPython, SQLAlchemy, and MVC

wxPython and SqlAlchemy: An Intro to MVC and CRUD

In this article, we will be creating a program to store our personal books or perhaps just the books you’ve read. It will demonstrate how to combine wxPython and SqlAlchemy, a SQL Object Relational Mapper (ORM). This program will also give you an introduction to the model-view-controller (MVC) and “create, read, update and destroy” (CRUD) methodologies. The aim is to show you how to create an application that can do the following:

  • Create a database (create)
  • Add records to that database (sort of create)
  • Display the records (read and view)
  • Modify records (update)
  • Delete records (destroy)

The order of this tutorial will follow MVC, so we will start with the model. The model is the basis for the GUI (the View) anyway, so it’s a good place to start. Continue reading wxPython and SqlAlchemy: An Intro to MVC and CRUD

SqlAlchemy ProgrammingError 42000 and MS SQL

I have been working on a software inventory script lately using SqlAlchemy on Windows XP to connect to a newly created table in a Microsoft SQL Server 2005 database. I created the table using Aqua Data Studio, logging in as the SQL Administrator (sa) and thought all was well until I tried to commit some data to the table. Here’s the screwy error that I received:

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the object "dbo.software" because it does not exist or you do not have permissions. (1088) (SQLExecDirectW)')

Continue reading SqlAlchemy ProgrammingError 42000 and MS SQL

Top Ten Articles of 2010

A lot of websites are doing year-end retrospectives this week, so I thought you might find it interesting to know which articles on this blog were the most popular this year. Below you will find links to each article along with the page view count I got from Google Analytics:

  1. A Simple Step-by-Step Reportlab Tutorial, 9,709 page views, posted 03/08/2010
  2. Another Step-by-Step SqlAlchemy Tutorial Part 1, 7,746 page views, posted 02/03/2010
  3. Another Step-by-Step SqlAlchemy Tutorial Part 2, 4,858 page views, posted 02/03/2010
  4. Manipulating PDFs with Python and pyPdf, 4,511 page views, posted 05/15/2010
  5. Python 101: Introspection, 4,473 page views, posted 10/14/2010
  6. wxPython: Grid Tips and Tricks, 3,476 page views, posted 04/04/2010
  7. wxPython: Creating a Simple MP3 Player, 3,401 page views, posted 04/20/2010
  8. Python and Microsoft Office – Using PyWin32, 3,323 page views, posted 07/16/2010
  9. wxPython and Threads, 3,183 page views, posted 05/22/2010

It would seem that SqlAlchemy and Reportlab are pretty popular topics. Are there any articles about either of these cool packages that you think I should write? As you can see, wxPython makes it into the top ten 3 times! What should I write about next regarding wxPython?

This upcoming year, I plan to write about some of the other GUI toolkits. Which one do you think I should do first? Tkinter, PySide, PyGUI or something else? What packages or standard libraries do you think I should cover? Feel free to let me know via the comments below or via my contact form (link at top). I’m looking forward to another year of Python tinkering and writing and I hope you are too! Thanks for your readership and encouragement this year!

SqlAlchemy: A Simple Tutorial for Elixir

Elixir is a lightweight declarative layer on top of SqlAlchemy that’s been around since 2006, well before SqlAlchemy released their own built-in Declarative syntax. Elixir was created by a collaboration between Jonathan LaCour, Daniel Haus and Gaëtan de Menten who had a passion for making SqlAlchemy even easier to use. In this tutorial, we’ll look at how to to create our own database with Elixir and how to communicate with a pre-existing database. Continue reading SqlAlchemy: A Simple Tutorial for Elixir

SqlAlchemy and Microsoft Access

Update (10/12/2010) – One of my alert readers told me that SqlAlchemy 0.6.x currently does NOT support the Access dialect. Read here for more info.

A year or two ago, I was asked to transfer some data from some old Microsoft Access files to our Microsoft SQL Server. Since I enjoy using SqlAlchemy, I decided to see if it supported Access. The documentation at the time was pretty unhelpful in this regard, but it did seem to be possible and I found one thread about it on SqlAlchemy’s Google group. Continue reading SqlAlchemy and Microsoft Access