Skip to content

Instantly share code, notes, and snippets.

@anandology
Created May 20, 2013 11:13
Show Gist options
  • Save anandology/5611664 to your computer and use it in GitHub Desktop.
Save anandology/5611664 to your computer and use it in GitHub Desktop.
Utility to translate MySQL queries to PostgreSQL
"""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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment