Here’s What No One Tells You About SQLITE3

Buddies there… Aren’t we cheered to explore more on SQLITE3. Let’s look into more.

The mobile phone we use, the computers we show up comes with bundles of Sqlite. Will you believe?? Why don’t we look up somethings which grabs us more….

SQL is a simple but effective language for managing and querying data from databases without needing to replicate it beforehand.

DATABASE- A database is a collection of logical, consistent, and organised data that can be easily accessed, controlled, and updated.

Example- storing records of student details

The use of database differs from organization. The most popular use is Relational Database, which facilitates table(row /columns)

Images https://www.toptal.com/database/database-design-bad-practices

Database Management System (DBMS): It is a software that facilitates users and different applications to store, retrieve, and manipulate data in a database.

What is SQL all about??

Looks like we hear it before… SQL denotes Structured Query Language, is a database language that allows to store, manipulate, and retrieve data.

How come Sqlite is getting known? Why is much to know?

• SQLite is a SQL-based relational database management system.
• It’s made for embedded devices that need data to be sent quickly and reliably.
• · It’s platform independent, lightweight, and doesn’t require any configuration.
• Sqlite enables to connect database in Python.

Connectivity of Database with Sqlite3

1. First step to make sqlite3 available in your compiler-

2. Connecting the sqlite3 with the database-

It leads to already created database path if any. Else, it will create a new database on the given path.

3. Creating Cursor() object, which is instances to get and call the inputs-

Not to forget…

1. Commit()-

Save (commit) the changes. It’s mandatory to commit before ending the queries, as any changes maybe be saved in database.

2. Close()- close the database connection to avoid misuse of data and database getting locked.

close()

SQL Queries plays an vital role in database

1. Create Table- To build a new table in a database, use the CREATE TABLE statement.

The table’s column names are specified by the column parameters and datatype parameter defines the type of data that can be stored in the column (e.g. varchar, integer, date, etc)

creating the table in database

2. Inserting the values- The INSERT INTO statement is used to insert new records in a table.

i. Both the column names and the values to be added must be specified:

ii. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:

To insert many values-

INSERT INTO table_name VALUES(followed by the elements to be inserted separated with comma)

Methods that are to be included with insert to run up with database-

• execute()
• executescript()
• executemany()

execute() — to store the values to be inserted in database

executescript()- The executescript() method allows us to execute the whole SQL code in one step.

executemany()- the executemany statement to insert multiple rows at once.

How to run the database, get the values??

SELECT- We utilize the SELECT command and the attribute values we want to retrieve to get values from the database.

To retrieve all the data stored in the database, we can use * instead of column name.

or can make use of for loop to select the items in the database

for loop with fetchone()

How to fetch the output

There are 2 core methods- fetchone() and fetchall()

1. fetchone()- outputs a single matching row

fetching single element

Selecting all element with fetchone(), fetches only single items.

2. fetchall()- to get a list of the matching rows.

Fetching all elements in the database

Catch you all with new fun … Got a question? Please pin me in the comment section, will get back to you :)

MORE BLOGS ON THE WAY !!!

Let’s have more talk on Linkedin

Cheers

Shifana Tasneem :)

More on SQL Queries on the way…

Resources from- https://www.w3schools.com/sql/default.asp https://docs.python.org/3/library/sqlite3.html