Python dynamic query in mysql

Question

I am using python mysql to grab the data from the database. But I don't know how to merge three very similar queries in one single query.This is my code:

if self.X and not self.Y:
        query = """
            SELECT tb1.email
            FROM table1 as tb1
            join table2 as tb2 ON tb2.id= tb1.p_id
            WHERE tb2.id={}
            """.format(self.X)

elif self.X and self.Y:
      query = """
            SELECT tb1.email
            FROM table1 as tb1
            join table2 as tb2 ON tb2.id= tb1.p_id
            WHERE tb2.id={}
            AND tb1.id ={}
            """.format(self.X, self.Y)
else:
    query = """
        SELECT tb1.email
        FROM table1 as tb1
        join table2 as tb2 ON tb2.id= tb1.p_id
        WHERE tb1.id={}
        """.format( self.Y)

As you see the only difference between three queries is in one line. How can I make my code more solid?

RomanPerekhrest · Accepted Answer · 2019-08-18 09:00:19Z

1

Simply (append to a query):

query = """
    SELECT tb1.email
    FROM table1 as tb1
    join table2 as tb2 ON tb2.id= tb1.p_id
    WHERE {}.id = {}
    """

if self.X:
    query = query.format('tb2', self.X)
    if self.Y: query += f' AND tb1.id = {self.Y}'
elif self.Y:
    query = query.format('tb1', self.Y)

edited Aug 18, 2019 at 9:00

answered Aug 18, 2019 at 8:38

RomanPerekhrest

93.1k4 gold badges75 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

J2015 Over a year ago

Thanks for your reply, please have a look to my code again, I just modified my question.

Ege Ismail Kösedağ · Accepted Answer · 2019-08-18 08:32:05Z

0

Like this ?

if(self.X):
    query = """
        SELECT tb1.email
        FROM table1 as tb1
        join table2 as tb2 ON tb2.id= tb1.p_id
        WHERE tb2.id=%s
        %s"""%(self.X,"AND tb1 = %s"%(self.Y) if self.Y else "")

This will works perfectly but if self.X and self.Y different from integer 0 value because integer 0 equal to boolean False. in this use this code

if(type(self.X)==int):
        query = """
        SELECT tb1.email
        FROM table1 as tb1
        join table2 as tb2 ON tb2.id= tb1.p_id
        WHERE tb2.id=%s
        %s"""%(self.X,"AND tb1 = %s"%(self.Y) if type(self.Y)==int else "")

answered Aug 18, 2019 at 8:32

Ege Ismail Kösedağ

113 bronze badges

1 Comment

J2015 Over a year ago

Thanks for your reply, please have a look to my code again, I just modified my question.

Collectives™ on Stack Overflow

Python dynamic query in mysql

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related