Python in the operation of sqlite3 database, often need to use the field name, but for sqlite use select statement can not be like MySql and other databases to return to the dictionary with the field name of the dataset. Especially for an unfamiliar sqlite database, if you need to use tools to view it when writing code, it is actually a bit sorry for python.
The following two pieces of code make it easy to get a list of all the data table names and field names in a sqlite database:
# python get table name and table field names in sqlite3 database
import sqlite3
conn=('')
cu=()
# Get the table name, saved in tab_name list
("select name from sqlite_master where type='table'")
tab_name=()
tab_name=[line[0] for line in tab_name]
# Get the column names (field names) of the table, stored in the col_names list, the set of field names for each table as a tuple
col_names=[]
for line in tab_name:
('pragma table_info({})'.format(line))
col_name=()
col_name=[x[1] for x in col_name]
col_names.append(col_name)
col_name=tuple(col_name)
# The reason for saving as a tuple is that it avoids modifying the field name by mistake, and the tuple is cleverly transformed into a string that can be
# Used directly in SQL insert statement. For example, the following code gets the set of bracketed field names for the first table:
'''
sql_col_name=str(col_names[0]).replace('\'','')
'''
Next, do something with the auto-fetched field names.
For example, the table data of one of my sqlite databases was imported from an Excel table, and some incorrectly entered carriage returns in the Excel table were imported along with it, which will be displayed as the character '\n' in the sqlite table, and I want to remove this character from all the fields, which I did by using the following code.
# Delete carriage returns from all text-based fields in the first table (note: take out the carriage returns that are stored in the database)
(# will be displayed as the original character '\n')
('select * from '+ tab_name[0])
cdset=()
cdset=[list(line) for line in cdset] # Convert the tuple of the result set to a list in order to modify it.
for line in cdset:
for x in range(len(line)):
#Note: As the For loop count variable cannot be changed in the loop body, you cannot replace it with x in line here.
if type(line[x])==str and (r'\n' in line[x]): # Only character data can be modified
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])
#This cm2 must be an exclusive primary key field in the table, not a character field.
(sql)
()
The above example of this python to get the table name and table field name of sqlite3 database is all that I have shared with you.