Django Integration With Snowflake

Author: Ayushi Yadav



Django

Django is a trendy web development framework that assists in building web applications. Django helps in eliminating repetitive tasks making the development process an easy and time-saving experience.


Snowflake

Snowflake is a cloud based data warehousing platform that allows you to store your data, process it and enable analytical solutions in a quick, efficient and flexible way.


Prerequisite:
  • System with any OS installed (Example: Windows, Mac etc. )

  • Python installed (Recommended version : 3.7 and above)

  • IDE installed (Example: PyCharm, VSCode etc)

  • Basic understanding of the frameworks (Django and the IDE you’re using)

Install Python

Django is a python based web framework and thus requires Python to be installed in your system.


Go to this website https://www.python.org/downloads/ and download Python based on the compatibility of your OS.


You can download the latest version of Python.



Here, I’m downloading the latest version of Python.


Click the “Download Python 3.10.5” button and the download will start automatically.


In the bottom, you will see that the .exe file has been downloaded.



Once the .exe file is downloaded, open it and a window will pop up.


Check all the boxes and click on “Install now”




Installing specific release

In case you want to download a specific version of Python, you can download that as well.


Go to https://www.python.org/downloads/ and scroll down.



Find your specific version release compatible with your OS and click on it


For example, I have downloaded python version 3.9.13.


Open the .exe file downloaded and click on Next and then install.


Python is installed successfully!


Install IDE

An IDE (Integrated Development Environment) understands your code much better than a text editor. It usually provides features such as build automation, code linting, testing and debugging.


To run Django, you can install any IDE ( like PyCharm, VScode etc) based on your choice.


PyCharm is preferable as it is one of the best Django IDEs for any project that uses Python language.


It is a cross platform IDE created by Jet Brains and it includes a smart code editor which allows you to write high quality Python code.


Steps to install PyCharm:

Go to https://www.jetbrains.com/pycharm/download/ Select the OS you are using Choose the desired edition Download




Once downloaded, open the .exe file. A window will appear.


Click on Next Choose the location you want to install Pycharm Next Install



Launch PyCharm

Windows search Pycharm File Click to launch (or) You can launch via the shortcut created at desktop



Create a Project Folder with a Virtual environment:

File New project Specify the name of your project Select the Base interpreter


(Note: As I have installed two versions of Python, I can choose any of the versions to create the virtual environment)




Click on ‘Create’


A virtual environment is being created



And your PyCharm setup is ready!



Before you begin with installation of Django, Check if pip is installed

Note: pip is already included with python versions greater than 3.4


Verify if pip is working properly by running the following command:

pip –version



Great! You’re good to go with the installation of Django!


What to do if pip is not working?

If your pip command is not returning the version of pip that is installed in your system, it means it is not installed or configured properly.


The most common reason why pip does not work is because it is not properly configured.


To resolve this, Check if the PATH variable is set for your project folder. If it is not set, add the location of the project folder to the PATH variable.


How to find the location?

Locate the folder where you created the project venv Scripts



Copy the path


In my case it is : C:\Users\DELL\PycharmProjects\Project\venv\Scripts


Paste the path in the environment variable PATH


Follow the steps:

Start Search Edit the Environment variables Environment variables User variables PATH Edit New Paste the Path



Once the path is set properly, you are ready to install Django!


Install Django

Open the terminal window of PyCharm



Run this command:

pip install django (To install latest version of Django)



OR


pip install django==3.2.1 (To install specific version of Django, replace 3.2.1 with your required version)



Verify you Django installation by running either of the following commands in the terminal:

django-admin --version



OR


python -m django --version



After successful installation of Django, you are now ready to create your web application!


Start your Django project

Run this command in the terminal:

django-admin startproject myapp (Specify your app name instead of myapp).


The project will be created as shown:



Install Snowflake connector for Python

To connect our Django project with the Snowflake database, we will use the snowflake-connector-python package.


This requires python version >= 3.7


Run this command in the terminal to install the package:

pip install snowflake-connector-python




In your views.py file of the Django project,

Import the snowflake connector using

import snowflake.connector



Now to setup the connection, configure it in the below format

ctx = snowflake.connector.connect(

user='<username>',

password='<password>',

account='<account>',

warehouse='<warehouse>',

database='<database>',

schema='<schema>',

role='<role>'

)

The query is specified like this:

cur = ctx.cursor()

sql = "write your query here"

cur.execute(sql)



Example:



To fetch the records, use function fetchall()

records = cur.fetchall();


Here is a demo of the connection between Django and Snowflake

Let us consider a Snowflake account with DEV_DB as the database and PLACES_DATA as the schema which contains a table named ‘PLACES’



‘PLACES’ table consists of 14 rows.


Each row specifies the name of the place.


The data is shown below:



Now let us retrieve the data present in the Snowflake database to our Django project.


Specify the path in Urls.py file as shown in the image



Views.py file


Import snowflake.connector in the Views.py file

Create a function and specify the following inside the function,

  • Set up the connection configuration


def index(request):

ctx = snowflake.connector.connect(

user='ayushiyadav805',

password='********',

account='su00845.ap-south-1',

warehouse='COMPUTE_WH',

database='DEV_DB',

schema='PLACES_DATA',

role='DEV_ENGINEER'

)

  • Specify the query that you want to run

cur = ctx.cursor()

sql = "select * from PLACES order by place_name"

cur.execute(sql)


records = cur.fetchall();


  • Return the template (home.html) along with the context dictionary ‘params’ (if you have any)


return render(request, "home.html", params)


So, the views.py file will contain the following code altogether:


import snowflake.connector

from django.shortcuts import render, redirect



def index(request):

ctx = snowflake.connector.connect(

user='ayushiyadav805',

password='********',

account='su00845.ap-south-1',

warehouse='COMPUTE_WH',

database='DEV_DB',

schema='PLACES_DATA',

role='DEV_ENGINEER'

)


cur = ctx.cursor()

sql = "select * from PLACES order by place_name"

cur.execute(sql)


records = cur.fetchall();


def convertTuple(tup):

str = ''

for item in tup:

str = str + item

return str


dbrecord = [];

for row in records:

str = convertTuple(row)

dbrecord.append(str)

print("Database record includes : \n", dbrecord)


params = {'places_data': dbrecord}

return render(request, "home.html", params)



Create a template

Home.html


The home.html is a file created inside the templates folder.


Django requires an efficient way to generate the HTML files dynamically and thus relies on templates.


A template consists of static HTML code along with a way to generate the dynamic content that needs to be inserted in the output.


<!doctype html>

<html lang="en">

<head>

<meta charset="utf-8">

<meta name="viewport" content="width=device-width, initial-scale=1">

<title>Demo</title>

<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet"

integrity="sha384-0evHe/X+R7YkIZDRvuzKMRqM+OrBnVFBL6DOitfPri4tjfHxaWutUpFmBp4vmVor" crossorigin="anonymous">

</head>

<body>


<div class="container pt-4">

<h1>Django integration with Snowflake Demo</h1>

<h5>We have connected our project to Snowflake database.</h5>

<h5>The results are being retrieved from the 'Places' table stored in our Snowflake database</h5>

<div class="container pt-3">

<table class="table table-striped">

<thead><tr><th scope="col">Name of the Place</th></tr></thead>

<tbody>

{% for i in places_data%}

<tr><td>{{i}}</td></tr>

{% endfor %}

</tbody>

</table>

</div>

</div>


</body>

</html>



Start the Django development server


Use the following command to run server python manage.py runserve.


Take a look at the output in the terminal window.



You can see the database records printed in the console.


Now, Click on the development server URL ( http://127.0.0.1:000/).


This will redirect you to the browser with your project running on the server


Output:

The data from the snowflake database will be displayed on the website.


All the records present in the table are shown into our Django web application.



Conclusion

A successful connection between Snowflake and Django web app has been established.


We can define the query and perform the basic operations including:

  • Creation of tables

  • Creation of schemas

  • Creation of databases

  • Insertion of data into tables

  • Creation of roles

  • Performing select operations on the data stored in the tables


References
  1. https://docs.djangoproject.com/en/4.0/

  2. https://pypi.org/project/snowflake-connector-python/

  3. https://docs.snowflake.com/en/user-guide/python-connector.html

35 views0 comments

Recent Posts

See All