Problem when recording data

Asked

Viewed 52 times

0

I’m performing a query on a table where the structure is :

Documento  Produto
123        Camisa Posh rosa
127        Calça Handara 36
127        Meia Barby 
158        Calça Handara 38
129        Blusa Yoll M
129        Blusa Yoll azul P
129        Short preto 36

In Python I created a course for this query, because my intention and save the results in a string separated by ,, but in that way:

Camisa Posh rosa,
Calça Handara 36, Meia Barby ,
Calça Handara 38,
Blusa Yoll M, Blusa Yoll azul P, Short preto 36

In this case he would take the products that are on the same coupon and write on a single line, but my problem is that he is doubling the amount of registration for these documents:

Camisa Posh rosa,
Calça Handara 36, Meia Barby ,
Calça Handara 36, Meia Barby ,
Calça Handara 38,
Blusa Yoll M, Blusa Yoll azul P, Short preto 36   
Blusa Yoll M, Blusa Yoll azul P, Short preto 36 
Blusa Yoll M, Blusa Yoll azul P, Short preto 36 

I believe I have to create a FOR to check the coupons and not repeat the data, however I am not able to play this logic in the code. Follows the code

import os
os.chdir("C:\\oracle\\product\\10.2.0\\client")
import cx_Oracle
con = cx_Oracle.connect('venda/[email protected]:1521/local')

curVendas = con.cursor()
curVendaProd = con.cursor()
base = ''

curVendas.execute('select * from base_venda')
for vendas in curVendas:
    print(vendas)

    quantidade = curVendaProd.execute('select produto from base_venda where documento = ' + str(vendas[0]))
    i = 1
    for produtos in curVendaProd:
        print(produtos)

        if (i == quantidade) :
            base = base + produtos[0]
        else:
            base = base + produtos[0] + ',' 
            i += 1

    base = base + '\n'

arquivo = open("base_import.csv", "w")
arquivo.write(base)
arquivo.close()
  • 1

    You don’t need to make the logic in Python, because you can already get the desired result directly from the database by doing the GROUP BY by the document number and concatenating the products.

  • Anderson thanks for the interaction really using the concatena LISTAGG as I want but returns this error due to having many record.ora-01489 result of string concatenation is Too long

  • Did the group by?

  • Yes! If I use a record quantity constraint works normally, however if form on top of base it returns this error. I’m researching that there is a xmlagg function for base with many record, I’m seeing how apply.

  • The group by should already solve the problem of repeated records in the result. If you are still repeating, you must be doing something wrong.

  • Actually it solved, I have no more record repeated, I just can’t see all the data in the query because it presents the error I told you, actually the problem is not in the script you sent but in my base have many records.

Show 2 more comments
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.