-2

Right now, when I print gameuniqueteams it shows as a string. On SQL, at each row a new team gets added while I want each to show individually. At this stage, gameuniqueteams will show the following string

 ['Arsenal', 'Bournemouth', 'Brighton', 'Burnley', 'Chelsea']

I want it to show per row so that when I transfer it to sql each team shows in a row by itself.

['Arsenal']
['Bournemouth']
['Brighton']
['Burnley']
['Chelsea']

This is my entire code in case it helps! What should I do?

#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys
import csv
from itertools import count, cycle
from _tkinter import create
from setuptools.dist import sequence
from email.policy import default
path = r'C:\Users\sammy\Downloads\E0.csv'
with open(path, "r") as csvfile:
    readCSV = csv.reader(csvfile, delimiter=",")
    firstline = 1
    con = None
    con = psycopg2.connect("host='localhost' dbname='football' user='postgres' password='XXX'")   
    cur = con.cursor()
    cur.execute("DROP TABLE teams")
    cur.execute("CREATE TABLE teams (HomeTeamID SERIAL PRIMARY KEY, AllTeams123 VARCHAR)")

    hometeams = []
    awayteams = []
    uniqueteams = []
    allteams = []
    gameuniqueteams = []    
    try:
        for row in readCSV:
            if firstline:
                firstline=0
                continue
            HomeTeam = row[2]
            AwayTeam = row[3]
            hometeams.append(HomeTeam)
            awayteams.append(AwayTeam)
            allteams = hometeams + awayteams
            for x in allteams:
                if x not in uniqueteams:
                    uniqueteams.append(x)
            gameuniqueteams = sorted(uniqueteams)
            for x in gameuniqueteams:
                print (x)
            gameuniqueteams = (x)
            data1 = (gameuniqueteams,)
            query1 = "INSERT INTO teams (AllTeams123) VALUES (%s);"
            cursor = con.cursor()
            cursor.execute(query1, data1)



    except psycopg2.DatabaseError as e:
        if con:
            con.rollback() 
            print ("Error %s % e", e)
            sys.exit(1) 
    finally:
        if con:
            con.commit()
            con.close()
sammmm
  • 63
  • 1
  • 1
  • 10
  • 1
    Possible duplicate of [Transpose a matrix in Python](https://stackoverflow.com/questions/17037566/transpose-a-matrix-in-python) – Ian E Nov 24 '17 at 17:39
  • @ElisByberi it's not considering the aim is not to print but to move them to sql – sammmm Nov 24 '17 at 17:44
  • @IanE won't work considering transpose is for a matrix while I am dealing with a list here – sammmm Nov 24 '17 at 17:45
  • We don't want to see your entire code, just a minimal example that illustrates the problem (runnable greatly preferred). – martineau Nov 24 '17 at 17:47
  • @martineau now? – sammmm Nov 24 '17 at 17:50
  • 2
    sammyam: Not so much. Seems to me at the most basic level you're trying to read a CSV file using the `csv` module and format some of the data in a certain way. The database stuff isn't really relevant—and in fact just obscures the problem (I think) you want us to solve. – martineau Nov 24 '17 at 17:59
  • @martineau you're right. I want to export it to the database but not sure how to put them in rows instead of one string. Could you please help me with that? – sammmm Nov 24 '17 at 18:02
  • Many of us will be able and glad to help you. But first I suggest you [edit] your question and take out all the database stuff. Have it read the rows of sample csv data (which you've also provided) and produce the undesired result (and maybe any attempt you've made yourself to reformat it the way you want). – martineau Nov 24 '17 at 18:06
  • @martineau I really appreciate this. What do you think now? – sammmm Nov 24 '17 at 18:13
  • sammyam: Sorry, I didn't mean just delete most but not all of the `psycopg2` stuff and leave something completely unrunnable. You're getting data from a csv file (need show a sample of the the data that's in it), analyzing it, but it's not in a form you want so needs to be reformated but you don't know how. That's that the kind of code/question that will get you some actual answers. – martineau Nov 24 '17 at 18:21
  • @martineau I tried my best in truncating the parts of code you might need. What do you think now? – sammmm Nov 24 '17 at 18:23
  • 1
    sammyam: I don't see anything show the contents of the `E0.csv` file. If this is the best you can come up with, so be it and good luck. – martineau Nov 24 '17 at 18:26
  • @martineau what do you want me to add? – sammmm Nov 24 '17 at 18:59
  • Copy and paste 3-5 rows of data from the `C:\Users\sammy\Downloads\E0.csv` file into your answer. Indent each line by four spaces as though it was code. It's OK if the lines are long. – martineau Nov 24 '17 at 19:18
  • @martineau I already printed 5 before the code – sammmm Nov 24 '17 at 19:29
  • 1
    You appear to be confusing the representation when printing to the representation when saving/persisting. You can use string manipulation to change the way it's printed. – Adam Smith Nov 24 '17 at 20:28
  • sammmm: I think @Adam Smith might be partially right about your confusion. Do as I suggested and copy the data from the file and paste it into your question. – martineau Nov 24 '17 at 20:49
  • @AdamSmith what do you think now? – sammmm Nov 24 '17 at 20:53
  • @martineau what do you think now? – sammmm Nov 24 '17 at 20:53
  • @sammmm see my answer. – Adam Smith Nov 24 '17 at 20:56
  • @sammmm: What part of " copy the data from the file and paste it into your question" was unclear? – martineau Nov 24 '17 at 22:04
  • Please don't ask the same question within hours in several threads. https://stackoverflow.com/questions/47476434/how-to-show-data-per-row-instead-of-string – Mr. T Nov 24 '17 at 22:21

2 Answers2

0

Here is the answer to the question you asked:

import pprint

teams = ['Arsenal', 'Bournemouth', 'Brighton', 'Burnley', 'Chelsea']
for team in teams:
    print([team])

teams1 = [[team]
          for team in teams[:3]]
print(teams1)
pprint.pprint(teams1, width=1)
pprint.pprint(teams, width=1)

['Arsenal']
['Bournemouth']
['Brighton']
['Burnley']
['Chelsea']
[['Arsenal'], ['Bournemouth'], ['Brighton']]
[['Arsenal'],
 ['Bournemouth'],
 ['Brighton']]
['Arsenal',
 'Bournemouth',
 'Brighton',
 'Burnley',
 'Chelsea']

It's not obvious to me how that relates to your DB code. It looks like your allteams assignment is indented too much - you want that and subsequent processing to happen after reading all CSV rows. And perhaps you want to iterate for team in sorted(uniqueteams):, then data1 = (team,), and store that as a DB row.

Here is a datastructure it would be useful for you to know about: the set.

unique_teams = set(['Bournemouth', 'Brighton', 'Brighton'])
unique_teams.add('Burnley')
print(unique_teams)


{'Bournemouth', 'Burnley', 'Brighton'}

The set will take care of uniqueness for you, without the hassle of making repeated membership queries.

J_H
  • 17,926
  • 4
  • 24
  • 44
  • I appreciate your help. Don't worry about the indentations, they are correct in my environment. But to be honest you are not answering the question. When printing gameuniqueteams, they show as ['Arsenal', 'Bournemouth', 'Brighton', 'Burnley', 'Chelsea'] and not as rows. Back to case 0. If I follow your iteration, I am able to print them but not to store them. – sammmm Nov 24 '17 at 19:14
  • Edit: I tacked on list of lists `teams1` in the answer. But that is a silly representation, much better to stick with `teams`. The `data1` assignment in the answer appears to be what is relevant to you. You may have been having trouble sending a bare `team` string to the DB, when it requires a list or tuple of strings, as described in the answer. The notation `(s,)` converts string `s` into a 1-element tuple, which is suitable for sending through the database API. – J_H Nov 24 '17 at 19:24
  • Your help is more than appreciated. I edited my code and added for x in gameuniqueteams: print (x) The issue is when I do this they print, but I want to store them into one word, which comes before data1 in the code in order to change data1. Many thanks! – sammmm Nov 24 '17 at 19:34
  • Making a string from a list is straightforward: `words = 'ab cd ef'.split()`, and then `'_'.join(words)` will yield `'ab_cd_ef'`. – J_H Nov 24 '17 at 20:28
  • this is not what I want.. I want the string to become rows which functioned thanks to your earlier piece of code. I just want the for x in gameuniqueteams: print (x) to come before data1 in my code but unsure about how to make it happen. – sammmm Nov 24 '17 at 20:36
0

Your problem appears to have nothing to do with the database or the file, but simply with string manipulation while printing.

Some list

foo = ["some", "words", "go", "here"]

becomes rows when you join it by newlines

>>> "\n".join(foo)
"""some
words
go
here"""

those quotation marks are just markup by Python, so printing it makes those go away.

>>> print("\n".join(foo))
some
words
go
here

I would argue that your data structure is correct as a list, and you should just manipulate that list of strings to get the output you're looking for.

Adam Smith
  • 52,157
  • 12
  • 73
  • 112
  • First many thanks for your help. I know that the issue is with data manipulation. The quotation marks are not my issue. As you can see my edited code earlier, I found a solution. The issue I am facing is the following: Let's say I have two tables and repeating the steps. For " for x in gameuniqueteams: print (x) alluniqueteams = (x) data1 = (alluniqueteams,) for y in alluniquereferees: print (y) data2 = (alluniquereferees,)" there is a certain over lap between the two for statements and the data is overlapping. What to do? – sammmm Nov 24 '17 at 21:00
  • @sammmm I don't understand your question, but it doesn't appear to be the same one you had when you asked "how to show data in rows instead of strings" 3 hours ago. Consider opening a new question asking about this new problem? – Adam Smith Nov 24 '17 at 21:10
  • 1
    Sure will do! Have to wait another hour.. Many thanks for your help :) – sammmm Nov 24 '17 at 21:17
  • @sammmm ping me here when you do I'll take a look if I can. – Adam Smith Nov 24 '17 at 21:40
  • https://stackoverflow.com/questions/47514201/how-to-add-different-rows-to-different-loops-with-a-loop-using-python Thanks! – sammmm Nov 27 '17 at 15:16