插入列表作为新列MySQL

发布于 2025-02-07 22:27:48 字数 669 浏览 2 评论 0原文

我有一个字符串值new_values的列表，我想将其插入Companies表中的新列中的新列，我的mysql数据库。由于我有数百行，我无法使用？我遇到的语法。

import MySQLdb

cursor = db.cursor()
cursor.execute("INSERT INTO companies ....")

lst_to_add = ["name1", "name2", "name3"]

db.commit()
db.close()

但是，我不确定应该在列表中传递什么查询，以及在查询中包括新的列名称（例如：“ newcol”）的正确语法。

编辑：当前表：

id     originalName     
1      Hannah           
2      Joi              
3      Kale

预期输出：

id     originalName     fakeName
1      Hannah           name1
2      Joi              name2
3      Kale             name3

原文

I have a list of string values new_values and I want to insert it as a new column in the companies tables my mysql database. Since I have hundreds of rows, I cannot manually type them using the ? syntax that I came across on SO.

import MySQLdb

cursor = db.cursor()
cursor.execute("INSERT INTO companies ....")

lst_to_add = ["name1", "name2", "name3"]

db.commit()
db.close()

However, i am not sure what query I should use to pass in my list and what's the correct syntax to include the new column name (eg: "newCol") into the query.

Edit:
current table:

id     originalName     
1      Hannah           
2      Joi              
3      Kale

expected output:

id     originalName     fakeName
1      Hannah           name1
2      Joi              name2
3      Kale             name3

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

_失温 2025-02-14 22:27:48

使用Alter Table添加新列。

cursor.execute("ALTER TABLE companies ADD COLUMN fakeName VARCHAR(100)")

然后循环浏览数据，更新每一行。您需要Python数据，以指示从原始名称到假名的映射。例如，您可以使用字典。

names_to_add = {
    "Hannah": "name1",
    "Joi": "name2",
    "Kale": "name3"
}

for oldname, newname in names_to_add.items():
    cursor.execute("UPDATE companies SET fakeName = %s WHERE originalName = %s", (newname, oldname))

Use ALTER TABLE to add the new column.

cursor.execute("ALTER TABLE companies ADD COLUMN fakeName VARCHAR(100)")

Then loop through the data, updating each row. You need Python data that indicates the mapping from original name to fake name. You can use a dictionary, for example.

names_to_add = {
    "Hannah": "name1",
    "Joi": "name2",
    "Kale": "name3"
}

for oldname, newname in names_to_add.items():
    cursor.execute("UPDATE companies SET fakeName = %s WHERE originalName = %s", (newname, oldname))

回复收藏 0 原文

~没有更多了~