Python代码不在数据库上创建表但能够查询结果postgres

Question

我的用例是在 postgres 数据库中创建一个临时表并从中获取记录并插入到不同的表中。

我使用的代码是:

import psycopg2
import sys
import pprint

from __future__ import print_function
from os.path import join,dirname,abspath
import xlrd
import os.path

newlist = []
itemidlist = []

def main():

    conn_string = "host='prod-dump.cvv9i14mrv4k.us-east-1.rds.amazonaws.com' dbname='ebdb' user='ebroot' password='*********'"
    # print the connection string we will use to connect
    # print "Connecting to database" % (conn_string)

    # get a connection, if a connect cannot be made an exception will be raised here
    conn = psycopg2.connect(conn_string)

    # conn.cursor will return a cursor object, you can use this cursor to perform queries
    cursor = conn.cursor()

    dealer_id = input("Please enter dealer_id: ")
    group_id = input("Please enter group_id: ")

    scriptpath = os.path.dirname('__file__')
    filename = os.path.join(scriptpath, 'Winco - Gusti.xlsx')

    xl_workbook = xlrd.open_workbook(filename, "rb")

    xl_sheet = xl_workbook.sheet_by_index(0)
    print('Sheet Name: %s' % xl_sheet.name)

    row=xl_sheet.row(0)

    from xlrd.sheet import ctype_text 

    print('(Column #) type:value')
    for idx, cell_obj in enumerate(row):
        cell_type_str = ctype_text.get(cell_obj.ctype, 'unknown type')
        #print('(%s) %s %s' % (idx, cell_type_str, cell_obj.value))

    num_cols = xl_sheet.ncols

    for row_idx in range(0, xl_sheet.nrows):    # Iterate through rows
        num_cols = xl_sheet.ncols

        id_obj = xl_sheet.cell(row_idx, 1)  # Get cell object by row, col
        itemid = id_obj.value
        #if itemid not in itemidlist:
        itemidlist.append(itemid)

        # execute our Query
        '''
        cursor.execute("""
        if not exists(SELECT 1 FROM model_enable AS c WHERE c.name = %s);
        BEGIN;
            INSERT INTO model_enable (name) VALUES (%s) 
        END;
        """ %(itemid,itemid))
        '''    
    cursor.execute("drop table temp_mbp1")

    try:
        cursor.execute("SELECT p.model_no, pc.id as PCid, g.id AS GROUPid into public.temp_mbp1 FROM products p, \
        model_enable me, products_clients pc, groups g WHERE p.model_no = me.name \
        and p.id = pc.product_id and pc.client_id = %s and pc.client_id = g.client_id and g.id = %s"\
        % (dealer_id,group_id)

    except (Exception, psycopg2.DatabaseError) as error:
        print(error)

    cursor.execute("select count(*) from public.temp_mbp1")
    # retrieve the records from the database
    records = cursor.fetchall()

    # print out the records using pretty print
    # note that the NAMES of the columns are not shown, instead just indexes.
    # for most people this isn't very useful so we'll show you how to return
    # columns as a dictionary (hash) in the next example.
    pprint.pprint(records)

if __name__ == "__main__":
        main()

程序之间的 try except block 没有抛出任何错误，但表没有像我在数据管理中看到的那样在 postgres 数据库中创建。

显示的输出是:

Please enter dealer_id: 90
Please enter group_id: 13
Sheet Name: Winco Full 8_15_17
(Column #) type:value
[(3263,)]

谢谢，桑托斯

Answer 1

您没有提交更改，因此它们不会保存在数据库中。添加到底部，就在 pprint 语句的下面:

conn.commit()