Tag Archives: SQL

Easy SQL Query Counting in Django

I’ve become somewhat of a Django ninja during my time as GradeSolve’s developer (okay, a Django hacker…). One of the things that I have noticed as I have written several thousand lines of Django code is that SQL queries tend to be made pretty regularly–often, a little too regularly. Some of GradeSolve’s pages were loading a little slowly for my tastes, so I decided to do some basic SQL profiling to see where the trouble was.

The issue there was that django-debug-toolbar didn’t want to play nice, and I lacked the motivation to try to get it working. Instead, I re-invented the wheel a little bit and write a simple piece of middleware to print out the SQL execution time and number of SQL queries made for a particular request. Obviously, this is going to go away in the production version of GradeSolve (which is being released in eight days!), but for the time being, I like the extra information on my debug console.

Anyway, here’s the middleware class. Stick this in one of your project’s files:

from django.db import connection
class SqlPrintMiddleware(object):
    def process_response(self, request, response):
        sqltime = 0 # Variable to store execution time
        for query in connection.queries:
            sqltime += float(query["time"])  # Add the time that the query took to the total
 
        # len(connection.queries) = total number of queries
        print "Page render: " + unicode(sqltime) + "sec for " + unicode(len(connection.queries)) + " queries"
 
        return response

Now, in your settings.py file, add the path to the middleware class. For example, if your project’s name is gradesolve and your file’s name is middleware.py, your middleware classes setting would end up looking like:

MIDDLEWARE_CLASSES = (
    # ...
    'gradesolve.middleware.SqlPrintMiddleware',
    # ...
)

Happy (very simple) profiling!

Quotebook, a Grotesquely Over-complicated Version of a Program I Wrote for a Quiz

Last week in my video game development class (which is an introductory-level course on Python 3 with some focus on games), we had a quiz. We had to talk about some things we’d learned, describe the intricate differences between Python’s if, elif and else statements, then write a program. The program intrigued me: the goal was to “simulate a fortune cookie” and display one of five different, pre-determined strings at random.

My first reaction was relief that I was going to be able to write the program with little issue. My second reaction was how incredibly far I could take that idea. An entire social network materialized in my head; a place where people could go to share and discuss quotes or fortunes. Then I realized that I was crazy, but I did decide to make some changes to the program we’d written in my spare time:

  1. Create an online component that would allow anyone to submit quotes to the “fortune cookie”. The “cookie” has become a system that I call Quotebook, for lack of a better name.
  2. Create a Python client for some kind of bare-bones Quotebook API that would accomplish the same task as the quiz program, except after retrieving the quote from Quotebook.

I knew the Python part would be the easy part, so I fired up Visual Studio and got cracking. I used most of the default template but changed the top text and removed the menu. I kept the HeadLoginView and all the other assorted login-related items so I could use the Membership class to attribute submitted quotes to a user. I also added a database to the project and created a table called quotes. Quotes has four columns:

  1. id (int)
  2. byUser (nvarchar)
  3. timestamp (timestamp)
  4. quoteText (nvarchar)

On the homepage, I added a big TextBox and a submit button. Then, I wired it up to write to the database:

protected void btnSubmitQuote_Click(object sender, EventArgs e)
{
    if (HttpContext.Current.User.Identity.IsAuthenticated) // Make sure user is authed before writing bad things to DB
    {
        // Connect to the DB
        SqlConnection sconn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["messageDB"].ConnectionString);
        sconn.Open();
 
        // Create the command to insert the values into the database
        // PARAMETERS are used to STERILIZE all inputs.
        SqlCommand scomm = new SqlCommand("INSERT INTO quotes (byUser, quoteText) VALUES (@byuser, @quotetext)", sconn);
        scomm.Parameters.Add(new SqlParameter("@byuser", System.Data.SqlDbType.NVarChar));
        scomm.Parameters.Add(new SqlParameter("@quotetext", System.Data.SqlDbType.NVarChar));
        scomm.Parameters["@byuser"].Value = Membership.GetUser().UserName;
        scomm.Parameters["@quotetext"].Value = tbQuote.Text;
 
        // Commit to DB
        scomm.ExecuteNonQuery();
 
        // Close and refresh page.
        sconn.Close();
        Response.Redirect("Default.aspx");
    }
    else
    {
        lblError.Text = "
 
You must login or register before adding a quote!"; // Alert user
    }
}

There are a lot of points to cover here. First, the user state gets checked so quotes don’t get attributed to some kind of phantom (and so no exceptions get thrown!). I added a red, textless label to the area next to the submit button so that if the user isn’t logged in, he will get an error message. If he is logged in, the quote gets written to the database. I had to provide my own connection string in the web.config file. The connection string gets used to connect to the database, and a command gets created on that connection.

The command has two parameters: the text of the quote and the user’s name. Those are set through SQL parameters. Although I have no plans to SQL injection attack my own database, it is good practice to use parameters whenever user input is to be committed to a database. After the parameters get set, the data gets written and the connection gets closed.

With the input side of the Quotebook I/O taken care of, I set out to write the output. I wanted two different kinds of output: a mess of all the quotes in reverse chronological order on the homepage and the “bare-bones API” I mentioned earlier. Before I wrote either, I created an easy way to get data out of the database that I call the QuoteEnumerator class. I created a struct called Quote, which contains the quote text and the name of the quote submitter. QuoteEnumerator has a static method that returns a list of all the Quotes stored in the database. I implemented the two like this:

/// <summary>
/// Reads the DB and creates lists of quotes.
/// </summary>
public class QuoteEnumerator
{
    /// <summary>
    /// Obtains a list of quotes from the ASP.NET database.
    /// </summary>
    /// <returns></returns>
    public static List<Quote> GetQuotes()
    {
        List<Quote> retval = new List<Quote>();
 
        // Connect to DB and set up the query
        SqlConnection sconn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["messageDB"].ConnectionString);
        sconn.Open();
        SqlCommand scomm = new SqlCommand("SELECT * FROM quotes", sconn);
 
        // Read the database
        SqlDataReader sdr = scomm.ExecuteReader();
        if (sdr.HasRows)
        {
            while (sdr.Read())
            {
                // Iterate through the results
                Quote q = new Quote();
                q.QuoteSubmitter = sdr["byUser"].ToString();
                q.QuoteText = sdr["quoteText"].ToString();
 
                retval.Add(q);
            }
        }
 
        return retval;
    }
}
 
/// <summary>
/// Contains a quote once it is extracted from the DB.
/// </summary>
public struct Quote
{
    public string QuoteText { get; set; }
    public string QuoteSubmitter { get; set; }
}

The QuoteEnumerator.GetQuotes() method is a relatively simplistic database reading method. It utilizes a SqlDataReader to iterate through all the rows. For each row, it retrieves the quote text and quote submitter, then adds them to a list containing all the other quotes. It returns that list. Using GetQuotes made implementing the homepage side of the output easy.

// Default.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
    foreach (Quote q in QuoteEnumerator.GetQuotes())
    {
        // Append the quote to the page in a pretty-looking (?) way
        lblError.Text += string.Format("<div style='display:block; padding:2px; margin:0 auto; border:1px solid black; width:80%; text-align:center;'>{0}<br/><br/><span style='font-size:smaller'>Submitted by {1}</span></div><br>", q.QuoteText.Replace("\n", "<br/>"), q.QuoteSubmitter);
    }
}

The result is, uh, kinda stunning…

Quotebook Image

That comment was sure telling the truth!


I only had two obstacles left: implementing the API and implementing the Python script that would utilize it. The API came first. I created a page called getmessage.aspx and deleted all of the code from the ASPX page except the top line indicating that it was, indeed, ASPX. In the code behind, I came up with this little gem to write a random quote as a string to the page (and to give credit to the submitter):

protected void Page_Load(object sender, EventArgs e)
{
    // Grab a random quote
    List<Quote> allQuotes = QuoteEnumerator.GetQuotes();
    Quote chosen = allQuotes[new Random().Next(0, allQuotes.Count)];
 
    // Write the quote as the response text
    Response.Write(chosen.QuoteText + "\n\nSubmitted by " + chosen.QuoteSubmitter);
}

Lastly, I created a Python script to read from the API. It uses urllib, and given my limited Python knowledge, it is more than good enough! (Keep in mind that this is Python 3; my last Python post was written in Python 2)

# randomMessage.py
# Random message downloader python script
print("Fortune Cookie")
DLURL = "http://localhost:6642/getmessage.aspx" # Replace with yours
 
import urllib.request
while input("Enter Q to quit or press ENTER to continue: ").lower() != "q":
    # Open the API page
    response = urllib.request.urlopen(DLURL)
    data = response.read().decode("utf-8") # Download and decode to a string
 
    print("\n" + data, end="\n\n")

With that, I entered some quotes and fired my creation up!

Quotebook Python Script Running

The awesome thing about Python is how incredibly easy it is to build relatively useful programs like this one.


Sure, it’s seven or eight times more code than the school version, but it’s extensible.

Do you have any improvements for this? Additional features? Let me know!