Execute .sql file in python

[pradeepkumarbe]

Hello,

I am trying to execute .sql file using python, I am using pymysql.

This is the code I am using to parse sql query

def parse_sql(filename):
    data = open("./ms.sql", 'r').read()
    stmts = []
    DELIMITER = ';'
    stmt = ''
    tab ='\t'

    for lineno, line in enumerate(data):
        if not line.strip():
            continue

        if line.startswith('--'):
            continue

        if 'DELIMITER' in line:
            DELIMITER = line.split()[1]
            continue

        if (DELIMITER not in line):
            stmt += line.replace(DELIMITER, ';')
            continue

        if 'tab' in line:
            tab=line.split()[1]
            continue


        if stmt:
            stmt += line
            stmts.append(stmt.strip())
            stmt = ''
        else:
            stmts.append(line.strip())
    return stmts

I using this definition in such a way

mysql_conn = mysql_getConn('mydb') stmts = parse_sql('my_sql_file.sql')
print(stmts)
with mysql_conn.cursor() as cursor:
    for stmt in stmts:
        cursor.execute(stmt)
    mysql_conn.commit()

My sql query is

SELECT OUTLET_ID,PRODUCT_KEY,DATE_FILTER,CAST(ACCT_SUM_PROD AS SIGNED)/CAST(ACCT_AGG_SUM AS SIGNED) share
FROM
(
SELECT DISTINCT L1.OUTLET_ID,L1.PRODUCT_KEY,L1.DATE_FILTER,SUM(L1.UNITS) OVER(PARTITION BY L1.OUTLET_ID,L1.PRODUCT_KEY,L1.DATE_FILTER) ACCT_SUM_PROD,
SUM(L1.UNITS) OVER(PARTITION BY L1.OUTLET_ID,L1.DATE_FILTER) ACCT_AGG_SUM
FROM
(
SELECT DISTINCT OUTLET_ID,PRODUCT_KEY,CAST(UNITS AS SIGNED) UNITS,
CASE WHEN PERIOD_ID <= '20180630' THEN 'PREV 6M' ELSE 'CURR 6M' END AS DATE_FILTER
FROM weekly
) L1
) L2;

I am getting the error near partition by, I am running my python code in Ubuntu 16.04.5 LTS (GNU/Linux 4.4.0-142-generic x86_64)

Error message is below

Traceback (most recent call last):
  File "test_jsonpick.py", line 67, in <module>
    cursor.execute(stmt)
  File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 165, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 321, in _query
    conn.query(q)
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 860, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 1061, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 1349, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 1018, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 384, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY L1.OUTLET_ID,L1.PRODUCT_KEY,L1.DATE_FILTER)ACCT_SUM_PROD,\nSUM(L1.U' at line 5")

I didnt unserstand whether its a Mysql error or SQL file formatting issue. Is there any other way to acheive this? Thanks in advance!!

[Gribouillis]

It doesn't look like an error from the python layer. You could try to run the query from a mysql client independently from python to see if it works.

[UFO]

Hello,

I modify your code and it goes well now.

try this:

def parse_sql(sql_file_path):
    with open(sql_file_path, 'r', encoding='utf-8') as f:
        data = f.read().splitlines()
    stmt = ''
    stmts = []
    for line in data:
        if line:
            if line.startswith('--'):
                continue
            stmt += line.strip() + ' '
            if ';' in stmt:
                stmts.append(stmt.strip())
                stmt = ''
    return stmts

[MckJohan]

hmmm... been awhile working with MySQL, can't remember exactly when did they support OVER() clause. I hope the API already implemented.