Managing Snowflake databases, schemas, and tables with Python

You can use Python to manage Snowflake databases, schemas, and tables. For more information about managing and working with data in Snowflake, see Databases, Tables and Views - Overview.

Prerequisites

The examples in this topic assume that you’ve added code to connect with Snowflake and to create a Root object from which to use the Snowflake Python API.

For example, the following code uses connection parameters defined in a configuration file to create a connection to Snowflake:

from snowflake.core import Root
from snowflake.snowpark import Session

session = Session.builder.config("connection_name", "myconnection").create()
root = Root(session)
Copy

Using the resulting Session object, the code creates a Root object to use the API’s types and methods. For more information, see Connect to Snowflake with the Snowflake Python API.

Managing databases

You can manage databases in Snowflake. The Snowflake Python API represents databases with two separate types:

  • Database: Exposes a database’s properties, such as its name.

  • DatabaseResource: Exposes methods you can use to fetch a corresponding Database object and to delete the database.

Creating a database

You can create a database by calling the DatabaseCollection.create method and passing a Database object that represents the database you want to create. To create a database, first create a Database object that specifies the database name.

Code in the following example creates a Database object representing a database called my_db and then creates the database by passing the Database object to the DatabaseCollection.create method:

from snowflake.core import Root
from snowflake.core.database import Database

my_db = Database(name="my_db")
root.databases.create(my_db)
Copy

Getting database details

You can get information about a database by calling the DatabaseResource.fetch method, which returns a Database object.

Code in the following example gets information about a database called my_db:

from snowflake.core import Root
from snowflake.core.database import Database

my_db = root.databases["my_db"].fetch()
print(my_db.to_dict())
Copy

Listing databases

You can list databases using the iter method, which returns a PagedIter iterator.

Code in the following example lists databases whose name begins with my:

from snowflake.core import Root

databases = root.databases.iter(like="my%")
for database in databases:
  print(database.name)
Copy

Deleting a database

You can delete a database using the DatabaseResource.delete method.

Code in the following example deletes the my_db database:

from snowflake.core import Root
from snowflake.core.database import Database

my_db_res = root.databases["my_db"]
my_db_res.delete()
Copy

Managing schemas

You can manage schemas in Snowflake. A schema is a database-level object. When you create or reference a schema, you do so in the context of its database.

The Snowflake Python API represents schemas with two separate types:

  • Schema: Exposes a schema’s properties, such as its name.

  • SchemaResource: Exposes methods you can use to fetch a corresponding Schema object and to delete the schema.

Creating a schema

To create a schema, first create a Schema object that specifies the schema name.

Code in the following example creates a Schema object representing a schema called my_schema:

from snowflake.core import Root
from snowflake.core.schema import Schema

my_schema = Schema(name="my_schema")
root.databases["my_db"].schemas.create(my_schema)
Copy

The code then creates the schema in the my_db database by passing the Schema object to the SchemaCollection.create method.

Getting schema details

You can get information about a schema by calling the SchemaResource.fetch method, which returns a Schema object.

Code in the following example gets a Schema object that represents the my_schema schema:

from snowflake.core import Root
from snowflake.core.schema import Schema

my_schema = root.databases["my_db"].schemas["my_schema"].fetch()
print(my_schema.to_dict())
Copy

Listing schemas

You can list the schemas in a specified database using the iter method. The method returns a PagedIter iterator of Schema objects.

Code in the following example lists schema names in the my_db database:

from snowflake.core import Root

schema_list = root.databases["my_db"].schemas.iter()
for schema_obj in schema_list:
  print(schema_obj.name)
Copy

Deleting a schema

You can delete a schema using the SchemaResource.delete method.

Code in the following example deletes the my_schema schema:

from snowflake.core import Root
from snowflake.core.schema import Schema

my_schema_res = root.databases["my_db"].schemas["my_schema"]
my_schema_res.delete()
Copy

Managing tables

You can manage tables in Snowflake. A table is a schema-level object. When you create or reference a table, you do so in the context of its schema.

The Snowflake Python API represents tables with two separate types:

  • Table: Exposes a table’s properties, such as its name and columns.

  • TableResource: Exposes methods you can use to fetch a corresponding Table object, update the properties of the table, and delete the table.

Creating a table

To create a table, first create a Table object that specifies the table name, column names, and column data types.

Code in the following example creates a Table object representing a table called my_table with the specified columns:

from snowflake.core import Root
from snowflake.core.table import Table, TableColumn

my_table = Table(
  name="my_table",
  columns=[TableColumn("c1", "int", nullable=False), TableColumn("c2", "string")]
)
root.databases["my_db"].schemas["my_schema"].tables.create(my_table)
Copy

The code then creates the table in the my_db database and my_schema schema by passing the Table object to the TableCollection.create method.

Getting table details

You can get information about a table by calling the TableResource.fetch method, which returns a Table object.

Code in the following example gets information about a table called my_table:

from snowflake.core import Root
from snowflake.core.table import Table

my_table = root.databases["my_db"].schemas["my_schema"].tables["my_table"].fetch()
print(my_table.to_dict())
Copy

Creating or updating a table

You can update characteristics of an existing table by setting properties of a Table object that represents an existing table, and then passing the updated object to Snowflake with the TableResource.create_or_update method.

You can also pass a Table object that describes a new table when you want to create the table.

Code in the following example appends a new column named c3 of datatype int to the my_table table, and then updates the table in Snowflake:

from snowflake.core import Root
from snowflake.core.table import PrimaryKey, Table, TableColumn

my_table = root.databases["my_db"].schemas["my_schema"].tables["my_table"].fetch()
my_table.columns.append(TableColumn(name="c3", datatype="int", nullable=False, constraints=[PrimaryKey()]))

my_table_res = root.databases["my_db"].schemas["my_schema"].tables["my_table"]
my_table_res.create_or_update(my_table)
Copy

Listing tables

You can list the tables in a specified schema using the iter method, which returns a PagedIter iterator of Table objects.

Code in the following example lists tables whose name begins with my:

from snowflake.core import Root

tables = root.databases["my_db"].schemas["my_schema"].tables.iter(like="my%")
for table_obj in tables:
  print(table_obj.name)
Copy

Deleting a table

You can delete a table using the TableResource.delete method.

Code in the following example deletes the my_table table:

from snowflake.core import Root
from snowflake.core.table import Table

my_table_res = root.databases["my_db"].schemas["my_schema"].tables["my_table"]
my_table_res.delete()
Copy
Language: English