peewee Python ORM to connect to MySQL

Posted under » MySQL » Python on 23 Aug 2023

There are several ways in python to connect to MySQL. I used to connect using pyMySQL. However, there are times I need to connect to other database too like SQLite and Postgresql. So you need an ORM. Object-Relational Mapping (ORM) is a technique that lets you query and manipulate data from a database using an object-oriented paradigm.

Connect

import peewee
from peewee import *
db = MySQLDatabase('taik', user='lky', passwd='1nJahan6')

class auth_user(Model):
    username = CharField()
    email = CharField()

    class Meta:
        database = db # This model uses the taik mysql database.

Note that model types are not according to MySQL but are similar to Django models because both are in Python.

Like django you don't have to put `id' as it is a given. However not all the time the index is `id'. For example Drupal uses `nid' instead. In this case, we specify the primary key

class node_field_data(Model):
    nid = IntegerField(primary_key=True)
    title = CharField()

    class Meta:
        database = db 

Take note that there are no int but IntegerField. PyMySQL doesn't need to define the Model because it is in MySQL.

To join between models using foreign key relationships.

class display_student(Model):
    student_id = ForeignKeyField(auth_user, backref='stud')
    student_name = CharField()
    title = CharField()
 
    class Meta:
        database = db 

First we do a query without join

query = display_student.select().where(display_student.student_id == '21')
for pet in query:
    print(pet.student_name,'-', pet.title)

danial - Differentiate \(\sqrt[3]{x}\) with respect to \(x\).
danial - Find \(\frac{d}{dx}(\frac{1}{x})\)
danial - Find \(\frac{d}{dx}((3x-4)\cos x)\)

Now we do a query using join with multiple lines which is easy to read.

query = (display_student
         .select(display_student, auth_user)
         .join(auth_user)
         .where(display_student.student_name == 'danial')).limit(3)

for pet in query:
    print(pet.student_name,'-', pet.title)

danial - Differentiate \(\sqrt[3]{x}\) with respect to \(x\).
danial - Find \(\frac{d}{dx}(\frac{1}{x})\)
danial - Find \(\frac{d}{dx}((3x-4)\cos x)\)

If you prefer one line, you can get the same results with this query.

for pet in display_student.select().join(auth_user).where(auth_user.username == 'danial'):
    print(pet.student_name,'-', pet.title)

As mentioned earlier, you must join using foreign key relationships or you get error. Also mentioned earlier is that if key is not id, you must specify it with the on= tag

query = (node_field_data
         .select(node_field_data, node__field_actress)
         .join(node__field_actress, on=(node_field_data.nid == node__field_actress.field_actress_target_id))
         .where(node__field_actress.field_actress_target_id == 1))
print(query) # to see the SQL join

Peewee is designed to provide a simple, expressive, and pythonic way of constructing SQL queries. This section will provide a quick overview of some common types of expressions.

User.username == 'charlie'

User.login_count < 5

(User.is_admin == True) & (User.last_login >= today)

(User.failed_logins > (User.login_count * .5)) & (User.login_count > 10)

You can also do updates with expressions

qry=postings_node.update({postings_node.cid:25}).where(postings_node.id==1)
print (qry.sql())
qry.execute()

Or insert

q = quiz.insert(difficulty=2, difficultydec=1.2, lpl='2', text='stand by your man')
q.execute()

or just use create

quiz.create(difficulty=2, difficultydec=1.2, lpl='2', text='stand by your man')

SQL functions, like COUNT() or SUM(), can be expressed using the fn() helper. Eg. get all users and the number of tweets they've authored. Sort the # results from most tweets -> fewest tweets.

query = (User
         .select(User, fn.COUNT(Tweet.id).alias('tweet_count'))
         .join(Tweet, JOIN.LEFT_OUTER)
         .group_by(User)
         .order_by(fn.COUNT(Tweet.id).desc()))
for user in query:
    print('%s -- %s tweets' % (user.username, user.tweet_count))

basic queries »