آموزش SQLite با پایتون

معرفی

این آموزش استفاده از SQLite در ترکیب با Python را پوشش می دهد sqlite3 رابط. SQLite یک پایگاه داده رابطه ای تک فایل است که با اکثر نصب های استاندارد پایتون همراه شده است. SQLite اغلب فناوری انتخابی برای برنامه های کوچک است، به ویژه سیستم ها و دستگاه های تعبیه شده مانند تلفن ها و تبلت ها، لوازم هوشمند و ابزار. با این حال، شنیدن استفاده از آن برای برنامه های کاربردی وب کوچک تا متوسط ​​و دسکتاپ غیر معمول نیست.

ایجاد پایگاه داده و ایجاد ارتباط

ایجاد یک پایگاه داده جدید SQLite به سادگی ایجاد یک اتصال با استفاده از ماژول sqlite3 در کتابخانه استاندارد پایتون است. برای ایجاد یک اتصال تنها کاری که باید انجام دهید این است که یک مسیر فایل را به آن ارسال کنید connect(...) روش در ماژول sqlite3 است، و اگر پایگاه داده ای که فایل ارائه می کند وجود نداشته باشد، در آن مسیر ایجاد می شود.

import sqlite3
con = sqlite3.connect('/path/to/file/db.sqlite3')

متوجه خواهید شد که در برنامه نویسی روزمره پایگاه داده شما دائماً در حال ایجاد اتصالات به پایگاه داده خود خواهید بود، بنابراین ایده خوبی است که این عبارت اتصال ساده را در یک تابع تعمیم یافته قابل استفاده مجدد قرار دهید.

import os
import sqlite3



DEFAULT_PATH = os.path.join(os.path.dirname(__file__), 'database.sqlite3')

def db_connect(db_path=DEFAULT_PATH):
    con = sqlite3.connect(db_path)
    return con

ایجاد جداول

برای ایجاد جداول پایگاه داده، باید از ساختار داده هایی که می خواهید ذخیره کنید، ایده ای داشته باشید. ملاحظات طراحی زیادی وجود دارد که به تعریف جداول یک پایگاه داده رابطه‌ای می‌پردازد، که کتاب‌های کاملی درباره آن نوشته شده است. من وارد جزئیات این عمل نمی شوم و در عوض بررسی بیشتر آن را به خواننده واگذار می کنم.

با این حال، برای کمک به بحث ما در مورد برنامه نویسی پایگاه داده SQLite با پایتون، من از این فرض استفاده خواهم کرد که یک پایگاه داده برای یک فروشگاه کتاب ساختگی که داده های زیر را قبلاً جمع آوری کرده است باید ایجاد شود. روی فروش کتاب

پس از بررسی این داده ها مشخص می شود که حاوی اطلاعاتی در مورد مشتریان، محصولات و سفارشات است. یک الگوی رایج در طراحی پایگاه داده برای سیستم های تراکنشی از این نوع، شکستن دستورات به دو جدول اضافی است. سفارشات و آیتم های خط (گاهی اوقات به عنوان جزئیات سفارش) برای رسیدن به عادی سازی بیشتر.

در یک مفسر پایتون، در همان دایرکتوری db_utils.py ماژول تعریف شده قبلی، SQL را برای ایجاد جداول مشتریان و محصولات به شرح زیر وارد کنید:

>>> from db_utils import db_connect
>>> con = db_connect() 
>>> cur = con.cursor() 
>>> customers_sql = """
... CREATE TABLE customers (
...     id integer PRIMARY KEY,
...     first_name text NOT NULL,
...     last_name text NOT NULL)"""
>>> cur.execute(customers_sql)
>>> products_sql = """
... CREATE TABLE products (
...     id integer PRIMARY KEY,
...     name text NOT NULL,
...     price real NOT NULL)"""
>>> cur.execute(products_sql)

کد بالا یک شی اتصال ایجاد می کند و سپس از آن برای نمونه سازی یک شی مکان نما استفاده می کند. شی مکان نما برای اجرای دستورات SQL استفاده می شود روی پایگاه داده SQLite

با ایجاد مکان نما، سپس SQL را برای ایجاد جدول مشتریان نوشتم، به آن یک کلید اصلی به همراه یک فیلد متنی نام و نام خانوادگی دادم و آن را به متغیری به نام اختصاص دادم. customers_sql. من سپس به execute(...) روش ارسال شی مکان نما به آن customers_sql متغیر. سپس یک را ایجاد می کنم محصولات جدول به روشی مشابه

می توانید پرس و جو کنید sqlite_master جدول، یک جدول فراداده داخلی SQLite، برای تأیید موفقیت آمیز بودن دستورات بالا.

برای دیدن همه جداول در پایگاه داده متصل فعلی، از آن پرس و جو کنید name ستون از sqlite_master جدولی که در آن type برابر است با “جدول”.

>>> cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
<sqlite3.Cursor object at 0x104ff7ce0>
>>> print(cur.fetchall())
(('customers',), ('products',))

برای مشاهده طرحواره جداول، پرس و جو کنید sql ستون همان جدول که در آن type هنوز “جدول” است و name برابر با «مشتریان» و/یا «محصولات» است.

>>> cur.execute("""SELECT sql FROM sqlite_master WHERE type='table'
… AND name='customers'""")
<sqlite3.Cursor object at 0x104ff7ce0>
>>> print(cur.fetchone()(0))
CREATE TABLE customers (
    id integer PRIMARY KEY,
    first_name text NOT NULL,
    last_name text NOT NULL)

جدول بعدی برای تعریف خواهد بود سفارشات جدولی که مشتریان را به سفارشات از طریق کلید خارجی و تاریخ خرید آنها مرتبط می کند. از آنجایی که SQLite از نوع داده تاریخ/زمان واقعی (یا کلاس داده برای سازگاری با زبان بومی SQLite) پشتیبانی نمی‌کند، همه تاریخ‌ها به عنوان مقادیر متنی نمایش داده می‌شوند.

>>> orders_sql = """
... CREATE TABLE orders (
...     id integer PRIMARY KEY,
...     date text NOT NULL,
...     customer_id integer,
...     FOREIGN KEY (customer_id) REFERENCES customers (id))"""
>>> cur.execute(orders_sql)

جدول نهایی برای تعریف خواهد بود آیتم های خط جدولی که حسابداری دقیق محصولات را در هر سفارش نشان می دهد.

lineitems_sql = """
... CREATE TABLE lineitems (
...     id integer PRIMARY KEY,
...     quantity integer NOT NULL,
...     total real NOT NULL,
...     product_id integer,
...     order_id integer,
...     FOREIGN KEY (product_id) REFERENCES products (id),
...     FOREIGN KEY (order_id) REFERENCES orders (id))"""
>>> cur.execute(lineitems_sql)

در حال بارگیری داده ها

در این بخش روش درج داده های نمونه خود را در جداول ایجاد شده نشان خواهم داد. یک مکان شروع طبیعی، پر جمعیت کردن است محصولات جدول اول زیرا بدون محصولات نمی‌توانیم فروش داشته باشیم و بنابراین کلیدهای خارجی برای ارتباط با اقلام خطی و سفارش‌ها را نخواهیم داشت. با نگاه کردن به داده های نمونه می بینم که چهار محصول وجود دارد:

• مقدمه ای بر ترکیبات (7.99 دلار)
• راهنمای نوشتن داستان های کوتاه (17.99 دلار)
• ساختارهای داده و الگوریتم ها (11.99 دلار)
• تئوری مجموعه‌های پیشرفته (16.99 دلار)

گردش کار برای اجرای دستورات INSERT به سادگی:

1. به پایگاه داده متصل شوید
2. یک شی مکان نما ایجاد کنید
3. یک دستور SQL درج پارامتر شده بنویسید و به عنوان یک متغیر ذخیره کنید
4. متد execute را فراخوانی کنید روی شی مکان نما که متغیر sql را به آن ارسال می کند و مقادیر به صورت چند تایی در جدول درج می شوند.

با توجه به این طرح کلی، اجازه دهید کد بیشتری بنویسیم.

>>> con = db_connect()
>>> cur = con.cursor()
>>> product_sql = "INSERT INTO products (name, price) VALUES (?, ?)"
>>> cur.execute(product_sql, ('Introduction to Combinatorics', 7.99))
>>> cur.execute(product_sql, ('A Guide to Writing Short Stories', 17.99))
>>> cur.execute(product_sql, ('Data Structures and Algorithms', 11.99))
>>> cur.execute(product_sql, ('Advanced Set Theory', 16.99))

کد بالا احتمالاً کاملاً واضح به نظر می رسد، اما اجازه دهید کمی در مورد آن بحث کنم زیرا موارد مهمی وجود دارد روی اینجا. دستور insert از دستور SQL استاندارد پیروی می کند به جز از ? بیت را ?‘ها در واقع جای‌بان‌هایی هستند که به عنوان “پرس و جوی پارامتری” شناخته می‌شود.

پرس و جوهای پارامتری یک ویژگی مهم اساساً تمام رابط های پایگاه داده به زبان های برنامه نویسی سطح بالا مانند ماژول sqlite3 در پایتون هستند. این نوع پرس و جو در خدمت بهبود کارایی پرس و جوهایی است که چندین بار تکرار می شوند. شاید مهم تر، آنها همچنین ورودی هایی را که جای آن را می گیرند، ضد عفونی می کنند ? متغیرهایی که در طول تماس به اجرا کردن روش شی مکان نما برای جلوگیری از ورودی های شیطانی منجر به تزریق SQL. مطلب زیر یک کمیک از محبوب است xkcd.com وبلاگی که خطرات تزریق SQL را شرح می دهد.

برای پر کردن جدول‌های باقی‌مانده، می‌خواهیم از یک الگوی کمی متفاوت پیروی کنیم تا اوضاع کمی تغییر کند. گردش کار برای هر سفارش، که با ترکیبی از نام و نام خانوادگی مشتری و تاریخ خرید مشخص می شود، به صورت زیر خواهد بود:

1. مشتری جدید را در جدول مشتریان وارد کنید و شناسه کلید اصلی آن را بازیابی کنید
2. یک ورودی سفارش بر اساس شناسه مشتری و تاریخ خرید ایجاد کنید، سپس شناسه کلید اصلی آن را بازیابی کنید
3. برای هر محصول در سفارش، شناسه کلید اصلی آن را تعیین کنید و یک ورودی مورد خطی ایجاد کنید که سفارش و محصول را مرتبط می کند

تا همه چیز ساده تر شود روی خودمان به ما اجازه می‌دهیم همه محصولات خود را به سرعت بررسی کنیم. در حال حاضر زیاد نگران مکانیزم دستور SELECT SQL نباشید زیرا به زودی بخشی را به آن اختصاص خواهیم داد.

>>> cur.execute("SELECT id, name, price FROM products")
>>> formatted_result = (f"{id:<5}{name:<35}{price:>5}" for id, name, price in cur.fetchall())
>>> id, product, price = "Id", "Product", "Price"
>>> print('\n'.join((f"{id:<5}{product:<35}{price:>5}") + formatted_result))
Id   Product                            Price
1    Introduction to Combinatorics       7.99
2    A Guide to Writing Short Stories   17.99
3    Data Structures and Algorithms     11.99
4    Advanced Set Theory                16.99

اولین سفارش انجام شد روی 22 فوریه 1944 توسط آلن تورینگ که خرید مقدمه ای بر ترکیب شناسی برای 7.99 دلار

با ایجاد یک رکورد مشتری جدید برای آقای تورینگ شروع کنید، سپس با دسترسی به شناسه کلید اصلی او را تعیین کنید lastrowid فیلد شی مکان نما

>>> customer_sql = "INSERT INTO customers (first_name, last_name) VALUES (?, ?)"
>>> cur.execute(customer_sql, ('Alan', 'Turing'))
>>> customer_id = cur.lastrowid
>>> print(customer_id)
1

اکنون می‌توانیم یک ورودی سفارش ایجاد کنیم، مقدار شناسه سفارش جدید را جمع‌آوری کنیم و آن را با محصولی که آقای تورینگ سفارش داده است به یک خط ورودی مرتبط کنیم.

>>> order_sql = "INSERT INTO orders (date, customer_id) VALUES (?, ?)"
>>> date = "1944-02-22" 
>>> cur.execute(order_sql, (date, customer_id))
>>> order_id = cur.lastrowid
>>> print(order_id)
1
>>> li_sql = """INSERT INTO lineitems 
...       (order_id, product_id, quantity, total)
...     VALUES (?, ?, ?, ?)"""
>>> product_id = 1
>>> cur.execute(li_sql, (order_id, 1, 1, 7.99))

رکوردهای باقیمانده دقیقاً به همان ترتیب بارگذاری می شوند، به جز سفارشی که به دونالد کنوت انجام شده است، که دو ورودی مورد خط را دریافت می کند. با این حال، ماهیت تکراری چنین کاری، نیاز به قرار دادن این قابلیت‌ها در توابع قابل استفاده مجدد را فریاد می‌زند. در db_utils.py ماژول کد زیر را اضافه کنید:

def create_customer(con, first_name, last_name):
    sql = """
        INSERT INTO customers (first_name, last_name)
        VALUES (?, ?)"""
    cur = con.cursor()
    cur.execute(sql, (first_name, last_name))
    return cur.lastrowid

def create_order(con, customer_id, date):
    sql = """
        INSERT INTO orders (customer_id, date)
        VALUES (?, ?)"""
    cur = con.cursor()
    cur.execute(sql, (customer_id, date))
    return cur.lastrowid

def create_lineitem(con, order_id, product_id, qty, total):
    sql = """
        INSERT INTO lineitems
            (order_id, product_id, quantity, total)
        VALUES (?, ?, ?, ?)"""
    cur = con.cursor()
    cur.execute(sql, (order_id, product_id, qty, total))
    return cur.lastrowid

اوه، حالا می توانیم با کمی کارایی کار کنیم!

شما نیاز خواهید داشت exit() مفسر پایتون خود را بارگذاری مجدد کنید تا توابع جدید شما در مفسر قابل دسترسی باشند.

>>> from db_utils import db_connect, create_customer, create_order, create_lineitem
>>> con = db_connect()
>>> knuth_id = create_customer(con, 'Donald', 'Knuth')
>>> knuth_order = create_order(con, knuth_id, '1967-07-03')
>>> knuth_li1 = create_lineitem(con, knuth_order, 2, 1, 17.99)
>>> knuth_li2 = create_lineitem(con, knuth_order, 3, 1, 11.99)
>>> codd_id = create_customer(con, 'Edgar', 'Codd')
>>> codd_order = create_order(con, codd_id, '1969-01-12')
>>> codd_li = create_lineitem(con, codd_order, 4, 1, 16.99)

من احساس می کنم به عنوان یک دانشجوی کاردستی نرم افزار یک توصیه اضافی به من می دهد. وقتی متوجه شدید که چندین دستکاری پایگاه داده (در این مورد INSERT) انجام می دهید تا آنچه را که در واقع یک کار تجمعی است (یعنی ایجاد یک سفارش) انجام دهید، بهترین کار این است که وظایف فرعی (ایجاد مشتری، سفارش، سپس آیتم های خط) را در یک مجموعه قرار دهید. تراکنش واحد پایگاه داده، بنابراین می توانید هر دو را انجام دهید روی اگر خطایی در مسیر رخ دهد موفقیت یا بازگشت.

این چیزی شبیه به این خواهد بود:

try:
    codd_id = create_customer(con, 'Edgar', 'Codd')
    codd_order = create_order(con, codd_id, '1969-01-12')
    codd_li = create_lineitem(con, codd_order, 4, 1, 16.99)

    
    con.commit()
except:
    
    con.rollback()
    raise RuntimeError("Uh oh, an error occurred ...")

من می خواهم این بخش را با نمایش سریع روش به روز رسانی یک رکورد موجود در پایگاه داده به پایان برسانم. بیایید قیمت راهنمای نوشتن داستان کوتاه را به 10.99 به روز کنیم روی فروش).

>>> update_sql = "UPDATE products SET price = ? WHERE id = ?"
>>> cur.execute(update_sql, (10.99, 2))

پرس و جو از پایگاه داده

به طور کلی رایج ترین عمل انجام شده است روی پایگاه داده بازیابی برخی از داده های ذخیره شده در آن از طریق دستور SELECT است. برای این بخش، روش استفاده از رابط sqlite3 برای انجام پرس و جوهای ساده SELECT را نشان خواهم داد.

برای انجام یک پرس و جوی چند ردیفی اولیه از جدول مشتریان، یک عبارت SELECT را به آن ارسال می کنید execute(...) روش شی مکان نما پس از این می توانید با فراخوانی روی نتایج پرس و جو تکرار کنید fetchall() روش همان شی مکان نما.

>>> cur.execute("SELECT id, first_name, last_name FROM customers")
>>> results = cur.fetchall()
>>> for row in results:
...     print(row)
(1, 'Alan', 'Turing')
(2, 'Donald', 'Knuth')
(3, 'Edgar', 'Codd')

فرض کنید می خواهید در عوض فقط یک رکورد را از پایگاه داده بازیابی کنید. شما می توانید این کار را با نوشتن یک پرس و جو خاص تر، مثلاً برای شناسه 2 دونالد کنوت، و پیگیری آن با تماس گرفتن انجام دهید. fetchone() روش شی مکان نما

>>> cur.execute("SELECT id, first_name, last_name FROM customers WHERE id = 2")
>>> result = cur.fetchone()
>>> print(result)
(2, 'Donald', 'Knuth')

ببینید چگونه ردیف تکی هر نتیجه به شکل یک تاپل است؟ خوب، در حالی که تاپل ها یک ساختار داده پایتونیک بسیار مفید برای برخی از موارد استفاده برنامه نویسی هستند، بسیاری از مردم در هنگام بازیابی داده ها، آنها را کمی مانع می شوند. اتفاقاً راهی برای نمایش داده ها به گونه ای وجود دارد که شاید برای برخی انعطاف پذیرتر باشد. تنها کاری که باید انجام دهید این است که row_factory روش اتصال شی به چیزی مناسب تر مانند sqlite3.Row. این به شما این امکان را می دهد که به تک تک موارد یک ردیف بر اساس موقعیت یا ارزش کلمه کلیدی دسترسی داشته باشید.

>>> import sqlite3
>>> con.row_factory = sqlite3.Row
>>> cur = con.cursor()
>>> cur.execute("SELECT id, first_name, last_name FROM customers WHERE id = 2")
>>> result = cur.fetchone()
>>> id, first_name, last_name = result('id'), result('first_name'), result('last_name')
>>> print(f"Customer: {first_name} {last_name}'s id is {id}")
Customer: Donald Knuth's id is 2

نتیجه

در این مقاله من به طور مختصر نشان دادم که مهم‌ترین ویژگی‌ها و قابلیت‌های رابط Python sqlite3 به پایگاه‌داده SQLite تک فایلی است که از قبل همراه با اکثر نصب‌های پایتون است. من همچنین سعی کردم نکاتی را در مورد بهترین روش ها در مورد برنامه نویسی پایگاه داده ارائه دهم، اما به تازه وارد هشدار می دهم که پیچیدگی های برنامه نویسی پایگاه داده به طور کلی یکی از مستعدترین حفره های امنیتی در سطح سازمانی و بیشتر است. قبل از چنین اقدامی دانش لازم است.

مثل همیشه از شما برای خواندن و استقبال از نظرات و انتقادات زیر تشکر می کنم.