DEV Community: MIYACHIN

Prisma: Further JOIN on JOINed records

MIYACHIN — Thu, 17 Mar 2022 08:15:42 +0000

I had a problem when I wanted to do a further JOIN on JOINed records, so I looked up how to do it.

Consider these table structure.
(For simplicity, createdAt and updatedAt are omitted.)

group

group_id
group_name

member

member_id
member_name
group_id

post

post_id,
post_content,
member_id

Each group has multiple members, and each member has multiple posts.

On schema.prisma, it is defined like this.



model Group {
  groupId   Int      @id @default(autoincrement()) @map("grooup_id")
  groupName String   @map("group_name") @db.VarChar(200)
  members   Member[]

  @@map("group")
}

model Member {
  memberId   Int    @id @default(autoincrement()) @map("member_id")
  memberName String @map("member_name") @db.VarChar(200)
  group      Group  @relation(fields: [groupId], references: [groupId])
  groupId    Int    @map("group_id")
  posts      Post[]

  @@map("member")
}

model Post {
  postId      Int    @id @default(autoincrement()) @map("post_id")
  postContent String @map("post_content") @db.VarChar(255)
  member      Member @relation(fields: [memberId], references: [memberId])
  memberId    Int    @map("member_id")

  @@map("post")
}

By the way, if you run prisma format, it aligns the vertical lines of shcema.prisma nicely!

How to query

With Nest.js, you can code it like this.



import { Injectable } from '@nestjs/common';
import { PrismaService } from './../prisma.service';
import { Group, Prisma } from '@prisma/client';

@Injectable()
export class GroupService {
  constructor(private prisma: PrismaService) {}

  findOne(id: number) {
    return this.prisma.group.findUnique({
      where: { groupId: id },
      include: {
        members: {
          include: {
            post: true
          },
        },
      },
    });
  }
}

And the response will look like this.



{

   "groupId":1,

   "groupName":"sample group",

   "members":[

      {

         "memberId":1,

         "memberName":"Nick",

         "groupId":1,

         "posts":[

            {

               "postId":1,

               "postContent":""

            },

            {

               "postId":2,

               "postContent":""

            }

         ]

      },

      {

         "memberId":2,

         "memberName":"John",

         "groupId":1,

         "posts":[

            {

               "postId":3,

               "postContent":""

            },

            {

               "postId":4,

               "postContent":""

            }

         ]

      }

   ]

}

References

https://www.prisma.io/docs/concepts/components/prisma-client/relation-queries#include-deeply-nested-relations

I am working on a web app called spliito.com that solves the tedious task of recording the money owed and borrowed that occurs when you go on a trip with friends in a spreadsheet and calculating who should pay back how much to whom at the end of the trip.

There are many options for connecting MySQL from Python, but let's use PyMySQL or mysql-connector-python for now.

MIYACHIN — Sun, 06 Mar 2022 06:15:28 +0000

I'm trying to build an app with Flask+MySQL, but there are various packages for connecting to MySQL with Python3 and I wasn't sure which one is good and which one is bad.

I tried to use various packages and found PyMySQL or mysql-connector-python to be the simplest and easiest to use for me personally, so I'm going to write down how to use it.

Looking at StackOverflow and other sites, it appears that many responses recommend PyMySQL　or mysql-connector-python.

Let's take a brief look at a brief overview of each and how to use them.

👀 Comparison of Overviews

mysql-connector-python

Officially supported by Oracle
Pure python
A little slow
Not compatible with MySQLdb

PyMySQL

Pure python
Faster than mysql-connector
Almost completely compatible with MySQLdb, after calling pymysql.install_as_MySQLdb()

It is encouraging that mysql-connector-python is supported by Oracle. On the other hand, if you want speed, use PyMySQL.

✅ How to use mysql-connector-python

installation

pip install mysql-connector-python

Connect to the database

import mysql.connector

# Connect to server
cnx = mysql.connector.connect(
    host="127.0.0.1",
    port=3306,
    user="mike",
    password="s3cre3t!")

Insert/Select records

from __future__ import print_function
from datetime import date, datetime, timedelta
import mysql.connector

cnx = mysql.connector.connect(user='scott', database='employees')
cursor = cnx.cursor()

tomorrow = datetime.now().date() + timedelta(days=1)

# Insert new employee
add_employee = ("INSERT INTO employees "
               "(first_name, last_name, hire_date, gender, birth_date) "
               "VALUES (%s, %s, %s, %s, %s)")

data_employee = ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14))
cursor.execute(add_employee, data_employee)
cnx.commit()

query = ("SELECT first_name, last_name, hire_date FROM employees "
         "WHERE hire_date BETWEEN %s AND %s")

hire_start = datetime.date(1999, 1, 1)
hire_end = datetime.date(1999, 12, 31)

cursor.execute(query, (hire_start, hire_end))

for (first_name, last_name, hire_date) in cursor:
  print("{}, {} was hired on {:%d %b %Y}".format(
    last_name, first_name, hire_date))

cursor.close()
cnx.close()

✅ How to use PyMySQL

installation

pip install PyMySQL

Connect to the database

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             database='db',
                             cursorclass=pymysql.cursors.DictCursor)

Insert/Select records

with connection:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save
    # your changes.
    connection.commit()

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('webmaster@python.org',))
        result = cursor.fetchone()
        print(result)

result

{'password': 'very-secret', 'id': 1}

Summary

I personally prefer PyMySQL. Which do you prefer? Or do you know a better package?

Reference page

Automatically reduce test cases using pairwise methods

MIYACHIN — Thu, 03 Mar 2022 12:40:45 +0000

When testing an application, the number of test cases depends on which browser is used, which device is used, and which impression method is used.

Let's take a look at this case.

devices: Mac, Windows, iPhone, iPad, Android
browsers: Safari, Chrome, Firefox, IE
login authentication methods: Facebook login, Twitter login, Google login, Instagram login

The number of all test cases is calculated as follows.
This is simple elementary level math.

devices (5) x browsers (4) x login authentication methods (4) = 80 cases

So there are 80 different ways.
Well, I can do it if I share it with my colleagues, but I don't want to if I can help it.

Use pairwise methods to overwhelmingly reduce the number of test cases

A simple explanation of the pair-wise method is: Basically, an app bug is caused by one or two factors. Therefore, even if there are more than three factors, there is no need to check all the combinations.

However, it is a bit difficult to adapt the pairwise method to already existing test cases on your own, so you should use an open source tool called "PICT" published by Microsoft.

Install PICT

You can implement it in the following steps.

$ git clone https://github.com/Microsoft/pict.git
$ cd pict/
$ make
$ sudo install -m 0755 pict /usr/local/bin/pict

Now that the pict command is available, the next step is to write the test case so that pict can read it. The way to write it is super simple and looks like this.

devices: mac,windows,iphone,ipad,android
browser: safari,chrome,firefox,ie
login_authentication_method: facebook,twitter,google,instagram

Save this with a .txt extension.

Run PICT

Now let's see how many test cases have been reduced from the 80 simple calculations made earlier.

pict test_case.txt

result

device  browser login_authentication_method
windows firefox facebook
ipad    safari  twitter
android chrome  google
windows ie  instagram
ipad    firefox google
iphone  ie  google
windows safari  google
iphone  safari  instagram
ipad    ie  facebook
iphone  firefox twitter
mac safari  facebook
android ie  twitter
mac ie  google
mac chrome  twitter
iphone  chrome  facebook
android firefox instagram
windows chrome  twitter
ipad    chrome  instagram
android safari  facebook
mac firefox instagram

Down to only 20 cases (1/4)!!!

For wrong combinations like Mac+IE, please google for details as you can write conditions and drop them.

If you simply want to know the number of cases, you can run it with /s at the end.

$ pict test_case.txt /s
　　　　Combinations:   56
　　　　Generated tests:20
　　　　Generation time:0:00:00

If you are wondering if this is really reliable, please read the paper published by Microsoft.
Pairwise Testing in the Real World: Practical Extensions to Test-Case Scenarios

Created a product used by 50,000 people per month

MIYACHIN — Wed, 02 Mar 2022 16:13:16 +0000

(This text is translated from Japanese into English by DeepL, so there may be some grammatical errors. Please understand.)

In October 2018, my coworker and I started a side project in web app development.

We wanted to create a web app that solves the tedious task of recording the money owed and borrowed that occurs when you go on a trip with friends in a spreadsheet and calculating who should pay back how much to whom at the end of the trip.

We developed the front-end in Vue.js and the back-end in Python (Flask) and deployed it on Heroku.

The functionality is simple: create a group page from a browser and add members to it. After that, you can share the URL of that group page in a group chat to keep track of who paid what to whom and how much they paid for what. As you record the expenditures, you will be able to see the calculation results of who should pay back how much to whom in real time.

We named it spliito.

Until we get on a growth path

The app grows semi-automatically once the number of users grows to a certain number because the action of "sharing with friends" is built into the user experience. However, my specialty is not marketing, so I have been taking measures through trial and error.

Contacted people who are about to travel via Twitter and introducing them to our product.
Printed QR codes on chocolate packages and hand them out in town
Gave a speech about product development to the developer community and introduced the product.

Roughly around the time MAUs increased to about 150, the product gradually began to enter a growth path.

Tenfold growth in 2021

Despite the impact of the coronavirus, the number of users increased tenfold from January to December 2021, finally reached 50,000 MAU.

Our main source of income is Google Adsense, which brings us about $300 per month so far.

This year, we are planning to expand to the U.S. and are writing an article introducing our product in this way.

Marketing measures for expansion to the U.S.

We are expanding to the U.S. without knowing what to expect, but I have conducted user interviews with about 5 of my American online English teachers and have confirmed that there is some need for this product.

We are now operating several channels.

Create a podcast channel that provides information about Japan, and insert a short product introduction there..
Write articles on Medium to introduce our product and attract customers through Google searches.
Opened Twitter account
Posted on ProductHunt

However, I have yet to see results from any of them, so if you have any advice, please let me know.

spliito | Calculate Complex Splits in Seconds

DEV Community: MIYACHIN

Prisma: Further JOIN on JOINed records

group

member

post

How to query

References

Advertisement

There are many options for connecting MySQL from Python, but let's use PyMySQL or mysql-connector-python for now.

👀 Comparison of Overviews

mysql-connector-python

PyMySQL

✅ How to use mysql-connector-python

installation

Connect to the database

Insert/Select records

✅ How to use PyMySQL

installation

Connect to the database

Insert/Select records

result

Summary

Reference page

Advertisement

Automatically reduce test cases using pairwise methods

Use pairwise methods to overwhelmingly reduce the number of test cases

Install PICT

Run PICT

Advertisement

Created a product used by 50,000 people per month

Until we get on a growth path

Tenfold growth in 2021

Marketing measures for expansion to the U.S.