Category: python Sqlite

Python SQLite – Delete Data ”; Previous Next To delete records from a SQLite table, you need to use the DELETE FROM statement. To remove specific records, you need to use WHERE clause along with it. Syntax Following is the syntax of the DELETE query in SQLite − DELETE FROM table_name [WHERE Clause] Example Assume we have created a table with name CRICKETERS using the following query − sqlite> CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); sqlite> And if we have inserted 5 records in to it using INSERT statements as − sqlite> insert into CRICKETERS values(”Shikhar”, ”Dhawan”, 33, ”Delhi”, ”India”); sqlite> insert into CRICKETERS values(”Jonathan”, ”Trott”, 38, ”CapeTown”, ”SouthAfrica”); sqlite> insert into CRICKETERS values(”Kumara”, ”Sangakkara”, 41, ”Matale”, ”Srilanka”); sqlite> insert into CRICKETERS values(”Virat”, ”Kohli”, 30, ”Delhi”, ”India”); sqlite> insert into CRICKETERS values(”Rohit”, ”Sharma”, 32, ”Nagpur”, ”India”); sqlite> Following statement deletes the record of the cricketer whose last name is ”Sangakkara”. sqlite> DELETE FROM CRICKETERS WHERE LAST_NAME = ”Sangakkara”; sqlite> If you retrieve the contents of the table using the SELECT statement, you can see only 4 records since we have deleted one. sqlite> SELECT * FROM CRICKETERS; First_Name Last_Name Age Place_Of_B Country ———- ———- —- ———- ——– Shikhar Dhawan 46 Delhi India Jonathan Trott 39 CapeTown SouthAfrica Virat Kohli 31 Delhi India Rohit Sharma 33 Nagpur India sqlite> If you execute the DELETE FROM statement without the WHERE clause, all the records from the specified table will be deleted. sqlite> DELETE FROM CRICKETERS; sqlite> Since you have deleted all the records, if you try to retrieve the contents of the CRICKETERS table, using SELECT statement you will get an empty result set as shown below − sqlite> SELECT * FROM CRICKETERS; sqlite> Deleting Data Using Python To add records to an existing table in SQLite database − Import sqlite3 package. Create a connection object using the connect() method by passing the name of the database as a parameter to it. The cursor() method returns a cursor object using which you can communicate with SQLite3. Create a cursor object by invoking the cursor() object on the (above created) Connection object. Then, invoke the execute() method on the cursor object, by passing an DELETE statement as a parameter to it. Example Following python example deletes the records from EMPLOYEE table with age value greater than 25. import sqlite3 #Connecting to sqlite conn = sqlite3.connect(”example.db”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving contents of the table print(“Contents of the table: “) cursor.execute(”””SELECT * from EMPLOYEE”””) print(cursor.fetchall()) #Deleting records cursor.execute(”””DELETE FROM EMPLOYEE WHERE AGE > 25”””) #Retrieving data after delete print(“Contents of the table after delete operation “) cursor.execute(“SELECT * from EMPLOYEE”) print(cursor.fetchall()) #Commit your changes in the database conn.commit() #Closing the connection conn.close() Output Contents of the table: [ (”Ramya”, ”Rama priya”, 27, ”F”, 9000.0), (”Vinay”, ”Battacharya”, 21, ”M”, 6000.0), (”Sharukh”, ”Sheik”, 26, ”M”, 8300.0), (”Sarmista”, ”Sharma”, 26, ”F”, 10000.0), (”Tripthi”, ”Mishra”, 24, ”F”, 6000.0) ] Contents of the table after delete operation [ (”Vinay”, ”Battacharya”, 21, ”M”, 6000.0), (”Tripthi”, ”Mishra”, 24, ”F”, 6000.0) ] Print Page Previous Next Advertisements ”;

Python SQLite – Insert Data ”; Previous Next You can add new rows to an existing table of SQLite using the INSERT INTO statement. In this, you need to specify the name of the table, column names, and values (in the same order as column names). Syntax Following is the recommended syntax of the INSERT statement − INSERT INTO TABLE_NAME (column1, column2, column3,…columnN) VALUES (value1, value2, value3,…valueN); Where, column1, column2, column3,.. are the names of the columns of a table and value1, value2, value3,… are the values you need to insert into the table. Example Assume we have created a table with name CRICKETERS using the CREATE TABLE statement as shown below − sqlite> CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); sqlite> Following PostgreSQL statement inserts a row in the above created table. sqlite> insert into CRICKETERS (First_Name, Last_Name, Age, Place_Of_Birth, Country) values(”Shikhar”, ”Dhawan”, 33, ”Delhi”, ”India”); sqlite> While inserting records using the INSERT INTO statement, if you skip any columns names, this record will be inserted leaving empty spaces at columns which you have skipped. sqlite> insert into CRICKETERS (First_Name, Last_Name, Country) values (”Jonathan”, ”Trott”, ”SouthAfrica”); sqlite> You can also insert records into a table without specifying the column names, if the order of values you pass is same as their respective column names in the table. sqlite> insert into CRICKETERS values(”Kumara”, ”Sangakkara”, 41, ”Matale”, ”Srilanka”); sqlite> insert into CRICKETERS values(”Virat”, ”Kohli”, 30, ”Delhi”, ”India”); sqlite> insert into CRICKETERS values(”Rohit”, ”Sharma”, 32, ”Nagpur”, ”India”); sqlite> After inserting the records into a table you can verify its contents using the SELECT statement as shown below − sqlite> select * from cricketers; Shikhar |Dhawan | 33 | Delhi | India Jonathan |Trott | | | SouthAfrica Kumara |Sangakkara | 41 | Matale| Srilanka Virat |Kohli | 30 | Delhi | India Rohit |Sharma | 32 | Nagpur| India sqlite> Inserting Data Using Python To add records to an existing table in SQLite database − Import sqlite3 package. Create a connection object using the connect() method by passing the name of the database as a parameter to it. The cursor() method returns a cursor object using which you can communicate with SQLite3. Create a cursor object by invoking the cursor() object on the (above created) Connection object. Then, invoke the execute() method on the cursor object, by passing an INSERT statement as a parameter to it. Example Following python example inserts records into to a table named EMPLOYEE − import sqlite3 #Connecting to sqlite conn = sqlite3.connect(”example.db”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Preparing SQL queries to INSERT a record into the database. cursor.execute(”””INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Ramya”, ”Rama Priya”, 27, ”F”, 9000)””” ) cursor.execute(”””INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Vinay”, ”Battacharya”, 20, ”M”, 6000)”””) cursor.execute(”””INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Sharukh”, ”Sheik”, 25, ”M”, 8300)”””) cursor.execute(”””INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Sarmista”, ”Sharma”, 26, ”F”, 10000)”””) cursor.execute(”””INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Tripthi”, ”Mishra”, 24, ”F”, 6000)”””) # Commit your changes in the database conn.commit() print(“Records inserted……..”) # Closing the connection conn.close() Output Records inserted…….. Print Page Previous Next Advertisements ”;

Python SQLite – Useful Resources ”; Previous Next The following resources contain additional information on Python SQLite. Please use them to get more in-depth knowledge on this. Useful Video Courses Python Flask and SQLAlchemy ORM 22 Lectures 1.5 hours Jack Chan More Detail Python and Elixir Programming Bundle Course 81 Lectures 9.5 hours Pranjal Srivastava More Detail TKinter Course – Build Python GUI Apps 49 Lectures 4 hours John Elder More Detail A Beginner”s Guide to Python and Data Science 81 Lectures 8.5 hours Datai Team Academy More Detail Deploy Face Recognition Project With Python, Django, And Machine Learning Best Seller 93 Lectures 6.5 hours Srikanth Guskra More Detail Professional Python Web Development with Flask 80 Lectures 12 hours Stone River ELearning More Detail Print Page Previous Next Advertisements ”;

Python SQLite – Where Clause ”; Previous Next If you want to fetch, delete or, update particular rows of a table in SQLite, you need to use the where clause to specify condition to filter the rows of the table for the operation. For example, if you have a SELECT statement with where clause, only the rows which satisfies the specified condition will be retrieved. Syntax Following is the syntax of the WHERE clause in SQLite − SELECT column1, column2, columnN FROM table_name WHERE [search_condition] You can specify a search_condition using comparison or logical operators. like >, <, =, LIKE, NOT, etc. The following examples would make this concept clear. Example Assume we have created a table with name CRICKETERS using the following query − sqlite> CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); sqlite> And if we have inserted 5 records in to it using INSERT statements as − sqlite> insert into CRICKETERS values(”Shikhar”, ”Dhawan”, 33, ”Delhi”, ”India”); sqlite> insert into CRICKETERS values(”Jonathan”, ”Trott”, 38, ”CapeTown”, ”SouthAfrica”); sqlite> insert into CRICKETERS values(”Kumara”, ”Sangakkara”, 41, ”Matale”, ”Srilanka”); sqlite> insert into CRICKETERS values(”Virat”, ”Kohli”, 30, ”Delhi”, ”India”); sqlite> insert into CRICKETERS values(”Rohit”, ”Sharma”, 32, ”Nagpur”, ”India”); sqlite> Following SELECT statement retrieves the records whose age is greater than 35 − sqlite> SELECT * FROM CRICKETERS WHERE AGE > 35; First_Name Last_Name Age Place_Of_B Country ———- ———- —- ———- ———– Jonathan Trott 38 CapeTown SouthAfrica Kumara Sangakkara 41 Matale Srilanka sqlite> Where Clause Using Python The Cursor object/class contains all the methods to execute queries and fetch data, etc. The cursor method of the connection class returns a cursor object. Therefore, to create a table in SQLite database using python − Establish connection with a database using the connect() method. Create a cursor object by invoking the cursor() method on the above created connection object. Now execute the CREATE TABLE statement using the execute() method of the Cursor class. Example Following example creates a table named Employee and populates it. Then using the where clause it retrieves the records with age value less than 23. import sqlite3 #Connecting to sqlite conn = sqlite3.connect(”example.db”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Doping EMPLOYEE table if already exists. cursor.execute(“DROP TABLE IF EXISTS EMPLOYEE”) sql = ”””CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )””” cursor.execute(sql) #Populating the table cursor.execute(”””INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Ramya”, ”Rama priya”, 27, ”F”, 9000)”””) cursor.execute(”””INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Vinay”, ”Battacharya”, 20, ”M”, 6000)”””) cursor.execute(”””INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Sharukh”, ”Sheik”, 25, ”M”, 8300)”””) cursor.execute(”””INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Sarmista”, ”Sharma”, 26, ”F”, 10000)”””) cursor.execute(”””INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Tripthi”, ”Mishra”, 24, ”F”, 6000)”””) #Retrieving specific records using the where clause cursor.execute(“SELECT * from EMPLOYEE WHERE AGE <23″) print(cursor.fetchall()) #Commit your changes in the database conn.commit() #Closing the connection conn.close() Output [(”Vinay”, ”Battacharya”, 20, ”M”, 6000.0)] Print Page Previous Next Advertisements ”;

Python SQLite – Establishing Connection ”; Previous Next To establish connection with SQLite Open command prompt, browse through the location of where you have installed SQLite and just execute the command sqlite3 as shown below − Establishing Connection Using Python You can communicate with SQLite2 database using the SQLite3 python module. To do so, first of all you need to establish a connection (create a connection object). To establish a connection with SQLite3 database using python you need to − Import the sqlite3 module using the import statement. The connect() method accepts the name of the database you need to connect with as a parameter and, returns a Connection object. Example import sqlite3 conn = sqlite3.connect(”example.db”) Output print(“Connection established ……….”) Print Page Previous Next Advertisements ”;

Python SQLite – Introduction ”; Previous Next SQLite3 can be integrated with Python using sqlite3 module, which was written by Gerhard Haring. It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249. You do not need to install this module separately because it is shipped by default along with Python version 2.5.x onwards. To use sqlite3 module, you must first create a connection object that represents the database and then optionally you can create a cursor object, which will help you in executing all the SQL statements. Python SQLite3 Module APIs Following are important sqlite3 module routines, which can suffice your requirement to work with SQLite database from your Python program. If you are looking for a more sophisticated application, then you can look into Python sqlite3 module”s official documentation. Sr.No. API & Description 1 sqlite3.connect(database [,timeout ,other optional arguments]) This API opens a connection to the SQLite database file. You can use “:memory:” to open a database connection to a database that resides in RAM instead of on disk. If database is opened successfully, it returns a connection object. 2 connection.cursor([cursorClass]) This routine creates a cursor which will be used throughout your database programming with Python. This method accepts a single optional parameter cursorClass. If supplied, this must be a custom cursor class that extends sqlite3.Cursor. 3 cursor.execute(sql [, optional parameters]) This routine executes an SQL statement. The SQL statement may be parameterized (i. e. placeholders instead of SQL literals). The sqlite3 module supports two kinds of placeholders: question marks and named placeholders (named style). For example − cursor.execute(“insert into people values (?, ?)”, (who, age)) 4 connection.execute(sql [, optional parameters]) This routine is a shortcut of the above execute method provided by the cursor object and it creates an intermediate cursor object by calling the cursor method, then calls the cursor”s execute method with the parameters given. 5 cursor.executemany(sql, seq_of_parameters) This routine executes an SQL command against all parameter sequences or mappings found in the sequence sql. 6 connection.executemany(sql[, parameters]) This routine is a shortcut that creates an intermediate cursor object by calling the cursor method, then calls the cursor.s executemany method with the parameters given. 7 cursor.executescript(sql_script) This routine executes multiple SQL statements at once provided in the form of script. It issues a COMMIT statement first, then executes the SQL script it gets as a parameter. All the SQL statements should be separated by a semi colon (;). 8 connection.executescript(sql_script) This routine is a shortcut that creates an intermediate cursor object by calling the cursor method, then calls the cursor”s executescript method with the parameters given. 9 connection.total_changes() This routine returns the total number of database rows that have been modified, inserted, or deleted since the database connection was opened. 10 connection.commit() This method commits the current transaction. If you don”t call this method, anything you did since the last call to commit() is not visible from other database connections. 11 connection.rollback() This method rolls back any changes to the database since the last call to commit(). 12 connection.close() This method closes the database connection. Note that this does not automatically call commit(). If you just close your database connection without calling commit() first, your changes will be lost! 13 cursor.fetchone() This method fetches the next row of a query result set, returning a single sequence, or None when no more data is available. 14 cursor.fetchmany([size = cursor.arraysize]) This routine fetches the next set of rows of a query result, returning a list. An empty list is returned when no more rows are available. The method tries to fetch as many rows as indicated by the size parameter. 15 cursor.fetchall() This routine fetches all (remaining) rows of a query result, returning a list. An empty list is returned when no rows are available. Print Page Previous Next Advertisements ”;

Python SQLite – Create Table ”; Previous Next Using the SQLite CREATE TABLE statement you can create a table in a database. Syntax Following is the syntax to create a table in SQLite database − CREATE TABLE database_name.table_name( column1 datatype PRIMARY KEY(one or more columns), column2 datatype, column3 datatype, ….. columnN datatype ); Example Following SQLite query/statement creates a table with name CRICKETERS in SQLite database − sqlite> CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); sqlite> Let us create one more table OdiStats describing the One-day cricket statistics of each player in CRICKETERS table. sqlite> CREATE TABLE ODIStats ( First_Name VARCHAR(255), Matches INT, Runs INT, AVG FLOAT, Centuries INT, HalfCenturies INT ); sqlite> You can get the list of tables in a database in SQLite database using the .tables command. After creating a table, if you can verify the list of tables you can observe the newly created table in it as − sqlite> . tables CRICKETERS ODIStats sqlite> Creating a Table Using Python The Cursor object contains all the methods to execute quires and fetch data etc. The cursor method of the connection class returns a cursor object. Therefore, to create a table in SQLite database using python − Establish connection with a database using the connect() method. Create a cursor object by invoking the cursor() method on the above created connection object. Now execute the CREATE TABLE statement using the execute() method of the Cursor class. Example Following Python program creates a table named Employee in SQLite3 − import sqlite3 #Connecting to sqlite conn = sqlite3.connect(”example.db”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Doping EMPLOYEE table if already exists. cursor.execute(“DROP TABLE IF EXISTS EMPLOYEE”) #Creating table as per requirement sql =”””CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )””” cursor.execute(sql) print(“Table created successfully……..”) #Commit your changes in the database conn.commit() #Closing the connection conn.close() Output Table created successfully…….. Print Page Previous Next Advertisements ”;