我有变量类型的模式CSV文件，工作台无法理解，如何导入此CSV格式？

Question

"bio({""constraints"":[],""type"":""STRING"",""dataSize"":500})","birthday({""constraints"":[],""type"":""DATETIME""})","chattiness({""constraints"":[],""type"":""INT"",""defaultValue"":""1""})","firstName({""constraints"":[],""type"":""STRING"",""dataSize"":250})","gender({""constraints"":[],""type"":""BOOLEAN"",""dataSize"":500})","image({""constraints"":[],""type"":""FILE_REF"",""dataSize"":500,""defaultValue"":""""})","lastName({""constraints"":[],""type"":""STRING"",""dataSize"":500})","token({""constraints"":[],""type"":""STRING"",""dataSize"":500,""dynamicProps"":{""localData"":{}}})","objectId({""constraints"":[],""type"":""STRING_ID"",""dataSize"":36})","ownerId({""constraints"":[],""type"":""STRING"",""dataSize"":36})","created({""constraints"":[],""type"":""DATETIME""})","updated({""constraints"":[],""type"":""DATETIME""})","countryInformations({""constraints"":[],""type"":""RELATION"",""autoLoad"":false,""relatedTable"":""countryInformation""})","statistics({""constraints"":[""UQ""],""type"":""RELATION"",""autoLoad"":false,""relatedTable"":""userStatistics""})"

我从Backendless.com导出Person表时得到了这个CSV。我从未见过这样的CSV格式。这个结构有一个我可以在网上查到的合适的名字吗？有没有软件可以读取它并相应地将其导入mysqldb？

Answer 1

我将撤回对将其称为CSV文件的反对意见。这正是事实。这并不能完成全部工作，但也许这会给你一个开始。这假设文件中只有一行；如果在单独的文件中有多个表，则需要一个循环，而不是在cvs.reader上使用next()。

import json
import csv
row = next(csv.reader(open('x.txt')))
for f in row:
column,_,rest = f.partition('(')
data = json.loads(rest[:-1])
print(column, data)

现在可以打印data['type']和data['dataSize']。这里还有一些尴尬的事情。它显示了与其他表有关系的字段，但没有说明这些字段的类型。

Answer 2

基于Tim Roberts提供的代码，我编写了一个脚本，将Backendless CSV模式转换为MYSQL 8.0模式

import json
import csv
from os import listdir
import mysql.connector
host="host"
user="admin"
password="admin"
database="mydb"
# when true all tables with the same names will be dropped before a new ones are created
drop_existing_tables = False
mydb = mysql.connector.connect(
host=host,
user=user,
password=password,
database=database
)
path = "./"
filenames = listdir(path)
files = [ filename for filename in filenames if filename.endswith(".csv") ]
# select table
commands = "USE " + database + ";n"
#to be able to reference a table before it s even created.
commands += "SET foreign_key_checks = 0;n"
def constraints_to_string(constraints):
if len(constraints) == 0:
return ""
stringConstraints = ""
addIndex = False
for constraint in constraints:
if constraint == "NN":
constraint = "NOT NULL"
if constraint == "UQ":
constraint = "UNIQUE"
if constraint == "IDX":
addIndex = True
if not addIndex:
stringConstraints +=  " " + str(constraint)
if addIndex:
stringConstraints += ",n INDEX (`" + column + "`)"
return stringConstraints
def map_data_type(data, column, constraints):
dataType = data["type"]
if column == "ownerId":
dataType = "BINARY(16) " + constraints + ", " + 
" FOREIGN KEY (`" + column + "`) REFERENCES `users`(`objectId`)"
elif dataType == "STRING":
dataSize = data.get("dataSize")
dataType = "VARCHAR(" + str(dataSize) + ") " + constraints
elif dataType == "STRING_ID":
dataType = "BINARY(16) NOT NULL PRIMARY KEY " + constraints
elif dataType == "RELATION":
dataType = "BINARY(16) " + constraints + ", " + 
"FOREIGN KEY (`" + column + "`) REFERENCES `" + data["relatedTable"] + "`(`objectId`)"
elif dataType == "FILE_REF":
dataType = "VARCHAR(256) " + constraints
else:
dataType += " " + constraints
return dataType

# drop tables.
if drop_existing_tables:
for file in files:
tableName = file.rpartition('.')[0]
commands += "DROP TABLE IF EXISTS " + tableName + ";n"
commands += "n"
# generate SQL scripts from backendless schema csv file.
for file in files:
row = next(csv.reader(open(file)))
tableName = file.rpartition('.')[0]
variablesString = ""
first=True
for f in row:
column,_,rest = f.partition('(')
data = json.loads(rest[:-1])

constraints = constraints_to_string(data.get("constraints"))
dataType = map_data_type(data, column, constraints)

if not first:
variablesString +=  ", n"

variablesString += "`" + column + "`" + " " + dataType
first = False        
commands += "CREATE TABLE IF NOT EXISTS " + tableName + " (" + variablesString + ");nn"

print(commands)
mycursor = mydb.cursor()
results = mycursor.execute(commands, multi=True)
if input("are you sure you want to execute the SQL script above? (y/n)") != "y":
mydb.commit()