汽车租赁系统-将租赁添加到数据库中

Question

我刚回到编程领域，决定创建一个汽车租赁系统(GitHub page 这里。)，教自己有关数据库、gui编程和web开发的知识。我想要一些关于我能在我刚刚重写的特定函数上所能得到的信息。

函数所做的是在客户名称中添加汽车租赁，使汽车不可供其他人租用，并添加租赁记录。全部转到数据库.

我对此很满意，但我知道有一些地方我可以提高。

这里是函数：

import sqlite3
import datetime


conn = sqlite3.connect("db/test.db")
c = conn.cursor()
current_datetime = datetime.datetime.now()

def add_lease(customer_id, car_id, lease_expire):
  """
  Adds lease to customers account and 
  removes the car from available cars list.
  """

  lease_start = current_datetime.strftime("%Y-%m-%d")
  car_active = c.execute("SELECT IS_LEASED FROM CARS WHERE ID=?",
      (car_id,)).fetchone()[0]
  lease_active = c.execute("SELECT ACTIVE_LEASE FROM CUSTOMERS WHERE ID=?",
      (customer_id,)).fetchone()[0]

  if not car_active or car_active == None:
      if not lease_active or lease_active == None:
          c.execute("UPDATE CARS SET IS_LEASED = 1 WHERE ID = ?",
              (car_id,))
          c.execute("""INSERT INTO LEASES (CAR_ID, LEASE_START, LEASE_EXPIRE, CUSTOMER, IS_ACTIVE) 
              VALUES (?, ?, ?, ?, ?)""", (car_id, lease_start, lease_expire, customer_id, 1,))

          lease_id = c.execute("SELECT ID FROM LEASES").fetchall()
          lease_id = ''.join(c for c in lease_id if c not in '[](),')
          c.execute("UPDATE CUSTOMERS SET LEASE_ID = ? WHERE ID = ?",
              (lease_id, customer_id,))

          c.execute("UPDATE CUSTOMERS SET ACTIVE_LEASE = 1 WHERE ID = ?",
              (customer_id,))

          conn.commit()
  else:
      return 2

--如果你想自己尝试，这里是db_setup.py文件：

import sqlite3 as lite

con = lite.connect('db/test.db')

with con:
    cur = con.cursor()
    cur.execute("""CREATE TABLE CUSTOMERS(
        ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
        NAME TEXT, 
        ADDRESS CHAR(50), 
        EMAIL_ADDRESS CHAR(50),
        PHONE_NUMBER CHAR(50),
        LEASE_ID TEXT,
        ACTIVE_LEASE INT)
        """)
    cur.execute("""CREATE TABLE CARS(
        ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,  
        CAR TEXT, 
        CAR_COLOR TEXT, 
        CAR_LICENSE TEXT,
        LAST_LEASED_BY INT,
        IS_LEASED INT)""")
    cur.execute("""CREATE TABLE LEASES(
        ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        CAR_ID INT, 
        LEASE_START TEXT, 
        LEASE_EXPIRE TEXT,
        CUSTOMER INT,
        IS_ACTIVE INT)""")
    cur.execute("""INSERT INTO CUSTOMERS (NAME, ADDRESS, EMAIL_ADDRESS, PHONE_NUMBER) VALUES 
        ("Rooney Paul","P.O. Box 569, 4862 Urna St.","at.velit@per.com","(01112) 129811"),
        ("Wallace Armstrong","7690 Est. Rd.","Nunc.lectus@dapibusquam.co.uk","070 7854 9488"),
        ("Erich Robertson","Ap #431-4682 Adipiscing St.","eu@morbi.ca","(012777) 33360"),
        ("Kennan Peterson","P.O. Box 438, 4390 Dapibus Road","Lorem@mollisvitae.org","0800 822615"),
        ("Brock Cantu","P.O. Box 223, 1483 Nunc St.","nec@Seddiam.net","055 0209 8442"),
        ("Fulton Buchanan","4090 Posuere Avenue","nunc@fringillaeuismodenim.co.uk","07624 328923"),
        ("Duncan Pruitt","P.O. Box 567, 6726 Et, Rd.","In.tincidunt.congue@nullavulputatedui.net","07624 343171"),
        ("Simon Horton","532-1265 Arcu Rd.","eros.non.enim@quis.ca","0800 398826"),
        ("Samuel Dunlap","905-1619 Felis. Av.","Vivamus.nibh@mollisPhaselluslibero.com","076 9655 0399"),
        ("James Rosa","530-7282 Tellus Av.","imperdiet.nec@liberolacus.org","055 3901 7569");
        """)
    cur.execute("""INSERT INTO CARS (CAR, CAR_COLOR, CAR_LICENSE) VALUES 
        ("BMW", "RED", "AAA 111"), 
        ("AUDI", "BLUE", "BBB 111"),
        ("MERCEDES", "BLACK", "CCC 111"),
        ("VOLVO", "WHITE", "CCC 111"),
        ("PORCHE", "YELLOW", "DDD 111"),
        ("FORD", "GREEN", "EEE 111"),
        ("DODGE", "GREY", "FFF 111"),
        ("HONDA", "DARK-RED", "GGG 111"),
        ("CADILLAC", "WHITE", "HHH 111"),
        ("ALFA ROMEO", "PURPLE", "JJJ 111")
        """)

我很高兴回答你的任何问题！

谢谢!

Answer 1

我真的不喜欢把db连接保持为全局的，并且总是打开

conn = sqlite3.connect("db/test.db")
c = conn.cursor()

相反，您可以创建一个简单的上下文管理器，它将帮助您完成这一任务，并使您的代码更漂亮:从contextlib导入contextmanager。

@contextmanager
def get_cursor(db_name='db/test.db'):
    conn = sqlite3.connect(db_name)
    yield conn.cursor()
    try:
        conn.commit()
    finally:
        conn.close()

因此，无论何时需要游标，您都可以这样做：

with get_cursor() as c:
    c.execute(...)
    c.execute(...)

一旦它退出with statement，它将提交并关闭连接。

这里的另一个问题是变量名称c不是最佳实践，更好的用户是全名cursor，因此其他人更容易理解这里发生的事情。

使current_datetime = datetime.datetime.now()全局化也不是一个好的选择，相反，您可以在每次调用add_lease时初始化它。

因此，最终您的代码应该如下所示：

import datetime
import sqlite3
from contextlib import contextmanager


@contextmanager
def get_cursor(db_name='db/test.db'):
    conn = sqlite3.connect(db_name)
    yield conn.cursor()
    try:
        conn.commit()
    finally:
        conn.close()


def add_lease(customer_id, car_id, lease_expire):
    """
    Adds lease to customers account and
    removes the car from available cars list.
    """

    current_datetime = datetime.datetime.now()
    lease_start = current_datetime.strftime("%Y-%m-%d")
    with get_cursor() as cursor:
        car_active = cursor.execute("SELECT IS_LEASED FROM CARS WHERE ID=?",
                                    (car_id,)).fetchone()[0]
        lease_active = cursor.execute("SELECT ACTIVE_LEASE FROM CUSTOMERS WHERE ID=?",
                                      (customer_id,)).fetchone()[0]

    if not car_active and not lease_active:
        with get_cursor() as cursor:
            cursor.execute("UPDATE CARS SET IS_LEASED = 1 WHERE ID = ?",
                           (car_id,))
            cursor.execute("INSERT INTO LEASES (CAR_ID, LEASE_START, LEASE_EXPIRE, CUSTOMER, IS_ACTIVE) "
                           "VALUES (?, ?, ?, ?, ?)", (car_id, lease_start, lease_expire, customer_id, 1))

            lease_id = cursor.execute("SELECT ID FROM LEASES").fetchall()
            lease_id = ''.join(char for char in lease_id if char not in '[](),')
            cursor.execute("UPDATE CUSTOMERS SET LEASE_ID = ? WHERE ID = ?",
                           (lease_id, customer_id))

            cursor.execute("UPDATE CUSTOMERS SET ACTIVE_LEASE = 1 WHERE ID = ?",
                           (customer_id,))
    else:
        return 2

我没有得到这个神奇的数字2，所以我没有触及这个部分。