Lightweight Database + DuckDB

A 'Hafif Veritabanı' (Lightweight Database) refers to a database system designed for minimal overhead, easy embedding into applications, and typically operating without a separate server process. These databases are ideal for local data storage, mobile applications, edge computing, small to medium-scale data analysis, and development environments where a full-fledged client-server database might be overkill or too resource-intensive.\n\nDuckDB is an excellent example of a modern lightweight database, specifically optimized for analytical workloads (OLAP - Online Analytical Processing). Unlike traditional OLTP (Online Transactional Processing) databases which are designed for high-volume, short transactions, DuckDB excels at complex queries, aggregations, and large-scale data scans. It's often referred to as an 'in-process SQL OLAP database'.\n\nKey features and benefits of DuckDB:\n\n1. Embedded & Serverless: It runs directly within your application's process, requiring no separate server installation, configuration, or management. This simplifies deployment and reduces operational overhead.\n2. Columnar Storage: Data is stored column-wise, which is highly efficient for analytical queries as it allows DuckDB to read only the necessary columns and process them in a vectorized fashion, leading to significant performance gains.\n3. Vectorized Query Execution: Queries are executed using vectorized operations on blocks of data, making optimal use of CPU caches and modern processor capabilities.\n4. SQL Compatibility: Supports a rich dialect of SQL, including advanced analytical functions, window functions, and common table expressions (CTEs).\n5. Direct Data Integration: DuckDB can query data directly from various external formats without explicit loading, including Parquet, CSV, JSON, Apache Iceberg, Delta Lake, and even Pandas/Polars DataFrames. This 'zero-copy' integration with in-memory data structures like Pandas DataFrames is particularly powerful for data science workflows.\n6. High Performance for Analytics: Its design makes it incredibly fast for aggregation, filtering, joining, and grouping operations on large datasets.\n7. Lightweight Footprint: Despite its power, DuckDB has a small memory and disk footprint, making it suitable for environments with limited resources.\n\nDuckDB fills a crucial gap for data professionals who need fast, local analytical capabilities without the complexity and resource demands of a distributed data warehouse or a client-server RDBMS. It's particularly popular in Python for data analysis, ETL, and local data warehousing tasks.

import duckdb\nimport pandas as pd\n\n 1. Connect to an in-memory DuckDB database\n For a persistent database that stores data on disk: con = duckdb.connect(database='my_data.duckdb')\ncon = duckdb.connect(database=':memory:')\n\nprint("--- Creating a table and inserting data ---")\ncon.execute("""\n    CREATE TABLE sales (\n        id INTEGER,\n        product_name VARCHAR,\n        category VARCHAR,\n        sales_amount DECIMAL(10, 2),\n        sale_date DATE\n    );\n""")\n\ncon.execute("INSERT INTO sales VALUES (1, 'Laptop', 'Electronics', 1200.50, '2023-01-15');")\ncon.execute("INSERT INTO sales VALUES (2, 'Mouse', 'Electronics', 25.00, '2023-01-15');")\ncon.execute("INSERT INTO sales VALUES (3, 'Keyboard', 'Electronics', 75.25, '2023-01-16');")\ncon.execute("INSERT INTO sales VALUES (4, 'Desk Chair', 'Furniture', 300.00, '2023-01-16');")\ncon.execute("INSERT INTO sales VALUES (5, 'Monitor', 'Electronics', 250.75, '2023-01-17');")\ncon.execute("INSERT INTO sales VALUES (6, 'Coffee Table', 'Furniture', 150.00, '2023-01-17');")\n\nprint("\\n--- Querying all data ---")\n fetchdf() returns results as a Pandas DataFrame\nresult_df = con.execute("SELECT - FROM sales;").fetchdf()\nprint(result_df)\n\nprint("\\n--- Performing an analytical query: Total sales per category ---")\nanaly_result_df = con.execute("""\n    SELECT \n        category, \n        SUM(sales_amount) AS total_sales,\n        COUNT(-) AS number_of_sales\n    FROM sales\n    GROUP BY category\n    ORDER BY total_sales DESC;\n""").fetchdf()\nprint(analy_result_df)\n\nprint("\\n--- Reading directly from a Pandas DataFrame (zero-copy integration) ---")\ndf_cities = pd.DataFrame({\n    'city': ['New York', 'Los Angeles', 'Chicago', 'Houston'],\n    'population': [8400000, 3900000, 2700000, 2300000]\n})\n\n DuckDB can directly query Pandas DataFrames that are present in the Python environment.\n The DataFrame 'df_cities' is treated as a table named 'df_cities'.\npopulation_query_result = con.execute("SELECT - FROM df_cities WHERE population > 3000000;").fetchdf()\nprint(population_query_result)\n\n Close the connection\ncon.close()\nprint("\\n--- Database connection closed ---")