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

2024-03-29 10:29:51 发布

您现在位置:Python中文网/ 问答频道 /正文

我的用例是在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块没有抛出任何错误,但是表没有在postgres数据库中创建,正如我在dataadmin中看到的那样。在

显示的输出是:

^{pr2}$

谢谢, 桑托什


Tags: thenamefromimportidobjexecutecell