Using Python 🐍 with Google Sheets as a database

in #utopian-io7 years ago (edited)

banner

Imagine you want to build an internal progamm with Python and need a small database. Why not use Google Sheets? In this tutorial I want to show you how to use Google Sheets in Python 3.


Requirements

For this tutorial you need

Difficulty

Let's say it's Intermediate.

Programming

Installation

First of all we have to install some dependencies.

$ pip install gspread oauth2client

After this quick installation we have to create a new file. Let's call it grocery-list.py. Insert a quick Python barebone into it and give it a try with python grocery-list.py.

#!/usr/bin/python3

def main():
  print('Hello world!')

if __name__ == '__main__':
  main()

Create a client

First be sure that you've shared your spreadsheet with the client_email you find in your Google API credential JSON.
Rename this JSON into client-secret.json and modify your script.

#!/usr/bin/python3
import gspread
import os.path
import sys
from oauth2client.service_account import ServiceAccountCredentials

client_secret = 'client-secret.json'

def main():
  if not os.path.isfile(client_secret):
    print('Client secret not found. Create one here: https://console.developers.google.com/apis')
    sys.exit(1)

  client = get_client()

def get_client():
  scope = ['https://spreadsheets.google.com/feeds']
  creds = ServiceAccountCredentials.from_json_keyfile_name(client_secret, scope)
  client = gspread.authorize(creds)

  return client

if __name__ == '__main__':
  main()

At this point we have successfully created a client for the Google Drive API. Furthermore we have ensured that our client-secret.json does exist, before we try to use it.

Fetch all rows

It's time to get some data and pretty print it into the console.

#!/usr/bin/python3
# ...
import pprint

def main():
  # ...

  client = get_client()
  sheet = client.open('grocery_list').sheet1
  results = get_all(sheet)
  pp = pprint.PrettyPrinter()
  pp.pprint(results)

def get_all(sheet):
  results = sheet.get_all_records()
  return results

# ...

If we did it all right it should look something like this:


grocery-list-1

Append a row

Now we want to write a new row as last element into the spreadsheet.

#!/usr/bin/python3
# ...

def main():
  # ...

  last_id = results[-1].get('id')
  insert_new(sheet, [(int(last_id) + 1), 1, 'food', 'Mango'], len(results) + 2)

def insert_new(sheet, row, index):
  sheet.insert_row(row, index)

# ...

Execute the script with python grocery-list.py and open the spreadsheet in your browser. It should look like this:


grocery-list-2




À voilà! A simple database like structure using Python and Google Sheets.


With that in mind, happy coding.

(() => {
  const colors = [
    '001f3f', '0074d9', '7fdbff', '39cccc',
    '3d9970', '2ecc40', '01ff70', 'ffdc00',
    'ff851b', 'ff4136', '85144b', 'f012be',
  ];
  const contents = ['%cI', '%c❤', '%cweb', '%cdev'];
  const options = Array.from(
    new Array(contents.length),
    () => `
      color:#${colors[Math.floor(Math.random() * colors.length)]};
      font-size:64px;
    `
  );
  console.log.apply(console, [contents.join('')].concat(options));
})();



Posted on Utopian.io - Rewarding Open Source Contributors

Sort:  

Hey @drookyn I am @utopian-io. I have just upvoted you!

Achievements

  • You have less than 500 followers. Just gave you a gift to help you succeed!
  • This is your first accepted contribution here in Utopian. Welcome!

Suggestions

  • Contribute more often to get higher and higher rewards. I wish to see you often!
  • Work on your followers to increase the votes/rewards. I follow what humans do and my vote is mainly based on that. Good luck!

Get Noticed!

  • Did you know project owners can manually vote with their own voting power or by voting power delegated to their projects? Ask the project owner to review your contributions!

Community-Driven Witness!

I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!

mooncryption-utopian-witness-gif

Up-vote this comment to grow my power and help Open Source contributions like this one. Want to chat? Join me on Discord https://discord.gg/Pc8HG9x

Good tutorial, excellent information for small and medium solutions.

Thanks :)

In my last company we used Google spreadsheet quite often as a easy backend, even for some static websites for very big clients! 😉 It's really a good way to get a cheap and somehow safe backend quiet fast 😊

Really? That sounds crazy! For something small I could imagine using it but not for something big 😅

The projects weren't that big, just the client was a really big one ;)

Thank you for the contribution. It has been approved.

In the future don't add irrelevant code to the end of your tutorials.

You can contact us on Discord.
[utopian-moderator]