由于在建表不规范,存在字段长度过长和类型不符等原因,导致后面在存数据的时候报错,要一个一个表去检查、修改,工作量很大。
修改起来,半天时间过去了,改完已经两眼昏花,四肢无力,真的是泪两行啊!!!!!
为了节省我们的工作量,释放双手玩手机,这种事情交给程序来完成就好了。
嗯,真香。。。。
说干就干,干起来
(此处有分割线)
一、步骤分解
1、连接数据库
import psycopg2
conn=psycopg2.connect(database='database',user='username',password='password',host='ip地址',port='5432')
cursor=conn.cursor()
2、查询数据库所有表名
cursor.execute("select tablename from pg_tables where schemaname='public';")
table_name=cursor.fetchall()
3、查出单张表的数据情况,需要再下面语句中替换table_name为具体表的名称
select a.attnum,a.attname,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as type,d.description from pg_class c, pg_attribute a , pg_type t, pg_description d
where c.relname = 'table_name' and a.attnum>0 and a.attrelid = c.oid and a.atttypid = t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum
或(建议用下面这条)
select a.attnum,a.attname,case when a.attlen > 0 then a.attlen else a.atttypmod - 4 end as type,d.description from pg_class c, pg_attribute a , pg_type t, pg_description d
where c.relname = 'gn_cl_gnqcjrczxx' and a.attnum>0 and a.attrelid = c.oid and a.atttypid = t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum
4、修改字段长度为255
cursor.execute("ALTER TABLE table_name alter COLUMN column_name type varchar(255);")
5、修改字段类型为text类型
cursor.execute("ALTER TABLE table_name alter COLUMN column_name type text;")
6、添加主键
ALTER TABLE table_name ADD primary key(id);
二、完整代码
前方高能,放大招
由于我们的表不止一个,要一个一个输入程序,不够智能,我们用一个for循环就可以解决表查询,字段查询也是一样的,那些字段长度小于255的,我们就不管了。
数据库中存储有数据后,字段长度大于255的要修改回255可能会报错,用一个try…except来解决。
接下来输出完整程序:
import psycopg2
conn=psycopg2.connect(database='my_database',user='postgres',password='postgres',host='ip',port='5432')
cursor=conn.cursor()
#查出数据库中所有表名称
cursor.execute("select tablename from pg_tables where schemaname='public';")
table_name=cursor.fetchall()
for i in range(len(table_name)):
#查出表的所有字段长度和注释
cursor.execute("select a.attnum,a.attname,case when a.attlen > 0 then a.attlen else a.atttypmod - 4 end as type,d.description \
from pg_class c, pg_attribute a , pg_type t, pg_description d where c.relname = '%s' and a.attnum>0 \
and a.attrelid = c.oid and a.atttypid = t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum"%table_name[i])
column_name=cursor.fetchall()
for j in range(len(column_name)):
if int(column_name[j][0])>255:
try:
#修改字段长度为255
cursor.execute("ALTER TABLE %s1 alter COLUMN %s2 type varchar(255);"%(table_name[i],column_name[j]))
except:
#修改字段类型为text类型
cursor.execute("ALTER TABLE %s1 alter COLUMN %s2 type text;"%(table_name[i],column_name[j]))
conn.commit()
cursor.close()
conn.close()
如果要把具体修改了哪些字段,可以建一个list表把try里面的语句存起来,这样就知道具体执行哪些内容,毕竟用程序执行这些会存在很大的风险。
温馨提示:程序有风险,慎用慎用!!!