How do I do date math in a Django ORM query?

I'm using Django with Python 3.7. I have teh below model ...

I'm using Django with Python 3.7. I have teh below model ...

class Article(models.Model):
    ...
    title = models.TextField(null=False)
    created_on = models.DateTimeField(default=datetime.now)

I would like to write a Django ORM query where I find all articles that are older than 5 minutes. But I'm not sure how to write such a query. I tried

Article.objects.filter((datetime.now(timezone.utc) - created_on)__gte==300)

But this results in a

SyntaxError: invalid syntax

error.

Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

What’s new in HTML6

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

Python Django Tutorial | Django Course

Python Django Tutorial | Django Course

🔥Intellipaat Django course: https://intellipaat.com/python-django-training/ 👉This Python Django tutorial will help you learn what is django web development &...

This Python Django tutorial will help you learn what is django web development & application, what is django and introduction to django framework, how to install django and start programming, how to create a django project and how to build django app. There is a short django project as well to master this python django framework.

Why should you watch this Django tutorial?

You can learn Django much faster than any other programming language and this Django tutorial helps you do just that. Our Django tutorial has been created with extensive inputs from the industry so that you can learn Django and apply it for real world scenarios.

Python with SQL Server Database

Python with SQL Server Database

In this article you will learn: How to connect through SQL Server Database using Python? How can we store some information into the SQL Server database using Python? How can we perform an update and delete operations? How we will retrieve stored information from the SQL Server database?

Introduction

In this  article you will learn:

  • How to connect through SQL Server Database using Python?
  • How can we store some information into the SQL Server database using Python?
  • How can we perform an update and delete operations?
  • How we will retrieve stored information from the SQL Server database?

Steps

Firstly, we will create a console based Python project.

  1. Open Visual Studio.

  2. Select FileNew,then Project.

  1. Now select Install template, Python, then Python Application. and give the name to the application and choose location where you want to save your application and then press OK.

  1. After performing above operation you need to Install Python package for SQL Server Connectivity.

Python Package Installing For SQL Server Connectivity

If you want to connect your python code to SQL Server there is no direct method. You need to install packages for connecting SQL Server. To install package perform the following steps:

  1. Open Python Environment in Visual Studio by clicking VIEW, Other Windows, then Python Environment.

  1. Now click on Python Interpreter which you have installed in your system (Note: A Computer can contain more than 1 Python interpreter), select pip from the drop-down and search form **pypyodbc.

Note: "pypyodbc"** is a module that is used to connect to the database and perform operations on that. pypyodbc can be used with any type of database: Oracle, SQL Server, MySQL, Microsoft Access or Excel, etc.

After successfully installing pypyodbc. Now open SQL Server and create a database and create the table.

I am creating a database Payroll and inside that database, I am creating a table named EmployeeMaster which contains the following fields.

Here EmployeeID is the primary key and Auto Incremented.

You can use the following code to create this EmployeeMaster table.

CREATE TABLE EmployeeMaster(    
    EmployeeID int IDENTITY(1,1) NOT NULL,    
    Name nvarchar(100) NOT NULL,    
    Salary decimal(18, 2) NOT NULL,    
    Mobile varchar(15) NOT NULL,    
    Designation varchar(50) NOT NULL,    
 CONSTRAINT [PK_EmployeeMaster1] PRIMARY KEY CLUSTERED     
(    
    [EmployeeID] ASC    
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]    
) ON [PRIMARY]   

Now switch to visual studio again and now we will see database connection insert, update, delete and select operations with Python step by step.

Connect to SQL Server Database: The following Python code shows you how you can connect to the SQL Server database.

#importing module  
import pypyodbc  
#creating connection Object which will contain SQL Server Connection  
connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
  
print("Connection Successfully Established")  
  
#closing connection  
connection.close()  

Connect in the function which contains connection string.

  • Driver for connection to a particular database.
  • Server: Which database server we are using.
  • Database: Name of the database which we will use.
  • uid: the user ID of the database server.
  • **pwd: **Password for connection to the database.

Insert Data To Database

The following command is used to Insert data into the database.

#importing module  
import pypyodbc  
#creating connection Object which will contain SQL Server Connection  
connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
#Creating Cursor  
cursor = connection.cursor()   
#SQL Query  
SQLCommand = ("INSERT INTO EmployeeMaster(Name, Salary, Mobile, Designation) VALUES ('Sourabh','200000','9928486447','Computer Programmer')")  
#Processing Query  
cursor.execute(SQLCommand)   
#Commiting any pending transaction to the database.  
connection.commit()  
#closing connection  
connection.close()  
print("Data Successfully Inserted")  

Here,

  • cursor() : This is a method which returns a new cursor object using the connection.
  • execute() : Prepares and executes SQL.
  • commit() :Commits any pending transaction to the database.

Output

And check the database, it will contain data which you have inserted.

Using Question Mark (?)

We can also execute our query with a question mark sign. Sometimes we need to insert our data at run time and we want to form our query with the + symbol and sometimes we forget to open/close double quotes or single quotes or plus symbols. So we can also execute our query with Question Mark Sign**?** like the following:

SQLCommand = ("INSERT INTO EmployeeMaster(Name, Salary, Mobile, Designation) VALUES (?,?,?,?)")

And we will execute our query like the following:

Values = [Name,Salary,Mobile,Designation]  
  
cursor.execute(SQLCommand,Values) 

Example

#importing module  
import pypyodbc  
#creating connection Object which will contain SQL Server Connection  
connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
#Creating Cursor  
cursor = connection.cursor()   
#############Database Parameters##########  
Name= "DJ"  
Salary=50000  
Mobile="9876543210"  
Designation="Computer Programmer"  
##########################################  
  
#SQL Query  
SQLCommand = ("INSERT INTO EmployeeMaster(Name, Salary, Mobile, Designation) VALUES (?,?,?,?)")  
Values = [Name,Salary,Mobile,Designation]  
  
#Processing Query  
cursor.execute(SQLCommand,Values)   
#Commiting any pending transaction to the database.  
connection.commit()  
#closing connection  
connection.close()  
print("Data Successfully Inserted")  

Output

Inside Database

Data read from user and inserted into the database

#importing module  
import pypyodbc  
#creating connection Object which will contain SQL Server Connection  
connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
#Creating Cursor  
cursor = connection.cursor()   
#############Database Parameters##########  
Name= input("Please Enter Name:")  
Salary=input("Please Enter Salary:")  
Mobile=input("Please Enter Mobile Number:")  
Designation=input("Please Enter Designation:")  
##########################################  
  
#SQL Query  
SQLCommand = ("INSERT INTO EmployeeMaster(Name, Salary, Mobile, Designation) VALUES (?,?,?,?)")  
Values = [Name,Salary,Mobile,Designation]  
  
#Processing Query  
cursor.execute(SQLCommand,Values)   
#Commiting any pending transaction to the database.  
connection.commit()  
#closing connection  
connection.close()  
print("Data Successfully Inserted")  

Output

Inside Database

Update In Database

#importing module  
import pypyodbc  
#creating connection Object which will contain SQL Server Connection  
connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
#Creating Cursor  
cursor = connection.cursor()   
#SQL Query  
SQLCommand = ("Update EmployeeMaster set Name='Dhananjay' where EmployeeID=2")  
#Processing Query  
cursor.execute(SQLCommand)   
#Commiting any pending transaction to the database.  
connection.commit()  
#closing connection  
connection.close()  
print("Updated Successfully")  

Output

Inside Database

Delete From the Database

#importing module  
import pypyodbc  
#creating connection Object which will contain SQL Server Connection  
connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
#Creating Cursor  
cursor = connection.cursor()   
#SQL Query  
SQLCommand = ("Delete from EmployeeMaster where EmployeeID=3")  
#Processing Query  
cursor.execute(SQLCommand)   
#Commiting any pending transaction to the database.  
connection.commit()  
#closing connection  
connection.close()  
print("Deleted Successfully")  

Output

Inside Database

Retrieve Data from the Database

We can retrieve data from the database in two ways.

  • Fetch one row at a time: We call fetchone() method.
  • Fetch all rows: We call fetchall() method.

fetchone method example

#importing module  
import pypyodbc  
#creating connection Object which will contain SQL Server Connection  
connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
#Creating Cursor  
cursor = connection.cursor()   
  
#SQL Query  
SQLCommand = ("select * from EmployeeMaster")  
#Processing Query  
cursor.execute(SQLCommand)   
results = cursor.fetchone()   
while results:  
    print ("Name:" +  str(results[0]))  
    print ("Salary:" +  str(results[1]))  
    print ("Mobile:" +  str(results[2]))  
    print ("Designation:" +  str(results[3]))  
    print()  
    results = cursor.fetchone()  
  
#closing connection  
connection.close()  

Output

Fetch All Rows

The following example shows how can we fetch all the rows at a time from the database using fetchall() method.

#importing module  
import pypyodbc  
#creating connection Object which will contain SQL Server Connection  
connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
#Creating Cursor  
cursor = connection.cursor()   
  
#SQL Query  
SQLCommand = ("select * from EmployeeMaster")  
#Processing Query  
cursor.execute(SQLCommand)   
i=1  
for rows in cursor.fetchall():  
    print("------------Employee %d-----------------"%i)  
    for field in rows:  
        print(str(field))  
    print("---------------------------------------")  
    print('')  
    i=i+1  
  
#closing connection  
connection.close()  

Output

Thank you for reading!