Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Python Using Databases in Python Meet Peewee Queries Are Your Friend

Nicolas Hampton
Nicolas Hampton
44,626 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

Josh Keenan
Josh Keenan
19,528 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,064 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,626 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,626 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,064 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,626 Points

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