pymysql对数据库的基本操作

代码

import pymysql

conn = pymysql.connect(host='127.0.0.1', user='root', password='985211', db='my_data', port=3306)
cursor = conn.cursor()

# 1、插入一行数据
def insert_data(table, data):
    data = tuple(data)
    # table_name = 'vibration_feature_data'
    sql_insert = f"insert into {table} values{data};"
    cursor.execute(sql_insert)
    conn.commit()

# 2、查询表格内容并显示
def query_data(table):
    sql_query = f'select * from {table};'
    sql_flush = f'flush table {table};'
    cursor.execute(sql_flush)  # 每次查询前刷新表格，避免数据库不更新
    cursor.execute(sql_query)
    fetch_out = cursor.fetchall()  # fetch all：取来 全部
    for row in fetch_out:
        print(row)

# 3、获取数据库表格名字
def get_table_names(database):
    sql_get_table_names = f'show tables from {database};'
    cursor.execute(sql_get_table_names)
    fetch_out = cursor.fetchall()
    table_names = [name[0] for name in fetch_out]
    return table_names

# 4、快速创建多列相同字段类型表格
def create_table(table_name):
    try:
        columns_def = ', '.join([f'v{i+1} float' for i in range(100)])
        sql_create_table = f'create table if not exists {table_name} (id int primary key, {columns_def});'
        cursor.execute(sql_create_table)
        conn.commit()
    except Exception as e:
        print(f"Error occurred: {e}")

# 5、获取表格字段名（列名）
def get_column_names(table, database):
    sql_get_column_names = f"select column_name from information_schema.columns where table_name = '{table}' and table_schema = '{database}';"
    cursor.execute(sql_get_column_names)
    fetch_out = cursor.fetchall()
    column_names = [name[0] for name in fetch_out]
    return column_names