New style string formatting in Python

In this section, you will learn the usage of the new style formatting.

Learn more here.

Python 3 introduced a new way to do string formatting that was also later back-ported to Python 2.7. 
This “new style” string formatting gets rid of the modulo-operator special syntax and makes the syntax for string formatting more regular.
Formatting is now handled by calling .format() on a string object.
You can use format() to do simple positional formatting, just like you could with “old style” formatting:

name = "Emmanuel"
print("Hello, {}".format(name))

We can also use .format() to do simple positional formatting just like we could with old-style formatting

name = "Emma"
age = 24
print("Hey {}, You are {}!".format(age, name))

Or, you can refer to your variable substitutions by name and use them in any order you want. 
This is quite a powerful feature as it allows for re-arranging the order of display without changing the arguments passed to format()

name = "Emma"
error = 0xbadc0ffee
print('Hey {name}, there is a 0x{error:x} error!'.format(name=name, error=error))

This example shows that the syntax to format an int variable as a hexadecimal string has changed. 
Now you need to pass a format spec by adding a :x suffix.
In Python 3, this “new style” string formatting is preferred over old-style formatting. 
While “old style” formatting has been de-emphasized, it has not been deprecated. It is still supported in the latest versions of Python.
In the next section, I will show you the string literals way of formatting strings that lets you use embedded Python expressions inside string constants.

Create Store procedures

Store procedure has some similarities with views in the sense that it allows encapsulation. i.e. to retrieve for further, later use a series of commands, like the select command.

Learn Python  here.

With store procedure, it is possible to branch. It means you can get different statements to run based on specific requirements.

The begin and the end is compulsory in store procedure as it shows where the query starts and where it ends.

Note, you can use a select statement and create a procedure out of it.  Likewise, you can do with views.

The Store procedure is used as a data access layer, which means that it could access the data without going to the table directly.

Let's use the table schema below, to learn more about store procedures.

The TABLE_NAME is tblEmployee and has 7 columns under the COLUMN_NAME, each column will have data.

Step 1. 

Create Procedure and Execute

We will be using the table tblEmployee to create a procedure out of it.

In order to create and execute a procedure, the following commands are used.

The create procedure command, created a procedure with three columns, EmployeeNumber, EmployeeFirstName and EmployeeLastName out of the seven columns in the table tblEmployee.

And the execute or exec commands execute the created procedure and return the data as we can see below.

Note aside from the two execute command shown before, it is possible to return data from store procedure also by just using the name of the store procedure simply like:

EmployeesName 

without the execute or exec preceding it. But this can only be done after the end of a batch and go ends a batch, so it has to be after the go statement.

Step 2.

Create Procedure and Return data based on Input

The procedure we created at step 1 returns all the data for the employee three columns that we specified. Supposed we don't want this to happen, and we only want the return data based on a given input then how can this be achieved?

Let see that in action.

first I will drop the procedure that I have created earlier

There are things to put into consideration while dropping the procedure, 

1.  You can't drop procedure and drop it again - (with same name)
2. You can't create procedure and create it again - (with same name)

So we need to test it to make sure if exist or not and based on that we can perform the necessary actions.

Check if procedure exists before dropping the procedure.

The code above will return employee based on the given EmployeeNumber, that was passed in as an augment of integer type.

How to use IsNull in T-SQL

Enjoy the ongoing promo:

Python Intermediate Features Course On Udemy

The IsNull method is a built-in function that takes two parameters. The first parameter is the expression or variable that the IsNull method checks and returns true if the variable contains Null. The second parameter is the replacement value that the IsNull method returns if the expression is Null.

Below is an instance of a table with Null 

 

The student table contains student numbers and student names, and there are NULL in rows 3, 8 and 10.

Those three rows represent the case where there could be NULL in the database table.

Step 1.

To use the Isnull method, let us declare a variable StudentName and set it to Null.

Like this:  declare @StudentName as nvarchar(10) = Null

Then let use the Isnull method to check for the variable value, like this:

 

select Isnull(@StudentName, 'No name') as StudentName

By now you should know the result we are expecting right?

When we run the two lines of code together 

what happened is this:

If the StudentName is not equal to Null it returns the student name otherwise it returns No Name.

Step 2. 

The Isnull method can select all the Nulls in tblStudent1 and replace it with 'No Name'.

The code above replaces three rows 3, 8 and 10 in the student table with No Name instead of Null.

This shows that you can also use the Isnull method to create table.

How to Use "IsNull"  Within CASE to Calculate Data from Tables.

For example, I will shown two tables here as A and B, this could be any table from your database.

CASE

WHEN ISNULL(A, 0)=0

THEN

ISNULL(A, 0)

WHEN

ISNULL(A, 0) > ISNULL(B, 0)

THEN

ISNULL(A, 0) - ISNULL(B, 0)

ELSE

ISNULL(B, 0) - ISNULL(A, 0)

END,

ISNULL(B, 0) AS B,

As shown above the is null can be use to calculate and get result from two or more table using the join.

The join part is not shown, but here we consider two tables A and B tables. 

Below is the analysis of what the code above does.

Case

When isNull return 0

Then the result is 0

Case 2

When A is not null and is greater than B

Then subtract B from A

else is the case when A is not null and is less than B

then subtract A from B.

Note that Isnull is faster than coalesce and it is also better than coalesce where you've got two different types of  data type.

Creating an AFTER trigger

To understand what the trigger is, see it as automation. It fires whenever an event occurs.

A good example is an automated email that is sent out whenever someone submitted a form or registered to a website.

Trigger in SQL server act the same way. It is activated based on certain conditions.

We will use the table tblStudent below to explain the trigger.

You can read how to create this table here.

Step 1.

Create a Trigger for tblStudent table

To create a trigger for a table or view the following SQL syntax are used.

-- SQL Server Syntax  

-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  

  

CREATE TRIGGER trigger_name   

ON { table | view }   

{ FOR | AFTER | INSTEAD OF }   

{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }     

AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  

Now let use the above syntax to create a trigger for the student table.

If the above step is executed correctly then it created a trigger inside the Triggers folder on the dbo.tblStudent.

Step 2.

Delete Data and Roll it Back

Now let us write an SQL delete statement to delete from the tblStudent where StudentNumber equals 1.

Execute the code and you will see two results. 

The first one is empty and the second is with the data that we tried to delete.

Trigger prints the two results based on the two select statements in the trigger named tr_tblStudent that we have created in step 1. The first select returns table with empty data because we haven't inserted any data.

The second select returns the data we tried to delete which did not delete anyways because of the rollback tran.

So note, we executed the delete statements then trigger fires up and prints the results.

Difference between Delete and Truncate

Delete

Removes the records and remember the last primary key of the removed records.

This will allow the next records that will be inserted to start from the next number of the last deleted record.

For instance, if you create a table tblStudent with two columns:

Step 1

Create table tblStudent

(StudentNumber int Constraint PK_tblStudent PRIMARY KEY IDENTITY(1,1),

StudentName nvarchar(20))

StudentNumber - that has an int type, a constraint PK_tblStudent  and serves as the Primary Key with identity(1,1). The identity(1, 1) helps you to incrementally assign primary key starting from 1 without manually inserting those.

StudentName - that has just an nvarchar(20)

Step 2

Then use the insert  to insert into tblStudent values ('Maxy'), ('Byte')

once inserted it shows a table like this:

Step 3

Then use the delete to delete from tblStudent, then the two records will be deleted

as you can see below:

Because you have deleted the record using delete, now when inserting new records the last primary key number deleted was taken into remembrance and new records studentNumber start from the next number of the previously deleted number. To see that proceed to step 4. Note, this isn't the same when the records are truncated.

Step 4

Insert again using the insert into tblStudent values ('Maxy'), ('Byte').

This time what do you expect to see at StudentNumber column, 1 and 2 ? no that is wrong, it will come as 3 and 4.

                                           

If  I had used truncate to remove the records intially before inserting a new one then what would have happened? will it still come as 3 and 4 or 1 and 2 next time? 

Yes it will come 1 and 2. 

What is Truncate then?

TRUNCATE

Removes the records but never remember the last primary key of the removed records. This will allow the next records that will be inserted to start from the beginning again.

 

To verify this, instead of using delete at Step 3, do the same for truncate , with command 

truncate table tblStudent

Then repeat Step 4 to insert a new record, once inserted,  It shows a table where the StudentNumber start from the beginning and not from 3 down to 4 but from 1 down to 2:

TSQL-Working With Date And Time

Here you will learn how to work with the date and time in SQL Server.

select  GETDATE() as dateNow
This returns the current date and time.

select CURRENT_TIMESTAMP as dateNow

This also return the current date and time.

select SYSDATETIME() as dateNow

This return the current date and its more accurate. 

select datename(WEEKDAY, GETDATE()) as weekDay

This return the current weekday from the current date and time

select DATEADD(YEAR, 1, GETDATE()) as nextYear

This allow adding extra year to the current year.

select DATEDIFF(SECOND, '1984-09-07 12:24:08', GETDATE()) as monthsElapsed

The DateDiff  takes the time unit, the early date , the later date and return the months elapsed. 

The DateDiff shows the difference between two dates.

select DATEPART(hour, '2020-01-02 12:20:05') as theHour

This select the hour of the current date and time

Converting from dates to strings

declare @mydate as datetime = '2021-07-27 03:15:04.453'

select 'The date and time is: ' + @mydate

go

declare @mydate as datetime = '2021-07-27 03:15:04.453'

select 'The date and time is: ' + convert(nvarchar(20),@mydate,104) as convertedDate

go

declare @mydate as datetime = '2021-07-27 03:15:04.453'

select cast(@mydate as nvarchar(20)) as castDate

select try_convert(date,'Tuesday, 27 July 2021') as convertedDate

select parse('Tuesday, 27 July 2021' as date) as parsedDate

select format(cast('2021-07-27 03:15:04.453' as datetime),'D') as formattedLongDate

select format(cast('2021-07-27 03:15:04.453' as datetime),'d') as formattedShortDate

select format(cast('2021-07-27 03:15:04.453' as datetime),'dd-MM-yyyy') as formattedBritishDate

select format(cast('2021-07-27 03:15:04.453' as datetime),'D','zh-CN') as formattedInternationalLongDate

Date offsets

declare @myDateOffset as datetimeoffset(2) = '2021-07-27 03:15:04.453 +03:00' 

select @myDateOffset as dateOffset

go

declare @myDate as datetime2 = '2021-07-27 03:15:04.453'

select TODATETIMEOFFSET(@myDate,'+03:00') as dateOffset

select DATETIME2FROMPARTS(2015,06,25,1,2,3,456,3)

select DATETIMEOFFSETFROMPARTS(2015,06,25,1,2,3,456,5,30,3) as dateOffset

select SYSDATETIMEOFFSET() as timeNowWithOffset;

select SYSUTCDATETIME() as timeNowUTC;

declare @myDateOffset as datetimeoffset = '2021-07-27 03:15:04.453 +03:00'

select SWITCHOFFSET(@myDateOffset,'-03:00') as dateOffsetTallinn

 

TSQL- How to remove a character from a string

A string is a group of characters in a specified order, called sequence of characters.

This sequences are zero-indexed.

To remove a character from a string in TSQL use SUBSTRING.

The substring is a built-in function that takes, expression, starting point and length of the expression.

Based on those three parameters, it decides on what string to return

To remove the last character, what do you have to do?

Think about it, all you need to do is change the starting point value from 2 to 1.

CONCAT String With TSQL

Joining strings together

We will declare three variables and set their values, then the various select statement will be use for joining the string and print the outcome.

declare @firstname as nvarchar(20)

declare @middlename as nvarchar(20)

declare @lastname as nvarchar(20)

set @firstname = 'Maxybyte'

set @middlename ='Technologies'

set @lastname = 'Program'

select @firstname + ' ' @middlename +  ' '  + @lastname as FullName

FullName 

Maxybyte Technologies Program

What happens when you are adding three string while just two strings are set? then you get a Null as answer.

declare @firstname as nvarchar(20)

declare @middlename as nvarchar(20)

declare @lastname as nvarchar(20)

set @firstname = 'Maxybyte'

set @lastname = 'Program'

select @firstname + '  ' iif(@middlename is null, '', '  '  + @middlename+  '  '  + @lastname as FullName

FullName

Maxybyte Program

The following select statement can also be used.

select @firstname + CASE WHEN @middlename IS  NULL THEN ''  ELSE '  '  + @middlename END +  '  '  + @lastname as FullName

FullName

Maxybyte Program

The following select statement can also be used.

select @firstname + coalesce('  '  + @middlename, ") +  '  ' + @lastname as FullName

FullName

Maxybyte Program

The best use case for the select statement to join two string together is the CONCAT method

select CONCAT(@firstname, '  '  +  @middlename,  '  ', @lastname as FullName

 

 

Python Testcases and Testsuite in 38 minutes

In this video we are going to dive in quickly into something that mostly confuse some people that are just learning software testing. What are testcases and what are the purpose of testsuites? How are they use together? e.t.c. Find out in this video by watching it. Enjoy learning!

How to use Interactive Console to store data into database

In this tutorial, I will briefly write on how to use the Interactive Console in PyCharm to add data into a database.

Let us use the models below and populate data into the language field using the interactive console in PyCharm :

from django.db import models

# Create your models here.
class Language(models.Model):
    language_name = models.CharField(max_length=264, unique=True)

    def __str__(self):
        return self.top_name


class Webpage(models.Model):
    language = models.ForeignKey(Language, on_delete= models.CASCADE)
    name = models.CharField(max_length=264, unique=True)
    url = models.URLField(unique=True)

    def __str__(self):
        return self.name


class Record(models.Model):
    name = models.ForeignKey(Webpage,on_delete=models.CASCADE)
    date = models.DateField()

    def __str__(self):
        return str(self.date)

Now let us Add data to Language field

Step 1. use command python manage.py shell
Step 2. Import the class or field to add data into, e.g, from  app_name.models import class_name
Step 3. Create an instance of the class_name, e.g  L = Language(lan_name = "Python")
Step 4. L.save()
Step 5 print(Language.objects.all())

The last step prints what has been saved in the database.

Then type python manage.py runserver, if everything is working fine then you should see the following:

System check identified no issues (0 silenced).
June 11, 20-- - 16:28:01
Django version 3.0.7, using settings 'first_project.settings'
Starting development server at http://127.0.0.1:8000/
Quit the server with CTRL-BREAK.

However, In order to fully use the database and the Admin, we will need to create a “superuser”

We can do this with the following:
        python manage.py createsuperuser

Then go to the following link, http://127.0.0.1:8000/admin and log in with your superuser details and you should see your data inside the field you have added it to. In our case the Language field.

Watch this.