title: Dynamic SQL statement in Python
body: <p>I'm building a Python function that creates SQL statements as a string that will later be passed to the database to extract data using a <code>pyodbc</code> connection.</p>
  <p>I have trouble finding the right way to handle different combinations of filter arguments in both <code>SELECT</code> and <code>WHERE</code> statement without using multiple if/else statements and without breaking the syntax of the SQL.</p>
  <p>Here is a minimal reproducible example:</p>
  <p><code>sqldata.py</code></p>
  <pre><code>import pandas as pd
  
  class SqlData(object):
      def __init__(self, customers=None, last_version=None, attributes=None, age=None):
          self.customers = customers
          self.last_version = last_version
          self.attributes = attributes
          self.default_columns = [&quot;customer_id&quot;, &quot;date&quot;, &quot;value&quot;]
          self.age = age
  
      def buildSQL(self):
          default_columns = &quot;, &quot;.join(self.default_columns)
  
          if self.attributes == None:
              attributes = &quot; &quot;
          else:
              attributes = &quot; ,&quot; + &quot;, &quot;.join(self.attributes) + &quot; &quot;
  
          if self.customers == None:
              customers = &quot; &quot;
          else:
              customers = &quot;customer_id in ('&quot; + &quot;','&quot;.join(self.customers) + &quot;')&quot;
  
          if self.last_version == None:
              last_version = &quot; &quot;
          else:
              last_version = &quot; AND last_version = 'Y'&quot;
  
          if self.age == None:
              age = &quot; &quot;
          else:
              age = &quot; AND age &gt; &quot; + self.age
  
          self.sql = &quot;SELECT &quot; + default_columns + attributes + \
                     &quot;FROM myTable &quot; + \
                     &quot;WHERE &quot; + customers + last_version  + age
          return self.sql
  </code></pre>
  <p><code>__init__.py</code></p>
  <pre><code>from sqldata import SqlData
  </code></pre>
  <p><code>__main__.py</code></p>
  <pre><code>from sqldata import SqlData
  
  data = SqlData(customers = [&quot;Arthur&quot;, &quot;Ben&quot;],
                     last_version = True, 
                     attributes = [&quot;name&quot;, &quot;age&quot;],
                     age = &quot;14&quot;)
  
  print(data.buildSQL())
  </code></pre>
  <p>If all arguments are given it works just fine and I get the following:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT customer_id, date, value ,name, age 
  FROM myTable 
  WHERE customer_id in ('Arthur','Ben') AND last_version = 'Y' AND age &gt; 14
  </code></pre>
  <p>But if <code>customers</code> is not given, it makes no more sense:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT customer_id, date, value ,name, age 
  FROM myTable 
  WHERE   AND last_version = 'Y' AND age &gt; 14
  </code></pre>
  <p>Ideally, when <code>customers</code> is not given, I would like to have the following:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT customer_id, date, value ,name, age 
  FROM myTable 
  WHERE last_version = 'Y' AND age &gt; 14
  </code></pre>
  <p>All in all, I would like to know the following:</p>
  <ol>
  <li>Is it possible to &quot;bypass&quot; the use of those multiple if/else statements ? There must be a better way.</li>
  <li>How to handle the case where the <code>customers</code> variable is not given, hence breaking the syntax of the SQL statement?</li>
  </ol>