我正试图基于此字符串(这是一个SQL创建查询)在Python中构建列值和声明类型的列表:
creation_sql = '''CREATE TABLE "objects_users" (
"id" serial NOT NULL PRIMARY KEY,
"clientID" integer NOT NULL,
"email" varchar(100) NOT NULL,
"first_name" varchar(100) NOT NULL,
"last_name" varchar(100) NOT NULL,
"phone" varchar(100) NOT NULL,
"password" varchar(100) NOT NULL,
"type" varchar(100) NOT NULL,
"date_created" timestamp with time zone NOT NULL,
"active" varchar(100) NOT NULL);'''
我已经从表达式中解析出引用的值,如下所示:
def parse(creation_query):
quoted_values = re.compile('"[^"]*"')
values = quoted_values.findall(creation_sql)
column_names = []
for value in values:
column_names.append(value)
table_name = column_names[0]
column_names = [names.replace('"','') for names in column_names]
del column_names[0] # this will be table name
print(table_name)
print(column_names)
例如,我已经收集了
id
clientID
在列表中。现在我要申报,
serial NOT NULL PRIMARY KEY
和
integer NOT NULL
等在列表中。
下面是更新后的代码,它根据creation_sql语句为我提供了一个我所需的所有内容的字典:
import re
creation_sql = '''CREATE TABLE "objects_users" (
"id" serial NOT NULL PRIMARY KEY,
"clientID" integer NOT NULL,
"email" varchar(100) NOT NULL,
"first_name" varchar(100) NOT NULL,
"last_name" varchar(100) NOT NULL,
"phone" varchar(100) NOT NULL,
"password" varchar(100) NOT NULL,
"type" varchar(100) NOT NULL,
"date_created" timestamp with time zone NOT NULL,
"active" varchar(100) NOT NULL);'''
def parse(creation_query):
quoted_values = re.compile('"[^"]*"')
table_name = quoted_values.findall(creation_query)[0]
dictionary_values = dict(re.findall('"(.*?)"\s+(.+)[,)]', creation_query))
dictionary_values['table_name'] = table_name.replace('"','')
return dictionary_values
d = parse(creation_sql)
print(d.get('table_name'))
for key, item in d.items():
if key != 'table_name':
print(key + ":"+ item)
