python用pymysql向数据表插入数据

⼀、 加载库、封装函数import pandas as pdimport pymysql

def mycursor(db_name=None): '''连接数据库，创建游标'''

config = dict(zip(['host', 'user', 'port', 'password'],

['192.168.137.155', 'shanger', 3306, '0123'])) config.update(database=db_name) connection = pymysql.connect(**config) cursor = connection.cursor() return cursor

def use(db_name):

'''切换数据库，返回游标''' return mycursor(db_name)

def insert_many(table, data): '''向全部字段插⼊数据'''

val = '%s, ' * (len(data[0])-1) + '%s' sql = f'insert into {table} values ({val})' cursor.executemany(sql, data) cursor.connection.commit()

def query(sql):

'''以数据框形式返回查询据结果''' cursor.execute(sql)

data = cursor.fetchall() # 以元组形式返回查询数据 header = [t[0] for t in cursor.description]

df = pd.DataFrame(list(data), columns=header) # pd.DataFrem 对列表具有更好的兼容性 return df

def select_database(): '''查看当前数据库'''

sql = 'select database();' return query(sql)

def show_tables():

'''查看当前数据库中所有的表''' sql = 'show tables;' return query(sql)

def select_all_from(table): sql = f'select * from {table};' return query(sql)

⼆、插⼊数据

1、选则数据库、查看数据

2、插⼊数据data = [

('德岛', '100'), ('⾹川', '200'), ('爱媛', '150'), ('⾼知', '200'), ('福冈', '300'), ('佐贺', '100'), ('长崎', '200'), ('东京', '400'), ('群马', '50')]

insert_many('poptbl', data)select_all_from('poptbl')