How to execute Snowflake Stored Procedure from Python?

Question

I have created stored procedure in snowflake which is executed fine in snowflake UI and also from server by using snowsql. Now I want to execute procedure from python program, I tried to execute from python, here are the steps that I have followed:

establish the connection to snowflake ( successfully able to connect.)

cs = ctx.cursor()

Used appropriate role,warehouse,database and schema.
tried to execute procedure like this:

cs.execute("call test_proc('value1', 'value2')")   
x = cs.fetchall() 
print(x)

But getting an erorr:

snowflake.connector.errors.ProgrammingError: 002140 (42601): SQL compilation error: Unknown function test_proc

Can you please help me to resolve this problem.

Thanks,

Lukasz Szozda · Accepted Answer · 2021-04-18 06:50:46Z

When connecting to Snowflake using Python connector you could define DATABASE/SCHEMA

conn = snowflake.connector.connect(
                user=USER,
                password=PASSWORD,
                account=ACCOUNT,
                warehouse=WAREHOUSE,
                database=DATABASE,
                schema=SCHEMA
                );

Once you have it set up, you could call your stored procedure without using fully-qualified name:

cs.execute("call test_proc('value1', 'value2')");

Alternative way is:

Using the Database, Schema, and Warehouse

Specify the database and schema in which you want to create tables. Also specify the warehouse that will provide resources for executing DML statements and queries.

For example, to use the database testdb, schema testschema and warehouse tiny_warehouse (created earlier):
conn.cursor().execute("USE WAREHOUSE tiny_warehouse_mg")
conn.cursor().execute("USE DATABASE testdb_mg")
conn.cursor().execute("USE SCHEMA testdb_mg.testschema_mg")

Rab · Accepted Answer · 2021-04-17 21:55:47Z

0

Actually, I have to have command like this

cs.execute("call yourdbname.schemaname.test_proc('value1', 'value2')")

and It is working as expected.

Thanks

answered Apr 17, 2021 at 21:55

Rab

1591 gold badge2 silver badges11 bronze badges

1 Comment

Mike Walton Over a year ago

Had you connected to the DB and schema in your connection string, your first attempt would've worked, as well.

Collectives™ on Stack Overflow

How to execute Snowflake Stored Procedure from Python?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related