SQL is a structured query language that is used to communicate with a database where the database is the organized collection of data that is usually stored electronically on your computer. There various types of database management systems include: Hierarchical databases Network databases Relational databases Object-oriented databases Graph databases ER model databases Document databases NoSQL databases This tutorial will focus on relational database management systems of which is its standard language. SQL Some common relational database management systems that use SQL are: Oracle Sybase Microsoft SQL Server Access Ingres, etc. Structured Query Language (SQL) SQL communicates with the database using SQL statements, which are used to perform tasks such as update data on a database or retrieve data from a database. Note: to be able to completely follow-through this tutorial you need to be a little familiar with SQL statement but even if you’re not,  you can still read through it. What will you learn? How to connect to Database using Python How to interact with the database by performing all CRUD operations. stands for , , , and operations. CRUD Create Read Update Delete Requirements There are various libraries in Python that interact with the database depending on the type of database you’re using, which include: SQLite PyMySQL python-sql myql-connector In this tutorial, we are going to use which comes by default with Python standard library, therefore, you don’t need to install anything. SQLite Let’s get started Creating a new database We use the method to connect to an existing SQLite database and if there is no database in your project directory it will automatically create a new database. connect ( ) It receives the name of the database as a parameter, also you need to make sure that your database name has an extension of .db for instance Customers.db Example of Usage >>> sqlite3
>>> sqlite3.connect( )
<sqlite3.Connection object at > import 'Customers.db' 0x7f2ec8c90b90 Once you execute the above code it will create a new database on your project directory with the name as shown below. Customers.db .
├── app.py
└── Customers.db​ directories, files 0 2 Now that  you have a ,  we then supposed to a object and call its execute method to perform commands: connection create Cursor SQL Creating a new Table On creating new tables we have to use , don’t worry If you never learned before about them they are kinda intuitive. SQL Statements sqlite3
connection = sqlite3.connect( ) #connecting to our database​
Cursor = connection.cursor() #Creating a cursor obect 
SQL_stement = import 'Customers.db' 'create table Customers(name varchar(20), age int);
#Sql statement to create a table with name and age column
Cursor.execute(SQL_stement) #excuting our sql statement 
connection.commit() #Saving changes to database Once you the above code it a on a database with name and age as columns,  you can view your Database using run creates new table DB Browser Inserting data to our Table The procedures during inserting new data to the database we have just created It same as we have used to create the table. The only difference lies in the SQL statement just as shown below >>> sqlite3
>>> database = sqlite3.connect( )
>>> Cursor = database.cursor()
>>> Cursor.execute( )
>>> Cursor.execute( )
>>> Cursor.execute( )
>>> database.commit() import 'Customers.db' 'insert into Customers values("Jordan", 20);' 'insert into Customers values("Frederick", 26);' 'insert into Customers values("Stephen", 56);' Output When you open again your database, your table values should be updated as shown in the picture below. Reading data from our Database Now Let’s read our data from the database by iterating over all values contained within the Customer table. sqlite3 database = sqlite3.connect( ) Cursor = database.cursor() row Cursor.execute( ): print(row) ( , )
( , )
( , ) >>> import >>> 'Customers.db' >>> >>> for in 'select * from Customers' ... ... 'Jordan' 20 'Frederick' 26 'Stephen' 56 Updating data on a table in Database Let’s try updating one of the Customer details in table, for instance, let’s change Jordan's age to 27. >>> sqlite3
>>> database = sqlite3.connect( )
>>> Cursor = database.cursor()
>>> Cursor.execute( )
<sqlite3.Cursor object at > import 'Customers.db' 'update Customers set age = 27 where name = "Jordan";' 0x7fd055bd5d50 Now when reading again the table, the age of Jordan has updated to 27. >>> row Cursor.execute( ):
...     print(row)
... 
( , )
( , )
( , ) for in 'select * from Customers' 'Jordan' 27 'Frederick' 26 'Stephen' 56 Altering our Database the database including changing its structure, for instance, we can try adding a new column to the existing table. Altering Let’s add the profession column to our customer's table >>> sqlite3
>>> database = sqlite3.connect( )
>>> Cursor = database.cursor()
>>> Cursor.execute( )
<sqlite3.Cursor object at > import 'Customers.db' 'ALTER TABLE Customers add column Profession;' 0x7fd055bd5d50 Now when we view our database again it will look as shown below: I hope you found this post interesting, do share it with your fellow developers if you think they'd benefit from reading it. In case you have any suggestions or comments, drop it in the comment box and I will reply to you immediately. Previously published here: https://kalebujordan.com/beginners-guide-to-integrating-python-and-sql/

Microsoft

Oracle

Read My Stories

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

Integrating Python and SQL: A Guide for Beginners

Too Long; Didn't Read

Companies Mentioned

Kalebu Jordan

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

Integrating Python and SQL: A Guide for Beginners

Too Long; Didn't Read

Companies Mentioned

Kalebu Jordan

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES