Databases
StackOverflow2013
Postsdbo
POHow to check and perform SQL statements in Python
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to check and perform SQL statements in Python
    primarykey
    data
    text
    <p>I am using the MySQLdb in Python. I was told that to properly create SQL statements and to avoid SQL injection attacks I should code something like:</p> <pre><code>sql = "insert into table VALUES ( %s, %s, %s )" args = var1, var2, var3 cursor.execute( sql, args ) </code></pre> <p>Or:</p> <pre><code>cursor.execute( "insert into table VALUES ( %s, %s, %s )", var1, var2, var3 ) </code></pre> <p>Or even this (this might be wrong):</p> <pre><code>header = ( 'id', 'first_name', 'last_name' ) values = ( '12', 'bob' , 'smith' ) cursor.execute( "insert into table ( %s ) values ( %s )", ( header + values ) ) </code></pre> <p>When I programmed in PHP, I would normally store my entire SQL statement as a long string then execute the string. Something like (with PostgreSQL):</p> <pre><code>$id = db_prep_string( pg_escape_string( $id ) ); $first_name = db_prep_string( pg_escape_string( $first_name ) ); $last_name = db_prep_string( pg_escape_string( $last_name ) ); $query = "insert into table ( id, first_name, last_name ) values ( $id, $first_name, $last_name )" $result = pg_query( $con, $query ); $retval = pg_fetch_array( $result ); </code></pre> <p>where <code>db_prep_string</code> is</p> <pre><code>function db_prep_string( $value ) { if( !isset( $value ) || (is_null($value)) || preg_match( '/^\s+$/', $value ) || ( $value == '' ) ) { $value = 'null'; } else { $value = "'$value'"; } return $value; } </code></pre> <p>Then to debug, I could simply echo out <code>$query</code> to view the entire SQL statement. Is there something similar I can do with Python? In other words, can I store the <strong>safe</strong> SQL statement as a string, print it to debug, then execute it?</p> <p>I would like to do something like:</p> <pre><code>id = prevent_sql_injection_string( id ) first_name = prevent_sql_injection_string( first_name ) last_name = prevent_sql_injection_string( last_name ) sql = "insert into table ( id, first_name, last_name ) values " sql += id + ", " sql += first_name + ", " sql += last_name print sql #for debugging cursor.execute( sql ) </code></pre> <p>If not, then how do I <em>view</em> the SQL statement in its entirety in Python?</p> <p>Bonus question: how do I enter null values in a SQL statement using Python?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UShobbes3
    1. UShobbes3
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to check and perform SQL statements in Python
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThis is called using a "parameterized" query or SQL.
      singulars
      1. POHow to check and perform SQL statements in Python
      1. USDOK
    2. COIf you want to enter a null value, use `None`.
      singulars
      1. POHow to check and perform SQL statements in Python
      1. USMRAB
    3. COYou embedded arguments in pgsql queries to get one huge string? Yuck! Even in PHP you have `pg_query_params` where you can separate the query from the values.
      singulars
      1. POHow to check and perform SQL statements in Python
      1. USThiefMaster
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload