数据库相关SQL

发布时间:2024年01月04日

一般来说各个数据库的sql都是一样的语法,但还是说明一下这里的是postgres数据库。

下列示例中,
param_maintain是table,
type, param, param_value, unit, start_use_time, maintain_time是列名。

  • 查询
    SELECT type, param, param_value, unit, start_use_time, maintain_time
    FROM public.param_maintain;
  • 删除
    delete from param_maintain where param = ‘35’ ;
  • 插入
    INSERT INTO param_maintain (type, param, param_value, unit, start_use_time, maintain_time) VALUES
    (‘04’, ‘35’, 0.4, ‘um’, ‘2023-09-27’, ‘2024-01-04’), (‘04’, ‘35’, 0.4, ‘um’, ‘2023-09-27’, ‘2024-01-04’)
  • 更新
    UPDATE table_name
    SET column1 = value1, column2 = value2…, columnN = valueN
    WHERE [condition];
    eg:
    update param_maintain set param_value = 0.4
    where type = ‘4’ and param = ‘35’

示例代码

def get_conn_pg():
        f = open(r'./config.yaml', encoding='utf-8')
        config_param = yaml.load(f.read(), Loader=yaml.FullLoader).get('postpsql')

        conn = psycopg2.connect(host=config_param.get('host'),
                                port=config_param.get('port'),
                                user=config_param.get('user'),
                                password=config_param.get('password'),
                                database=config_param.get('database'))
        cur = conn.cursor()

        return conn, cur

def close_conn_pg(cur, conn):
    cur.close()
    conn.close()

conn, cur = get_conn_pg()
table = 'param_maintain'
sql_select = f""" select * from "{table}" where type = '04' """
cur.execute(sql_select)
# conn.commit()
data_list = cur.fetchall()
fields = [desc[0] for desc in cur.description]
close_conn_pg(cur, conn)

df_db_params = pd.DataFrame(data_list)
df_db_params.columns = fields

tips:
查询不需要commit。增删改需要commit,提交事务,操作真正会影响到数据库中,否则数据库数据不变。

文章来源:https://blog.csdn.net/weixin_46870583/article/details/135394619
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。