暑期实训记录：第四周（1）

最新推荐文章于 2021-07-28 21:13:04 发布

夏天啵啵雨

最新推荐文章于 2021-07-28 21:13:04 发布

阅读量83

点赞数

分类专栏：暑期实训

本文链接：https://blog.csdn.net/weixin_43515246/article/details/118682614

版权

暑期实训专栏收录该内容

15 篇文章 2 订阅

订阅专栏

这个博客展示了如何使用Python的Flask框架与PostgreSQL数据库进行交互，包括读取、插入、创建和删除数据表。示例代码中定义了find、insert_food、create_table、delete_table、get和read_pic等函数，用于执行特定的数据操作。

摘要由CSDN通过智能技术生成

flask连接完整的数据库

实现从postgresql远程数据库调用数据

import psycopg2


def find(user_id):
    conn = psycopg2.connect(
        database="USER_TABLE", user='postgres', password='',
        host='', port='5432')
    cursor = conn.cursor()
    conn.autocommit = True
    cursor.execute(
        "SELECT food_name,url,food_infor,RELATION_TABLE.food_id FROM RELATION_TABLE INNER JOIN FOOD_TABLE  ON RELATION_TABLE.food_id = FOOD_TABLE.food_id where id='" + user_id + "' and relationship='1'")
    n = cursor.fetchall()
    conn.closed()
    return n


def insert_food(user_id, food_url, food_infor, food_name, time):
    conn = psycopg2.connect(
        database="USER_TABLE", user='postgres', password='',
        host='', port='5432')
    cursor = conn.cursor()
    conn.autocommit = True
    cursor.execute("SELECT * from FOOD_TABLE")
    n = cursor.rowcount + 1
    str(n)
    cursor.execute("INSERT INTO FOOD_TABLE(food_id,food_name,food_infor,url,time)VALUES('%s','%s','%s','%s','%s')" % (
    n, food_name, food_infor, food_url, time))
    conn.commit()
    cursor.execute("INSERT INTO RELATION_TABLE(id,food_id,relationship)VALUES('%s','%s','%s')" % (user_id, n, '1'))
    conn.commit()
    conn.closed()


def createtable(user_id):
    conn = psycopg2.connect(
        database="USER_TABLE", user='postgres', password='',
        host='', port='5432')
    cursor = conn.cursor()
    conn.autocommit = True
    cursor.execute(
        "CREATE TABLE FOOD_" + user_id + " AS SELECT relation_table.food_id,food_name,url,food_infor,time  FROM  FOOD_TABLE,RELATION_TABLE WHERE relation_table.id='" + user_id + "' AND FOOD_TABLE.food_id=RELATION_TABLE.food_id and relationship='1'")


def deletetable(user_id):
    conn = psycopg2.connect(
        database="USER_TABLE", user='postgres', password='',
        host='', port='5432')
    cursor = conn.cursor()
    conn.autocommit = True
    cursor.execute("DROP TABLE IF EXISTS FOOD_" + user_id)


def get(user_id):
    conn = psycopg2.connect(
        database="USER_TABLE", user='postgres', password='',
        host='', port='5432')
    cursor = conn.cursor()
    conn.autocommit = True
    cursor.execute("SELECT * FROM FOOD_" + user_id)
    n = cursor.fetchall()
    conn.closed()
    return n


def read_pic(user_id):
    conn = psycopg2.connect(
        database="USER_TABLE", user='postgres', password='',
        host='', port='5432')
    cursor = conn.cursor()
    conn.autocommit = True
    sql = "select food_name,food_url from FOOD_TABLE where id = '{0}'".format(user_id)
    print(sql)
    cursor.execute(sql)
    pic_result = cursor.fetchall()
    conn.commit()
    conn.closed()
    return pic_result