我有一个将数据保存到postgres数据库的大型脚本。请告诉我如何删除[' and ']。我需要在保存存储中的数据时不使用这些字符
total.to_csv("C:/Users/.../total_type19.csv", index = False, sep =';')
conn5 = psycopg2.connect(dbname='', user='',
password='', host='', port = '')
cursor5 = conn5.cursor()
with open("C:/Users/.../total_type19.csv", "r",encoding = 'utf-8') as file:
reader = csv.reader(file, delimiter = ";")
header = next(reader )
# print(f"HEADER {header}")
counter = 1
for row in reader:
# print(row)
test_id =row[0]
test_id = ''.join([i for i in test_id if i.isdigit()])
if test_id == '':
counter +=1
test_id = counter
else:
test_id = int(test_id)
# print(test_id)
cursor5.execute(
'INSERT INTO interaction_fillword (test_id, data_size, data_words_selection, data_matrix, data_colors, data_answers) VALUES(%s,%s, %s, %s, %s, %s)',
(test_id, row[1], row[2], row[3], row[4], row[5]))
conn5.commit()
这是数据库中数据的外观
id test_id data_size data_words_selection data_matrix data_colors data_answers
0 224 312229 ['12'] ['["v","b","c","c","a","h","i","e","r","s","f"... ['[{from:[3,4],to:[7,4],color:2},{from:[3,6],t... ['"#ff0000","#00fe00","#0000ff","#d2ea9a","#40... ['"place","cartable","gomme","bureau","bibliot...
1 225 400606 ['12'] ['["v","b","c","c","a","h","i","e","r","s","f"... ['[{from:[3,4],to:[7,4],color:2},{from:[3,6],t... ['"#ff0000","#00fe00","#0000ff","#d2ea9a","#40... ['"place","cartable","gomme","bureau","bibliot...
2 226 410051 ['12'] ['["v","b","c","c","a","h","i","e","r","s","f"... ['[{from:[3,4],to:[7,4],color:2},{from:[3,6],t... ['"#ff0000","#00fe00","#0000ff","#d2ea9a","#40... ['"place","cartable","gomme","bureau","bibliot...
请告诉我如何去掉这些字符,可能是写入 Dataframe 时解析后出现的问题。必须去掉第一个['和'],后面的括号必须保留。
这是我希望数据看起来像什么的一个例子
id test_id data_size data_words_selection data_matrix data_colors data_answers
0 224 312229 12 ["v","b","c","c","a","h","i","e","r","s","f"... [{from:[3,4],to:[7,4],color:2},{from:[3,6],t... "#ff0000","#00fe00","#0000ff","#d2ea9a","#40... "place","cartable","gomme","bureau","bibliot...
1 225 400606 12 ["v","b","c","c","a","h","i","e","r","s","f"... [{from:[3,4],to:[7,4],color:2},{from:[3,6],t... "#ff0000","#00fe00","#0000ff","#d2ea9a","#40... "place","cartable","gomme","bureau","bibliot...
2 226 410051 12 ["v","b","c","c","a","h","i","e","r","s","f"... [{from:[3,4],to:[7,4],color:2},{from:[3,6],t... "#ff0000","#00fe00","#0000ff","#d2ea9a","#40... "place","cartable","gomme","bureau","bibliot...
替换没有帮助。
2条答案
按热度按时间eivgtgni1#
尝试使用特定列进行Map,并在lambada中添加lstrip和rstrip
希望这对你有用。
pnwntuvh2#
您可以使用dataframe模块。您可以使用df.replace()函数替换DataFrame中的特定字符,然后再将其写入PostgreSQL表。以下代码...删除所有列中字符“#”的所有示例
然后使用to_sql()函数将修改后的DataFrame写入PostgreSQL表