首页 > itarticle > mysql：获取表名、列名和全局搜索sql拼接

mysql：获取表名、列名和全局搜索sql拼接

admin 11月 14, 2020 0

  1. 查找表名
    1
    		 
    select table_name from information_schema.tables where table_schema='TSCHEMANAME' and table_type='base table'

其中SCHEMANAME为要查的数据库。

python获取表名：

1
2
3
4
5
6
7
 

def (cursor):
    cursor.execute(
        "SELECT table_name FROM information_schema.tables WHERE table_schema='SCHOOL' AND table_type='base table'")
    tables = cursor.fetchall()
    tables = [table[0] for table in tables]
    return tables

  1. 查找列名
    1
    		 
    SELECT column_name FROM information_schema.Columns WHERE table_name=%s AND table_schema=%s"

其中第一个变量为表名，第二个变量为数据库名。

python获取列名：

1
2
3
4
5
6
 
def get_table_columns(cursor, table_name, schema_name):
    cursor.execute("SELECT column_name FROM information_schema.Columns WHERE table_name=%s AND table_schema=%s",
                   (table_name, schema_name))
    columns = cursor.fetchall()
    columns = [column[0] for column in columns]
    return columns

  1. 拼接全局查找sql
    全局查找的sql样式：
    1
    		 
    select * from TABLE where `id` like "%COLUMN%" or `name` like "%COLUMN%" ...

python拼接：

1
2
3
4
5
6
7
8
9
10
 
def get_global_select_sql(table_name, word):
    sql = "select * from " + table_name + " where "
    columns = get_table_columns(cursor, table_name, schema_name)

    for column in columns:
        sql += "`" + column + "`" + " like '%" + word + "%' or "

    sql = sql[:-4]

    return sql

拼接好的sql如下所示：

1
 
select * from TABLE where `id` like '%WORD%' or `title` like '%WORD%' or `author` like '%WORD%' or `ISBN` like '%WORD%' or `publisher` like '%WORD%' or `publisherDateStr` like '%WORD%' or `publisherDate` like '%WORD%' or `description` like '%WORD%' or `price` like '%WORD%' or `eBookPrice` like '%WORD%' or `currency` like '%WORD%' or `rating` like '%WORD%' or `thumb` like '%WORD%' or `thumb1` like '%WORD%' or `fromlink` like '%WORD%' or `indexTimes` like '%WORD%' or `verify` like '%WORD%' or `isPub` like '%WORD%' or `memo` like '%WORD%' or `status` like '%WORD%' or `createUser` like '%WORD%' or `createDate` like '%WORD%' or `updateUser` like '%WORD%' or `updateDate` like '%WORD%'

微海报分享

近期文章

近期评论

标签

热门

文章归档

分类目录

功能