SQL Using Python | Set 1

Here we are going to connect SQLite to Python. Python has a built-in library for SQLite. Let`s explain how it works.

  1. To use SQLite, we must import sqlite3.
  2. Then create a connection using the connect () method and pass the name of the database to which you want to refer to, if there is a file with that name, it will open that file. Otherwise, Python will create a file with the given name.
  3. The cursor object is then called to be able to send commands to SQL. Cursor — it is a control structure used to traverse and retrieve database records. The cursor plays an important role in Python. All commands will only be executed using the cursor object.
  4. To create a table in the database, create an object and write a comment SQL command in it. Example: — sql_comm = ”SQL statement”
  5. And the command is very easy to execute. Call the execute pointer method and pass the name of the sql command as a parameter. Save a few commands as sql_comm and run them. After completing all the steps, save the changes to the file, commit the changes, and then lose the connection.

# Python code to demonstrate table creation and
# inserts with SQL

 
# import module

import sqlite3

 
# database connection

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

 
# cursor

crsr =   connection.cursor ()

 
# SQL command to create a table in the database

sql_command = & quot; & quot; & quot; CREATE TABLE emp (

staff_number INTEGER PRIMARY KEY,
name VARCHAR (20 ),
NAME VARCHAR (30),
gender CHAR (1),
Start date); & quot; & quot; & quot;

 
# execute the statement
crsr.execute (sql_command)

 
# SQL command to insert data into the table

sql_command = & quot; & quot; & quot; INSERT INTO emp VALUES (23, & quot; Rishabh & quot;, & quot; Bansal & quot;, & quot; M & quot;, & quot; 2014-03-28 & quot;); & quot; & quot; & quot;

crsr.execute (sql_command)

 
# another SQL command to insert data into the table

sql_command = & quot; & quot; & quot; INSERT INTO emp VALUES (1, & quot; Bill & quot;, & quot; Gates & quot;, & quot; M & quot;, & quot; 1980-10-28 & quot;); & quot; & quot; & quot;

crsr.execute (sql_command)

 
# Save changes to files. Never skip this.
# If we skip this, nothing will be saved in the database.
connection.commit ()

 
# close the connection
connection.close ()

In this section, we discussed how to create a table and how to add new rows to the database.

Extracting data from a record is as simple as inserting it. The execute method uses a SQL command to get all data from a table using “Select * from table_name” and all table data can be retrieved in an object as a list of lists.

# Python code to demonstrate SQL for retrieving data.

  
# module import

import sqlite3

 
# connect to myTable database

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

 
# cursor object

crsr = connection.cursor ()

 
# run command to fetch all data from emp table

crsr.execute ( "SELECT * FROM emp"

 
# save all extracted data in ans variable

ans = crsr.fetchall () 

 
# loop to print all data

for i in ans:

print (i)

 

It should be noted that the database file that will be created will be in the same folder as the python file. If we want to change the file path, change the path when opening the file.

SQL Using Python | Set 3 (Big Data Processing)

This article courtesy of Rishabh Bansal . If you are as Python.Engineering and would like to contribute, you can also write an article using contribute.python.engineering or by posting the article [email protected] ... See my article appearing on the Python.Engineering homepage and help other geeks.

Please write in comments if you find anything wrong or if you`d like to share more information on the topic discussed above.