I have a WebTest that I'm running using django-webtest against a database in memory.
# settings.py
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.sqlite3',
}
}
There's a piece of code that I just cannot run through the ORM, it runs 5x faster as straight SQL. Yes, I'm familiar with select_related, batch_select and prefetch_related. This is how my SQL code is executed in my view.
db = MySQLdb.connect(host=settings.DATABASES['default']['HOST'],
user=settings.DATABASES['default']['USER'],
passwd=settings.DATABASES['default']['PASSWORD'],
db=settings.DATABASES['default']['NAME'])
cursor = db.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("SELECT something FROM sometable WHERE somecondition = 'somevariable';")
count_queryset = cursor.fetchall()
cursor.close()
db.close()
When my test script gets to this point in the view it chokes out with the following error:
File "/srv/reports/views.py", line 473, in my_view
db=settings.DATABASES['default']['NAME'])
File "build/bdist.macosx-10.8-x86_64/egg/MySQLdb/__init__.py", line 81, in Connect
return Connection(*args, **kwargs)
File "build/bdist.macosx-10.8-x86_64/egg/MySQLdb/connections.py", line 187, in __init__
super(Connection, self).__init__(*args, **kwargs2)
OperationalError: (1049, "Unknown database ':memory:'")
I'm not sure why the in memory sqlite3 database works nicely through the ORM but not when I'm using MySQLdb. The same thing happens if I run the code through a unittest.TestCase, too. Any ideas?
As alecxe points out in comments, you can't use MySQLdb connections and cursors with a sqlite3 database. Here was my quick and dirty workaround, which probably isn't suitable for any code you're going to have in production. I replaced my connection string with the sqlite3 equivalent:
import sqlite3
db = sqlite3.connect("settings.DATABASES['default']['HOST']")
cursor = db.cursor() # No direct equivalent to MySQLdb.cursors.DictCursor
cursor.execute("SELECT something FROM sometable WHERE somecondition = 'somevariable';")
...