python Gets an instance of the table name and table field name of the sqlite3 database

In Python, field names are often used when operating sqlite3 database. However, using select statement for sqlite can not return dictionary data set with field names like MySql and other database 1. Especially for an unfamiliar sqlite database, if you need to look at it with the help of tools when writing code, it is actually a little sorry for python.

A list of all the table and field names in an sqlite database can be easily obtained with the following two pieces of code:

# python  Get sqlite3 Database mydb.db Table name and table field name in 
 
import sqlite3
conn=sqlite3.connect('mydb.db')
cu=conn.cursor()
 
# Gets the table name and saves it in the tab_name List 
cu.execute("select name from sqlite_master where type='table'")
tab_name=cu.fetchall()
tab_name=[line[0] for line in tab_name]
 
# Gets the column name (field name) of the table and saves it in the col_names List , The field name set for each table is 1 Tuples 
col_names=[]
for line in tab_name:
  cu.execute('pragma table_info({})'.format(line))
  col_name=cu.fetchall()
  col_name=[x[1] for x in col_name]
  col_names.append(col_name)
  col_name=tuple(col_name)
 
# The reason why it is saved as a tuple, 1 Is to avoid misoperation to modify field names, 2 Is a tuple used skillfully to convert strings, which can 
# Directly used for SQL Adj. insert Statement. For example, the following code can get the 1 A collection of parenthesized field names for tables: 
'''
  sql_col_name=str(col_names[0]).replace('\'','')
'''
 
      

Next, do something with the automatically obtained field names.

For example, the table data of my 1 sqlite database is imported from the Excel table, and the carriage return character incorrectly entered in the Excel table is also imported by 1, and the character '\ n' will be displayed in the sqlite table. I want to remove this character in all fields. I realized it through the following code.

# Delete the 1 Carriage returns in all text fields in the table (Note: Carriage returns stored in the database are taken out 
# It will be displayed as the original character '\n' ) 
cu.execute('select * from '+ tab_name[0])
cdset=cu.fetchall()
cdset=[list(line) for line in cdset] # Turn the tuples of the result set into lists before modifying them. 
for line in cdset:
  for x in range(len(line)):
  # Note: As For Loop count variables cannot be modified in the loop body, and cannot be used here x in line Substitute 
    if type(line[x])==str and (r'\n' in line[x]):  # You can only modify character data 
      line[x]=line[x].replace(r'\n','')
      sql="update {bm} set {cm}='{vm}' where {cm2}={vm2}".format(
        bm=tab_name[0],cm=col_names[0][x],vm=line[x],cm2=col_names
        [0][0],vm2=line[0])
        # Here cm2 Must be an exclusive primary key field in the table , Non-character field 
      cu.execute(sql)
conn.commit()