Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Python Using Databases in Python Meet Peewee Queries Are Your Friend

Posted by Nicolas Hampton
Nicolas Hampton
44,638 Points

Writing tests for the ORM

So, after completing the Python Testing course, I came back to these lessons to try and write tests for each model we make, and the database in general. However, I'm running into a problem I haven't been able to troubleshoot...

For some reason, I'm able to create multiple identical instances of a model that is using unique fields, without ever raising an IntegrityError.

Here's my simple user model in models.py:

from peewee import *

DATABASE = SqliteDatabase('users.db')

class User(Model):
    username = CharField(unique=True)
    email = CharField(unique=True)

    class Meta:
        database = DATABASE

And here are my tests in tests.py:

import unittest

import models

class DatabaseTests(unittest.TestCase):

    def setUp(self):
        models.DATABASE.connect()
        models.DATABASE.create_table(models.User, safe=True)

    def tearDown(self):
        try:
            models.DATABASE.drop_table(models.User)
        except:
            pass
        models.DATABASE.close()

    def test_check_user_table(self):
        assert models.User.table_exists()    

    def test_safe(self):
        with self.assertRaises(models.OperationalError):
            models.DATABASE.create_table(models.User)

    def test_drop_table(self):
        models.DATABASE.drop_table(models.User)
        assert models.User.table_exists() == False     


class UserTableTests(unittest.TestCase):

    def setUp(self):
        models.DATABASE.connect()
        try:
            models.DATABASE.create_table(models.User)
        except:
            pass
        models.User.create(
                username='testUsername',
                email='testEmail@testEmail.com')
        models.DATABASE.close()

    def tearDown(self):
        models.User.delete().where(models.User.username=='testUsername').execute()

    def test_create_username(self):
        user = models.User.get(username='testUsername')
        self.assertEqual(user.username, 'testUsername')

    def test_safe(self):
        with self.assertRaises(models.IntegrityError):
            models.User.create(
                username='testUsername',
                email='testEmail@testEmail.com')

if __name__ == '__main__':
    unittest.main()

I've been able to get every test to pass and work properly except the last one, testing for an IntegrityError when a duplicate record is added to the user table. If anyone has any information on how to fix this, I'd really appreciate it, thanks!

Nicolas

Nicolas Hampton
Nicolas Hampton
44,638 Points

Kenneth Love ?

Josh Keenan
Josh Keenan
20,315 Points
Josh Keenan
Josh Keenan
20,315 Points

Tagging Chris Freeman, he will help you out. I got an idea but I am really uncertain so I will leave it to the master.

2 Answers

Chris Freeman
MOD
Chris Freeman
Treehouse Moderator 68,457 Points
Chris Freeman
Chris Freeman
Treehouse Moderator 68,457 Points

The error is your database table for User wasn't being created correctly.

In your code you are using the Model create_table() method on a Database. Instead use db.create_tables() (plural) method:

            models.DATABASE.create_tables([models.User], safe=True)

After this change, I was able to see all 5 tests pass.

Nicolas Hampton
Nicolas Hampton
44,638 Points

Thanks Chris!

While I was waiting for an answer, I managed to solve the problem, but couldn't pin down why, so this helped a lot. I'll post some improvements I made to the working tests later, so others can have a look at it.

Nicolas

Nicolas Hampton
Nicolas Hampton
44,638 Points

Ok, I solved the tables problem. However, now I can't seem to raise a ValueError when the max_length limits on the username and password are hit. I've added a "create_user" method that changes the IntegrityError to a ValueError on record creation, as class recommends, and standardized the initiation of the database in an intialize function.

models.py

import datetime

from peewee import *

DATABASE = SqliteDatabase('users.db')


class User(Model):
    username = CharField(max_length=50, unique=True)
    email = CharField(unique=True)
    password = CharField(max_length=20)
    joined_at = DateTimeField(default=datetime.datetime.now)

    class Meta:
        database = DATABASE

    @classmethod    
    def create_user(cls, username, email, password):
        try:
            cls.create(
                username=username,
                email=email,
                password=password)
        except IntegrityError:
            raise ValueError("User already exists")


def initialize():
    """Called when the program starts if not called as an imported module."""
    DATABASE.connect()
    DATABASE.create_tables([User], safe=True)
    User.create(username='testUsername',
                email='testEmail@testEmail.com',
                password='testPassword')
    DATABASE.close()

tests.py

import unittest

import models


class UserTableTests(unittest.TestCase):

    def setUp(self):
        models.initialize()

    def tearDown(self):
        try:
            models.User.delete().execute()
        except:
            pass



    def test_check_user_table(self):
        assert models.User.table_exists()    



    def test_drop_table(self):
        models.DATABASE.drop_table(models.User)
        assert models.User.table_exists() == False

    def test_delete_user(self):
        models.User.get(username="testUsername").delete_instance()
        with self.assertRaises(Exception):
            models.User.get(username="testUsername").delete_instance()



    def test_username(self):
        user = models.User.get(username='testUsername')
        self.assertEqual(user.username, 'testUsername')

    def test_email(self):
        user = models.User.get(username="testUsername")
        self.assertEqual(user.email, 'testEmail@testEmail.com')

    def test_password(self):
        user = models.User.get(username="testUsername")
        self.assertEqual(user.password, 'testPassword')



    def test_table_safe(self):
        with self.assertRaises(models.OperationalError):
            models.DATABASE.create_table(models.User)

    def test_entry_repeat_username(self):
        with self.assertRaises(ValueError):
            models.User.create_user(
                username='testUsername',
                email='Email@testEmail.com',
                password='testPassword')

    def test_entry_repeat_email(self):
        with self.assertRaises(ValueError):
            models.User.create_user(
                username='Username',
                email='testEmail@testEmail.com',
                password='testPassword')

    def test_username_length_limit(self):
        with self.assertRaises(ValueError):
            models.User.create_user(
                username='uuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu',
                email='Email@testEmail.com',
                password='testPassword')

    def test_password_length_limit(self):
        with self.assertRaises(ValueError):
            models.User.create_user(
                username='Username',
                email='Email@testEmail.com',
                password='uuuuuuuuuuuuuuuuuuuuuuuuuuuuuu')


if __name__ == '__main__':
    unittest.main()
Chris Freeman
MOD
Chris Freeman
Treehouse Moderator 68,457 Points
Chris Freeman
Chris Freeman
Treehouse Moderator 68,457 Points

Sometimes you have to try things out manually. I created an "illegal" user...

$ python
Python 3.4.0 (default, Jun 19 2015, 14:20:21) 
[GCC 4.8.2] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import models
>>> models.initialize()
>>> models.User.create(username="test1", email="test@example.com", password='uuuuuuuuuu0000000000xxxxxxxxxx')
<models.User object at 0x7fb13ad01a20>

Then checked the database:

$ sqlite3 users.db 
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
user
sqlite> .dump user
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE "user" ("id" INTEGER NOT NULL PRIMARY KEY, "username" VARCHAR(50) NOT NULL, "email" VARCHAR(255) NOT NULL, "password" VARCHAR(20) NOT NULL, "joined_at" DATETIME NOT NULL);
INSERT INTO "user" VALUES(1,'testUsername','testEmail@testEmail.com','testPassword','2016-01-13 19:35:02.757562');
INSERT INTO "user" VALUES(2,'test1','test@example.com','uuuuuuuuuu0000000000xxxxxxxxxx','2016-01-13 19:36:25.580892');
CREATE UNIQUE INDEX "user_username" ON "user" ("username");
CREATE UNIQUE INDEX "user_email" ON "user" ("email");
COMMIT;

Then to the sqlite3 docs on VARCHAR to find out: "SQLite does not enforce the length of a VARCHAR. You can declare a VARCHAR(10) and SQLite will be happy to store a 500-million character string there".

So that puppy ain't never gonna bark!

Nicolas Hampton
Nicolas Hampton
44,638 Points

AHA! I was manually checking and creating all types of records, but never read that doc!!! Lesson learned, thanks Chris!