Re: [sqlalchemy] Help regarding SQL Alchemy Oracle Error

[Mike Bayer]

those NVARCHAR2's probably need a length:

CAST(expr AS NVARCHAR2(20))


On Fri, Nov 10, 2017 at 7:01 PM, venu v  wrote:
> Hi,
>
> I have sql alchemy , cx_Oracle installed , Python 2.7 installed on my
> windows machine.
>
> Trying to run a sql using SQL Alchemy and it fails with the below error:
>
>
> "sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00906: missing
> left parenthesis"
>
> This is the SQL i have:
>
> ('SELECT count(:count_2) AS count_1 \nFROM measure \nWHERE
> lower(CAST(measure.measure_code AS NVARCHAR2)) LIKE lower(:param_1) OR
> lower(CAST(measure.measure_name AS NVARCHAR2)) LIKE lower(:param_2)' ,
> {'param_1': u'%da%', 'count_2': '*', 'param_2': u'%da%'})
>
> Stack Trace:
> Traceback (most recent call last):
>   File "", line 1, in 
>   File
> "C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\engine\base.py",
> line 939, in execute
> return self._execute_text(object, multiparams, params)
>   File
> "C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\engine\base.py",
> line 1097, in _execute_text
> statement, parameters
>   File
> "C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\engine\base.py",
> line 1189, in _execute_context
> context)
>   File
> "C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\engine\base.py",
> line 1402, in _handle_dbapi_exception
> exc_info
>   File
> "C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\util\compat.py",
> line 203, in raise_from_cause
> reraise(type(exception), exception, tb=exc_tb, cause=cause)
>   File
> "C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\engine\base.py",
> line 1182, in _execute_context
> context)
>   File
> "C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\engine\default.py",
> line 470, in do_execute
> cursor.execute(statement, parameters)
> sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00906: missing
> left parenthesis [SQL: 'SELECT count(:count_2) AS count_1 \nFROM measure
> \nWHERE lower(CAST(measure.measure_code AS N
> ARCHAR2)) LIKE lower(:param_1) OR lower(CAST(measure.measure_name AS
> NVARCHAR2)) LIKE lower(:param_2)'] [parameters: {'param_1': u'%da%',
> 'count_2': '*', 'param_2': u'%da%'}]
>
> I modified the SQL to remove the CAST function and tried again . It worked.
>
> As the first SQL is system generated SQL, i cannot modify it .
> Is there any way i can force SQL Alchemy not to do CAST on the measure code
> column?
>
> Request you please help.
>
> Thanks,
> Venugopal.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Help regarding SQL Alchemy Oracle Error

[venu v]

Hi,

I have sql alchemy , cx_Oracle installed , Python 2.7 installed on my 
windows machine.

Trying to run a sql using SQL Alchemy and it fails with the below error:


"sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00906: missing 
left parenthesis"

This is the SQL i have:

('SELECT count(:count_2) AS count_1 \nFROM measure \nWHERE 
lower(CAST(measure.measure_code AS NVARCHAR2)) LIKE lower(:param_1) OR 
lower(CAST(measure.measure_name AS NVARCHAR2)) LIKE lower(:param_2)' , 
{'param_1': u'%da%', 'count_2': '*', 'param_2': u'%da%'})

Stack Trace:
Traceback (most recent call last):
  File "", line 1, in 
  File 
"C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\engine\base.py", 
line 939, in execute
return self._execute_text(object, multiparams, params)
  File 
"C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\engine\base.py", 
line 1097, in _execute_text
statement, parameters
  File 
"C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\engine\base.py", 
line 1189, in _execute_context
context)
  File 
"C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\engine\base.py", 
line 1402, in _handle_dbapi_exception
exc_info
  File 
"C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\util\compat.py", 
line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File 
"C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\engine\base.py", 
line 1182, in _execute_context
context)
  File 
"C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\engine\default.py",
 
line 470, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00906: missing 
left parenthesis [SQL: 'SELECT count(:count_2) AS count_1 \nFROM measure 
\nWHERE lower(CAST(measure.measure_code AS N
ARCHAR2)) LIKE lower(:param_1) OR lower(CAST(measure.measure_name AS 
NVARCHAR2)) LIKE lower(:param_2)'] [parameters: {'param_1': u'%da%', 
'count_2': '*', 'param_2': u'%da%'}]

I modified the SQL to remove the CAST function and tried again . It worked.

As the first SQL is system generated SQL, i cannot modify it .
Is there any way i can force SQL Alchemy not to do CAST on the measure code 
column?

Request you please help.

Thanks,
Venugopal.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Airflow > Configuring A SQL Alchemy Connection String To Use Unix Sockets

[james . lloyd]

Found it!

I wasn't using the $AIRFLOW_HOME environment variable (I didn't think it 
relied on it).

As such 
airflow initdb
Must've been using it's own ariflow.cfg file.Not the one in /airflow.



On Friday, 10 November 2017 07:31:54 UTC, james...@netnatives.co.uk wrote:
>
> Hello.
>
> I'm using the SQL Cloud Proxy on a Compute engine VM instance. I'm then 
> configuring Airflow (which uses SQL Alchemy).
>
> I've setup a unix socket like this:
>
> /opt/cloud_sql_proxy/cloud_sql_proxy 
> -instances=myproject:europe-west1:airflowinstance -dir=/cloudsql &
>
>
> I can connect to the Cloud SQL Proxy using this socket without any 
> trouble. i.e.,
>
> mysql -u airflowuser -p -S /cloudsql/myproject:europe-west1:
> airflowinstance
>
> But I can't get Airflow to connect using the below connection string:
>
> sql_alchemy_conn = mysql://airflowuser:xxx@/airflowdatabase?unix_socket=
> /cloudsql/myproject:europe-west1:airflowinstance
>
> I get a 'connection refused' error.
>
> Any help much appreciated!
>
>
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Airflow > Configuring A SQL Alchemy Connection String To Use Unix Sockets

[Mike Bayer]

hi -

work with mysqlclient first so you can determine if this is an error:

import MySQLdb

conn = MySQLdb.connect(user='user', passwd='passwd', db='dbname',
unix_socket='/cloudsql/myproject:europe-west1:airflowinstance')
cursor = conn.cursor()
cursor.execute("select 1")

see http://mysqlclient.readthedocs.io/user_guide.html#mysqldb.  If you
can get that to work then I can show you how to make whatever
parameters you end up with work with SQLAlchemy.



On Fri, Nov 10, 2017 at 2:31 AM,   wrote:
> Hello.
>
> I'm using the SQL Cloud Proxy on a Compute engine VM instance. I'm then
> configuring Airflow (which uses SQL Alchemy).
>
> I've setup a unix socket like this:
>
> /opt/cloud_sql_proxy/cloud_sql_proxy
> -instances=myproject:europe-west1:airflowinstance -dir=/cloudsql &
>
>
> I can connect to the Cloud SQL Proxy using this socket without any trouble.
> i.e.,
>
> mysql -u airflowuser -p -S /cloudsql/myproject:europe-west1:airflowinstance
>
> But I can't get Airflow to connect using the below connection string:
>
> sql_alchemy_conn =
> mysql://airflowuser:xxx@/airflowdatabase?unix_socket=/cloudsql/myproject:europe-west1:airflowinstance
>
> I get a 'connection refused' error.
>
> Any help much appreciated!
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.