How to Skip Table Creation Again in Sqlite Python

In this article, integrating SQLite3 with Python is discussed. Here we will hash out all the CRUD operations on the SQLite3 database using Python. CRUD contains four major operations –

Note: This needs a basic understanding of SQL.

Here, we are going to connect SQLite with Python. Python has a native library for SQLite3 called sqlite3. Let u.s. explicate how it works.

Connecting to SQLite Database

• To utilise SQLite, we must import sqlite3.

import sqlite3

• Then create a connection using connect() method and pass the proper noun of the database you want to admission if there is a file with that name, it will open that file. Otherwise, Python will create a file with the given proper noun.

sqliteConnection = sqlite3.connect('gfg.db')

• After this, a cursor object is called to be capable to send commands to the SQL.

cursor = sqliteConnection.cursor()

Example: Connecting to SQLite3 database using Python

Python3

import sqlite3

connection = sqlite3.connect( "gfg.db" )

crsr = connexion.cursor()

print ( "Connected to the database" )

connexion.close()

Output:

Connected to the database

Cursor Object

Earlier moving further to SQLite3 and Python let'due south talk over the cursor object in cursory.

• The cursor object is used to make the connection for executing SQL queries.
• It acts as middleware between SQLite database connection and SQL query. It is created after giving connection to SQLite database.
• The cursor is a control structure used to traverse and fetch the records of the database.
• All the commands volition be executed using cursor object only.

Executing SQLite3 Queries – Creating Tables

After connecting to the database and creating the cursor object let'southward see how to execute the queries.

• To execute a query in the database, create an object and write the SQL command in it with existence commented. Example:- sql_comm = "SQL statement"
• And executing the command is very easy. Call the cursor method execute() and pass the proper noun of the sql control every bit a parameter in information technology. Save a number of commands as the sql_comm and execute them. After you lot perform all your activities, save the changes in the file by committing those changes and then lose the connexion.

Example: Creating SQLite3 tables using Python

In this example, nosotros will create the SQLite3 tables using Python. The standard SQL command will be used for creating the tables.

Python

import sqlite3

connectedness = sqlite3.connect( "gfg.db" )

crsr = connectedness.cursor()

sql_command =

crsr.execute(sql_command)

connectedness.close()

Output:

Inserting into Table

To insert information into the table nosotros will again write the SQL command every bit a string and will utilise the execute() method.

Case 1: Inserting Data into SQLite3 table using Python

Python3

import sqlite3

connexion = sqlite3.connect( "gfg.db" )

crsr = connexion.cursor()

sql_command =

crsr.execute(sql_command)

sql_command =

crsr.execute(sql_command)

connection.commit()

connection.close()

Output:

Example two: Inserting data input by the user

Python3

import sqlite3

connection = sqlite3.connect( "gfg.db" )

crsr = connection.cursor()

pk = [ ii , 3 , 4 , 5 , vi ]

f_name = [ 'Nikhil' , 'Nisha' , 'Abhinav' , 'Raju' , 'Anshul' ]

l_name = [ 'Aggarwal' , 'Rawat' , 'Tomar' , 'Kumar' , 'Aggarwal' ]

gender = [ 'One thousand' , 'F' , 'Yard' , 'One thousand' , 'F' ]

engagement = [ '2019-08-24' , '2020-01-01' , '2018-05-14' , '2015-02-02' , '2018-05-14' ]

for i in range ( five ):

crsr.execute(f 'INSERT INTO emp VALUES ({pk[i]}, "{f_name[i]}", "{l_name[i]}", "{gender[i]}", "{date[i]}")' )

connexion.commit()

connection.close()

Output:

Fetching Data

In this department, nosotros accept discussed how to create a table and how to add together new rows in the database. Fetching the data from records is uncomplicated equally inserting them. The execute method uses the SQL command of getting all the data from the table using "Select * from table_name" and all the tabular array data can be fetched in an object in the form of a listing of lists.

Instance: Reading Data from sqlite3 table using Python

Python

import sqlite3

connexion = sqlite3.connect( "gfg.db" )

crsr = connection.cursor()

crsr.execute( "SELECT * FROM emp" )

ans = crsr.fetchall()

for i in ans:

print (i)

Output:

Note: It should exist noted that the database file that will be created will be in the same binder as that of the python file. If we wish to change the path of the file, modify the path while opening the file.

Updating Data

For updating the data in the SQLite3 tabular array we will use the UPDATE statement. We tin can update single columns as well as multiple columns using the UPDATE statement as per our requirement.

UPDATE table_name SET column1 = value1, column2 = value2,…   WHERE condition;        

In the above syntax, the Set up statement is used to set new values to the particular column, and the WHERE clause is used to select the rows for which the columns are needed to be updated.

Example: Updating SQLite3 table using Python

Python3

import sqlite3

conn = sqlite3.connect( 'gfg.db' )

cursor = conn.cursor()

cursor.execute( )

conn.commit()

conn.shut()

Output:

Deleting Information

For deleting the data from the SQLite3 tabular array we can apply the delete command.

DELETE FROM table_name [WHERE Clause]

Instance: Deleting from SQLite3 table using Python

Python3

import sqlite3

conn = sqlite3.connect( 'gfg.db' )

cursor = conn.cursor()

cursor.execute( )

conn.commit()

conn.close()

Output:

Deleting Tabular array

Drop is used to delete the entire database or a tabular array. It deleted both records in the table forth with the table structure.

Syntax:

Drop TABLE TABLE_NAME;

Example: Drop SQLite3 table using Python

Full tables in the gfg.db before dropping

Now let's drop the Student table and so once more check the total table in our database.

Python3

import sqlite3

conn = sqlite3.connect( 'gfg.db' )

cursor = conn.cursor()

cursor.execute( )

conn.commit()

conn.shut()

Output:

Notation: To learn more than nearly SQLit3 with Python refer to our Python SQLite3 Tutorial.

This article is contributed by Rishabh Bansal. If you like GeeksforGeeks and would like to contribute, you tin too write an commodity using write.geeksforgeeks.org or mail your commodity to review-team@geeksforgeeks.org. See your commodity appearing on the GeeksforGeeks main page and assistance other Geeks.

Please write comments if you find anything wrong, or y'all want to share more data nigh the topic discussed above.

spencersionceend1960.blogspot.com

Source: https://www.geeksforgeeks.org/sql-using-python/

Share this post