Database Basic

With the help of Navicat for Mysql, we can easily transform different data format into mysql. And when we install MySQL Workbench, it helps us to deal with any sql operation easily and conviently.

get column name from table

lite

select column_name from information_schema.COLUMNS where table_name='table name'

• 修改表名：alter table t_book rename to newname;
• 添加列：alter table 表名 add column 列名 varchar(30);
• 删除列：alter table 表名 drop column 列名;
• 修改列名MySQL： alter table bbb change oldname newname type;

python and mysql

python 3 利用 Pymysql 包来进行

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='passcode',
                             db='database name',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

mysql_cursor = connection.cursor()

sql_col_property="show columns from table_name"

mysql_cursor.execute(sql_col_property)

connection.commit()
connection.close()

利用列的位置选取数据库中的列

首先获取想要选取位置的列名

lite

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'your database schema' AND TABLE_NAME = 'the wanted table name' AND ORDINAL_POSITION = 2;

利用获取到的列名提取列*

lite

SELECT SPECIFICED_NAME FROM TABLE_NAME

选取列名中包含特定字符

lite

select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME like '%control%' order by TABLE_NAME

更新数据库中的数据

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config
 
 
def update_book(book_id, title):
    # read database configuration
    db_config = read_db_config()
 
    # prepare query and data
    query = """ UPDATE books
                SET title = %s
                WHERE id = %s """
 
    data = (title, book_id)
 
    try:
        conn = MySQLConnection(**db_config)
 
        # update book title
        cursor = conn.cursor()
        cursor.execute(query, data)
 
        # accept the changes
        conn.commit()
 
    except Error as error:
        print(error)
 
    finally:
        cursor.close()
        conn.close()
 
 
if __name__ == '__main__':
    update_book(37, 'The Giant on the Hill *** TEST ***')