So I have one or more databases with various tables of data.
所以我有一个或多个包含各种数据表的数据库。
I need to store some database specific properties. E.g. country of scraped website (the website has multiple countries).
我需要存储一些特定于数据库的属性。例如。被抓网站的国家(该网站有多个国家)。
What is the best way of doing this? I think it would be best to keep the properties in the database but (SQLite3) is the only way to have another table? (ID, Key, Value) However value may need to be string/text, integer, float/real. To get round this would I just convert them all to string with something like json.dumps/loads Or this there a better way?
这样做的最佳方式是什么?我认为最好将属性保留在数据库中,但(SQLite3)是另一个表的唯一方法吗? (ID,Key,Value)但是值可能需要是字符串/文本,整数,浮点数/实数。为了解决这个问题,我只需将它们全部转换为字符串,例如json.dumps / loads或者这样有更好的方法吗?
Thanks
2 个解决方案
#1
2
So, it sounds like you wish you had something like a dictionary associated with a database where you could store key-value information about how your application interacts with the database. There are a number of approaches to this; I'd recommend picking the one that works best for your application.
因此,听起来您希望您拥有类似于与数据库关联的字典,您可以存储有关应用程序如何与数据库交互的键值信息。有很多方法可以做到这一点;我建议选择最适合您应用的那个。
First, assuming you know the keys that you will use, a simple approach is to create a table that will have at most one row in it:
首先,假设您知道要使用的键,一个简单的方法是创建一个最多只有一行的表:
create table properties( country_of_website varchar(2) not null, time_downloaded timestamp not null);
I didn't include an id column. Depending on your ORM model, you may need one. You will need to make sure that the table never has more than one row. This approach is nice because you rely on the database's typing. As has been pointed out sqlite uses dynamic typing, but you may use another database some day. This approach works well if you have a small number of properties and you know what they are ahead of time.
我没有包含id列。根据您的ORM模型,您可能需要一个。您需要确保该表永远不会有多行。这种方法很好,因为你依赖于数据库的输入。正如已经指出的那样,sqlite使用动态类型,但有一天你可能会使用另一个数据库。如果您拥有少量属性并且您知道它们提前了什么,那么这种方法很有效。
On the other end of the spectrum you can do something like
在频谱的另一端你可以做类似的事情
create table properties(key varchar(20) primary key, value varchar(256));
There you have a table that contains a key and a string representation of the value. The advantage is that it is fairly dynamic. The disadvantage is that you will be using the string representation of the property. That's not much of a disadvantage for sqlite though, but with other databases you can lose the advantage of strong typing to detect programming errors.
你有一个表,其中包含一个键和值的字符串表示。优点是它相当动态。缺点是您将使用属性的字符串表示形式。这对sqlite来说并不是一个缺点,但是对于其他数据库,你可能会失去强类型的优势来检测编程错误。
A third approach is to have a table per type.
第三种方法是每种类型有一个表。
create table float_properties(key varchar(20) primary key, value float not null);
And also create a string_properties table etc. I recommend against this approach with sqlite because of the dynamic typing. This third approach is the most complex and I'd only use it if I needed strong typing and couldn't use the first approach.
并且还创建了一个string_properties表等。由于动态类型的原因,我推荐使用sqlite这种方法。第三种方法是最复杂的,如果我需要强类型并且不能使用第一种方法,我只会使用它。
#2
0
SQLite uses dynamic typing, so you can just store the property values directly in a column.
SQLite使用动态类型,因此您可以直接将属性值存储在列中。
#1
2
So, it sounds like you wish you had something like a dictionary associated with a database where you could store key-value information about how your application interacts with the database. There are a number of approaches to this; I'd recommend picking the one that works best for your application.
因此,听起来您希望您拥有类似于与数据库关联的字典,您可以存储有关应用程序如何与数据库交互的键值信息。有很多方法可以做到这一点;我建议选择最适合您应用的那个。
First, assuming you know the keys that you will use, a simple approach is to create a table that will have at most one row in it:
首先,假设您知道要使用的键,一个简单的方法是创建一个最多只有一行的表:
create table properties( country_of_website varchar(2) not null, time_downloaded timestamp not null);
I didn't include an id column. Depending on your ORM model, you may need one. You will need to make sure that the table never has more than one row. This approach is nice because you rely on the database's typing. As has been pointed out sqlite uses dynamic typing, but you may use another database some day. This approach works well if you have a small number of properties and you know what they are ahead of time.
我没有包含id列。根据您的ORM模型,您可能需要一个。您需要确保该表永远不会有多行。这种方法很好,因为你依赖于数据库的输入。正如已经指出的那样,sqlite使用动态类型,但有一天你可能会使用另一个数据库。如果您拥有少量属性并且您知道它们提前了什么,那么这种方法很有效。
On the other end of the spectrum you can do something like
在频谱的另一端你可以做类似的事情
create table properties(key varchar(20) primary key, value varchar(256));
There you have a table that contains a key and a string representation of the value. The advantage is that it is fairly dynamic. The disadvantage is that you will be using the string representation of the property. That's not much of a disadvantage for sqlite though, but with other databases you can lose the advantage of strong typing to detect programming errors.
你有一个表,其中包含一个键和值的字符串表示。优点是它相当动态。缺点是您将使用属性的字符串表示形式。这对sqlite来说并不是一个缺点,但是对于其他数据库,你可能会失去强类型的优势来检测编程错误。
A third approach is to have a table per type.
第三种方法是每种类型有一个表。
create table float_properties(key varchar(20) primary key, value float not null);
And also create a string_properties table etc. I recommend against this approach with sqlite because of the dynamic typing. This third approach is the most complex and I'd only use it if I needed strong typing and couldn't use the first approach.
并且还创建了一个string_properties表等。由于动态类型的原因,我推荐使用sqlite这种方法。第三种方法是最复杂的,如果我需要强类型并且不能使用第一种方法,我只会使用它。
#2
0
SQLite uses dynamic typing, so you can just store the property values directly in a column.
SQLite使用动态类型,因此您可以直接将属性值存储在列中。