时间戳

首页 > 解决方案 > 尝试使用 sqlobject 和 python 3 添加数据和查询 mysql 数据库

mysql - 尝试使用 sqlobject 和 python 3 添加数据和查询 mysql 数据库

问题描述

我的代码引发了一个异常，如下所示。我很确定问题出在我对表关系的定义上。我尝试了多种会产生不同错误的替代方法:-(

我有一个可以有一个或多个 MacAddresses 的许可证，如下所示：

class MacAddress(sqlobject.SQLObject):
    # noinspection PyPep8Naming,PyClassHasNoInit
    class sqlmeta:
        table = "mac_address_table"

    mac_address = sqlobject.StringCol(unique=True, length=50)
    license = sqlobject.ForeignKey('License', default=None)


class License(sqlobject.SQLObject):
    # noinspection PyPep8Naming,PyClassHasNoInit
    class sqlmeta:
        table = "license_table"

    user_name = sqlobject.StringCol(unique=True, length=50)
    mac_addresses = sqlobject.MultipleJoin('MacAddress')

    @staticmethod
    def create(user_name, mac_address):
        lic = License(user_name=user_name)
        mac = MacAddress.get(mac_address)
        lic.add_mac_address(mac)
        return lic

    def add_mac_address(self, mac_address):
        mac_address.license = self

    @staticmethod
    def is_valid(user_name, mac_address):
        query = License.selectBy(user_name=user_name)
        found = False
        if query.count() != 0:
            lic = query.getOne()
            # Here is where I am having a problem:
            for mac in lic.mac_addresses:
                if mac.mac_address == mac_address:
                    found = True
                    break
            if found:
                return True
        return False

当我尝试像这样测试许可证时：

lic.is_valid(user_name="Fred", mac_address="1234"),

我在这一行得到一个错误：对于 lic.mac_addresses 中的 mac：

我收到以下错误：

sqlobject.dberrors.OperationalError: Unknown column 'license_table_id' in 'where clause'

我在数据库中有以下表：

mac_address_table：

+----------------------+---------+------+-----+---------+-------+
| Field                | Type    | Null | Key | Default | Extra |
+----------------------+---------+------+-----+---------+-------+
| license_table_id     | int(11) | NO   |     | NULL    |       |
| mac_address_table_id | int(11) | NO   |     | NULL    |       |
+----------------------+---------+------+-----+---------+-------+

许可证表：

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| user_name | varchar(50) | YES  | UNI | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

mac_address_table_license_table：

+----------------------+---------+------+-----+---------+-------+
| Field                | Type    | Null | Key | Default | Extra |
+----------------------+---------+------+-----+---------+-------+
| license_table_id     | int(11) | NO   |     | NULL    |       |
| mac_address_table_id | int(11) | NO   |     | NULL    |       |
+----------------------+---------+------+-----+---------+-------+

以下是数据库的内容：

    select * from license_table; select * from mac_address_table; select * from license_table_mac_address_table;
+----+-----------+----------+---------------------+
| id | user_name | password | created             |
+----+-----------+----------+---------------------+
|  1 | Fred      | xxx      | 2019-04-29 17:59:47 |
+----+-----------+----------+---------------------+
    1 row in set (0.00 sec)


+----+-------------+------------+
| id | mac_address | license_id |
+----+-------------+------------+
|  1 | 1234        |          1 |
+----+-------------+------------+
1 row in set (0.00 sec)

+------------------+----------------------+
| license_table_id | mac_address_table_id |
+------------------+----------------------+
|                1 |                    1 |
+------------------+----------------------+

标签： mysqlpython-3.xsqlobject

解决方案

我似乎已经修复了它：

class MacAddress(sqlobject.SQLObject):
    # noinspection PyPep8Naming,PyClassHasNoInit
    class sqlmeta:
        table = "mac_address_table"

    value = sqlobject.StringCol(unique=True, length=50)
    license = sqlobject.ForeignKey('License', default=None)


class License(sqlobject.SQLObject):
    # noinspection PyPep8Naming,PyClassHasNoInit
    class sqlmeta:
        table = "license_table"

    user_name = sqlobject.StringCol(unique=True, length=50)
    mac_addresses = sqlobject.MultipleJoin('MacAddress', joinColumn='license_id')

    @staticmethod
    def create(user_name, mac_address):
        lic = License(user_name=user_name)
        lic.add_mac_address(mac_address=mac_address)
        return lic

    def add_mac_address(self, mac_address):
        mac_address.license = self

    @staticmethod
    def is_valid(user_name, mac_str):
        query = License.selectBy(user_name=user_name)
        found = False
        if query.count() != 0:
            lic = query.getOne()
            for mac in lic.mac_addresses:
                if mac_str == mac.value:
                    found = True
                    break
            if found:
                return True
        return False

现在工作正常：

def test_license():
    mac_address = MacAddress(value="123")
    lic = License.create(user_name="Fred", mac_address=mac_address)
    mac_address = MacAddress(value="2468")
    lic.add_mac_address(mac_address=mac_address)
    print(lic.is_valid(user_name="Fred", mac_str=mac_address.value))
    print(lic.is_valid(user_name="Fred", mac_str="3456"))

我不知道我做了什么来纠正这个问题，除非它是愚蠢的，比如在 MacAddress 中覆盖 SQLObject get 方法......谢谢你的帮助。

推荐阅读