Better way to write this SQL in Python

Question

Is there anyway I can reduce the duplication of my arguments? For example, timepattern is repeated 4 times and it gets hard to keep track of queries that are big.

    sql = ( "SELECT IFNULL(b.inviters/COUNT(DISTINCT c.id),0),                                  " 
            "       FROM_UNIXTIME(c.registered_at, %s)                                          "
            "FROM   (   SELECT COUNT(1) AS inviters, joindate                                   "
            "           FROM                                                                    "
            "               (   SELECT  DISTINCT(y.id) AS inviters,                             " 
            "                           FROM_UNIXTIME(y.registered_at, %s) AS joindate          "
            "                   FROM user_invites z                                             "
            "                   INNER JOIN users y ON y.id = z.inviter_id                       "
            "                   WHERE z.created_at >= %s                                        "
            "               ) a                                                                 "
            "           GROUP BY a.joindate                                                     "
            "       ) b                                                                         "   
            "INNER JOIN users c ON FROM_UNIXTIME(c.registered_at, %s) = b.joindate              "
            "WHERE c.registered_at BETWEEN %s AND %s                                            "
            "GROUP BY FROM_UNIXTIME(c.registered_at, %s)                                        "   )

    args =  (   timepattern, timepattern, datestart_int, timepattern,
                datestart_int, dateend_int, timepattern )   

    cursor.execut(sql, args)

    data = list(cursor.fetchall())

    cursor.close()
    connection.close()

Cédric Julien · Accepted Answer · 2011-05-09 08:57:19Z

try with this with using args as a parameters dictionnary and using explicit string interpolation args (ie %(parameter_name)s ) :

sql = ( "SELECT IFNULL(b.inviters/COUNT(DISTINCT c.id),0),                                  " 
            "       FROM_UNIXTIME(c.registered_at, %(timepattern)s)                                          "
            "FROM   (   SELECT COUNT(1) AS inviters, joindate                                   "
            "           FROM                                                                    "
            "               (   SELECT  DISTINCT(y.id) AS inviters,                             " 
            "                           FROM_UNIXTIME(y.registered_at, %(timepattern)s) AS joindate          "
            "                   FROM user_invites z                                             "
            "                   INNER JOIN users y ON y.id = z.inviter_id                       "
            "                   WHERE z.created_at >= %(datestart_int)s                                        "
            "               ) a                                                                 "
            "           GROUP BY a.joindate                                                     "
            "       ) b                                                                         "   
            "INNER JOIN users c ON FROM_UNIXTIME(c.registered_at, %(timepattern)s) = b.joindate              "
            "WHERE c.registered_at BETWEEN %(datestart_int)s AND %(dateend_int)s                                            "
            "GROUP BY FROM_UNIXTIME(c.registered_at, %(timepattern)s)                                        "   )

    args =  {
        "timepattern" : timepattern,
        "datestart_int" : datestart_int,
        "dateend_int" : dateend_int,
    }   

    cursor.execute(sql, args)

tmg · Accepted Answer · 2011-05-09 08:59:38Z

You can give args as dict and then use it in query like %(name)s For example

args = {'timepattern': timepattern, 'dateend_int': dateend_int}
sql = ( "SELECT IFNULL(b.inviters/COUNT(DISTINCT c.id),0),                                  " 
            "       FROM_UNIXTIME(c.registered_at, %(timepattern)s)                                          "
            "FROM   (   SELECT COUNT(1) AS inviters, joindate                                   "
            "           FROM                                                                    "
            "               (   SELECT  DISTINCT(y.id) AS inviters,                             " 
            "                           FROM_UNIXTIME(y.registered_at, %(timepattern)s AS joindate          "
            "                   FROM user_invites z                                             "
            "                   INNER JOIN users y ON y.id = z.inviter_id                       "
            "                   WHERE z.created_at >= %(datestart_int)s                                        "
            "               ) a                                                                 "
            "           GROUP BY a.joindate                                                     "
            "       ) b                                                                         "

            "INNER JOIN users c ON FROM_UNIXTIME(c.registered_at, %(timepattern)s) = b.joindate              "
            "WHERE c.registered_at BETWEEN %(datestart_int)s AND %(datetart_int)s                                            "
            "GROUP BY FROM_UNIXTIME(c.registered_at, %(timepattern)s)" )
cursor.execute(sql, args)

Collectives™ on Stack Overflow

Better way to write this SQL in Python

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related