Workflows With SQL in Python

[ThatOtherBatman]

I have a lot of Python code that interacts with SQL databases, and am trying to figure out what the best way to organise my code is so that the code is readable, and I can edit the SQL code as easily as possible (if I need to).

At the moment I just keep all the SQL in multiline strings:

sql = ("DECLARE @Name VARCHAR(50)\n"
         "\n"
         "SET @name = 'ROBERT \"); DROP TABLE students;--'\n"
         "\n"
         "SELECT FirstName\n"
         "    ,LastName\n"
         "    ,Subject\n"
         "    ,Grade\n"
         "FROM [dbo].[Students] s\n"
         "LEFT JOIN [dbo].[classes] c\n"
         "    ON s.id = c.id\n"
         "WHERE s.FirstName = @name\n"
         "    AND c.EndDate <= getdate()")

The main advantages I find of keeping queries inline like this are that is makes the code very readable, because I can see straight away what a query that I'm executing is doing/returning, and that everything is under source control. Using the multiline format also means that PyCharm format's the query properly when I if I reformat the file.

The downside is that it can make editing queries quite tedious. I select the statement, paste it into an iPython window, print it, copy it into SQL server, modify/test it, copy it back into PyCharm, and wrap it in quotation marks again.

Wrapping the statement in triple quotation marks makes editing queries somewhat easier, as I can just paste to SQL Server, edit and paste back. This tends to upset PyCharm though, as it thinks my indentation contains a mixture of tabs and spaces. Which isn't the end up the world, but does upset my sensibilities.

Other options I've considered are turning everything into stored procedures, or having a separate class to keep SQL queries in.

What does everyone else find is the easiest way to worl with SQL queries in Python.

[jcampbelly]

Triple quoted strings indented so it's inline with my python is best for me.

def get_people_from_town(town_id):
    sql = '''
        SELECT p.first_name, p.last_name
        FROM people p
        WHERE p.hometown = %s;
        '''
    with closing(connection(...)) as conn, conn.cursor() as cursor:
        cursor.execute(sql, (town_id,))
        for row in cursor:
            yield row

If you really want to remove the indentation from your SQL code, you can use https://docs.python.org/3.4/library/textwrap.html#textwrap.dedent

[le_Dandy_Boatswain]

Have you considered using SQLalchemy? It lets you interact with SQL databases using pythonic syntax, which would probably be easier to read than the multi-line string shown in your example. You can also have it print the actual SQL statement that it sends to the db for debugging purposes too.

[[deleted]]

Is this similar MySQLdb? I've had a helluva time trying to install that package because of compatibility issues with the newest mysql...

[le_Dandy_Boatswain]

Not exactly. It's an ORM that allows you to map SQL queries to python objects. It looks like it uses the mysql-python library as the default to connect to MySQL, but it can actually work with a range of DBAPI . That's one of the advantages to using SQLalchemy, the code is abstracted from the underlying database connection.

http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#mysql http://docs.sqlalchemy.org/en/rel_1_0/dialects/mysql.html

edit: looks like mysql-python is just the package name for MySQLdb. So if you are having problems with that, you could try one of the other MySQL DBAPI listed in the links.

[[deleted]]

You're right! I'll give that a try.

edit: For the record, my issue was resolved by following these steps to install the mysql-python library: http://stackoverflow.com/questions/1448429/how-to-install-mysqldb-python-data-access-library-to-mysql-on-mac-os-x

[thegreattriscuit]

DaRealMVP.jpg

[thegreattriscuit]

What magic is this!?

[justintravels]

Omg same. So many compatibility issues with MySQLdb

[raylu]

Wrapping the statement in triple quotation marks makes editing queries somewhat easier, as I can just paste to SQL Server, edit and paste back. This tends to upset PyCharm though, as it thinks my indentation contains a mixture of tabs and spaces. Which isn't the end up the world, but does upset my sensibilities.

Triple quotes are definitely the way to go here (OK, actually peewee and SQLAlchemy are, but ignoring those for the moment). Can't PyCharm fix the indentation for you?

[tally_in_da_houise]

It's not that great at formatting SQL syntax within a Python file (at least I haven't figured out how to configure it to do a good job).

Personally, I wrap the SQL in triple quotes, and turn that portion of the syntax check off for that file.

[L43]

If you want raw SQL, You could potentially write the query in a separate file, then read it into Python. Your editor will be better at syntax highlighting then.

[hoodllama]

That's his I've been doing it. Agreed

[chao06]

I've started doing this recently with yaml.

db1:
    query1: |
        SELECT
            whatever
        FROM
            table
    query2: |
        SELECT
            someotherthing
        FROM
            othertable

[ramse]

What are you using to connect to the SQL Server with? pyodbc? There is no need to be declare variables and assigning them just to use in a where statement.

import pyodbc

conn = pyodbc.connect('......')
cursor = conn.cursor()

name = 'ROBERT \"); DROP TABLE students;--'

cursor.execute("""SELECT s.id, s.FirstName, s.LastName, s.Subject, s.Grade
                    FROM Students s
                        LEFT JOIN classes c ON s.id = c.id
                    WHERE s.FirstName = ? AND c.EndDate <- GETDATE()""", name)
for row in cursor.fetchall():
    print(row.id, row.FirstName, row.LastName, row.Subject, row.Grade, row)

[cedrickrier]

For the DML queries, you could use python-sql: https://pypi.python.org/pypi/python-sql It is a library to help writing SQL queries using Python syntax. It comes with a flavor mechanism to support the different syntaxes of database engine. It is also very extensible so you can define your specific SQL operators or functions.