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:

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

cs = ctx.cursor()

2. Used appropriate role,warehouse,database and schema.
3. 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,

Answer 1

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")

Answer 2

Actually, I have to have command like this

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

and It is working as expected.

Thanks