Store Google Sheets data into SQLite Database using Python

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 credentials

• Go to Marketplace in Cloud Console.
• Click on ENABLE APIS AND SERVICES
• Then Search for Google Drive API and enable it
• Then go to the Credentials tab on the left navigation bar on the screen.
• Then click on Create Credentials then select Service Account Key
• Then 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:

1. Create a Google Form. Here, we kept a simple form with only two fields.
2. Make the Google form accept responses in Google Sheets.
3. Then lookup the downloaded JSON file for the field client_email and copy that email.
4. 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.

1. We will create a database and a table according to the entry schemas of the form.
2. Make a connection to the sheets via the API and fetch all the rows.
3. Execute insert query to insert the row data from sheets to the database.

Approach:

1. First, we initialize the credentials from the service account JSON.
2. We then use these credentials object to access the Sheet that we generated from the Google Form.
3. Once we have the access to the sheets, we simply extract all the rows at once to reduce the number of API calls.
4. 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.
5. Once the connection is established, we create the table (if it does not exist)
6. 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.