SQLite Database in Python for Personal Data Storage

I’ve been developing prototype Python Pandas codes to transform observational data housed on an OMOP schema into a SDTM schema containing the tables, DM, and MH, in particular. My in-house setup is with MySQL on a bare metal Ubuntu Server. I realize that there are already R solutions to do this and I personally worked on a SAS solution, but it’s my my belief Python may be the best programming approach. Also I don’t have SAS, thus my interest in developing a set of codes using free tools, but it may have greater potential, overall, and it’s definitely worth the development time.

🚀 I just published my first Python package on PyPI — sqliter-lochan! The problem: Every time you use Python's built-in sqlite3, you write the same boring boilerplate. Connect. Cursor. Execute. Commit. Close. Every. Single. Time. so I made sqlite a little lighter → sqliter 👀 ❌ The Old Way (standard sqlite3): ────────────────────────── conn = sqlite3.connect('app.db') cursor = conn.cursor() cursor.execute("INSERT INTO users (name) VALUES (?)", ("Elone Musk",)) conn.commit() conn.close() ✅ The sqliter Way: ────────────────────────── from sqliter import Database db = Database("app.db") db.query("INSERT INTO users (name) VALUES (?)", ("Elone Musk",)) That's it. Clean. Simple. Done. ⚡ What makes it different: → Zero external dependencies — pure Python, nothing to install extra → One db.query() method handles INSERT, SELECT, UPDATE, DELETE — everything → Results come back as dict-like rows — no more index gymnastics → Built-in error handling — database failures never go unnoticed 📦 Install it right now: pip install sqliter-lochan 🔗 GitHub (contributions welcome!): https://lnkd.in/gr5x35m7 #Python #OpenSource #PyPI #SQLite #Developer #PythonPackage

🚀 Day 14 – SQL Challenge “Find students who know C”… Simple? Not when your data looks like this: 👉 C, Python, C++ Now the real question: How do you match only ‘C’ and ignore C++ / C#? I explored multiple approaches 👇 🔹 1. LIKE-based logic (pattern matching) Works, but gets messy with multiple conditions. 🔹 2. Split-based approach using SUBSTRING_INDEX Simulates splitting and gives more control: 🔹 3. FIND_IN_SET (simple & effective) Sometimes the simplest solution wins: WHERE FIND_IN_SET('C', skills) 🔥 Key Insight: There’s no single “best” solution in SQL. It’s about choosing between readability, scalability, and simplicity based on the situation. Which approach would you pick? 👇 Thanks for the suggestion, Ratan Kumar jha! 🙌 Tried a split-based approach as well using SUBSTRING_INDEX to simulate splitting in MySQL. Really helped make the logic cleaner and more structured. #SQL #DataAnalytics #SQLChallenge #ProblemSolving #LearningInPublic

🎯 Tech Learning Journey - Day 08: JSON & Data Serialization - Universal Data Language! JSON is how different applications talk to each other. It's a simple text format that stores data in a way any programming language can understand - perfect for sending data between servers, apps, and websites. import json # Python data to JSON data = \{"name": "Dhanush", "skills": \["Python", "SQL"\]\} json\_string = json.dumps\(data\) # JSON back to Python parsed = json.loads\(json\_string\) print\(parsed\["name"\]\) # Dhanush Where I use this: Storing configuration files, exchanging data with web APIs, and saving user preferences in applications. #Python #Coding #Programming #JSON

🚀 Built a Lightweight File-Based Database in Python Over the past few days, I worked on a project called mini-DB — a simple yet powerful key-value database built from scratch using Python. 🔹 What it does: Stores data in JSON files 📁 Supports basic operations: create, get, set, update Uses file-level locking to handle concurrent access 🔒 Provides a clean CLI interface using argparse ⚡ 🔹 Why I built this: I wanted to understand how databases actually work under the hood — especially concepts like: Data persistence Concurrency control Storage abstraction Instead of just using tools like Redis or MongoDB, I tried building a minimal version myself. 🔹 Key learnings: Designing layered architecture (Storage → Connector → Interface) Handling race conditions with file locks Building CLI tools that feel like real-world systems Example usage: python tool.py master set user Jashan python tool.py master get user python tool.py master update user Singh 🔗 GitHub: https://lnkd.in/gXduQnez This project may look simple, but it gave me a deeper appreciation of how real databases manage data safely and efficiently. 💡 Next step: applying these concepts to more complex systems and scaling ideas further. Would love feedback or suggestions! #Python #BackendDevelopment #SystemDesign #Databases #CLI #LearningByBuilding

Every new skill starts with confusion, errors, and a lot of debugging. Working with SQL connections and PostgreSQL through Python has been exactly that—figuring out errors, understanding why queries fail, and learning how databases actually function. But that’s also where the real learning happens. Step by step, I’m becoming more comfortable with querying data, managing connections, and thinking more logically about how information is stored and retrieved. It’s a process, but one that’s building a solid foundation for data analytics and future work in finance. #SQL #PostgreSQL #Python #DataAnalytics #DataScience #Finance #LearningByDoing #MittalschoolofBusiness #lpu

Continuing from last week’s report, this week I learned about SQL and Git. I had some experience with databases before using Python, but mostly limited to CRUD. So at first, I thought SQL would be similar. Turns out, it’s much more than that. Here are some key things I learned this week: • Basic SQL (DDL, DML, queries like SELECT, WHERE, etc.) • Data processing using aggregate functions & GROUP BY • Joining multiple tables using JOIN • Version Control System (Git) and its workflow • Using GitHub for collaboration and tracking changes This made me realize that SQL is not just about CRUD, but about structuring and processing data more effectively. Still learning, but turns out it’s starting to make more sense why these fundamentals matter. If you’re interested, feel free to check out the slides I’ve shared. #DigitalSkola #LearningProgressReview #DataScience

Day 03 #StudyClub SQL, we performed data transformation and loaded the data into the database we created on Day 02. This time, we used Python for the transformation and loading process into MySQL. Stay tuned for the next episode to find out the results and the ultimate goal of this #StudyClub SQL episode. Fyi, we successfully load 72 million records of data. We encountered many errors and issues during this process. I've already written down all the issues and their solutions in the report. Mentor: Hilmi ‎. Team: Monika Hermiani Yolanda Simamora Irpan Pilihan Rambe nadhira M. Aprida Sapitri Br Saragih Dyah Ayu Goldy Cahyastuti Medina Uli Alba Somala Siti Komala Hafsaninda MR Rintaldi Ghazian Hindami Reihan Nanda R. #SQLDay #MySQL #EntityRelationshipDiagram #ManifoldDatafolk #DatabaseDesignPrinciple #StudyClubDay3 #Python #ETLProcess

Day 04 #StudyClub SQL, we learned and implemented indexing strategies. The purpose of this indexing is to reduce the time it takes for the database to process our queries, due to the large volume of data. With 72 million records, processing a single query without an index would take an extremely long time. Mentor: Hilmi ‎. Team: Monika Hermiani Yolanda Simamora Irpan Pilihan Rambe nadhira M. Aprida Sapitri Br Saragih Dyah Ayu Goldy Cahyastuti Medina Uli Alba Somala Siti Komala Hafsaninda MR Rintaldi Ghazian Hindami Reihan Nanda R. #SQLDay #MySQL #EntityRelationshipDiagram #ManifoldDatafolk #DatabaseDesignPrinciple #StudyClubDay4 #Python #ETLProcess #IndexingStrategies

🚀✨ 𝗗𝗮𝘆 𝟳 𝗼𝗳 𝗠𝘆 𝗣𝘆𝘁𝗵𝗼𝗻 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗝𝗼𝘂𝗿𝗻𝗲𝘆🐍💻 Today I explored File Handling in Python and understood how programs can actually store and manage data permanently💾🔥 🔹 📂 𝗪𝗵𝗮𝘁 𝗜 𝗟𝗲𝗮𝗿𝗻𝗲𝗱: 📁 Types of Files: ✔️ Text Files → (.txt, .csv, .log) ✔️ Binary Files → (.jpg, .png, .mp4, .pdf, .exe) 📖 File Handling Methods: 👉 open()→ to open files 👉 read(), readline(), readlines()→ to read data 👉 write()→ to store data 👉 "a" mode → to append data 🔐 Best Practice: ✔️ Used with open() for automatic file closing 🔧 File Management (New Learning): 👉 Copy files → shutil.copy() 👉 Rename files → os.rename() 👉 Delete files → os.remove() 🔹 ✨ 𝗞𝗲𝘆 𝗧𝗮𝗸𝗲𝗮𝘄𝗮𝘆𝘀: ✔️ File handling makes programs practical ✔️ Data can be stored and reused anytime ✔️ Learned how to manage files using Python 🔥 𝗗𝗮𝘆 𝟳 — 𝗙𝗿𝗼𝗺 𝗖𝗼𝗱𝗲 𝘁𝗼 𝗥𝗲𝗮𝗹-𝗪𝗼𝗿𝗹𝗱 𝗔𝗽𝗽𝗹𝗶𝗰𝗮𝘁𝗶𝗼𝗻𝘀! Thank you Saumya Singh for making learning so easy #Python #LearningJourney #Day7 #FileHandling #Coding #Programming #🚀