从Python中的字符串推断适当的数据库类型声明
我正在用Python字典创建一些Postgres表格，这些字典里的{'key': 'value'}对就对应了表格的列名和字段值。这些字典是从.dbf文件生成的——我现在把.dbf文件的内容传给一个脚本，这个脚本会返回一个字典列表，像这样：
{'Warngentyp': '', 'Lon': '-81.67170', 'Zwatch_war': '0', 'State':...
目前，我把这些数据放进一个sqlite数据库里，没有指定数据类型，然后再把它导出到一个.sql文件，手动编辑一下结构，再导入到Postgres里。
我希望能够自动推断出正确的数据类型，基本上就是遍历一个字符串列表，比如['0', '3', '5']、['ga', 'ca', 'tn']或者['-81.009', '135.444', '-80.000']，然后生成像'int'、'varchar(2)'、'float'这样的类型。（我对Python、Postgres或SQLite的工具都很满意。）
有没有这样的包，或者简单的方法可以实现这个呢？
4 个回答
谢谢大家的帮助，这个更新有点长，我来分享一下我是怎么把答案结合起来的。我开始时有一个字典列表，像这样，是从一个dbf文件生成的：
dbf_list = [{'Warngentyp': '', 'Lon': '-81.67170', 'Zwatch_war': '0', 'State':...
然后我写了一个函数，可以每列返回1000个值，用来测试最合适的数据库类型声明：{'列名':['样本', '值', '列表'], 'col2':['1','2','3','4'...，大概是这样的：
def sample_fields(dicts_, number=1000): #dicts_ would be dbf_list from above
sample = dict([[item, []] for item in dicts_[1]])
for dict_ in dicts_[:number]:
for col_ in dict_:
sample[col_].append(dict_[col_])
return sample
接着，我结合了Unknown和jacob的方法：varchar是一个不错的默认选项，浮点数和整数基本上可以满足其他所有需求，all的使用简单明了，速度也快：
def find_typedefs(sample_dict): #arg is output of previous function
defs_ = {}
for key in sample_dict:
defs_[key] = 'varchar(255)'
try:
if all([int(value) for value in sample_dict[key]]):
defs_[key] = 'int'
except:
try:
if all([float(value) for value in sample_dict[key]]):
defs_[key] = 'float'
except:
continue
return defs_
然后把返回的字典格式化成一个create table语句，遍历原始大列表中的值，把它们输入到数据库中。这个方法效果很好，我现在省略了中间的sqlite步骤，再次感谢大家。
给John Machin的更新：我正在使用与PostGIS一起分发的shp2pgsql库。它会创建像下面这样的模式，数据源类似于这个：
Column | Type |
------------+-----------------------+-
gid | integer |
st_fips | character varying(7) |
sfips | character varying(5) |
county_fip | character varying(12) |
cfips | character varying(6) |
pl_fips | character varying(7) |
id | character varying(7) |
elevation | character varying(11) |
pop_1990 | integer |
population | character varying(12) |
name | character varying(32) |
st | character varying(12) |
state | character varying(16) |
warngenlev | character varying(13) |
warngentyp | character varying(13) |
watch_warn | character varying(14) |
zwatch_war | bigint |
prog_disc | bigint |
zprog_disc | bigint |
comboflag | bigint |
land_water | character varying(13) |
recnum | integer |
lon | numeric |
lat | numeric |
the_geom | geometry |
那里有些东西可能是错误的——Fips是联邦信息处理标准，它应该是一个在0到大约100,000之间的整数。人口、海拔等等。也许我有一个更具体于postgres的问题，我不介意丢失少量数据，或者把错误的数据放到一个表里，试着改变比如人口字段的类型。dbf的类型检查有多严格？例如，我看到根据shp2pgsql，人口字段是varchar(12)。是否有可能有一小部分人口字段包含像'2,445 Est.'这样的内容？如果我按照这个问题中提到的方法，使用前一千条记录，我得到的模式是这样的：
Column | Type |
------------+------------------------+-
warngentyp | character varying(255) |
lon | double precision |
zwatch_war | character varying(255) |
state | character varying(255) |
recnum | character varying(255) |
pop_1990 | integer |
land_water | character varying(255) |
elevation | integer |
prog_disc | integer |
comboflag | character varying(255) |
sfips | integer |
zprog_disc | integer |
pl_fips | integer |
county_fip | integer |
population | integer |
watch_warn | integer |
name | character varying(255) |
st | character varying(255) |
lat | double precision |
st_fips | integer |
cfips | integer |
id | integer |
warngenlev | integer |
另一方面，如果我检查所有值在all(['list', 'of', 'everything'...])中，我得到的模式更像第一个。我可以接受这里有一点数据丢失——如果某个城镇的条目是错误的，而这并不会显著影响人口数据等等。
我只是使用一个叫dbview的旧包，把dbf文件传入这些脚本——我并不想映射任何格式的本地功能。我原以为shp2pgsql在这方面已经处理好了简单的部分。对于dbview或其他包的建议都欢迎——虽然在其他情况下我可能不会使用dbf文件，仍然需要找到最佳类型。我还打算问一个关于postgresql的问题，看看能否在那个层面找到解决方案。
不要使用eval这个东西。如果有人插入了坏代码，可能会搞坏你的数据库或者服务器。
相反，应该使用这些
def isFloat(s):
try:
float(s)
return True
except (ValueError, TypeError), e:
return False
str.isdigit()
其他的都可以用varchar类型。
你不需要去推测类型声明！！！
你可以直接从 .dbf 文件中获取你想要的信息。每一列都有一个名字、一个类型代码（C=字符，N=数字，D=日期（格式为yyyymmdd），L=逻辑值（真/假），如果文件来自Foxpro，还有更多类型）、一个长度（如果适用），以及小数位数（对于类型N）。
你用来提取 .dbf 文件数据的软件需要利用这些信息，把每一条数据转换成合适的Python数据类型。
字典？为什么要用字典？只需稍微改动一下，那款软件就可以根据这些列的定义生成一个CREATE TABLE语句，并为每一行数据生成一个INSERT语句。
我猜你在使用一些已经发布的Python DBF读取模块。任何一个都应该具备你需要的功能：打开 .dbf 文件，获取列名，获取列类型等信息，获取每一行数据。如果你对正在使用的模块不满意，可以和我聊聊；我有一个未发布的模块，读取DBF时结合了其他模块的优点，避免了缺点，速度也很快，能处理所有Visual Foxpro的数据类型和_NullFlags伪列，还能处理备忘录等。
希望这些对你有帮助
========= 补充说明： 当我说你不需要推测类型时，你并没有明确说明你有一些C类型的字段里包含数字。
FIPS字段：有些字段前面有零，有些没有。如果你打算使用它们，就会遇到'012' != '12' != 12的问题。我建议去掉前面的零，把它们放在整数列中，如果真的需要，可以在报告中恢复前导零。为什么州和县的FIPS各有两个？
人口：在样本文件中，几乎所有都是整数。四个像40552.0000这样的，合理数量是空白的。你似乎认为人口很重要，并问“是否有可能一些小比例的人口字段包含……？”数据中任何事情都是可能的。不要猜测，去调查！我强烈建议你按人口顺序对数据进行排序，亲自查看；你会发现同一个州的多个地方共享相同的人口数。例如，纽约州有35个地方的人口都标记为8,008,278，分布在6个县中。29个地方的PL_FIPS值是51000；5个是5100——看起来像是尾零问题 :-(
决定使用浮点数还是整数的小技巧：先试试 anum = float(chars)；如果成功，再检查 int(anum) == anum。
ID：很棒的“唯一ID”；有59个案例不是整数——其中几个在加拿大（网站上说“美国城市”；这是否是某个未解决的边界争议的产物？），一些包含“Number”这个词，还有一些是空的。
简单易做的事情：我本以为推断人口实际上是整数是非常简单的事情 :-)
如果你在做 all([int(value) ... 逻辑时，有一个严重的缺陷：
>>> all([int(value) for value in "0 1 2 3 4 5 6 7 8 9".split()])
False
>>> all([int(value) for value in "1 2 3 4 5 6 7 8 9".split()])
True
>>>
你显然认为你在测试所有字符串是否可以转换为整数，但你加了个条件“并且都是非零的”。几行后浮点数也是如此。
换句话说，如果有一个零值，你就声明这一列不是整数。即使修复了这个问题，如果有一个空值，你就称其为varchar。我建议你：统计有多少个空值（在标准化空格后，包括NBSP），有多少个符合整数条件，有多少个非整数且非空的符合浮点数条件，还有多少个是“其他”。检查这些“其他”的值；决定是拒绝还是修复；重复这个过程直到满意为止 :-)
希望这些对你有帮助。