pandas Function reference ：https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html#pandas.read_csv

sql Sentence reference ：https://www.runoob.com/sql/sql-in.html

Code 1

import pandas as pd
from sqlalchemy import create_engine
select_env = 'dev' # 'prd'/'pre'/'qa'/'dev'
database_env = 'xx'
target_db_info = {
'user': 'xx',
'password': 'xx',
'host': 'xx',
'port': 6033,
'db': 'xx'
}
table_name = 'ai_chat_bot'
path = r'./qa_pair.csv'
data = pd.read_csv(path, sep='\t', encoding='utf-8')
data_add = list(range(len(data['question'])))
data.insert(0, 'id', data_add)
print(data)
# target connect
con_target = create_engine(
'mysql+pymysql://%(user)s:%(password)[email protected]%(host)s:%(port)d/%(db)s' %
target_db_info, encoding='utf-8')
data.to_sql(table_name, con_target, index=False, if_exists='replace')
print(' Successful import ...')

Code 2

In this way , stay mysql Create index directly

data.to_sql(table_name, con_target, index_label=['id'], if_exists='replace')

Code 3

Search for qualified , Read in once , And then convert to dict

SELECT id,answer FROM `ai_chat_bot` WHERE id in ('0', '1', '2', '3', '4')

1、 Add the title

2、 Determine the separator

3、python dataframe Add a new column

1、 Add a new column directly behind
Specify column name , And assign a value ：
data[‘addlist’]=[1,2]
2、 Add a new column at the specified position
use insert() function ,data.insert( Location , Name , The column value ), for example ：
data.insert(2,‘c’,’’)
3、 Generate new columns based on existing column calculations
dataframe Generate a new column based on the value of a column
df2[‘ Is it overdue? ’]=df2.apply(lambda x:0 if x. Due date >today_time else 1,axis=1)
df2[‘ Is it due 90 God ’]=(today_time - df2. Due date ).map(lambda x:1 if x.days >= 90 else 0)
4、 Merge existing columns into one column
Direct use + No ,
dataframe[“newColumn”] = dataframe[“age”].map(str) + dataframe[“phone”] + dataframe["address”]
among ,map(str) : Not string Column to string And then merge

4、mysql Create index

CREATE INDEX index_id ON ai_chat_bot (id);

In execution CREATE TABLE You can create an index when using the , It can also be used alone CREATE INDEX or ALTER TABLE To add an index to a table .

1、 Use CREATE INDEX establish , The grammar is as follows ：

CREATE INDEX indexName ON tableName (columnName(length));
For example, we are right ip_address This column creates a column with a length of 16 The index of ：

CREATE INDEX index_ip_addr ON t_user_action_log (ip_address(16));
2、 Use ALTER Sentence creation , The grammar is as follows ：

ALTER TABLE tableName ADD INDEX indexName(columnName);
ALTER Statement to create an index , The following provides an example of setting the index length ：

ALTER TABLE t_user_action_log ADD INDEX ip_address_idx (ip_address(16));

SHOW INDEX FROM t_user_action_log;
3、 Create an index when creating a table

CREATE TABLE tableName(
id INT NOT NULL,
columnName columnType,
INDEX [indexName] (columnName(length))
);