A Python Module for the "General SQL Parser" library (sqlparser.com)
This library currently supports: Windows 32bit/64bit and Linux 32bit/64bit. There are currently no binaries for Mac OSX available.
Also, please note that this module does not support Python 3.*
Simply clone or download this git and execute
python setup.py build
python setup.py install
The setup script will automatically download the right library (from sqlparser.com) for you.
Simply import the module with import sqlparser The following example will parse a simple query:
import sqlparser
query = "SELECT a, b FROM table_1 WHERE c > 20"
parser = sqlparser.Parser()
# Check for syntax errors
if parser.check_syntax(query) == 0:
# Get first statement from the query
stmt = parser.get_statement(0)
# Get root node
root = stmt.get_root()
print root.__dict__
And print the node information the SELECT node:
{"computeClause": None,
"cteList": None,
"expandOnClause": None,
"fetchFirstClause": None,
"forupdateClause": None,
"fromTableList": <sqlparser.Node object at 0x7ff48c5eed50>,
"groupByClause": None,
"hierarchicalClause": None,
"intoClause": None,
"intoTableClause": None,
"isolationClause": None,
"leftNode": None,
"limitClause": None,
"lockingClause": None,
"node_type": 5,
"optimizeForClause": None,
"orderbyClause": None,
"qualifyClause": None,
"resultColumnList": <sqlparser.Node object at 0x7ff48c5ee618>,
"rightNode": None,
"sampleClause": None,
"selectDistinct": None,
"selectToken": "SELECT",
"setOperator": 0,
"topClause": None,
"valueClause": None,
"whereCondition": <sqlparser.Node object at 0x7ff48c5eea78>,
"windowClause": None,
"withClauses": None}
For more examples please check the examples directory.
python-sqlparser's People
Forkers
maxliu oreh antbean toontong xiamingwudi cloudfull dukeharris andrew0hill amnesia01 532848980 antallx idweball rich85730 jinseopim naisongwen sxhylkl rmurugaiyan1 lenmore anthonylouisbsb imsathiya17 edhurtadopython-sqlparser's Issues
Python3 support
Hi @TwoLaid - what would be required to get this working with py3?
I don't know enough about writing in C for python, so any direction would be helpful.
M
no parser for alterTableStatement
A parser function for alterTableStatement is left out in Node.c:1690
support for "interval x day"
Love using your parser.
I noticed it has trouble with "interval 1 day" as in this example.
select order_no from orders where created >= subdate(now(), interval 1 day)
Thanks, David
setup issue
Not able to run setup.py. I get this error. I could see this error in both windows 64 bit and on Linux.
https://www.screencast.com/t/V5bd1k2urKC
Provide access to tokenize and token list
It would be nice if Parser.sourcetokenlist and gsp_tokenize() were exposed.
Mac Symbol not found: _CStringAppend
➜ test python test.py
Traceback (most recent call last):
File "test.py", line 2, in <module>
import sqlparser
ImportError: dlopen(/Library/Python/2.7/site-packages/sqlparser.so, 2): Symbol not found: _CStringAppend
Referenced from: /Library/Python/2.7/site-packages/sqlparser.so
Expected in: flat namespace
in /Library/Python/2.7/site-packages/sqlparser.so
➜ test ll /Library/Python/2.7/site-packages/sqlparser.so
-rwxr-xr-x 1 root wheel 626K 5 4 17:25 /Library/Python/2.7/site-packages/sqlparser.so
➜ test more test.py
#!/usr/bin/env python
import sqlparser
query = "SELECT a, b FROM table_1 WHERE c > 20"
parser = sqlparser.Parser()
# Check for syntax errors
if parser.check_syntax(query) == 0:
# Get first statement from the query
stmt = parser.get_statement(0)
# Get root node
root = stmt.get_root()
print root.__dict__
mac
python 2.7
pls help
Issues with procedures containing multiple queries.
Hi,
I've been having issues with queries with multiple statements. I tested using the java library and got the expected output.
Example Code
import sqlparser
query = """select * from table1;
select * from table24"""
parser = sqlparser.Parser()
if parser.check_syntax(query) == 0:
for i in xrange(0,parser.get_statement_count(query)):
print(i)
stmt = parser.get_statement(i)
root = stmt.get_root()
print(root.get_text())
Expected output
0
select * from table1
1
select * from table2
Actual output
0
select * from table1
1
ct * from table1;
I've had a look at the source, but haven't had much luck getting started on finding the issue. Do you have any guidance for me?
Thanks.
How to ues it in c++
How to ues it in c++,thank you
Support for Nested SQL queries
I was trying to get this running with nested select statements, so I was wondering if you have a work-around for that. My SQL statement was something like,
SELECT sub.* FROM (SELECT * FROM tutorial.sf_crime_incidents_2014_01 WHERE day_of_week = 'Friday') sub WHERE sub.resolution = 'NONE
Can this be made to work?
Thanks for the time. Cheers!
Software broken with Python3?
Hi,
I tried to compile the software with Python 3.7 on Windows 10. After 2to3 I get nevertheless an error (translated from German):
Parser.c(20): error C2039: "ob_type": is no element of "Parser"
python-sqlparser-master\Parser.h(11): note: see declaration of "Parser"
Parser.c(93): warning C4013: "PyInt_FromLong" undefined; Assumption: extern mwith return type int
Parser.c(93): warning C4047: "=": Number of de-references at "PyObject *" and "int" is different
error: command 'C:\\Program Files (x86)\\Microsoft Visual Studio\\2017\\Community\\VC\\Tools\\MSVC\\14.16.27023\\bin\\HostX86\\x64\\cl.exe' failed with exit status 2
I'm not sure wether this is coming from Python3 or another error.
Limit and offset clauses fail the syntax validation
Sql string, "select * from table0 limit 100", is taken as syntax invalid. Is this expected?
documentation?
Thank you so much for the python-sqlparser. (https://www.sqlparser.com/sql-parser-python.php)
But I didn't find official documentation, or how to use it.
Hope you can tell me where the usage documentation is.
Fatal Python error: GC object already tracked
When using the library by running up and down the parse tree and extracting used columns and attributes of the parsed query I frequently ran into inexplicable errors and eventually into segmentation faults that crashed my python program. When going for a proof of concept I ended with the following code that only works for me with iPython. However, I am pretty sure that the problem is not iPython but sqlparser as segmentation faults occurred on multiple occasions when using the library and when doing the proof of concept I was able to eliminate everything but sqlparser related code - however the overall error changed to the GC problem.
I am using:
- an Ubuntu 14.04
- Python 2.7.6 (default, Oct 26 2016, 20:30:19)
- IPython 1.2.1
The code:
import sqlparser
def analyze_result_column_list(node):
ret = []
for subnode in node.list:
if subnode.expr.leftOperand is not None:
operand = subnode.expr.leftOperand
for item in operand.caseExpression.whenClauseItemList:
ret.append(item.comparison_expr.leftOperand.objectOperand.get_text())
return ret
def analyze_select_statement(root):
result_attributes = analyze_result_column_list(root.resultColumnList)
return [], []
class SqlQuery():
_query_string = None
_defines = None
_uses = None
def __init__(self, stmt, vendor=sqlparser.gsp_dbvendor.dbvmysql):
root = stmt.get_root()
self._uses, self._defines = analyze_select_statement(root)
parser = sqlparser.Parser()
query = "SELECT (CASE WHEN password = MD5( 'bitnami ') THEN 1 ELSE 0 END) AS zend_auth_credential_match FROM si_user WHERE (email = '[email protected] ')"
parser.check_syntax(query)
stmt = parser.get_statement(0)
pq = SqlQuery(stmt)
pq._defines
pq._uses
print "This was a triump,h \nI make a note here: \nHUGE SUCCESS."
Removing self._uses, self._defines = analyze_select_statement(root)
leads to the error not manifesting.
The bug can be recreated using a fresh virtual environment and then installing nothing but the sqlparser ,as described in the readme. Then do the following steps:
- activate the virtenv
- start IPython
- mark and copy the above code
- type %paste
Python 2.7.6 (default, Oct 26 2016, 20:30:19)
Type "copyright", "credits" or "license" for more information.
IPython 1.2.1 -- An enhanced Interactive Python.
? -> Introduction and overview of IPython's features.
%quickref -> Quick reference.
help -> Python's own help system.
object? -> Details about 'object', use 'object??' for extra details.
In [1]: %paste
import sqlparser
def analyze_result_column_list(node):
ret = []
for subnode in node.list:
if subnode.expr.leftOperand is not None:
operand = subnode.expr.leftOperand
for item in operand.caseExpression.whenClauseItemList:
ret.append(item.comparison_expr.leftOperand.objectOperand.get_text())
return ret
def analyze_select_statement(root):
result_attributes = analyze_result_column_list(root.resultColumnList)
return [], []
class SqlQuery():
_query_string = None
_defines = None
_uses = None
def __init__(self, stmt, vendor=sqlparser.gsp_dbvendor.dbvmysql):
root = stmt.get_root()
self._uses, self._defines = analyze_select_statement(root)
parser = sqlparser.Parser()
query = "SELECT (CASE WHEN password = MD5( 'bitnami ') THEN 1 ELSE 0 END) AS zend_auth_credential_match FROM si_user WHERE (email = '[email protected] ')"
parser.check_syntax(query)
stmt = parser.get_statement(0)
pq = SqlQuery(stmt)
pq._defines
pq._uses
## -- End pasted text --
Fatal Python error: GC object already tracked
Aborted (core dumped)
If any additional feedback/versions/etc. are needed please notify me as I am happy to help in any way possible to get to the root of this issue.
Issue with valid but long query
parser = sqlparser.Parser()
query = "SELECT iv.id, iv.index_id as index_id, b.name AS biller, c.name AS customer, DATE_FORMAT(date,'%Y-%m-%d') AS date, (SELECT coalesce(SUM(ii.total), 0) FROM si_invoice_items ii WHERE ii.invoice_id = iv.id AND ii.domain_id = '1') AS invoice_total, (SELECT coalesce(SUM(ac_amount), 0) FROM si_payment ap WHERE ap.ac_inv_id = iv.id AND ap.domain_id = '1') AS INV_PAID, (SELECT invoice_total - INV_PAID) As owing, (SELECT IF((owing = 0 OR owing < 0), 0, DateDiff(now(), date))) AS Age, (SELECT (CASE WHEN Age = 0 THEN '' WHEN Age <= 14 THEN '0-14' WHEN Age <= 30 THEN '15-30' WHEN Age <= 60 THEN '31-60' WHEN Age <= 90 THEN '61-90' ELSE '90+' END)) AS aging,iv.type_id As type_id, pf.pref_description AS preference, pf.status AS status, (SELECT CONCAT(pf.pref_inv_wording,' ',iv.index_id)) as index_name FROM si_invoices iv LEFT JOIN si_biller b ON (b.id = iv.biller_id AND b.domain_id = iv.domain_id) LEFT JOIN si_customers c ON (c.id = iv.customer_id AND c.domain_id = iv.domain_id) LEFT JOIN si_preferences pf ON (pf.pref_id = iv.preference_id AND pf.domain_id = iv.domain_id) WHERE iv.domain_id = '1' GROUP BY iv.id ORDER BY index_id desc"
parser.check_syntax(query)
the result is 2
However, the query is perfectly valid and belongs to the default instance of the bitnami virtual machine for simpleinvoices. One can download the VM and execute the query manually:
mysql> SELECT iv.id, iv.index_id as index_id, b.name AS biller, c.name AS customer, DATE_FORMAT(date,'%Y-%m-%d') AS date, (SELECT coalesce(SUM(ii.total), 0) FROM si_invoice_items ii WHERE ii.invoice_id = iv.id AND ii.domain_id = '1') AS invoice_total, (SELECT coalesce(SUM(ac_amount), 0) FROM si_payment ap WHERE ap.ac_inv_id = iv.id AND ap.domain_id = '1') AS INV_PAID, (SELECT invoice_total - INV_PAID) As owing, (SELECT I
F((owing = 0 OR owing < 0), 0, DateDiff(now(), date))) AS Age, (SELECT (CASE WHEN Age = 0 THEN '' WHEN Age <= 14 THEN '0-14' WHEN Age <= 30 THEN '15-30' WHEN Age <= 60 THEN '31-60' WHEN Age <= 90 THEN '61-90' ELSE '90+' END)) AS aging,iv.type_id As type_id, pf.pref_description AS preference, pf.status AS status, (SELECT CONCAT(pf.pref_inv_wording,' ',iv.index_id)) as index_name FROM si_invoices iv LEFT JOIN si_biller b
ON (b.id = iv.biller_id AND b.domain_id = iv.domain_id) LEFT JOIN si_customers c ON (c.id = iv.customer_id AND c.domain_id = iv.domain_id) LEFT JOIN si_preferences pf ON (pf.pref_id = iv.preference_id AND pf.domain_id = iv.domain_id) WHERE iv.domain_id = '1' GROUP BY iv.id ORDER BY index_id desc;
+----+----------+---------------+----------------------+------------+---------------+----------+------------+------+-------+---------+------------+--------+------------+
| id | index_id | biller | customer | date | invoice_total | INV_PAID | owing | Age | aging | type_id | preference | status | index_name |
+----+----------+---------------+----------------------+------------+---------------+----------+------------+------+-------+---------+------------+--------+------------+
| 1 | 1 | Fawlty Towers | Kath and Kim Pty Ltd | 2008-12-30 | 719.000000 | 0 | 719.000000 | 3019 | 90+ | 2 | Invoice | 1 | Invoice 1 |
+----+----------+---------------+----------------------+------------+---------------+----------+------------+------+-------+---------+------------+--------+------------+
1 row in set (0.01 sec)
Consequently this has to be a bug.
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.