- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
SQL using Python and SQLite
In this article, we are going to learn how to use SQL with Python and SQLite database. Python has a built-in module to connect with SQLite database. We are going to use sqlite3 module to connect Python and SQLite.
We have to follow the below steps to connect the SQLite database with Python. Have a look at the steps and write the program.
- Import the sqlite3 module.
- Create a connection using the sqlite3.connect(db_name) the method that takes a database name is an argument. It creates one file if doesn't exist with the given name else it opens the file with the given name.
- Get the cursor object from the connection using conn.cursor(). It's the mediator between Python and SQLite database. We have to use this cursor object to execute SQL commands.
Above three steps are helps us to create a connection with an SQLite database. These steps are similar to any database in Python. See the below code if you have any confusion in the above steps.
# importing the module import sqlite3 # creating an connection conn = sqlite3.connect("tutorialspoint.db") # db - database # Cursor object cursor = conn.cursor()
Now, we a connection with a database. Let's create a database with SQL queries by following the below steps.
- Write SQL code to create a table with column names and types.
- Execute the code using cursor.execute() to create the table in the database.
- Write SQL code to insert some rows into the table. And execute them similar to the above step.
- Commit the changes to save them in the file using conn.commit() method.
- Close the connection using conn.close() method.
# importing the module import sqlite3 # creating an connection conn = sqlite3.connect("tutorialspoint.db") # db - database # Cursor object cursor = conn.cursor() # code to create a databse table create_table_sql = """ CREATE TABLE students ( id INTEGER PRIMARY KEY, first_name VARCHAR(20), last_name VARCHAR(30), gender CHAR(1) ); """ # executing the above SQL code cursor.execute(create_table_sql) # inserting data into the students table insert_student_one_sql = """INSERT INTO students VALUES (1, "John", "Hill", "M");""" cursor.execute(insert_student_one_sql) insert_student_two_sql = """INSERT INTO students VALUES (2, "Jessy", "Hill", "F");""" cursor.execute(insert_student_two_sql) insert_student_three_sql = """INSERT INTO students VALUES (3, "Antony", "Hill", "M");""" cursor.execute(insert_student_three_sql) # saving the changes using commit method of connection conn.commit() # closing the connection conn.close()
If you didn't get any error after executing the above code, then you are good to go.
How to see the data from the database tables? Let's write the code with the given steps.
- Connect to the database.
- Create a cursor object.
- Write a SQL query to get the data that you want from the table.
- Now execute it.
- Cursor object will have the data what you want. Get it using the fetchall() method.
- See the data by printing it.
You can see the below code if you have any doubts.
# importing the module import sqlite3 # creating an connection conn = sqlite3.connect("tutorialspoint.db") # db - database # Cursor object cursor = conn.cursor() # SQL query to get all students data fetch_students_sql = """ SELECT * FROM students; """ # executing the SQL query cursor.execute(fetch_students_sql) # storing the data in a variable using fetchall() method students = cursor.fetchall() # a list of tuples # printing the data print(students)
If you execute the above program, then you will get the result similar to the output.
[(1, 'John', 'Hill', 'M'), (2, 'Jessy', 'Hill', 'F'), (3, 'Antony', 'Hill', 'M')]
Now, you are ready to work with databases in Python. Practice more to get more. If you have any doubts in the tutorial, mention them in the comment section.
- SQL using C/C++ and SQLite
- SQL using Python
- Difference between SQL and PL/SQL
- Difference Between SQL and T-SQL
- How to count SQL table columns using Python?
- Difference between Static SQL and Dynamic SQL
- Difference Between T-SQL and PL-SQL
- Difference between SQL(Structured Query Language) and T-SQL(Transact-SQL).
- Python interface for SQLite databases
- Using SQL statements in ABAP Programming and Database performance
- Sort data in SQL using Dynamic SQL in SAP HANA
- How to filter data using where Clause and “AND” in Android sqlite?
- Difference between SQL and NoSQL
- Explain the use of sql LIKE operator using MySQL in Python?
- Load and unload a table in SAP HANA using SQL query