In this Python & SQLite Database article we are going to learn How to Connect Python with SQLite Database, first of all let’s talk about some concepts.
What is Python ?
Python is an interpreted, high level and general purpose programming language that was first released in 1991 by Guido van Rossum. It is designed to be easy to read and write and it emphasizes code readability and simplicity.
Python has large and active community of users who have developed many libraries and tools for the language and this makes it a popular choice for different types of applications, including web development, scientific computing, data analysis, artificial intelligence and many more.
Python is an interpreted language it means that the code is not compiled but instead executed line by line by the interpreter. this allows for rapid development and debugging of code.
Python syntax is easy to learn and read and this makes it an ideal language for beginners to learn programming. It supports multiple programming paradigms including object oriented, imperative and functional programming, and it has different library of builtin functions and modules that make common programming tasks easy.
We can say that Python is popular and powerful language that can be used for different types of applications and is accessible to both beginners and experienced programmers.
What is SQLite ?
SQLite is an embedded relational Database engine. the documentation calls it a self-contained, serverless, zero configuration and transactional SQL database engine. It is very popular and there are hundreds of millions copies worldwide in use today. Several programming languages have built-in support for SQLite including Python and PHP. In Python Sqlite3 is a built in and you don’t need to Install that.
So now lets write code for Python SQLite Database For Beginners
1: SQLite Database Connection
So first of all we are going to Create SQLite Connection.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
import sqlite3 as lit def main(): try: db = lit.connect('myemployee.db') print("Database created") except: print("failed to create database") finally: db.close() if __name__ == "__main__": main() |
In the above code we have used sqlite3, The sqlite3 module is builtin Python module that provides lightweight database engine for SQLite databases. It allows developers to interact with SQLite databases using Python code.
The main function is defined, which attempts to create connection to the SQLite database file “myemployee.db”. if the connection is successful, function prints a message “Database created”. If the connection fails, the function prints a message “failed to create database”. Finally, the function closes the database connection using the close method.
and also if __name__ == “__main__”: statement checks if the script is being run as the main program, and if so, it calls the main function. This allows the script to be used as standalone program or imported as a module in another Python script.
In summary, this script creates a new SQLite database file named “myemployee.db” and prints a message indicating whether the database creation was successful or not.
If you run your code you will see this result
2: Creating Table in SQLite
Using this code we are going to create tables for the Sqlite Database. you can use the SQL query for creating of tables in Sqlite.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
import sqlite3 as lit def main(): try: db = lit.connect('myemployee.db') cur = db.cursor() tablequery = "CREATE TABLE users (id INT, name TEXT, email TEXT)" cur.execute(tablequery) print("Table Created Succesfully") except lit.Error as e: print("Unable To Create Table") finally: db.close() if __name__ == "__main__": main() |
In the above code tablequery variable is defined as string that contains an SQL query to create new table named “users” with three columns: “id” of type INT, “name” of type TEXT and “email” of type `TEXT”.
The execute method of the cursor object is called with tablequery as an argument to execute the SQL query and create the new table. If the execution is successful, the function prints a message “Table Created Successfully”. If there is any error while executing the SQL query, the function catches the exception and prints an error message “Unable To Create Table”.
Finally, the function closes the database connection using the close method.
The if __name__ == “__main__”: statement checks if the script is being run as the main program, and if so, it calls the main function. This allows the script to be used as a standalone program or imported as a module in another Python script.
If you run the code, this will be the result
3: Inserting Data in SQLite Database
Now it is time to insert data in to Sqlite database, so you can use this code for inserting data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
import sqlite3 as lit myuser = ( (1, 'Parwiz', 'par@gmail.com'), (2, 'John', 'john@gmail.com'), (3, 'Bob', 'bob@gmail.com'), (4, 'Tom', 'tom@gmail.com'), ) db = lit.connect('myemployee.db') with db: cur = db.cursor() cur.executemany('INSERT INTO users VALUES (?,?,?)', myuser) print("Data Inserted Successfully") |
This is the output
4: Selecting Data in SQLite Database
For selecting data first of all you need to create a cursor() object and after that you can select all the data from the Sqlite database in Python.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
import sqlite3 as lit db = lit.connect('myemployee.db') with db: cur = db.cursor() selectquery = "SELECT * FROM users" cur.execute(selectquery) rows = cur.fetchall() for data in rows: print(data) |
This is the output
5: Updating Data in SQLite Database
Now let’s update our data, first you need to create object of the cursor(), after that you can use update query for updating the specific data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
import sqlite3 as lit db = lit.connect('myemployee.db') with db: newname = "updated name" user_id = 1 cur = db.cursor() cur.execute('UPDATE users SET name = ? WHERE id = ?', (newname, user_id)) db.commit() print("Data Updated Successfully") |
This is the output
6: Deleting Data in SQLite Database
This is the code for deleting the data, and we are going to delete just one item from the table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
import sqlite3 as lit db = lit.connect('myemployee.db') with db: newname = "updated name" user_id = 1 cur = db.cursor() cur.execute('DELETE FROM users WHERE id = ? ', (user_id,) ) db.commit() print("Data Deleted Successfully") |
Also you can watch the complete video for this article.
Subscribe and Get Free Video Courses & Articles in your Email
faz botao pra remove item selecionado pelo mousse