[sqlalchemy] Re: accessing functions in the postgres public schema using sqlalchemy.func

[Brian Cherinka]

Yeah everything is working in psql just fine.   

select current_schema();
 current_schema

 public
(1 row)


Time: 1.226 ms
show search_path;
   search_path
-
 "$user", public
(1 row)

select add(2,4);
 add
-
   6
(1 row)
 

But you jogged my memory and think I found the problem.  I dug back through 
my code and found this buried in it

def clearSearchPathCallback(dbapi_con, connection_record):
'''
When creating relationships across schema, SQLAlchemy
has problems when you explicitly declare the schema in
ModelClasses and it is found in search_path.


The solution is to set the search_path to "$user" for
the life of any connection to the database. Since there
is no (or shouldn't be!) schema with the same name
as the user, this effectively makes it blank.


This callback function is called for every database connection.


For the full details of this issue, see:
http:
//groups.google.com/group/sqlalchemy/browse_thread/thread/88b5cc5c12246220


dbapi_con - type: psycopg2._psycopg.connection
connection_record - type: sqlalchemy.pool._ConnectionRecord
'''
cursor = dbapi_con.cursor()
cursor.execute('SET search_path TO "$user",functions')
dbapi_con.commit()


listen(sqlalchemy.pool.Pool, 'connect', clearSearchPathCallback)

which shows my setting the search_path to exclude public.  Changing that 
line 'SET search_path TO "$user",functions, public' fixes everything SQLA.  
It's been a while since I've looked at my code that sets up the 
DatabaseConnection.  I must have had an original reason to exclude public 
but it doesn't seem relevant anymore.  Thanks for your help.


On Wednesday, August 1, 2018 at 2:18:55 PM UTC-4, Brian Cherinka wrote:
>
> Hi,
>
> What's the best way to access functions that live in schema='public' in 
> the postgres databases?  Some postgresql extensions install functions in 
> the public schema that I would like accessible via *sqlachemy.func,* however 
> I get an error when attempting to call them.   Am I missing something when 
> setting up my Base Classes?  Maybe regarding the `search_path`?  I'm 
> defining my models and tables with a Declarative Base.  My 
> default_schema_name is set to 'public'.   I've read through this page, 
> http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path
>   
> but
>  
> it's not clear where I should be setting my search_path to ensure it 
> includes the public schema.  
>
> As an example, I have a test "add" function in the 'public' schema , which 
> crashes presumably because it cannot find the function definition
>
> session.query(func.add(2,4)).all()
>
> ProgrammingError: (psycopg2.ProgrammingError) function add(integer, 
> integer) does not exist
> LINE 1: SELECT add(2, 4) AS add_1
>^
> HINT:  No function matches the given name and argument types. You might 
> need to add explicit type casts.
>  [SQL: 'SELECT add(%(add_2)s, %(add_3)s) AS add_1'] [parameters: {'add_3': 
> 4, 'add_2': 2}]
>
>
>  and a test "newadd" function defined in an explicit schema called 
> "functions".  This seems to automatically get reflected and mapped.  And 
> works perfectly.  
>
> session.query(func.newadd(2,4)).all()
>
> [(6)]
>
> One solution is to install the postgres extension into the functions 
> schema, but this kind of breaks the usage within postgres itself.  I have 
> to always explicity set search_path='functions' in order to use them.  So 
> it's not ideal.  Ideally, I'd like *sqlachemy.func* to understand 
> functions that live either in the "functions" or "public" schema.  Any 
> ideas on how to fix this? 
>
> Cheers, Brian
>

-- 
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: integrate with cherrypy / sqlalchemy

[Mike Bayer]

On Tue, Jul 31, 2018 at 3:12 AM, Amin M  wrote:
> Thanks Mike, really helped me out.
>
> I've done the following, in env.py
>
> import sys
> import os
>
> project_root = os.path.abspath('./')  # get Cherrypy root
> sys.path.insert(0, project_root)  # add it to Path, so we can import models,
> and read Cherrypy configurations
>
>
> def get_models():
> """
> get cherrypy models (SQLAlchemy)
> :return:
> """
> from models.data_model import BASE
> from models.comment import Comment
> from models.file import File
> from models.user import User
> return [BASE.metadata]
>
>
> def get_db_configurations():
> """
> read database configurations from cherrypy
> :return:
> """
> from helpers.config import config_data
>
> sqlalchemy_url = '%s://%s:%s@%s:%s/%s' % (
> config_data['database']['type'],
> config_data['database']['user'],
> config_data['database']['password'],
> config_data['database']['host'],
> config_data['database']['port'],
> config_data['database']['db'],
> )
>
> return sqlalchemy_url
>
>
> Works like a charm, though I would love to know more about
> "now for the import above to work, your Python application needs to be
> part of the Python environment.  Usually folks build their web
> application with a setup.py file and then they install it into a local
> virtual environment.  If you aren't doing that, you may have to alter
> your PYTHONPATH environment variable outside, or use sys.path inside
> env.py in order to add where it can locate your models for import.
> "
>
> Could you point me to documentations or examples of this? I only know what a
> venv is.

make your application into a package, here's an overview:
http://python-packaging.readthedocs.io/en/latest/




>
> Thanks a lot.
>
> On Monday, July 30, 2018 at 9:35:17 PM UTC+3, Mike Bayer wrote:
>>
>> the env.py needs to things to work:
>>
>> 1. a way of connecting to the database.   if you just want to give it
>> a database URL that is fixed, you can stick that in alembic.ini under
>> sqlalchemy.url and you are done.  If you want to use cherrypy's
>> configurational facilities, then you'd need to add code to env.py that
>> calls upon cherrypy's configuration.   But just putting the database
>> URL into alembic.ini is a quick way just to get started.
>>
>> 2. for autogenerate to work (which is optional, but everyone wants it)
>> you have to "import" your model into it, like "from myapp.models
>> import BASE", then refer to BASE.metadata which is set up as the
>> "target_metadata" for autogenerate:
>>
>> from myapp.models import BASE
>> target_metadata = BASE.metadata
>>
>> now for the import above to work, your Python application needs to be
>> part of the Python environment.  Usually folks build their web
>> application with a setup.py file and then they install it into a local
>> virtual environment.  If you aren't doing that, you may have to alter
>> your PYTHONPATH environment variable outside, or use sys.path inside
>> env.py in order to add where it can locate your models for import.
>>
>>
>> You might want to ask on cherrypy's mailing list for more specifics on
>> these two points, they should be able to tell you.
>>
>>
>>
>>
>>
>> On Mon, Jul 30, 2018 at 7:30 AM, Amin M  wrote:
>> > hello, I have a cherrypy application with the following structure
>> > controllers/
>> > models/
>> > views/
>> > app.py
>> >
>> >
>> > and my models exist in models folder.
>> > My BASE model contains the following:
>> >
>> > from sqlalchemy import create_engine
>> > from sqlalchemy.ext.declarative import declarative_base
>> > from sqlalchemy.orm import scoped_session, sessionmaker
>> >
>> > from helpers.config import config_data
>> >
>> > # base ORM model
>> > BASE = declarative_base()
>> > ENGINE = create_engine('%s://%s:%s@%s:%s/%s' %
>> >(
>> >config_data['database']['type'],
>> >config_data['database']['user'],
>> >config_data['database']['password'],
>> >config_data['database']['host'],
>> >config_data['database']['port'],
>> >config_data['database']['db'],
>> >), pool_recycle=7200,
>> > echo=config_data["debug_sql"]
>> >)
>> > DB_SESSION = scoped_session(sessionmaker(bind=ENGINE))
>> >
>> >
>> > And my models extend this class, like below:
>> >
>> > from sqlalchemy import Column, Integer, String
>> >
>> > from helpers.config import config_data
>> > from models.data_model import BASE, DB_SESSION
>> >
>> > class User(BASE):
>> > __tablename__ = 'users'
>> > id = Column(Integer, nullable=False, primary_key=True)
>> > username = Column(String(200), nullable=False)
>> > email = Column(String(255))
>> > avatar_path = Column(String(2080))
>> > role = Column(Integer,
>> >

Re: [sqlalchemy] accessing functions in the postgres public schema using sqlalchemy.func

[Mike Bayer]

On Wed, Aug 1, 2018 at 2:18 PM, Brian Cherinka  wrote:
> Hi,
>
> What's the best way to access functions that live in schema='public' in the
> postgres databases?  Some postgresql extensions install functions in the
> public schema that I would like accessible via sqlachemy.func, however I get
> an error when attempting to call them.   Am I missing something when setting
> up my Base Classes?  Maybe regarding the `search_path`?  I'm defining my
> models and tables with a Declarative Base.  My default_schema_name is set to
> 'public'.   I've read through this page,
> http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path
> but it's not clear where I should be setting my search_path to ensure it
> includes the public schema.

the search_path should automatically include the "public" schema by default.

>
> As an example, I have a test "add" function in the 'public' schema , which
> crashes presumably because it cannot find the function definition
>
> session.query(func.add(2,4)).all()
>
> ProgrammingError: (psycopg2.ProgrammingError) function add(integer, integer)
> does not exist
> LINE 1: SELECT add(2, 4) AS add_1

>^
> HINT:  No function matches the given name and argument types. You might need
> to add explicit type casts.
>  [SQL: 'SELECT add(%(add_2)s, %(add_3)s) AS add_1'] [parameters: {'add_3':
> 4, 'add_2': 2}]

if you log in via psql, can you run the "add()" function?   you need
to get things working there first.   check the search_path, etc.



>
>
>  and a test "newadd" function defined in an explicit schema called
> "functions".  This seems to automatically get reflected and mapped.  And
> works perfectly.
>
> session.query(func.newadd(2,4)).all()

The terms "reflected" and "mapped" are both SQLAlchemy terms, and in
that regard, neither has anything to do with a SQL function.   Your
result would indicate that Postgresql's search path for functions
includes this "functions" schema.I'm not aware if this is some
kind of default behavior in Postgresql or something but again, using
psql by itself to see what's happening is the first thing to work
with.


>
> [(6)]
>
> One solution is to install the postgres extension into the functions schema,
> but this kind of breaks the usage within postgres itself.  I have to always
> explicity set search_path='functions' in order to use them.

If you are doing this, that would be why your "newadd" function in the
"functions" schema works...

> So it's not
> ideal.  Ideally, I'd like sqlachemy.func to understand functions that live
> either in the "functions" or "public" schema.  Any ideas on how to fix this?

this is all stuff you should ask on stackoverflow.  I can't reproduce
your issue:

$ psql -U scott test
psql (10.4)
Type "help" for help.

test=# select current_schema();
 current_schema

 public
(1 row)

# create a function, goes into the "public" schema by default

test=# CREATE FUNCTION add(integer, integer) RETURNS integer
test-# AS 'select $1 + $2;'
test-# LANGUAGE SQL
test-# IMMUTABLE
test-# RETURNS NULL ON NULL INPUT;
CREATE FUNCTION

# works

test=# select add(1, 2);
 add
-
   3
(1 row)

test=# show search_path;
   search_path
-
 "$user", public
(1 row)

# now remove "public" from search path

test=# set search_path="%user";
SET

# function is gone
test=# select add(1, 2);
ERROR:  function add(integer, integer) does not exist
LINE 1: select add(1, 2);
   ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.

# put it back
test=# set search_path="%user",public;
SET


# works again

test=# select add(1, 2);
 add
-
   3
(1 row)

test=#





>
> Cheers, Brian
>
> --
> 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] accessing functions in the postgres public schema using sqlalchemy.func

[Brian Cherinka]

Hi,

What's the best way to access functions that live in schema='public' in 
the postgres databases?  Some postgresql extensions install functions in 
the public schema that I would like accessible via *sqlachemy.func,* however 
I get an error when attempting to call them.   Am I missing something when 
setting up my Base Classes?  Maybe regarding the `search_path`?  I'm 
defining my models and tables with a Declarative Base.  My 
default_schema_name is set to 'public'.   I've read through this page, 
http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path
  
but
 
it's not clear where I should be setting my search_path to ensure it 
includes the public schema.  

As an example, I have a test "add" function in the 'public' schema , which 
crashes presumably because it cannot find the function definition

session.query(func.add(2,4)).all()

ProgrammingError: (psycopg2.ProgrammingError) function add(integer, integer) 
does not exist
LINE 1: SELECT add(2, 4) AS add_1
   ^
HINT:  No function matches the given name and argument types. You might 
need to add explicit type casts.
 [SQL: 'SELECT add(%(add_2)s, %(add_3)s) AS add_1'] [parameters: {'add_3': 4
, 'add_2': 2}]


 and a test "newadd" function defined in an explicit schema called 
"functions".  This seems to automatically get reflected and mapped.  And 
works perfectly.  

session.query(func.newadd(2,4)).all()

[(6)]

One solution is to install the postgres extension into the functions 
schema, but this kind of breaks the usage within postgres itself.  I have 
to always explicity set search_path='functions' in order to use them.  So 
it's not ideal.  Ideally, I'd like *sqlachemy.func* to understand functions 
that live either in the "functions" or "public" schema.  Any ideas on how 
to fix this? 

Cheers, Brian

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