Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- 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.
Output
[(1, 'John', 'Hill', 'M'), (2, 'Jessy', 'Hill', 'F'), (3, 'Antony', 'Hill', 'M')]
Conclusion
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.
998 Views
