DEV Community: Zero | One

Introducing SheetDB: Turn Google Sheets into your database

Zero | One — Fri, 05 Dec 2025 21:22:28 +0000

Introduction

This year, I participated in Kiroween, and for this hackathon, I built SheetDB—a project that transforms Google Sheets into a fully operational relational database with SQL capabilities, multiple tables, relationships, and performance that's 500-1000x faster than making direct API calls to Google Sheets to achieve a similar goal.

You might wonder: why use Google Sheets as a database when there are fully-featured database solutions like PostgreSQL, MySQL, or managed services like Supabase, Firebase, and Appwrite? The answer is simple: because it is fun to hack something like this and a lot useful at the same time and more importantly, because I can

The Benefits of Using Google Sheets as a Database

Zero Hosting Costs - Leverage Google's infrastructure for free, with near-unlimited bandwidth and reliability
Built-in Admin Interface - Google Sheets provides a familiar, powerful UI that everyone already knows how to use
Instant Setup - No database installation, no server configuration, no DevOps—just publish your sheet and start querying
Real-time Collaboration - Multiple users can edit data simultaneously with Google's built-in collaboration features
Version History - Every change is tracked automatically, providing a built-in audit trail
Unlimited Writes via Forms - Use Google Forms for data collection without consuming API quota

The SheetDB Ecosystem

SheetDB is now a comprehensive ecosystem with three main components:

SheetDB Core - Python library and CLI tool providing the core database functionality
sheetdb-js - JavaScript/TypeScript client with React hooks for frontend integration
sheetdb-server - FastAPI REST API server that exposes SheetDB to any language or client

The Inspiration

There have been several attempts at using Google Sheets as a database for simple MVPs and CRUD applications. The most notable is Retrosheet, which elegantly turns Google Sheets into a single-table database and which was the main inspiration to this project(all credits to theapache64). It's brilliant for quick prototypes, but as I had more ambitious project in my mind on top of this which addresses following issues:

No real database queries - Couldn't write SQL or perform complex filtering
Single table only - All data had to live in one sheet with no relationships
No relational data - Impossible to model users → posts, products → orders, or any other relationships
Slow performance - Every query hits the Google Sheets API directly (~500ms per query)
Rate limits - Limited to 100 requests per 100 seconds
Limited flexibility - Rigid architecture with no room for customisation

I needed something more powerful, more flexible, but still free and easy to deploy.

That's when I decided to build SheetDB to fill exactly those gaps.

What SheetDB Does: A Complete Solution

SheetDB transforms Google Sheets into a full-featured relational database with all the power of traditional databases, but zero hosting costs.

Core Features

🗄️ True Relational Database

Multiple tables with relationships - Define foreign keys and join data across sheets
Full SQL support - JOINs, aggregations, subqueries, WHERE clauses, GROUP BY, ORDER BY
Schema validation - Type checking and constraint enforcement
Primary keys and referential integrity - Maintain data consistency

# Complex joins across multiple tables
hunts = db.query("""
    SELECT h.hunt_date, m.name as monster, hu.name as hunter, h.success
    FROM hunts h
    JOIN monsters m ON h.monster_id = m.id
    JOIN hunters hu ON h.hunter_id = hu.id
    WHERE h.success = true
    ORDER BY h.hunt_date DESC
""")

⚡ 500x Performance Boost

The secret sauce: DuckDB caching layer

Sync data once from Google Sheets into in-memory DuckDB
All queries run against the cache (~1ms vs ~500ms)
Smart sync strategies: aggressive, lazy, scheduled, or manual

Performance Comparison:

Operation	Direct API (Retrosheet)	SheetDB (Cached)	Speedup
Single query	500ms	1ms	500x
100 queries	50 seconds	0.1 seconds	500x
Join query	Not possible	1ms	∞

# One sync, unlimited fast queries
db.sync()  # ~2 seconds (one-time cost)

for i in range(1000):
    data = db.query('SELECT * FROM monsters')  # ~1ms each!
# Total: ~2 seconds vs Retrosheet's ~500 seconds

🎯 Three Flexible Operating Modes

1. Public Mode (No Authentication)

Read from published Google Sheets without any credentials
Unlimited reads from public CSV exports
Optional unlimited writes via Google Forms
Perfect for prototypes and public data

2. Authenticated Mode (Full CRUD)

Full create, read, update, delete operations
Private data support with service account authentication
Complete control over all operations

3. Hybrid Mode (Best of Both) ⚡ Recommended

Public reads (unlimited, fast)
Authenticated writes (controlled)
Optimal performance for production apps

# Public mode - zero setup!
db = SheetDB.from_public_url("https://docs.google.com/spreadsheets/d/YOUR_ID/")

# Authenticated mode - full control
db = SheetDB(credentials="creds.json", spreadsheet_id="YOUR_ID")

# Hybrid mode - best of both worlds
db = SheetDB(
    sheet_url="https://...",
    credentials="creds.json",
    mode="hybrid"
)

📝 Multiple Read/Write Options

For Reads:

Direct SQL queries with DuckDB
Django-like ORM for Python
REST API for any frontend
JavaScript client with React hooks

For Writes:

Direct API writes (authenticated mode)
Google Forms integration (unlimited writes, no API quota!)
Automated form creation via Forms API

# Unlimited writes via Google Forms (no API quota!)
form_url = db.enable_forms_writer('monsters', auto_create=True)

for i in range(1000):  # Write 1000 records - no limits!
    db.insert_via_form('monsters', {
        'name': f'Monster_{i}',
        'danger_level': (i % 10) + 1
    })

🤖 Everything Automated

Auto-generate schemas from existing sheets
Auto-create sheets from schema definitions
Auto-create forms for data entry
Auto-sync data on schedule
Auto-validate against schema

# Generate schema from existing sheets
sheetdb schema generate --credentials creds.json --spreadsheet-id YOUR_ID

# Create sheets from schema
sheetdb sheets create --schema schema.yaml

# Create forms for all tables
sheetdb forms init --schema schema.yaml

🌐 Framework Agnostic

Works with any Python or JavaScript framework:

✅ Django (with ORM adapter)
✅ FastAPI (REST API server)
✅ Flask
✅ React (with hooks)
✅ Vue
✅ Angular
✅ Next.js
✅ Vanilla JS/Python

How I Built It: Architecture and Technical Decisions

Building SheetDB required careful architectural decisions to balance simplicity, performance, and flexibility.

Core Architecture

┌─────────────────────────────────────────────────────┐
│  Application Layer (React, Django, FastAPI, etc.)  │
└─────────────────────────────────────────────────────┘
                         ↓
┌─────────────────────────────────────────────────────┐
│  Interface Layer (JS Client, REST API, ORM)         │
└─────────────────────────────────────────────────────┘
                         ↓
┌─────────────────────────────────────────────────────┐
│  Core SheetDB Library (Unified API)                 │
│  ├─ Sheets Gateway (API + Public URLs)              │
│  ├─ DuckDB Engine (Relational Cache)                │
│  └─ Schema Config (Validation)                      │
└─────────────────────────────────────────────────────┘
                         ↓
┌─────────────────────────────────────────────────────┐
│  Data Sources (Google Sheets, Public URLs, Forms)   │
└─────────────────────────────────────────────────────┘

Key Technical Decisions

1. DuckDB as Cache Layer

The Problem: Google Sheets API is slow (~500ms per query) and rate-limited (100 requests per 100 seconds).

The Solution: Cache data in DuckDB, an embedded analytical database.

Why DuckDB?

In-memory - No disk I/O, blazing fast queries
Columnar storage - Optimized for analytical queries
Vectorized execution - SIMD operations for speed
Full SQL support - JOINs, aggregations, subqueries
Embedded - No separate server process

Implementation:

class DuckDBEngine:
    def __init__(self):
        self.conn = duckdb.connect(':memory:')

    def load_table(self, table_name, data, schema):
        # Create table from sheet data
        self.conn.execute(f"CREATE OR REPLACE TABLE {table_name} AS SELECT * FROM data")

    def execute_sql(self, query, params=None):
        # Execute with parameter binding (SQL injection safe)
        return self.conn.execute(query, params).fetchall()

Result: 500-1000x performance improvement!

2. Three Operating Modes

The Problem: Different use cases need different authentication strategies.

The Solution: Support three modes with seamless switching.

Public Mode Implementation:

class PublicSheetsReader:
    def read_sheet(self, sheet_url, gid=0):
        # Construct CSV export URL
        csv_url = f"https://docs.google.com/spreadsheets/d/{id}/export?format=csv&gid={gid}"

        # Fetch and parse CSV (no authentication!)
        response = urllib.request.urlopen(csv_url)
        return list(csv.DictReader(io.StringIO(response.read().decode('utf-8'))))

Benefit: Zero setup for prototypes, full control for production.

3. Forms API for Unlimited Writes

The Problem: Google Sheets API has strict rate limits for writes.

The Solution: Use Google Forms API to create forms that write to sheets without consuming API quota.

Implementation:

class FormsAPIGateway:
    def create_form(self, title, spreadsheet_id, sheet_name):
        # Create form via Forms API
        form = self.forms_service.forms().create(body={'info': {'title': title}}).execute()

        # Link to spreadsheet
        self.link_to_spreadsheet(form['formId'], spreadsheet_id, sheet_name)

        return form['formId']

    def submit_response(self, form_id, data, entry_mapping):
        # Submit via HTTP POST (no API quota!)
        form_data = {entry_mapping[field]: value for field, value in data.items()}
        response = requests.post(submit_url, data=form_data)
        return response.url

Benefit: Unlimited writes for high-volume data collection!

4. Schema-First Design

The Problem: Google Sheets are untyped and unstructured.

The Solution: YAML schema configuration with validation.

Schema Example:

tables:
  - name: monsters
    sheet_name: Monsters
    primary_key: id
    columns:
      - name: id
        type: integer
      - name: name
        type: string
      - name: danger_level
        type: integer

relationships:
  - name: hunt_monster
    from_table: hunts
    from_column: monster_id
    to_table: monsters
    to_column: id

Benefits:

Type safety and validation
Automatic code generation
Clear documentation
Enables relational queries

5. Framework Agnostic Core

The Problem: Different projects use different frameworks.

The Solution: Build a clean core library with adapters for specific frameworks.

Architecture:

Core Library - Framework-independent SheetDB class
Django Adapter - ORM layer mimicking Django models
REST API - FastAPI server for universal access
JS Client - npm package for frontend frameworks

Result: Works with any framework, no vendor lock-in!

Challenges I Faced and How I Solved Them

Challenge 1: Google API Rate Limits

Problem: The Sheets API has strict rate limits (100 requests per 100 seconds).

Solution: DuckDB caching. Sync once, query unlimited times. This turned a limitation into our biggest performance advantage.

Impact: 500x performance improvement and no rate limit issues for reads.

Challenge 2: Relational Data in Flat Sheets

Problem: Google Sheets are inherently flat, but we needed relationships.

Solution: Built a schema system that maps sheets to tables and defines foreign keys, then DuckDB handles the joins in-memory.

Implementation:

# Schema defines relationships
relationships:
  - from_table: hunts
    from_column: monster_id
    to_table: monsters
    to_column: id

# DuckDB executes joins
SELECT h.*, m.name as monster_name
FROM hunts h
JOIN monsters m ON h.monster_id = m.id

Challenge 3: Type Safety Without a Real Database

Problem: Sheets store everything as strings.

Solution: Implemented automatic type inference and validation, converting strings to proper types (integers, dates, booleans) based on the schema.

def infer_type(value):
    try:
        return int(value)
    except ValueError:
        try:
            return float(value)
        except ValueError:
            if value.lower() in ('true', 'false'):
                return value.lower() == 'true'
            return value

Challenge 4: Multiple Framework Support

Problem: Supporting Django, FastAPI, React, and vanilla JS meant building for many different environments.

Solution: Used the adapter pattern to keep framework-specific code separate from the core library.

Result: Clean architecture with well-defined interfaces.

Challenge 5: Authentication Complexity

Problem: Google's authentication can be tricky and intimidating for beginners.

Solution: Simplified it with three modes: public (no auth), authenticated (service account), and hybrid. Each mode has clear setup instructions and examples.

Documentation: Created comprehensive guides with step-by-step instructions for each mode.

Challenge 6: Schema Management

Problem: Manually writing schemas is tedious and error-prone.

Solution: Built three ways to create schemas:

Interactive CLI - Step-by-step guided creation
Auto-generation - Infer from existing sheets
Manual YAML - Full control for advanced users

# Interactive creation
sheetdb schema create

# Auto-generation
sheetdb schema generate --credentials creds.json --spreadsheet-id YOUR_ID

Challenge 7: Forms API Integration

Problem: The Forms API is powerful but complex.

Solution: Abstracted it into simple functions: create a form, link it to a sheet, and get unlimited writes. All automated.

# One line to enable unlimited writes
form_url = db.enable_forms_writer('monsters', auto_create=True)

Accomplishments I'm Proud Of

🎯 True Relational Database

We didn't just add features—we fundamentally changed what's possible. Multiple tables, foreign keys, SQL joins, aggregations. It's a real database now.

🆓 Still Completely Free

Despite all the advanced features, SheetDB remains free to use. No hosting costs, no bandwidth limits, no hidden fees. Google's infrastructure handles everything.

🤖 Automation That Actually Works

From schema generation to form creation, everything can be automated. This saves hours of manual setup and reduces errors.

📚 Comprehensive Documentation

I built extensive documentation with examples, tutorials, troubleshooting guides, and API references. The Django app and React examples show real-world usage.(check repo link)

🎨 Beautiful Demo Apps

The Monster Hunt Tracker (Django) showcases the full stack with a Kiro dark mode + Halloween theme. It's functional and fun. (check repo link)

🌐 Framework Flexibility

Works with Python, JavaScript, Django, FastAPI, React—whatever you're building with. Not locked into any specific framework.

📊 Production-Ready

Type safety, validation, error handling, logging, testing. SheetDB isn't just a prototype—it's ready for real applications.

What I Learned

Technical Lessons

1. Caching is Everything
The DuckDB layer transformed performance. Sometimes the best solution isn't faster APIs, it's not calling them at all.

2. Constraints Breed Creativity
Google Sheets' limitations forced us to think differently. The result is more innovative than a traditional database wrapper.

3. Simplicity Wins
The three operating modes (public, authenticated, hybrid) cover 99% of use cases. We resisted adding complexity.

4. Documentation Matters
Good docs with examples are as important as good code. We invested heavily in guides, tutorials, and working demos.

5. Framework Agnostic is Hard
Supporting multiple frameworks requires careful architecture. The adapter pattern and clean interfaces were essential.

How I Used Kiro: Spec-Driven Development Meets Vibe Coding

Building SheetDB with Kiro combined two approaches: spec-driven development for core architecture and vibe coding for rapid iteration—achieving ~20x faster development.

Spec-Driven Development

For critical features, I used Kiro's structured workflow with formal requirements (.kiro/specs/sheetdb/requirements.md), detailed design documents with correctness properties, and phased task lists. This provided clear direction, testable acceptance criteria, and maintainable architecture for the database engine, ORM layer, and API contracts.

Vibe Coding

For UI/UX and documentation, I used conversational iteration with Kiro:

Theme Design: "Make it like Kiro dark mode with Halloween emojis" → Complete CSS in 20 minutes (vs 4-6 hours)
Documentation: "Cover authentication for 3 Google APIs" → Production-ready guides in 30 minutes (vs 2-3 days)
Debugging: Screenshot of error → Complete fix with automation scripts in 10 minutes (vs 1-2 hours)

The Key Insight

Use spec-driven when correctness matters most (architecture, core logic). Use vibe coding when speed matters most (styling, docs, automation). Together: production-ready system in ~2 hours vs ~4-5 days traditional development.

What's Next for SheetDB

Short-term (Next 3 Months)

1. Incremental Sync and some improvements

Only update changed data instead of full reloads
Track modifications and sync deltas
Free analytics for data via Google Forms
Finish and police the product, write detailed documentation and host it for users.
expose sheetdb server to be used for any other project.

2. More Examples

Flask app example
Full React App example using sheetdb-js
Show SheetDB working with every major framework

Medium-term (6 Months)

1. Multi-Spreadsheet Support

Combine data from multiple Google Sheets
Join across spreadsheets
Aggregate data from multiple sources

2. Advanced and more complete SQL Relationships

Many-to-many relationships
Polymorphic associations
Self-referential relationships

3. Schema Migrations

Version control for schemas
Add columns, change types
Migrate data safely

4. Offline Support

Local-first architecture with sync
Work offline, sync when connected
Conflict resolution

Long-term (1 Year+)

1. Visual Schema Builder

Web UI for designing schemas
No YAML required
Drag-and-drop table relationships

2. GraphQL API

Alternative to REST API
More flexible queries from frontends
Better for complex data fetching

Try SheetDB Today!

Ready to build your next project with zero hosting costs and database power?

Quick Start (5 Minutes)

# Install
pip install git+https://github.com/yourusername/sheetdb.git

# Initialize from public URL (no auth required!)
sheetdb init --url "https://docs.google.com/spreadsheets/d/YOUR_ID/"

# Start querying!

from sheetdb import SheetDB

# Connect to your sheet
db = SheetDB.from_public_url("https://docs.google.com/spreadsheets/d/YOUR_ID/")
db.sync()

# Query with SQL
monsters = db.query("SELECT * FROM monsters WHERE danger_level > 7")

# That's it! 🎉

Join the Community

⭐ Star the project on GitHub
🐛 Report bugs and request features
💡 Share your projects built with SheetDB
📝 Contribute to documentation
🤝 Help other developers

Built with 🎃 Kiroween spirit

Because the best solutions come from working around limitations, not throwing money at them.

Regular Expressions(RegEx) in Python

Zero | One — Thu, 17 Oct 2019 22:36:35 +0000

Any developer or a wanna-be developer knows how useful regular expressions can be while dealing with strings and text. But generally, regex is considered to be tough and people don't want to get their hands dirty with it. At least it was the case for me until I found this Quora thread. The most upvoted answer in this quora question, in a way, provides a path to learn, understand and practice regex quickly and in a systematic manner. I have learnt from that path and I am going to share what I have learnt so that anyone can find all the information in a single place.

So, What is Regex?

Regular expressions are the group of characters which represent a pattern to be matched by strings. What this means is that usually, text documents like log files and phone book etc. contain many characters, words and we may want to extract meaningful information from those strings. Regular expressions come into play here. Regular expressions filter out specific data from given strings according to your needs and its up to you what you want to do with those extracted characters.
For example, in many web applications which require a user to sign up into their website, while creating password there may be some constraints as shown below:

password should:
contain at least 6 alphanumeric characters,
start with an uppercase letter, 
end with at least one special symbol(*,@,# etc.)

In such case we can create a pattern through regular expressions as shown below:

'^[A-Z]\w{5}.*[*@#]$'

What above symbols mean is all about regex. So, There is no need to worry at this point. if you enter password violating above rules, your password won't match with regular expression and password cannot be created as shown below:

Create a password: 1223as //-->password cannot be created!!
Create a password: A123bc@# //-->password created!!

The regex written above defined some pattern and if any string did not match that pattern it did not allow password creation. Likewise, there can be many uses of regex to filter out text and extract specific information from text and do whatever we desire to do with them.

Working with Regex(using Python3)

First things first, regex is generally used with some programming languages to operate on text from given text file or log file etc. We are going to use python to work with regex. One needs to be a little familiar with python syntax, the concept of modules and OOP to work with it. To use regular expressions in python we need to import module re. re module has many methods which facilitate working with regex. We will quickly look at the most important methods with which can perform almost every actions related to regex. While explaining these methods I will use regex in its simplest terms, that is, without any metacharacter. Metacharacters are special characters which are used in regular expressions. It will make us realize that regular expressions are string types object and not an only complicated piece of symbols that usually beginners think they are. In the simplest form, they can be used as simple string objects as we will see. Main methods that we are going to look at are:

1. re.search()

This, in my view, is the simplest and best way to work with regex in python. In fact, if done wisely, you can do almost every kind of string filtering and searching with this function alone. This method has the following signature:

re.search(pattern,string,flags)
where the pattern is the regex written by you
the string is a string with which we want to match our pattern
flags are optional third arguments used for different purposes briefly discussed below

Let's see how this works.

import re
res=re.search(r'coding','dev is the best place to learn coding.')
print(res) 
res=re.search(r'code','dev is the best place to learn coding.')
print(res)

you can run above code and see the result yourself but for reference the output of the above code is:

Output:
_sre.SRE_Match object; span=(31, 37), match='coding'
None

re.search() returns what is known as match object as shown above, if the regex matches with the string. if the regex match object is returned it indicates that pattern was matched with string and if no such pattern is present we get 'None' as a result. If we want to see the content of the match object we can use the group() method.So, replace print(res) statement in above code with print(res.group()). Try it on IDE yourself and the output will be:

Output:
Runtime Errors:
Traceback (most recent call last):
  File "/home/f79b34f7380e5d299ca6998dec4c6fef.py", line 6, in 
    print(res.group())
AttributeError: 'NoneType' object has no attribute 'group'

A runtime error occurs as "None" has no content and we are trying to access it. We will see more of search() and group() later.

2. re.match()

re.match() is a python specific method of re which is similar to search() except that it returns match object or in other words, it matches the pattern with string only if the given pattern is present at the beginning of the string. So, it is a special case of search() in which we are looking for a pattern only at the beginning of the string. Following example clarifies everything

# match() method of re module
import re
s1='China is the most populous country in the world'
s2='Most populous country in the world is China'
res=re.match(r'China',s1)
print(res) #prints Match object as China is at the beginning of s2.
res=re.match(r'China',s2)
print(res) #prints None as China is not at the beginning of s2.
if re.search('China',s1) and re.search('China',s2):
# another way to use search() and match()
print('search possible in s1 and s2')

Above code shows another way to use search() and match() methods. If search happens and pattern is matched we can do something based on that.

matching start and end

We saw that match() method returns match object only when the pattern occurs at the beginning of the test string. However, we have another way to achieve the same thing and that is with metacharacter '^'. If we want our pattern to occur at the beginning of the test string only then we can use ^ at the beginning of pattern as shown below. We use '^' with search() method. Its similarity with the match is shown below. Instead of using the match we can use search as shown below:

# replacing match using search and ^
import re
s1='China is the most populous country in the world'
s2='Most populous country in the world is China'
res=re.search(r'^China',s1)
print(res) #prints Match object as China is at the beginning of the s1.
res=re.search(r'^China',s2)
print(res) #prints None as China is not at the beginning of the s2.

Output:
<_sre.SRE_Match object; span=(0, 5), match='China'>
None

Similarly, we have '$' metacharacter which is placed at the end of regex and the pattern matches only if given pattern is present at the end of the test string. Example:

# Using $ to match last characterset in the test string
import re
s1='China is the most populous country in the world'
s2='Most populous country in the world is China'
res=re.search(r'China$',s1)
print(res) #prints None as China is not at the end of s1.
res=re.search(r'China$',s2)
print(res) #prints Match object as China is at the end of s2.

results are just reverse now as s2 has 'China' at the end of the sentence.

Output:
None
<_sre.SRE_Match object; span=(38, 43), match='China'>

If we combine both '^' and '$' in a pattern the test string will match if it contains whatever is between '^' and '$' and nothing else. Example:

import re
s1='China is the most populous country in the world'
s2='Most populous country in the world is China'
s3 = 'China'
res=re.search(r'^China$',s1)
print(res) #prints None as s1 does not start and end with 'China'.
res=re.search(r'^China$',s2)
print(res) #prints None as s2 does not start and end with 'China'.
res=re.search(r'^China$',s3)
print(res) #prints Match object as test string only contains "China" and nothing else.

Output:
None
None
<_sre.SRE_Match object; span=(0, 5), match='China'>

3. re.findall()

search() and match() methods only return a single matched substring in the form of the match object. But there can be many cases in which more than one substrings in the given string may match the regex pattern for example:

import re
res=re.search(r'.at','the cat was chasing rat while the bat was looking at them')
print(res.group())

Where '.' is a metacharacter which represents any character accept newline. Until now we have used simple ascii characters with regex but regex has many metacharacters too which allows us to create patterns which can do more powerful things. We will see all essential metacharacters in regex later. In the above case, only cat is recorded so the output will be 'cat'. But what if we want such words, then either we can use loops with search() or we can use findall() as shown below:

import re
res=re.findall(r'.at','the cat was chasing rat while bat was looking at them')
print(res)

Output is:

Output:
['cat', 'rat', 'bat', ' at']

So, findall() returns the list of all non-overlapping matches of the patterns.

4. re.compile()

With re.compile(), we can use the regex pattern again and again in our code. The signature of re.compile is:

re.compile(pattern,flags)
flags are optional arguments which we will see later.

the compile() returns an object known as regex object which has its own search(), match(),findall() and other methods that generally a match object has. So, below is the code which shows how compile() works and alternative way to work without compile() is also shown. There is no big difference between the two.

# compile method of re
import re
comp=re.compile('pattern')  #comp is a regex object not a match object
res=comp.search('the pattern is present in this sentence.') #res is match object
print(res,res.group())
res=re.search('pattern','the pattern is present in this sentence.') #alternate way without compile, here res is match object
print(res,res.group())

Output:
_sre.SRE_Match object; span=(4, 11), match='pattern' pattern
_sre.SRE_Match object; span=(4, 11), match='pattern' pattern

optional flag argument in re methods

There is an optional flag argument in all re pattern matching methods explained below like search(), match(),findall() and compile().
The flags can change the way regular expressions function. For that we will see one example:

import re
res = re.findall(r'Noun','Noun is the naming word. We use noun to name objects', re.IGNORECASE)
print(res)

Output:
['Noun', 'noun']

Without 3rd argument it matches only one instance of 'Noun' but when we use re.IGNORECASE(also re.I) it ignores the case and matches both instance of 'noun'. Likewise there are other flag values also. If you want to learn more about them you can refer this link

Syntax of Regular expressions

Now that we have seen how to work with regex in python using the search(), match() and findall(), let's dive deep into the syntax of regular expressions. It is called syntax of regular expressions because regex contains many metacharacters having their own meaning which makes regex very powerful tool to extract and filter information from text data. Until now we have seen how regex can be used in python but only using simple strings. Regex is rarely used that way. Actually regex comes with many metacharacters as described below one by one.

1. Any Charatcer(.)

The '.' is a metacharacter which matches anything in the test string(string in which we are looking for patterns) except a newline. As we saw before in example of findall() above we use '.' when we do not know beforehand which character we want to search for, for example, let the test string be

"the house no. is 74-3B."

and we are required to look if there is a house no. according to the number rule that says that- the house no. contains two characters followed by a '-' and that followed by two characters then we can write a regex as

"..-.."//matches test string mentioned above

As any character can occupy place around '-' we used '.'

2. Character Class([])

When we write [] in regex it represents character class and it matches only one out of several characters inside the square brackets. If we put '^' inside the square brackets before all the characters then it matches any character that is not in square brackets. if we put a hyphen(-) inside square brackets we can use it to match the range of characters in some sequence. The following list of examples will clarify these:

Character Class Example	Description
[aA]	matches 'a' or 'A'
[a-z]	matches any of the characters ‘a’,’b’,’c’, ’d’…. or ‘z’
[a-zA-Z]	matches one character which lies in range ‘a’ to ‘z’ or ‘A’ to ‘Z’.
[-abc]	matches ‘-‘ or ‘a’ or ‘b’ or ‘c’ i.e putting ‘-‘ as a standalone character has no special meaning.
[^ab]	matches anything but ‘a’ or ‘b’.
[^a-z]	matches anything but a character in the range a-z.
[a^b]	matches ‘a’ or ‘^’ or ‘b’ i.e putting ‘^’ at any place apart from the first place inside square brackets makes it like any other standalone characters.

3. Predefined Character Classes

With character classes, we can create any custom character class as we desire but regex also comes with some predefined character classes ready to use as mentioned below:

Character Class Example	Description
\d	matches any numerical digit.Equivalent to [0-9].
\D	matches any non-digit character. it is a complement to \d.Equivalent to [^0-9].
\s	matches any white space character.Equivalent to [\t\n\r\f\v].
\S	matches any character which is not white space.equivalent to [^\t\n\r\f\v].
\w	matches any alphanumeric character also called as a word character.Equivalent to[a-zA-Z0-9_] plus characters defined in current locale.
\W	matches anything but alphanumeric characters. Matches non-word characters.Equivalent to [^a-zA-Z0-9_]
\b	matches empty strings at the beginning and end of a word.
\B	matches empty strings but not at the beginning and end of a word.
\\	matches backslash(‘\’) character.
\.	matches period(‘.’) character.

4. Quantifiers

Quantifiers are very important metacharacters in the regex. As their name suggests, they are used to repeat characters the desired number of times. Quantifiers are always used after some character or group of characters and define how many times that character will repeat. There are basically 4 types of quantifiers that we will see.

a. ?(for optional preceding character)

It makes the preceding character or group of characters optional. for example

import re
res=re.findall(r"army?", "the arms and ammunitions should be provided to the army for security")
print(res)

b. *(zero or more repetitions)

It is used if we want the preceding character or group of characters to repeat zero or more times. For example:

import re
res = re.findall(r'ai*m', 'am aim aiim aiiims ai')
print(res)

Output:
['am', 'aim', 'aiim', 'aiiim']

Above code shows that regex matches if there is an arbitrary number of i's between a and m.

c. +(one or more repetitions)

It matches only if the preceding character to it occurs at least one or more times. For example:

import re
res = re.findall(r'ai+m', 'am aim aiim aiiims ai')
print(res)

Output:
['aim', 'aiim', 'aiiim']

We can see that for same test string, only those strings match which have at least one 'i' betweeb 'a' and 'm'.

d. {x}(fixed no. of repetitions)

Using + and * is nice but when we use them the preceding characters can repeat any no. of times. In some situations we want some characters or group of characters to repeat exactly desired no. of times. This can be achieved using {x} which matches only if preceding characters are repeated x no. of times. For example:

import re
print('Enter your phone no. it must be of 10 digits!!!')
phone = input()
res = re.search(r'\d{10}', phone)
if res:
    print("correct")
else:
    print("Incorrect no. of digits")

If the user enters anything less than or more than 10 digits above he or she will receive the message as "Incorrect no. of digits". Output when the phone no. is valid and invalid is shown below:

#valid
Output:
Enter your phone no. it must be of 10 digits!!!
9999999999
correct

#invalid
Output:
Enter your phone no. it must be of 10 digits!!!
100
Incorrect no. of digits

e. {x,y}(fixed and ranged repetitions):

In the above example, we can see that 100 is evaluated as invalid because of our code. But 100 is infact, a valid number. So, sometimes we may want restrictions in range. Then we use {x,y} which matches only if preceding characters repeat at least x times and at most y times. Example:

import re
print('Enter your phone no. it should be between 3 to 10 digits!!!')
phone = input()
res = re.search(r'\d{3,10}', phone)
if res:
    print("correct")
else:
    print("Incorrect no. of digits")

Output:
Enter your phone no. it should be between 3 to 10 digits!!!
100
correct

If we write {, y } it means 0 to y no. of preceding characters are allowed and if we write {x, } it means at least x characters and more no. of characters are allowed.

5. Grouping and Capturing

Next piece of syntax in regex involves groups and capturing groups. Grouping is one of the best features of regex. We can create a group by wrapping our regular expressions around parentheses (). If we use quantifiers after a group, it applies to the whole group and not to a single character. Let's understand grouping with an example:

import re
for _ in range(3):
    print('Enter your phone no. it must be of 10 digits!!!. ISD code is optional.')
    phone = input()
    res = re.search(r'(\+\d{2})?\d{10}', phone)
    if res:
        print("correct")
    else:
        print("Incorrect no. of digits")

Output:
Enter your phone no. it must be of 10 digits!!!. ISD code is optional.
9999999999
correct
Enter your phone no. it must be of 10 digits!!!. ISD code is optional.
+919999999999
correct
Enter your phone no. it must be of 10 digits!!!. ISD code is optional.
988
Incorrect no. of digits

Above example shows how groups work in the regex. To make the ISD code optional we could have used '?' operator character by character but groups make it very convenient as it treats all these characters as one unit. We grouped the characters and made it optional by applying '?' operator at once. However, this is not all that we can do with groups. we can capture groups and use them later in our programs. This makes regex very powerful. We can extract and desired strings and pattern and do some processing with them.

Before looking at how capturing is done lets first see another method that re module provides us.

group()

Until now we have been using match objects with if statements only but what if we want to see what characters were actually matched by the given test string? For this purpose, we use the group() method which was discussed briefly before. The group() method is applied on match object returned by search() or match() as discussed above. Let's see an example:

import re
res=re.search(r'coding','dev is the best place to learn coding.')
print(res)
print(res.group())

Output:
<_sre.SRE_Match object; span=(31, 37), match='coding'>;
coding

From the above output, we can see that when group method is used in it's simplest form without any arguments it simply returns the characters that were matched in a match object. When we try to print res itself as it is a match object we get <_sre.SRE_Match object; span=(31, 37), match='coding'> as output. But when we print res.group() we get the actual content of the match object that was matched.

Alternative matching in groups

We can use or operator with groups as shown below:

import re
regex = r'(Tom|Dick|Harry)'
res = re.search(regex, 'Tom is in the pattern')
res2 = re.search(regex, 'Dick is in the pattern')
res3 = re.search(regex, 'Harry is in the pattern')
res4 = re.search(regex, 'Sal is in the pattern')
print(res)
print(res2)
print(res3)
print(res4)

Output:
<_sre.SRE_Match object; span=(0, 3), match='Tom'>
<_sre.SRE_Match object; span=(0, 4), match='Dick'>
<_sre.SRE_Match object; span=(0, 5), match='Harry'>
None

From above example we can see that any pattern from the group matches.

Capturing and backreferences

With a group() we can capture expressions as shown above. It is capturing because it helps us to extract characters from matched objects which is useful as we will see. But the better use of group comes with arguments if we wrap some specific expression part inside a group(using paranthesis) we can get the content of the group using numbers as shown below:

import re
res=re.search(r'(cod)ing','gfg is the best place to learn coding.')
print(res)
print(res.group())
print(res.group(1))

Output:
<_sre.SRE_Match object; span=(31, 37), match='coding'>
coding
cod

As we can see after wrapping the character set 'cod' in paranthesis we formed the group (cod). So, to get all the matched characters we simply type res.group() and to get the contents of the group (cod) we type res.group(1). One natural question that arises is that why did we use 1 as an argument and nothing else? It will be clear when we will use multiple groups in the same regular expression. This is, in fact, another use of the group. To group different parts of expression and use them in our program conveniently. A simple example is shown below:

import re
res=re.search(r'(cod)(ing)','gfg is the best place to learn coding.')
print(res)
print(res.group())
print(res.group(1))
print(res.group(2))

Output:
<_sre.SRE_Match object; span=(31, 37), match='coding'>
coding
cod
ing

The use of number 1 and 2 as arguments becomes evident now. Numbers indicate the groups starting from left to right(leftmost group in the expression is assigned the value of 1 and then the value increases as we move towards right). And this process is what we call capturing. We can use groups to capture specific character sets and use them in our programs.
To see how capturing is useful, we will see an example borrowed from hackerrank. The problem is explained below:

We have been given the list of phone numbers of the format

[Country code]-[Local Area Code]-[Number]

Our job as a regex expert is to split it into country code, local area code and number and display them distinctly as shown below:

given number = 91-011-23413627
desired output after processing:
CountryCode=91,LocalAreaCode=011,Number=23413627

Some constraints are:

The number of numbers, N is in the range 1<=N<=20.
There might either be a '-' ( ascii value 45), or a ' ' ( space, ascii value 32) between the segments

We can solve above problem with the code below using groups and capturing:

import re
for i in range(int(input())):
    s=input()
    res=re.search('([0-9]{1,3})[- ]([0-9]{1,3})[- ]([0-9]{4,10})',s)
    if res:
        print("CountryCode={},LocalAreaCode={},Number={}".format(res.group(1),res.group(2),res.group(3)))

The code above uses many concepts that we have learnt until now. Let's see one by one:

The first for..in loop is used because the user is asked how many numbers they want to process.
For every number, we check whether the number string matches our pattern which must contain from 1 to 3 digits in the country code segment, from 1 to 3 digits in local area code segment and 4 to 10 digits in actual number segment.
We use groups to capture each segment.
We display each segment using the group method with arguments from 1 to 2 to 3 from left to right.

6. Backreferences

Backreferences are useful when we use the same text or character set again and again in the same expression. Whenever we create a group it is automatically stored in what we call as a backreference so that latter it can be used in program or expression itself. In fact, what we used as arguments in the group() method were actually backreferences. We have already seen one use of backreferences. That was to use captured groups in our program. Now we will see the use of backreferences inside our expressions. If we are looking for the same group of characters, again and again, we can use backreferences. Let's look at a regular expression for instance:

[A-Za-z]{3}\d{3}[A-Za-z]{3}

This pattern simply requires that there should be 3 alphabetical characters followed by three numerical digits followed by 3 alphabetical letters again. But we don't need to write whole regex for alphabetical letters again and if that same regex is required more times it will become lengthier to write same regex again and again. Instead we can use backreferences by grouping the first occurence as shown below:

([A-Za-z]){3}\d{3}\1{3}

'\1' in the above example represents backreference of the group ([A-Za-z]) we can replace all the other occurrences of that group with stored backreference '\1'. Numbering is done the same as group method starting from 1 and increasing numbers from left to right.

Search and replace with re.sub() method

The final method that we will see before closing this tutorial is sub() method of re module which helps us to search for specific text in the test string and replace it with the desired text. The signature of sub() method is:

re.sub(pattern, replacement, string)
where the pattern is the regex written by you
replacement is the string which we want to replace our pattern with
the string is a test string

One simple example is shown below:

import re
test = "people in indie are called indiens"
res = re.sub(r'indie', 'India', test)
print(res)

Output:
people in India are called Indians

Conclusion

That's it. This is the end of this somewhat long tutorial. I don't claim in any way that this tutorial is complete and I have covered everything that can be done with regex in python but basics are pretty much covered. After reading this tutorial I would recommend doing the following:

Read some more topics in detail like word boundaries(\b and \B), splitting with regular expressions, more flags. But apart from these, this tutorial has covered all the basics.
Practice regex syntax from this site
practice using regex with python from hackerrank regex track

Follow these three steps after this tutorial and you will have decent enough knowledge to apply regex not only in python but any other language. As they say "practice makes permanent".

Cheers!!

DEV Community: Zero | One

Introducing SheetDB: Turn Google Sheets into your database

Introduction

The Benefits of Using Google Sheets as a Database

The SheetDB Ecosystem

The Inspiration

What SheetDB Does: A Complete Solution

Core Features

🗄️ True Relational Database

⚡ 500x Performance Boost

🎯 Three Flexible Operating Modes

📝 Multiple Read/Write Options

🤖 Everything Automated

🌐 Framework Agnostic

How I Built It: Architecture and Technical Decisions

Core Architecture

Key Technical Decisions

1. DuckDB as Cache Layer

2. Three Operating Modes

3. Forms API for Unlimited Writes

4. Schema-First Design

5. Framework Agnostic Core

Challenges I Faced and How I Solved Them

Challenge 1: Google API Rate Limits

Challenge 2: Relational Data in Flat Sheets

Challenge 3: Type Safety Without a Real Database

Challenge 4: Multiple Framework Support

Challenge 5: Authentication Complexity

Challenge 6: Schema Management

Challenge 7: Forms API Integration

Accomplishments I'm Proud Of

🎯 True Relational Database

🆓 Still Completely Free

🤖 Automation That Actually Works

📚 Comprehensive Documentation

🎨 Beautiful Demo Apps

🌐 Framework Flexibility

📊 Production-Ready

What I Learned

Technical Lessons

How I Used Kiro: Spec-Driven Development Meets Vibe Coding

Spec-Driven Development

Vibe Coding

The Key Insight

What's Next for SheetDB

Short-term (Next 3 Months)

Medium-term (6 Months)

Long-term (1 Year+)

Try SheetDB Today!

Quick Start (5 Minutes)

Links

Join the Community

Regular Expressions(RegEx) in Python

So, What is Regex?

Working with Regex(using Python3)

1. re.search()

2. re.match()

matching start and end

3. re.findall()

4. re.compile()

optional flag argument in re methods

Syntax of Regular expressions

1. Any Charatcer(.)

2. Character Class([])

3. Predefined Character Classes

4. Quantifiers

a. ?(for optional preceding character)

b. *(zero or more repetitions)

c. +(one or more repetitions)

d. {x}(fixed no. of repetitions)

e. {x,y}(fixed and ranged repetitions):

5. Grouping and Capturing

group()

Alternative matching in groups

Capturing and backreferences

6. Backreferences

Search and replace with re.sub() method

Conclusion

Credits(Cover Image):Image by msandersmusic from Pixabay