Created
May 20, 2013 11:13
-
-
Save anandology/5611664 to your computer and use it in GitHub Desktop.
Utility to translate MySQL queries to PostgreSQL
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
"""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) | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment