Добавить целый столбец со значениями в таблицу SQL
У меня есть таблица SQL, и я сделал ее фреймворком данных с помощью Python.
Затем я вычислил новые значения для нового столбца.
Как вы знаете, легко добавить новые столбцы со значениями в фрейм данных, если длина фрейма данных и добавленного столбца одинакова.
Теперь я могу взять весь фрейм данных и создать новую таблицу MySQL.
df.to_sql(name=key+".1", con=engine , if_exists ='fail', index=False)
Но мне это кажется слишком сложным, чтобы сделать это таким образом.
Поэтому я попытался вставить значения в новую таблицу, используя
# for index,x in enumerate(df["Time"]):
# try:
# mycursor = mydb.cursor()
# mycursor.execute("UPDATE "+key +" SET DhE = "+str(df["DhE"][index])+" WHERE Time='"+str(x)+"'")
# mydb.commit()
# except:
# print("UPDATE "+key+" SET DhE = "+str(df["DhE"][index])+" WHERE Time = "+str(x) +"****error ")
Это непригодно для использования.
Предложение WHERE сравнивает тип данных Date() с первичным ключом таблицы, что было плохим выбором. Не делайте этого, это уникальный ключ, но его нельзя использовать на практике.
По умолчанию предложение where слишком медленное для длины таблицы 731911, чтобы добавлять значения для всего столбца.
Мой вывод и мой вопрос Нужно ли мне записывать в мою базу данных совершенно новую таблицу или есть способ просто добавить столбец (со значениями!), который имеет ту же длину, что и таблица, в таблицу MySQL?
например, используя
INSER INTO XY.new_Column VALUES "whole new column values"
Ответы 2
То, как вы вставляете значения в таблицу MySQL, очень неэффективно. Он перебирает каждую строку и обновляет ее одну за другой. Это приводит к очень большому количеству отдельных запросов к базе данных, что очень медленно.
- Подключиться к вашей базе данных.
- Используйте
ALTER TABLE, чтобы добавить новый столбец. - Массовая вставка значений с помощью
INSERT INTO ... ON DUPLICATE KEY UPDATE. - Зафиксируйте изменения.
В Python это должно работать.
import pymysql
# Connect to database
connection = pymysql.connect(host='localhost', user='username', password='password', database='mydb')
cursor = connection.cursor()
# Add new column
cursor.execute("ALTER TABLE your_table ADD new_column_name datatype")
# Bulk insert values (assume values_list contains (time, value) tuples)
query = "INSERT INTO your_table (Time, new_column_name) VALUES (%s, %s) ON DUPLICATE KEY UPDATE new_column_name=VALUES(new_column_name)"
cursor.executemany(query, values_list)
# Commit and close
connection.commit()
cursor.close()
connection.close()
Замените your_table на имя вашей таблицы, new_column_name на имя столбца и datatype на тип данных столбца. Кроме того, используйте values_list в качестве списка кортежей со временем и значением для нового столбца.
Большое спасибо за ваш ответ.
Я получил предупреждение,
эта функция VALUES устарела и будет удалена в будущем выпуске. Используйте псевдоним (INSERT INTO... VALUES (...) AS псевдоним) и замените VALUES(col) в предложении ON DUPLICATE KEY UPDATE на alias.col.
Я изменил его на:
query = "INSERT INTO your_table (Time, new_column_name) VALUES (%s, %s) AS x DUPLICATE KEY UPDATE new_column_name=x.new_column_name)"
Кроме того, столбец «время» должен быть первичным ключом, поэтому я установил
ALTER your_table
MODIFY COLUMN TIME datetime NOT NULL
и
ALTER TABLE your_table ADD PRIMARY KEY (Time)
После этого sql может получить ключевую ошибку, и DUPLICATE KEY UPDATE может творить чудеса.
Теперь я пойду сравнивать это с добавлением всей новой таблицы :)
Большое спасибо за ваш ответ.