Python Forum

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!!

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.

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

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