Connect with us

programming-guides

Complete guide on python and sqlite [2020]

Published

on

If you are a beginner level programmer then you might don’t come across the database need into your project, but if you want to be a good programmer you should learn databases as they will be used in every application you might be building into future. If you are an intermediate level programmer, then you might encounter the need of databases into your project and that is why you want to learn database right now. No matter what is your intention the main focus is how you can set you up to become comfortable in using databases in any kind of project. In this article we will explore python and sqlite in detail.

First thing in learning databases and that is start from complete basic level database operations with probably some small database and then move on to the next big database.

Let me explain this to you in easy way. You want to learn database right? If you want to learn database then you probably want to start learning it from absolute beginning and you might also want to start learning database from completely scratch so that your logic become clear about database, right?

Let’s do that.

In this Article I will explain what is SQLite and how to perform basic database operations in sqlite3 database.

What is SQLite and sqlite3? (python and sqlite)

SQLite is a lightweight database mainly suitable for small applications and for beginner and intermediate level programmers who want to start digging into the field of databases. And sqlite3 is an upgraded form of SQLite database

Comparing SQLite with SQL:

I think that there is no need to compare these two databases because the actual and big database that is used most of the time is SQL and SQLite is just the light version of SQL database. But if there is no use of anything then why that thing even exists. The answer is pretty straight forward and that is SQL database is used for commercial applications and it can be learned easily after learning SQLite and that’s it.

Basic Crud Operations in SQLite3 database:

In order to perform any operation onto database the first thing that we should keep in mind is simple and that is to connect you programming language with the database itself. So let’s do that first.

Connect python with sqlite3||python sqlite create database:

To connect python with sqlite3 you just have to have python installed and that is all you need. That’s why most programmers prefer to start with sqlite3 database whenever you need to connect database with python.

Open up idle and create a new file there and name it as crud_operations.py. Once you do that just type the following command to connect python with sqlite3 database. Type the following code into code file.

Import sqlite3
conn = sqlite3.connect(“data.db”)
c = conn.cursor()

and your database is connected with your python. You didn’t have this data.db file into your pc and that is ok because it will create one for you. What the above code is doing let’s explain that.

So we are importing the sqlite3 database and don’t worry to install it, it comes built in with python.

In the second line we are establishing a connection of our python file to our database file.

And at last we are placing the cursor function into c variable to use it to execute sqlite3 commands. Now let’s start inserting some data into it so that you can see the database in action.

Insertion(python sqlite3 insert)

To insert data into database file you need to create a table, so we will be creating a function called create_table and we will add the code to create table into this function so that we can reuse this function whenever we need to create any table.

Type the following code:

def create_table():
	c.execute(“CREATE TABLE IF NOT EXISTS data (name TEXT, email TEXT, phone VALUE)”)
	conn.commit()

what the above code is doing is simple we are just creating a basic function of python and inside function there are two line the first line is used to execute database query and the second line is committing the changes inside database.

now we will create a function to insert data into our database.

def insert_data():
	c.execute(“INSERT INTO data VALUES (‘mubashar’, ‘sm@mubashartech.com’, 23233)”)
	conn.commit()
	c.close()
	conn.close()

So we are creating a function called insert_data and first two lines are performing the same functionality as above but the difference is above we are creating a table and here we are inserting data into table. c.close() is used to close the cursor once operation performed and conn.close() is used to close the connection to stop memory from being used. You can skip these two lines called c.close() and conn.close().

Run our program by adding these two lines at the end of code file

create_table()
insert_date()

Save our file as crud_operations.py.

Now run our program by pressing f5 if you are coding in python idle and if you are working on some other compilers then type the required key to run the program.

You will see a new file created called data inside the folder where your crud_operations.py file is present.

Installing db browser for sqlite3 (optional):

Now if you want to see the data then install db browser for sqlite3. And if you don’t want to see table data manually you can skip this step, in the next step we will read our data through sql commands. But if you want to see the data using db browser then search for it on google and download db browser and open data.db file inside db browser for sqlite3.

Read data from database|| python sqlite select:

Now you have successfully input data into database, now let’s retrieve data from the database.

To retrieve data from the database, we will use the SELECT statement. Add these lines of code at the end of crud_operations.py file.

def read_data():
	c.execute(‘SELECT * FROM data’)
	conn.commit()

what this function will does is simply it will select the entire data from the database table and now let’s print data by iterating over it. We will use a for loop to iterate over data to make it more readable.

Add these lines after conn.commit().

for items in c.fetchall():
		print(items)

And you will see the line retrieved that we had inserted before.

One issue you might encounter is cannot run execute on closed connection and that is because in the insert data function we closed the connection, to get rid from this just comment those two functions and add this line at the end of crud operation file.

#create_table()    # this sign will make it a comment and it will not run.

#insert_data()   # just comment both function calls so that you don’t get an error.

Add this line at the end

read_data()

You will see the output in console/terminal. Ok If you follow the exact same steps then you might get the desired output. The next step is to insert data through variables in python.

Insert Data dynamically|| python sqlite3 insert:

For this type the following code into your code file.

name = input(‘enter your name:’)
email = input(‘enter your email:’)
phone = input(‘enter your phone:’)

Now that you have taken 3 inputs from user, the next step is to store it inside database.

Create a function called insert_data_dynamically and type the following lines of code into it.

def insert_data_dynamically():
	c.execute(“INSERT INTO data (name, email, phone) VALUES (?, ?, ?)”,(name, email, phone))
	conn.commit()

Commit out the function calls we have made before and add this line at the end of code file.

Insert_date_dynamically()

Once you run the program then you will see it is asking for input your name, email and phone and once you give the input then it will store it inside database and you can check the data by running the function called read_data().

Type the following line of code at the end of file.

read_data()

Delete Data from Database||python sqlite delete:

Now we have successfully inserted and retrieve data from database. it is time to move ahead and start learning delete data from database.

To delete data from database, just make a new function and add these lines of code to it.

def del_data():
	c.execute(“DELETE FROM data WHERE name=’mubashar’”)
	conn.commit()

call this function and you will see that the line in which name = ‘mubashar’ will be deleted from database.

del_data()
read_data()

Update data in database|| python sqlite update:

Now we will make a new function that will update data into database.

def update_data():
	c.execute(“UPDATE data SET name=’mubashartech.com’ WHERE name=’mubashar’”)
	conn.commit()

If you call function.

update_data()
read_data()

then you will see the name updated successfully.

Complete code written in this tutorial:

import sqlite3
conn = sqlite3.connect('data.db')
c = conn.cursor()
def create_table():
    c.execute("CREATE TABLE IF NOT EXISTS data(name TEXT, email TEXT, phone REAL)")
# insert data dynamically
def insert_data_dynamically():
    #taking input from user
    name = input('Enter name:')
    email = input('Enter email:')
    phone = input('Enter phone:')
    c.execute("INSERT INTO data (name, email, phone) VALUES (?, ?, ?)", (name, email, phone))
    conn.commit()
# running create table and insert_into_table functions
#create_table()
#insert_data_dynamically()
#c.close()
#conn.close()
#Read from database
def read_from_db():
    c.execute("SELECT * FROM data")
    data = c.fetchall()
    for items in data:
        print(items)
     # selecting data based on particular name
    # c.execute("SELECT * FROM data WHERE name='mubashartech.com'")
     #for items in c.fetchall():
      #   print(items)
#update data from database
def update_data():
    
    c.execute("UPDATE data SET name='john1234' WHERE name='john'")
    conn.commit()
    #c.execute("SELECT * FROM data")
    #for rows in c.fetchall():
     #   print(rows)
    
def delete_data():
    c.execute("DELETE FROM data WHERE name='nothing'")
    conn.commit()
delete_data()
read_from_db()

I hope you have learned a lot with this complete guide and if you have any question feel free to ask it in the comment section and I will definitely answer. I have already make some cool project tutorials with python you can check them here as well.

Continue Reading
Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *

programming-guides

Very Important things to know about programming. [2020]

Published

on

By

In this post, I will discuss important things to know about programming, so read this post completely to get the most out of it.

Click here to read about the five best websites to learn to code.

Here are the things to know about programming:

Programming is not hard:

Most programmers tell you this because once you become a programmer and think about the learning curve. You follow on to become a programmer seems relatively easy because they all know that they can go to the place where they are right now if they struggled a little bit.

Most programmers are self-made because of no school. College or university can tell the origin or core of programming. You have to figure it on your own. And your desire to become a programmer helps you to do this.

Learn by Practicing:

The no. 1 mistake most beginners make while learning to code is; first, they sit at the place and start watching tutorials and reading books without practicing much. Programming is a knowledge that requires a lot of practice and logic building. The logic behind programming can be cleared simply by just practicing it. And then apply that logic to real-world projects and your routine tasks.

Many of us want to learn all the programming fundamentals first. And then we go for doing projects and dig deeper into problem-solving. But this is not the best practice because if you learned all the programming fundamentals without practicing much, it doesn’t worth it. After all, you don’t get the knowledge of working with real-life projects. Either you are just boring yourself. Either go for doing a small project whenever you are just starting out and then move ahead. In this way, once you finished all programming fundamentals. Then you will also have a pretty great real-world project to showcase on your resume, and all of your logic about programming would be cleared.

Don’t work hard Either work Smartly:

The essential advice in this article (things I wish I knew when I started coding?) is this:

The reason why I am telling you this is because I have worked 15 hours a day. While learning to code and that’s the result in so much confusion. And you do not understand what you want to know. It would help if you remembered to program smartly. Because in this way you will be able to get the most out of it and enjoy what you are doing.

Programming is Fun:

Programmers are intended to solve lots of complicated problems. And that is even good because once you have reached to point when you enjoy programming. Then think of yourself as a programmer. Because you have your profession just like your passion, and this keeps the programmers motivated.

Enjoy what you are doing in Programming:

Just learn to program because you want to know it is not a satisfactory answer to yourself. Because you will eventually get bored while doing so. Either thing of an idea and start collecting information about it. i.e., what you want to build and what languages are required to do so, then you will be enjoying programming. And You will be able to learn it even faster and transform your idea into reality. If you want to know more about things to know about programming by HONGIAT, then click here.

I think the above reading has motivated you a lot. And if it then gives your feedback in the comment section below. Ask a query in the comment section, and I will answer.

Continue Reading

programming-guides

How to monetize our programming skills? [2020]

Published

on

By

How To Monetize Our Programming Skills?

You probably thought about monetizing programming skills to make a living, and it is pretty straight forward. If you have the talent, then you will get paid. But before we start monetizing our programming skills, let’s discuss how you should go with your programming portfolio to be successful in making a living in programming.

How to choose the niche for learning advanced stuff in programming?

You might be a good programmer, but programming is not hard and not very easy to digest in a day; it is a long game that needs to be played continuously to get a grip on this.

The basic knowledge of programming cannot get you a job; either you have to dive into a framework of your choice or maybe learn an entirely new programming field to get your hands dirty.

You have done some programming till now, and you may have the idea of popular fields in programming such as web development, web designing, mobile application development, and so on. Just learn any one of these fields and try to monetize it by offering it to online customers.

At first, you might not get any work just like me, but eventually, as your experience grows, you probably get more jobs.

How should you structure your portfolio?

When it comes to making money online or starting a career in tech or programming in general, the first thing that every techie or programmer should consider is to create an excellent portfolio to stand out. A good portfolio can help you to stand out from the crowd to win the project or job.

To make a good portfolio, you should consider the following steps.

  1. Try to do some real-world projects.
  2. Your projects should be deployed somewhere so that you can show them to your client’s life.
  3. People are design buyers; what I mean by this is simple. An excellent looking project can be more attractive to clients, eventually helping you win the job.
  4. Don’t be stuck in trying to make something big to show to your client/Employee; instead, make a small but attractive and functional project to demonstrate your skills.
  5. Your project should be mobile responsive because the usage of mobile is high now a day.

Going with Freelancing:

As you go with freelancing, you might encounter some problems in getting projects at first, but keep trying and making you better day by day can lead to success.

Just try to do some cool projects and also try to make your profile better.

The importance of building a profile is much more important than making a portfolio. So try to present the real picture of yours, so that you get the people’s attraction.

Become a Blogger:

Blogging is the best thing that I recommend to my readers because it pays in one way or another. Once you get your blog up and running, you will have many options to monetize it.

Benefits of blogging:

  1. The benefits of blogging have a considerable number, but I will discuss some of them here.
  2. Your blog is your personal property; no one can steal it from you.
  3. You will get a huge audience if you put quality content into your blog.
  4. You will have a group of readers that you can sell products of your choice.
  5. While blogging about a particular topic, you will learn a lot.
  6. You will eventually monetize your blog with the best options, including Google AdSense, affiliate marketing, and probably some paid sponsorship that you will get from companies to promote their product or services.

Join a small Company/Software house:

If you have made an excellent portfolio to add to your resume, you can prepare your cv and apply for a remote job.

The benefits of doing a remote job for a company is also useful in numbers.

  1. You will learn a lot by working on real-world projects that your company will provide you.
  2. You will be introduced to the excellent practices that a firm follows while working.
  3. Professional behavior that the company you are working with will teach you.
  4. You can add this experience to your portfolio for further best opportunities.

I hope you have a lot of cool stuff in this article, and eventually, you will start applying it. If you, then please let me know in the comment section that you are going to adopt. Thanks.

Continue Reading

programming-guides

5 best website to learn code [2020]

Published

on

By

website to learn code

You probably wandering about best website to learn code and you might be getting unexpected results from your searches, but in this article i will help you to recognize the best platform to start your coding career. Without wasting more time lets focus on best website to learn code.

Why should you learn to code in 2020?

You are searching about best website to learn code because you may want to shift your career or you might be thinking about adding this skill into your skillset. But let’s take a little time to discuss why should you learn to code at all?

The answer to this question might vary on certain conditions but in my opinion everyone who needs to change their career or want to grow in a tech career should learn to code. Learning to code can be a fun part in your day to day routine. Adding this skill into your skillset have many benefits. Let’s discuss them shortly:

  1. It can help you to grow your business.
  2. Helps you to think more logically.
  3. Coding plays a vital role to reshape your business according to trends that world is adapting.
  4. By knowing how to code can help you to transform your ideas into a form.
  5. Provides benefits to you in earning a side income.
  6. Can help you in making a complete shift from your 9 to 5 job to something called a remote work.
  7. By knowing a little bit about coding can help you to teach what you are passionate about online.
  8. It influences you to help others using your tech/coding skills.
  9. Last but not least it eventually can help you to get a job.

Why it became very easy for you to learn coding free in 2020?

Now let’s discuss why it became very easy for you to learn coding free of cost in 2020.

There are probably huge amount of websites or tech bloggers who help you in learning the skills you want to learn. Talking about technology and coding related stuff is one of the favorite things that bloggers like me want to write about because teaching others how to code eventually help us to broaden our knowledge as well. Of course there are plenty of websites that are willing to teach you about coding free of cost or some of them might charge a little bit. We will discuss Some cool websites that I personally like to use whenever I want to something new that is related to coding and tech in general.

Best places to learn to code|| Best website to learn to code.

Now let’s discuss about best websites to learn code.

1-Treehouse:

It is one of my favorite and best website to learn code. Because the students enrolled here can learn coding by taking quizzes and interacting with coding challenges provided by treehouse team. It has more than 1500 interactive lectures. Those lectures eventually help students to learn what they want to learn. It has fairly cool challenges that help students to get a command on programming and coding core topics such as algorithms etc.

2-YouTube:

YouTube is amongst the favorite platform of developers when they want a quick getting started guide. It can help beginners in learning programming core topics free of cost. It is the world’s second largest search engine that is searched by uncountable number of people throughout the world. Of course you can learn coding from YouTube using the tutorials and playlists provided by other developers for you to learn.

3-GitHub:

GitHub is very cool website to learn and contribute to open source projects. It might be a very good website for intermediate level developers who want to broaden their knowledge by contributing to open source projects. Beginners can also use GitHub as a place where they can put their code to show off to someone else who can review their code and give a feedback to them.

4-Khan Academy:

This website offers coding education in a practical manner so that students can learn at their own pace and eventually grasp everything they have learned. Students can have a separate dashboard and also have multiple instructional videos that will help them to learn coding more efficiently.

5-Coursera:

It is also one of my favorite website to learn code because the courses taught here have practical knowledge up to an extent. You will find quizzes and exercise from the top professional teachers throughout the globe. You can collaborate with other students, you can see how other students solve the same problem and much more. Some courses here are free and some are paid. But you can apply financial aid to paid courses if you cannot afford those courses. You can audit courses to learn without getting certificate and it is also good if you don’t want to spend money. You just want to learn and if you became good at coding then you don’t need any certificate to get a job, you can show off your work to potential clients and can get a job.

I hope this article gives you a very cool idea about where to find resources in order to learn coding. If you want to create your own website then i have created a tutorial on that please checkout that as well here. As usual if you have any question then feel free to ask it in the comment section and i will definitely answer. Thanks.

Continue Reading

Trending