Store Google Sheets data into SQLite Database using Python Last Updated : 29 Dec, 2022 Comments Improve Suggest changes Like Article Like Report In this article, we are going to store google sheets data into a database using python. The first step is to enable the API and to create the credentials, so let's get stared. Enabling the APIs and creating the credentialsGo to Marketplace in Cloud Console.Click on ENABLE APIS AND SERVICESThen Search for Google Drive API and enable itThen go to the Credentials tab on the left navigation bar on the screen.Then click on Create Credentials then select Service Account KeyThen create a new service account by giving it a name and set the Role to Editor under the Projects sub-field and keep the key type as JSON and click on Create button. Keep the Downloaded JSON safely.After all these steps are done your page should look something like this.Again go to Dashboard and follow the same steps. This time search for Google Sheets and enable the API. After enabling the API lets' create the google form. Creating the Google Form:Create a Google Form. Here, we kept a simple form with only two fields.Make the Google form accept responses in Google Sheets.Then lookup the downloaded JSON file for the field client_email and copy that email.Open the newly created spreadsheet and click on the share option and type the paste the client_email there. After giving access to the client email, it should look something like this. The top email will be your personal email and the bottom one will be the client_email Google Form: So we are done with the setting up. Now, let's get to code. Before we begin, let's just understand the flow. We will create a database and a table according to the entry schemas of the form.Make a connection to the sheets via the API and fetch all the rows.Execute insert query to insert the row data from sheets to the database. Approach:First, we initialize the credentials from the service account JSON.We then use these credentials object to access the Sheet that we generated from the Google Form.Once we have the access to the sheets, we simply extract all the rows at once to reduce the number of API calls.Now, we first make our connection to the database. Here, we use the sqlite database for simplicity. You can use any type of database, all you need to do is pass on the connection string.Once the connection is established, we create the table (if it does not exist)Once the table is ready, we pass the fetched rows to the table. We simply iterate over all the rows obtained and then pass on each column value in the row to the database. Code: Python3 # imports import sqlite3 from sqlite3 import Error import gspread from oauth2client.service_account import ServiceAccountCredentials def get_from_sheet(): # name of the sheet # you should replace with the name # of your sheet sheet_name = "Details (Responses)" config = { Your_API # should contain the service account # key JSON as dict here } # use credentials to create a client to # interact with the Google Drive API scope = [ "https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive", ] # credential object for authenticating creds_obj = ServiceAccountCredentials.from_json_keyfile_dict(config, scope) # initializing gspread client with the # credentials object client = gspread.authorize(creds_obj) # Find a workbook by name and open the # first sheet Make sure you use the # right name here. sheet = client.open(sheet_name).sheet1 # returns all the data in the entire sheet return sheet.get_all_values() class SQLite: # change this to your sqlite file path # if you keep , then it will create # a sqlite database in your current working # directory DB_NAME = "db.sqlite" def __init__(self): self.conn = self.create_connection() self._get_or_create_table() @classmethod def create_connection(cls): """ create a database connection to the SQLite database specified by db_name :return: Connection object or None """ conn = None try: # connects or creates a sqlite3 file conn = sqlite3.connect(cls.DB_NAME) return conn except Error as e: print(e) # returns the connection object return conn def _get_or_create_table(self): """Creates the table if it does not exists""" # sql query to create a details table create_table_sql = """CREATE TABLE IF NOT EXISTS details ( timestamp varchar(20) PRIMARY KEY, name varchar(30) NOT NULL, year varchar(3) NOT NULL )""" try: # initializing the query cursor c = self.conn.cursor() # executes the create table query c.execute(create_table_sql) except Error as e: # prints the exception if any errors # occurs during runtime print(e) def add_data_to_table(self, rows: list): """Inserts the data from sheets to the table""" # initializing sql cursor c = self.conn.cursor() # excluding the first row because it # contains the headers insert_table_sql = """INSERT INTO details (timestamp, name, year) VALUES (?, ?, ?);""" for row in rows[1:]: # inserts the data into the table # NOTE: the data needs to be in the order # which the values are provided into the # sql statement c.execute(insert_table_sql, tuple(row)) # committing all the changes to the database self.conn.commit() # closing the connection to the database c.close() if __name__ == '__main__': # fetches data from the sheets data = get_from_sheet() sqlite_util = SQLite() sqlite_util.add_data_to_table(data) Output: As you can see, we now successfully have the data in our database. Comment More infoAdvertise with us Next Article Store Google Sheets data into SQLite Database using Python debdutgoswami Follow Improve Article Tags : Python python-utility Web-API Python-SQLite Practice Tags : python Similar Reads Python SQLite Python SQLite3 module is used to integrate the SQLite database with Python. It is a standardized Python DBI API 2.0 and provides a straightforward and simple-to-use interface for interacting with SQLite databases. There is no need to install this module separately as it comes along with Python after 4 min read SQLite in Python - Getting StartedIntroduction to SQLite in PythonSQLite is a lightweight, fast and embedded SQL database engine. SQLite is perfect for small to medium-sized applications, prototyping, embedded systems and local data storage in Python applications because it doesn't require a separate server process like other relational database management systems 4 min read Python SQLite - Connecting to DatabaseIn this article, we'll discuss how to connect to an SQLite Database using the sqlite3 module in Python. Connecting to the Database Connecting to the SQLite Database can be established using the connect() method, passing the name of the database to be accessed as a parameter. If that database does no 2 min read SQLite Datatypes and its Corresponding Python TypesSQLite is a C-language-based library that provides a portable and serverless SQL database engine. It has a file-based architecture; hence it reads and writes to a disk. Since SQLite is a zero-configuration database, no installation or setup is needed before its usage. Starting from Python 2.5.x, SQL 3 min read SQLite QueriesPython SQLite - Cursor ObjectA Cursor is an object used to execute SQL queries on an SQLite database. It acts as a middleware between the SQLite database connection and the SQL commands. It is created after establishing a connection to the SQLite database. Example:Pythonimport sqlite3 conn = sqlite3.connect('example.db') c = co 3 min read Python SQLite - Create TableIn this article, we will discuss how can we create tables in the SQLite database from the Python program using the sqlite3 module. In SQLite database we use the following syntax to create a table: CREATE TABLE database_name.table_name(                     column1 datatype PRIMARY 2 min read Python SQLite - Insert DataIn this article, we will discuss how can we insert data in a table in the SQLite database from Python using the sqlite3 module. The SQL INSERT INTO statement of SQL is used to insert a new row in a table. There are two ways of using the INSERT INTO statement for inserting rows: Only values: The firs 3 min read Python SQLite - Select Data from TableIn this article, we will discuss, select statement of the Python SQLite module. This statement is used to retrieve data from an SQLite table and this returns the data contained in the table. In SQLite the syntax of Select Statement is: SELECT * FROM table_name; *  : means all the column from the tab 3 min read Python SQLite - WHERE ClauseWhere clause is used in order to make our search results more specific, using the where clause in SQL/SQLite we can go ahead and specify specific conditions that have to be met when retrieving data from the database.If we want to retrieve, update or delete a particular set of data we can use the whe 4 min read Python SQLite - ORDER BY ClauseIn this article, we will discuss ORDER BY clause in SQLite using Python. The ORDER BY statement is a SQL statement that is used to sort the data in either ascending or descending according to one or more columns. By default, ORDER BY sorts the data in ascending order. DESC is used to sort the data i 3 min read Python SQLite - LIMIT ClauseIn this article, we are going to discuss the LIMIT clause in SQLite using Python. But first, let's get a brief about the LIMIT clause. If there are many tuples satisfying the query conditions, it might be resourceful to view only a handful of them at a time. LIMIT keyword is used to limit the data g 2 min read Python SQLite - JOIN ClauseIn this article, we discuss the JOIN clause in SQLite using the sqlite3 module in Python. But at first let's see a brief about join in SQLite. Join Clause  A JOIN clause combines the records from two tables on the basis of common attributes. The different types of joins are as follows: INNER JOIN (O 5 min read Python SQLite - Deleting Data in TableIn this article, we will discuss how we can delete data in the table in the SQLite database from the Python program using the sqlite3 module. In SQLite database we use the following syntax to delete data from a table: DELETE FROM table_name [WHERE Clause] To create the database, we will execute the 2 min read Python SQLite - Update DataIn this article, we will discuss how we can update data in tables in the SQLite database using Python - sqlite3 module. The UPDATE statement in SQL is used to update the data of an existing table in the database. We can update single columns as well as multiple columns using UPDATE statement as per 7 min read Python SQLite - DROP TableIn this article, we will discuss the DROP command in SQLite using Python. But first, let's get a brief about the drop command. DROP is used to delete the entire database or a table. It deleted both records in the table along with the table structure. Syntax: DROP TABLE TABLE_NAME; For dropping table 2 min read Python SQLite - Update Specific ColumnIn this article, we will discuss how to update a specific column of a table in SQLite using Python. In order to update a particular column in a table in SQL, we use the UPDATE query. The UPDATE statement in SQL is used to update the data of an existing table in the database. We can update single col 3 min read SQLite ClauseCheck if Table Exists in SQLite using PythonIn this article, we will discuss how to check if a table exists in an SQLite database using the sqlite3 module of Python. In an SQLite database, the names of all the tables are enlisted in the sqlite_master table. So in order to check if a table exists or not we need to check that if the name of the 2 min read How to list tables using SQLite3 in Python ?In this article, we will discuss how to list all the tables in the SQLite database using Python. Here, we will use the already created database table from SQLite. We will also learn exception handling during connecting to our database. Database Used:  Steps to Fetch all tables using SQLite3 in Pytho 2 min read SQLite Working with DataHow to Update all the Values of a Specific Column of SQLite Table using Python ?In this article, we are going to update all the values of a specific column of a given SQLite table using Python. In order to update all the columns of a particular table in SQL, we use the UPDATE query. The UPDATE statement in SQL is used to update the data of an existing table in the database. We 3 min read How to Insert Image in SQLite using Python?In this article, we will discuss how to insert images in SQLite using sqlite3 module in Python. Implementation: 1. Set the connection to the SQLite database using Python code. sqliteConnection = sqlite3.connect('SQLite_Retrieving_data.db') cursor = sqliteConnection.cursor() 2. We need to define an I 2 min read How to Read Image in SQLite using Python?This article shows us how to use the Python sqlite3 module to read or retrieve images that are stored in the form of BLOB data type in an SQLite table. First, We need to read an image that is stored in an SQLite table in BLOB format using python script and then write the file back to any location on 3 min read Working with ImagesCount total number of changes made after connecting SQLite to PythonIn this article, we are going to see how to count total changes since the SQLite database connection is open using Python. To get the total number of changes we use the connection object's total_changes property. Class Instance: sqlite3.Connection Syntax: <connection_object>.total_changes Retu 3 min read How to Show all Columns in the SQLite Database using Python ?In this article, we will discuss how we can show all columns of a table in the SQLite database from Python using the sqlite3 module. Approach:Connect to a database using the connect() method.Create a cursor object and use that cursor object created to execute queries in order to create a table and 3 min read Python SQLite - ExerciseHow to Execute a Script in SQLite using Python?In this article, we are going to see how to execute a script in SQLite using Python. Here we are executing create table and insert records into table scripts through Python. In Python, the sqlite3 module supports SQLite database for storing the data in the database. Approach Step 1: First we need to 2 min read How to store Python functions in a Sqlite table?SQLite is a relational database system contained in a C library that works over syntax very much similar to SQL. It can be fused with Python with the help of the sqlite3 module. The Python Standard Library sqlite3 was developed by Gerhard Häring. It delivers an SQL interface compliant with the DB-AP 3 min read How to Create a Backup of a SQLite Database using Python?In this article, we will learn How to Create a Backup of an SQLite Database using Python. To Create a Backup of an SQLite Database using Python, the required modules are SQLite3 and IO. First, let's create the original database to do that follow the below program: Python3 import sqlite3 import io fr 2 min read How to connect to SQLite database that resides in the memory using Python ?In this article, we will learn how to Connect an SQLite database connection to a database that resides in the memory using Python. But first let brief about what is sqlite. SQLite is a lightweight database software library that provides a relational database management system. Generally, it is a ser 3 min read Change SQLite Connection Timeout using PythonIn this article, we will discuss how to change the SQLite connection timeout when connecting from Python. What is a connection timeout and what causes it? A connection timeout is an error that occurs when it takes too long for a server to respond to a user's request. Connection timeouts usually occu 3 min read Using SQLite Aggregate functions in PythonIn this article, we are going to see how to use the aggregate function in SQLite Python. An aggregate function is a database management function that groups the values of numerous rows into a single summary value. Average (i.e., arithmetic mean), sum, max, min, Count are common aggregation functions 3 min read Launch Website URL shortcut using PythonIn this article, we are going to launch favorite websites using shortcuts, for this, we will use Python's sqlite3 and webbrowser modules to launch your favorite websites using shortcuts. Both sqlite3 and webbrowser are a part of the python standard library, so we don't need to install anything separ 3 min read Python SQLite AdditionalHow to Execute many SQLite Statements in Python?In SQLite using the executescript() method, we can execute multiple SQL statements/queries at once. The basic execute() method allows us to only accept one query at a time, so when you need to execute several queries we need to arrange them like a script and pass that script to the executescript() m 3 min read Python - Create or Redefine SQLite FunctionsThe SQLite does not have functions or stored procedure language like MySQL. We cannot create stored functions or procedures in SQLite. That means the CREATE FUNCTION or CREATE PROCEDURE does not work in SQLite. In SQLite instead of CREATE FUNCTION or CREATE PROCEDURE we have SQLite’s C API which all 3 min read Store Google Sheets data into SQLite Database using PythonIn this article, we are going to store google sheets data into a database using python. The first step is to enable the API and to create the credentials, so let's get stared. Enabling the APIs and creating the credentialsGo to Marketplace in Cloud Console.Click on ENABLE APIS AND SERVICESThen Searc 5 min read How to Execute a SQLite Statement in Python?In this article, we are going to see how to execute SQLite statements using Python. We are going to execute how to create a table in a database, insert records and display data present in the table. In order to execute an SQLite script in python, we will use the execute() method with connect() objec 2 min read How to Import a CSV file into a SQLite database Table using Python?In this article, we are going to discuss how to import a CSV file content into an SQLite database table using Python. Approach:At first, we import csv module (to work with csv file) and sqlite3 module (to populate the database table).Then we connect to our geeks database using the sqlite3.connect() 3 min read Python SQLite - CRUD OperationsIn this article, we will go through the CRUD Operation using the SQLite module in Python. CRUD Operations The abbreviation CRUD expands to Create, Read, Update and Delete. These four are fundamental operations in a database. In the sample database, we will create it, and do some operations. Let's di 4 min read Like