Utility to translate MySQL queries to PostgreSQL

mysql2pgsql.py

"""Script to translate MySQL query to PostgreSQL.

There are three main differences between MySQL and PostgreSQL.

* Postgres expects single-quotes for quoting values, but mysql allows
  both single and double quotes. Need to change all the double
  quoted values into single quotes.

* Postgres expects double-quotes for column names and mysql expects
  back-quotes. Need to change all back quotes to double quotes.

* using dbname.tablename is valid in mysql, but not in postgres. In
  some of our queries, we are prefixing the table names with the db
  name. This needs to be fixed.
"""

import re

def s_quote(scanner, token):
    bq = '`'
    sq = "'"
    dq = '"'

    # back quote in mysql is double-quote in postgres
    if token.startswith(bq):
        return dq + token[1:-1] + dq
    # double-quote in mysql in single-quote in postgres
    elif token.startswith(dq):
        return sq + token[1:-1] + sq
    # single-quote is single-quote
    else:
        return token

def s_echo(scanner, token):
    # retain the whitespace and special chars
    return token

def s_everythingelse(scanner, token):
    # using database.table name is valid in mysql, but not in postgres
    # My database name is archive, so fixing all those occurances
    if token.startswith("archive."):
        return token[len("archive."):]
    else:
        return token

# creating a scanner to match the imporant patterns and replace them appropriately. 
# The scanner will call the associated function for each matched pattern.     
scanner = re.Scanner([
    ("`[^`]+`", s_quote),
    ('"[^"]+"', s_quote),
    ("'[^']+'", s_quote),
    ("[\s()]+", s_echo),
    ("[^\s\"'`]*", s_everythingelse)])

def mysql2pgsql(query):
    """Translates MySQL query to PostgreSQL."""
    tokens = scanner.scan(query)
    return "".join(tokens[0])

if __name__ == "__main__":
    queries = [
        'SELECT * FROM table WHERE name="hello"',
        'SELECT identifier from archive.metadata WHERE ((`identifier` LIKE "wcd_%"))'
    ]
    for q in queries:
        print "mysql", q
        print "pgsql", mysql2pgsql(q)
        print