Posts tagged with Python

SQLAlchemy and "Lost connection to MySQL server during query"

Published at July 24, 2016 | Tagged with:

... a weird behaviour when pool_recycle is ignored

Preface: This is quite a standard problem for apps/websites with low traffic or those using heavy caching and hitting the database quite seldom. Most of the articles you will find on the topic will tell you one thing - change the wait_timeout setting in the database. Unfortunately in some of the cases this disconnect occurs much earlier than the expected wait_timeout (default ot 8 hours). If you are in one of those cases keep reading.

This issue haunted our team for weeks. When we first faced it the project that was raising it was still in dev phase so it wasn't critical but with getting closer to the release data we started to search for solution. We have read several articles and decided that pool_recycle is our solution.

Adding pool_recycle: According to SQL Alchemy's documentation pool_recycle "causes the pool to recycle connections after the given number of seconds has passed". Nice, so if you recycle the connection in intervals smaller that the await_timeout the error above should not appear. Let's try it out:

import time

from sqlalchemy.engine import create_engine

url = 'mysql+pymysql://user:pass@'
engine = create_engine(url, pool_recycle=1).connect()

query = 'SELECT NOW();'

while True:
    print('Q1', engine.execute(query).fetchall())
    engine.execute('SET wait_timeout=2')
    print('Q2', engine.execute(query).fetchall())

So what does the code do - we create a connection to a local MySQL server and state that it should be recycled every second(line 7). Then we execute a simple query (line 12) just to verify that the connection is working.
We set the wait_timeout to 2 second and wait for 3. At this stage the connection to the server will timeout, but SQL Alchemy should recycle it, so the last query should be executed successfully and the loop should continue.
Unfortunately the results looks like:

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError)
(2013, 'Lost connection to MySQL server during query') [SQL: 'SELECT NOW();']

Wait, what happened, why is not the connection recycled?

Solution: Well, as with all such problems the solution was much simpler compared to the time it took us to find it (we fought with this for days). The only change that solved it was on line 7:

engine = create_engine(url, pool_recycle=1)

# the result
Q1 [(datetime.datetime(2016, 7, 24, 20, 51, 41),)]
Q2 [(datetime.datetime(2016, 7, 24, 20, 51, 44),)]
Q1 [(datetime.datetime(2016, 7, 24, 20, 51, 44),)]
Q2 [(datetime.datetime(2016, 7, 24, 20, 51, 47),)]

Have you spot the difference? We are not calling the connect() method any more.

Final words: To keep it honest, I don't know why this solved the issue. Hopefully someone more familiar with SQL Alchemy will come with a reasonable explanation for it. The bad part is that the examples in the official docs are using "connect". So it is either a bug or a bad documentation. I will send this article to SQL Alchemy's Twitter account so hopefully we will see some input from them. Till then, if any of you have an idea explanation about the behaviour I'll be happy to hear it.

PyCon Sweden 2015

Published at May 19, 2015 | Tagged with: ,

In a few words PyCon Sweden 2015 was awesome. Honestly, this was my first Python conference ever but I really hope it won't be the last.

Outside the awesome talks and great organisation it was really nice to spend some time with similar minded people and talk about technology, the universe and everything else. I have met some old friends and made some new ones but lets get back to the talk. Unfortunately I was not able to see all of them but here is a brief about those I saw and found really interesting:

It all started with Ian Ozsvald and his awesome talk about "Data Science Deployed" (slides / video). The most important point here were:

  • log everything
  • think about data quality, don't use everything just what you need
  • think about turning data into business values
  • start using your data

Then Rebecca Meritz talked about "From Explicitness to Convention: A Journey from Django to Rails" (slides / video). Whether the title sounds a bit contradictive this was not the usual Django vs Rails talk. At least to me it was more like a comparison between the two frameworks, showing their differences, weak and strong sides. Whether I am a Django user, I am more and more keen to the position that none of the frameworks is better than the other one, they are just two different approaches for building great web apps.

Flavia Missi and "Test-Driven-Development with Python and Django" (slides / video). TDD will help you have cleaner, better structured and easy to maintian code. If you are not doing it the best moment to start is now. Whether it is hard at the beginning you will pretty soon realise how beneficial it is. Especially if someone pushed a bad commit and the tests saved your ass before the code goes to production.

Later Dain Nilsson talked about "U2F: Phishing-proof two-factor authentication for everyone" (video). Whether I don't use two-factor authentication at the moment I am familiar with the concept and I really like it. The U2F protocol looks like a big step towards making it more applicable over different tools and applications and the key holding devices are more and more accessible nowadays. Maybe it is time for me to get one )))

The second day started with Kate Heddleston who talked about ethics in computer programming (video). About how social networks can be used as a tool for ruining peoples lifes and that we as a developers should take a responsibility and work towards making the internet a safer place for everyone. A place where you can have your privacy and have protection if harassed. It is a big problem which won't be solved in a night, but talking about it is the first step towards solving it.

"Why Django Sucks" by Emil Stenström. Don't rush into raging cause this was one of best talks at the conference. Emil showed us the parts in Django where he sees flaws and that need improvement. The main point was the lack of common template language between Django and Javascript and what possible solutions are available or can be made.

Daniele Sluijters reminded us how easy is to work with "Puppet and Python" (video). No more fighting with Ruby, you can easily use your favorite language to build your own tools and helpers

Dennis Ljungmark and "Embedded Python in Practice". The last time I programmed embedded devices was 15 years ago as a part of short course in the high school. Dennis' work is much more complex than what I did then but his talk reminded me of things that are applicable to general programming. Whether using non-embedded systems we often have much more memory and processing power available that does not mean that we should waste it. So think when you code - RAM is not endless and processors are not that fast as we often wish. Also don't forget that Exceptions wil sooner or later occur so make your code ready to handle them.

"How to Build a Python Web Application with Flask and Neo4j" (video) by Nicole White. Well I have heard about Neo4J, but I have never used it or seen it in action so this one was really exciting. Neo4J offers you a whole new perspective about building databases and relations between objects but it is much far from panacea. Actually I can see it it is more like a special tool then as a general replacement of a relation database but it still worths to be tried. Oh, and the Neo4J browser - totally awesome.

In the lightning talks Tome Cvitan talked about Faker. If you are still not familiar with it now is a good time to try it. Especially if you are writing tests.

At the final Kenneth Reitz told us about "Python for Humans"(video). About the not that obvious things in Python and what solutions are out there. And also about The Hitchhiker’s Guide to Python! a great place for beginners and not only and shared the idea to make Python easier and more welcoming by introducing better tools for higher level of operations.

Finally I want to thank to everyone - the organisers, the speakers, the audience and practically everyone who was a part of the conference. Without you it would be the same (or be at all). Thanks, keep up the good work and hopefully we will sea each other again.

P.S. Have I mentioned that the whole conference was recorded on video so hopefully we will see be able to see all the talks pretty soon. I will try to keep this post updated with the links to the videos and/or slides when they become available. Of course if you know about any published slides from the conference that are not linked here please let me know.

The full set of videos are available at PyCon Sweden's Youtube channel.

Django, pytz, NonExistentTimeError and AmbiguousTimeError

Published at March 29, 2015 | Tagged with: , , ,

Brief: In one of the project I work on we had to convert some old naive datetime objects to timezone aware ones. Converting naive datetime to timezone aware one is usually a straightforward job. In django you even have a nice utility function for this. For example:

import pytz
from django.utils import timezone

timezone.make_aware(datetime.datetime(2012, 3, 25, 3, 52),
# returns datetime.datetime(2012, 3, 25, 3, 52, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>)

Problem: You can use this for quite a long time until one day you end up with something like this:

timezone.make_aware(datetime.datetime(2012, 3, 25, 2, 52),

# which leads to
Traceback (most recent call last):
  File "", line 1, in 
  File "/home/ilian/venvs/test/lib/python3.4/site-packages/django/utils/", line 358, in make_aware
    return timezone.localize(value, is_dst=None)
  File "/home/ilian/venvs/test/lib/python3.4/site-packages/pytz/", line 327, in localize
    raise NonExistentTimeError(dt)
pytz.exceptions.NonExistentTimeError: 2012-03-25 02:52:00

Or this:

timezone.make_aware(datetime.datetime(2012, 10, 28, 2, 52),

Traceback (most recent call last):
  File "", line 1, in 
  File "/home/ilian/venvs/test/lib/python3.4/site-packages/django/utils/", line 358, in make_aware
    return timezone.localize(value, is_dst=None)
  File "/home/ilian/venvs/test/lib/python3.4/site-packages/pytz/", line 349, in localize
    raise AmbiguousTimeError(dt)
pytz.exceptions.AmbiguousTimeError: 2012-10-28 02:52:00

Explanation: The reason for the first error is that in the real world this datetime does not exists. Due to the DST change on this date the clock jumps from 01:59 standard time to 03:00 DST. Fortunately (or not) pytz is aware of the fact that this time is invalid and will throw the exception above. The second exception is almost the same but it happens when switching from summer to standard time. From 01:59 DST the clock shifts to 01:00 standard time, so we end with a duplicate time.

Why has this happened(in our case)? Well we couldn't be sure how exactly this one got into our legacy data but the assumption is that at the moment when the record was saved the server has been in different timezone where this has been a valid time.

Solution 1: This fix is quite simple, just add an hour if the exception occurs.

    date = make_aware(
        datetime.fromtimestamp(date_time, timezone=pytz.timezone('Europe/Stockholm'))
except (pytz.NonExistentTimeError, pytz.AmbiguousTimeError):
    date = make_aware(
        datetime.fromtimestamp(date_time) + timedelta(hours=1),

Solution 2: Instead of calling make_aware call timezone.localize directly.

    date = make_aware(
        datetime.fromtimestamp(date_time, timezone=pytz.timezone('Europe/Stockholm'))
except (pytz.NonExistentTimeError, pytz.AmbiguousTimeError):
    timezone = pytz.timezone('Europe/Stockholm')
    date = timezone.localize(datetime.fromtimestamp(date_time), is_dst=False)

The second solution probably needs some explanation. First lets check what make_aware does. The code bellow is take from Django's sourcecode as it is in version 1.7.7

def make_aware(value, timezone):
    Makes a naive datetime.datetime in a given time zone aware.
    if hasattr(timezone, 'localize'):
        # This method is available for pytz time zones.
        return timezone.localize(value, is_dst=None)
        # Check that we won't overwrite the timezone of an aware datetime.
        if is_aware(value):
            raise ValueError(
                "make_aware expects a naive datetime, got %s" % value)
        # This may be wrong around DST changes!
        return value.replace(tzinfo=timezone)    

To simplify it, what Django does is to use the localize method of the timezone object(if it exists) to convert the datetime. When using pytz this localize method takes two arguments: the datetime value and is_dst. The last argument takes three possible values: None, False and True. When using None and the datetime matches the moment of the DST change pytz does not know how to handle the datetime and you get one of the exceptions shown above. False means that it should convert it to standard time and True that it should convert it to summer time.

Why isn't this fixed in Django? The simple answer is "because this is how it should work". For a bit longer check the respectful ticket.

Reminder: Do not forget that the "fix" above does not actually care whether the original datetime is during DST or not. In our case this was not criticla for our app, but in some other cases it might be, so use it carefully.

Thanks: Special thanks to Joshua who correctly pointed out in the comments that I have missed the AmbiguousTimeError in the original post which made me to look a bit more in the problem, research other solutions and update the article to its current content.

Python Interview Question and Answers

Published at April 28, 2013 | Tagged with:

Update 2: You can also check the follow up post Django interview questions.

For the last few weeks I have been interviewing several people for Python/Django developers so I thought that it might be helpful to show the questions I am asking together with the answers. The reason is ... OK, let me tell you a story first.
I remember when one of my university professors introduced to us his professor - the one who thought him. It was a really short visit but I still remember one if the things he said. "Ignorance is not bad, the bad thing is when you do no want to learn."
So back to the reason - if you have at least taken care to prepare for the interview, look for a standard questions and their answers and learn them this is a good start. Answering these question may not get you the job you are applying for but learning them will give you some valuable knowledge about Python.
This post will include the questions that are Python specific and I'll post the Django question separately.

  1. How are arguments passed - by reference of by value?
    The short answer is "neither", actually it is called "call by object” or “call by sharing"(you can check here for more info). The longer one starts with the fact that this terminology is probably not the best one to describe how Python works. In Python everything is an object and all variables hold references to objects. The values of these references are to the functions. As result you can not change the value of the reference but you can modify the object if it is mutable. Remember numbers, strings and tuples are immutable, list and dicts are mutable.
  2. Do you know what list and dict comprehensions are? Can you give an example?
    List/Dict comprehensions are syntax constructions to ease the creation of a list/dict based on existing iterable. According to the 3rd edition of "Learning Python" list comprehensions are generally faster than normal loops but this is something that may change between releases. Examples:
    # simple iteration
    a = []
    for x in range(10):
    # a == [0, 2, 4, 6, 8, 10, 12, 14, 16, 18]
    # list comprehension
    a = [x*2 for x in range(10)]
    # dict comprehension
    a = {x: x*2 for x in range(10)}
    # a == {0: 0, 1: 2, 2: 4, 3: 6, 4: 8, 5: 10, 6: 12, 7: 14, 8: 16, 9: 18}
  3. What is PEP 8?
    PEP 8 is a coding convention(a set of recommendations) how to write your Python code in order to make it more readable and useful for those after you. For more information check PEP 8.
  4. Do you use virtual environments?
    I personally and most(by my observation) of the Python developers find the virtual environment tool extremely useful. Yeah, probably you can live without it but this will make the work and support of multiple projects that requires different package versions a living hell.
  5. Can you sum all of the elements in the list, how about to multuply them and get the result?
    # the basic way
    s = 0
    for x in range(10):
        s += x
    # the right way
    s = sum(range(10))
    # the basic way
    s = 1
    for x in range(1, 10):
        s = s * x
    # the other way
    from operator import mul
    reduce(mul, range(1, 10))

    As for the last example, I know Guido van Rossum is not a fan of reduce, more info here, but still for some simple tasks reduce can come quite handy.
  6. Do you know what is the difference between lists and tuples? Can you give me an example for their usage?
    First list are mutable while tuples are not, and second tuples can be hashed e.g. to be used as keys for dictionaries. As an example of their usage, tuples are used when the order of the elements in the sequence matters e.g. a geographic coordinates, "list" of points in a path or route, or set of actions that should be executed in specific order. Don't forget that you can use them a dictionary keys. For everything else use lists.
  7. Do you know the difference between range and xrange?
    Range returns a list while xrange returns a generator xrange object which takes the same memory no matter of the range size. In the first case you have all items already generated(this can take a lot of time and memory) while in the second you get the elements one by one e.g. only one element is generated and available per iteration. Simple example of generator usage can be find in the problem 2 of the "homework" for my presentation Functions in Python
  • Tell me a few differences between Python 2.x and 3.x
    There are many answers here but for me some of the major changes in Python 3.x are: all strings are now Unicode, print is now function not a statement. There is no range, it has been replaced by xrange which is removed. All classes are new style and the division of integers now returns float.
  • What are decorators and what is their usage?
    According to Bruce Eckel's Introduction to Python Decorators "Decorators allow you to inject or modify code in functions or classes". In other words decorators allow you to wrap a function or class method call and execute some code before or after the execution of the original code. And also you can nest them e.g. to use more than one decorator for a specific function. Usage examples include - logging the calls to specific method, checking for permission(s), checking and/or modifying the arguments passed to the method etc.
  • The with statement and its usage.
    In a few words the with statement allows you to executed code before and/or after a specific set of operations. For example if you open a file for reading and parsing no matter what happens during the parsing you want to be sure that at the end the file is closed. This is normally achieved using the try... finally construction but the with statement simplifies it usin the so called "context management protocol". To use it with your own objects you just have to define __enter__ and __exit__ methods. Some standard objects like the file object automatically support this protocol. For more information you may check Understanding Python's "with" statement.
  • Well I hope this will be helpful, if you have any question or suggestion feel free to comment.

    Update: Due to the lots of comments on Reddit and LinkedIn, I understood that there is some misunderstanding about the post. First, the questions I have published are not the only ones I ask, the interview also includes such related to general programming knowledge and logical thinking. Second the questions above help me get a basic understanding of your Python knowledge but they are not the only think that makes my decision. Not answering some of them does not mean that you won't get the job, but it may show me on which parts we should to work.

    Simple Site Checker and the User-Agent header

    Published at Oct. 22, 2012 | Tagged with: , , , ,

    Preface: Nine months ago(I can't believe it was that long) I created a script called Simple Site Checker to ease the check of sitemaps for broken links. The script code if publicly available at Github. Yesterday(now when I finally found time to finish this post it must be "A few weeks ago") I decided to run it again on this website and nothing happened - no errors, no warning, nothing. Setting the output level to DEBUG showed the following message "Loading sitemap ..." and exited.
    Here the fault was mine, I have missed a corner case in the error catching mechanism i.e. when the sitemap URL returns something different from "200 OK" or "500 internal server error". Just a few second and the mistake was fix.

    Problem and Solution: I ran the script again and what a surprise the sitemap URL was returning "403 Forbidden". At the same time the sitemap was perfectly accessible via my browser. After some thinking I remembered about that some security plugins block the access to the website if there is not User-Agent header supplied. The reason for this is to block the access of simple script. In my case even an empty User-Agent did the trick to delude the plugin.

                                    headers={'User-Agent': USER_AGENT}))

    Final words: As a result of the issue mention above one bug in simple site checker was found fixed. At the same time another issue about missing status and progress was raised, more details can be found at Github but in a few words an info message was added to each processed URL to indicate the progress.

    If you have any ideas for improvement or anything else feel free to comment, create issues and/or fork the script.

    Automation, Fabric and Django - presentation

    Published at Oct. 3, 2012 | Tagged with: , , , , ,

    As a follow up post of Automated deployment with Ubuntu, Fabric and Django here are the slides from my presentation on topic "Automation, Fabric and Django". Unfortunately there is no audio podcast but if there is interest I can add some comments about each slide as part of this post.

    Automation - fabric, django and more from Ilian Iliev

    If there is anything that need explanation feel free to ask.

    Django project file structure

    Published at Sept. 24, 2012 | Tagged with: , ,

    As I promised in Automated deployment with Ubuntu, Fabric and Django I will use this post to explain the file structure that I use for my Django projects and what I benefit from it. So here is my project directory tree.

    The structure

    |-- bin
    |-- include
    |-- lib
    |-- local
    |-- src
       |-- .git
       |-- .gitignore
       |-- required_packages.txt
       |-- media
       |-- static
       |-- &lt;project_name&gt;
       |   |-- &lt;project_name&gt;
       |   |   |--
       |   |   |-- settings
       |   |   |   |--
       |   |   |   |-- &lt;environment_type&gt;.py
       |   |   |   |--
       |   |   |-- templates
       |   |   |--
       |   |   |--
       |   |--
       |   |--
       |-- &lt;project_name&gt;.development.nginx.local.conf
       |-- &lt;project_name&gt;.&lt; environment_type&gt;.nginx.uwsgi.conf
       |-- &lt;project_name&gt;.&lt; environment_type&gt;.uwsgi.conf


    At the top I have a directory named as the project and virtual environment inside of it. The benefit from it is complete isolation of the project from the surrounding projects and python packages installed at OS level and ability to install packages without administrator permissions. It also provides an easy way to transfer the project from one system to another using a requirements file.
    The src folder is where I keep everything that is going to enter the version control project source, requirements files, web server configuration etc.
    My default .gitignore is made to skip the pyc-files, the PyDev files and everything in the static and media directories.
    The media directory is where the MEDIA_ROOT settings point to, respectively static is for the STATIC_ROOT.
    All required packages with their version are placed in required_packages.txt so we can install/update them with a single command in the virtual environment.
    In a directory with the name of the project is where the python code resides. Inside it the project structure partly follows the new project layout introduced in Django 1.4.
    The big difference here is the settings part. It is moved as a separate module where all common/general settings are place in

    . We have one file for each possible environment(development, staging, production) with the environment specific settings like DEBUG and TEMPLATE_DEBUG. In are placed the settings specific for the machine where the project is running. This file should not go into the repository as this is where the access database password and API keys should reside.
    I having one local Nginx configuration file because I use the webserver to serve the static files when working locally this is the <project_name>.development.nginx.local.conf.
    For each environment there is also a couple of configuration files - one for the web server(<project_name>.< environment_type>.nginx.uwsgi.conf) and one for the uwsgi(<project_name>.< environment_type>.uwsgi.conf). I make symbolic links pointing to these files so any changes made are automatically pushed/pulled via version control, I only have to reload the configuration. There is no option to change something in the configuration at one station and to forget to transfer it to the rest.


    • Complete isolation from other projects using virtual environment
    • Easy to transfer and update packages on other machines thanks to the pip requirements file
    • Media/static files outside the source directory for higher security
    • Web server/uWSGI configuration as part of the repository for easier and error proof synchronization

    Probably it have some downside(I can not think any of these now but you never know) so if you think that this can be improved feel free to share your thoughts. Also if there is anything not clear enough, just ask me, I will be happy to clear it.

    Automated deployment with Ubuntu, Fabric and Django

    Published at Sept. 18, 2012 | Tagged with: , , , , , ,

    A few months ago I started to play with Fabric and the result was a simple script that automates the creation of a new Django project. In the last months I continued my experiments and extended the script to a full stack for creation and deployment of Django projects.

    As the details behind the script like the project structure that I use and the server setup are a bit long I will keep this post only on the script usage and I will write a follow up one describing the project structure and server.

    So in a brief the setup that I use consist of Ubuntu as OS, Nginx as web server and uWSGI as application server. The last one is controlled by Upstart. The script is available for download at GitHub.
    In a wait for more detailed documentation here is a short description of the main tasks and what they do:


    • Creates a new virtual environment
    • Installs the predefined packages(uWSGI, Django and South))
    • Creates a new Django project from the predefined template
    • Creates different configuration files for development and production environment
    • Initializes new git repository
    • Prompts the user to choose a database type. Then installs database required packages, creates a new database and user with the name of the project and generates the local settings file
    • Runs syncdb(if database is selected) and collectstatic


    • installs the required packages such as Nginx, PIP, GCC etc
    • prompts for a database type and install it
    • reboots the server as some of the packages may require it

    Once you have a ready server and a project just use this the following task to deploy it to the server. Please have in mind that it should be used only for initial deployment.


    • Creates a new virtual environment
    • Clones the project from the repository
    • Installs the required packages
    • Creates symbolic links to the nginx/uwsgi configuration files
    • Asks for database engine, creates new DB/User with the project name and updates the settings file
    • Calls the update_project task


    • Updates the source code
    • Installs the required packages(if there are new)
    • Runs syncdb, migrate and collect static
    • Restart the Upstart job that runs the uWSGI and the Nginx server

    The script is still in development so use it on your own risk. Also it reflects my own idea of server/application(I am planning to describe it deeper in a follow up post) setup but I would really like if you try it and give a feedback. Feel free to fork, comment and advice.

    Next page ⋙