![]() |
Execute .sql file in python - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: Execute .sql file in python (/thread-18678.html) |
Execute .sql file in python - pradeepkumarbe - May-27-2019 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 stmtsI 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!! RE: Execute .sql file in python - Gribouillis - May-27-2019 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. RE: Execute .sql file in python - UFO - Nov-05-2019 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 RE: Execute .sql file in python - MckJohan - Nov-05-2019 hmmm... been awhile working with MySQL, can't remember exactly when did they support OVER() clause. I hope the API already implemented. |