Add an order by clause

[Todd Lane]

Hi,

I have this statement working

sql = "SELECT DISTINCT FieldA, FieldB FROM IH_vw_Blah where FieldA=" + event['FieldA']

I need to add an order by FieldB but cant quite get the syntax right.

Thank in advance for any assistance.

Todd

[Jay Summet]

First of all, using string concatenation to build a SQL statement is a
bad idea, as it opens you up to SQL injection attacks.

So instead, you should use the built in pymysql execute command to
insert variables into the SQL statement using placeholders. Something like:

sql = "SELECT DISTINCT FieldA, FieldB FROM IH_vw_Blah WHERE FieldA=%s
ORDER BY FieldB"

cursor.execute( sql, ( event['FieldA'], ) )


(the %s in the SQL string is replaced by a str() of the event['FieldA']
variable....)

(Note that you MUST give the execute function a tuple of values, even if
you are only inserting a single value, this is the reason for the
trailing comma...)

Following these best practices will protect you from SQL injection
attacks, and will also probably resolve your syntax errors (or at least
make them much easier to figure out and fix).

Jay

> --
> You received this message because you are subscribed to the Google
> Groups "PyMySQL Users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to pymysql-user...@googlegroups.com
> <mailto:pymysql-user...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[Todd Lane]

Thanks so much Jay, for your response.

Just starting out.

Great advice.

Cheers Todd

[Todd Lane]

Jay,

What would be the syntax if I wanted to add a 2nd where?

Ive tried which didn't give me an error but returned no results

sql = "SELECT DISTINCT FieldA, FieldB FROM IH_vw_Blah WHERE FieldA=%s and FieldB=%s
ORDER BY FieldB"

cursor.execute( sql, ( event['FieldA'], ['FieldB'],  )   )

> <mailto:pymysql-users+unsub...@googlegroups.com>.

[Ellery Payne]

Hello Todd,

Looks like you are missing a reference to "event" on your second parameter, it should look like this:

cursor.execute( sql, ( event['FieldA'], event['FieldB'],  )   )

> <mailto:pymysql-users+unsubscri...@googlegroups.com>.

> For more options, visit https://groups.google.com/d/optout.

--

You received this message because you are subscribed to the Google Groups "PyMySQL Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to pymysql-users+unsubscribe@googlegroups.com.

[Todd Lane]

Disregard, just need to add event to the 2nd parameter

cursor.execute( sql, ( event['FieldA'], event['FieldB'],  )   )