replace text in a txt

[deanhystad]

but for now i have to insert data from txt to the database so something like:
INSERT INTO sbostats (partita,p1,pX,p2,p1x,px2,p12) VALUES ( "Verona - Napoli", "10.42%", "27.08%","62.5%", "37.5%", "89.58%", "72.92%")
val =

Are you sure? Do you want to put a string, "10.42%", in a database or do you want to put a number like 0.1042 or 10.42? Are you going to do with the percentage?

Do you want to use "Verona - Napoli", or do you want to create a table for Verona and add percentages for when they play Napoli? Or do you want partita to be two columns: HOME and VISITOR?

In this code I parse the file and load the values into a dataframe. Pandas supports writing dataframes to sql.

https://pandas.pydata.org/docs/reference...o_sql.html

from itertools import islice
import pandas as pd


names = {
    "Verona": "Hellas Verona",
    "Sassuolu": "Sassuolu Calcio",
    "Monza": "AC Monza",
}


def batched(iterable, n):
    "Batch data into tuples of length n. The last batch may be shorter."
    # batched('ABCDEFG', 3) --> ABC DEF G
    if n < 1:
        raise ValueError("n must be at least one")
    it = iter(iterable)
    while batch := tuple(islice(it, n)):
        yield batch


def percentage(text):
    return float(text.strip().split()[0].replace("%", ""))/100


def load_file(filename):
    with open(filename, "r") as file:
        for lines in batched(file, 7):
            home, visitor = lines[0].strip().split(" - ")
            yield {
                "HOME": names.get(home, home),
                "VISITOR": names.get(visitor, visitor),
                "P1": percentage(lines[1]),
                "PX": percentage(lines[2]),
                "P2": percentage(lines[3]),
                "P1X": percentage(lines[4]),
                "PX2": percentage(lines[5]),
                "P12": percentage(lines[6]),
            }


df = pd.DataFrame(load_file("test.txt"))
print(df)

Output:
             HOME        VISITOR      P1      PX      P2     P1X     PX2     P12
0   Hellas Verona         Napoli  0.1042  0.2708  0.6250  0.3750  0.8958  0.7292
1          Torino          Inter  0.1538  0.2308  0.6154  0.3846  0.8462  0.7692
2        Sassuolo          Lazio  0.2407  0.2963  0.4630  0.5370  0.7593  0.7037
3         AS Roma       AC Monza  0.5758  0.3333  0.0909  0.9091  0.4242  0.6667
4         Bologna      Frosinone  0.5238  0.2937  0.1825  0.8175  0.4762  0.7063
5     Salernitana       Cagliari  0.3778  0.2889  0.3333  0.6667  0.6222  0.7111
6        Atalanta          Genoa  0.7230  0.1757  0.1014  0.8986  0.2770  0.8243
7        AC Milan       Juventus  0.4335  0.3064  0.2601  0.7399  0.5665  0.6936
8         Udinese          Lecce  0.4410  0.3275  0.2314  0.7686  0.5590  0.6725
9      Fiorentina         Empoli  0.6859  0.1987  0.1154  0.8846  0.3141  0.8013
10          Genoa    Salernitana  0.5455  0.2597  0.1948  0.8052  0.4545  0.7403
11       Sassuolo        Bologna  0.4539  0.2695  0.2766  0.7234  0.5461  0.7305
12          Lecce         Torino  0.3396  0.2642  0.3962  0.6038  0.6604  0.7358
13       Juventus  Hellas Verona  0.6794  0.2214  0.0992  0.9008  0.3206  0.7786
14       Cagliari      Frosinone  0.2857  0.2381  0.4762  0.5238  0.7143  0.7619
15       AC Monza        Udinese  0.3704  0.3580  0.2716  0.7284  0.6296  0.6420
16          Inter        AS Roma  0.5510  0.2653  0.1837  0.8163  0.4490  0.7347
17         Napoli       AC Milan  0.3857  0.4000  0.2143  0.7857  0.6143  0.6000
18         Empoli       Atalanta  0.2500  0.3750  0.3750  0.6250  0.7500  0.6250
19          Lazio     Fiorentina  0.4100  0.2650  0.3250  0.6750  0.5900  0.7350
20  Hellas Verona         Napoli  0.1042  0.2708  0.6250  0.3750  0.8958  0.7292
21         Torino          Inter  0.1538  0.2308  0.6154  0.3846  0.8462  0.7692
22       Sassuolo          Lazio  0.2407  0.2963  0.4630  0.5370  0.7593  0.7037
23        AS Roma       AC Monza  0.5758  0.3333  0.0909  0.9091  0.4242  0.6667
24        Bologna      Frosinone  0.5238  0.2937  0.1825  0.8175  0.4762  0.7063
25    Salernitana       Cagliari  0.3778  0.2889  0.3333  0.6667  0.6222  0.7111
26       Atalanta          Genoa  0.7230  0.1757  0.1014  0.8986  0.2770  0.8243
27       AC Milan       Juventus  0.4335  0.3064  0.2601  0.7399  0.5665  0.6936
28        Udinese          Lecce  0.4410  0.3275  0.2314  0.7686  0.5590  0.6725
29     Fiorentina         Empoli  0.6859  0.1987  0.1154  0.8846  0.3141  0.8013
30          Genoa    Salernitana  0.5455  0.2597  0.1948  0.8052  0.4545  0.7403
31       Sassuolo        Bologna  0.4539  0.2695  0.2766  0.7234  0.5461  0.7305
32          Lecce         Torino  0.3396  0.2642  0.3962  0.6038  0.6604  0.7358
33       Juventus  Hellas Verona  0.6794  0.2214  0.0992  0.9008  0.3206  0.7786
34       Cagliari      Frosinone  0.2857  0.2381  0.4762  0.5238  0.7143  0.7619
35       AC Monza        Udinese  0.3704  0.3580  0.2716  0.7284  0.6296  0.6420
36          Inter        AS Roma  0.5510  0.2653  0.1837  0.8163  0.4490  0.7347
37         Napoli       AC Milan  0.3857  0.4000  0.2143  0.7857  0.6143  0.6000
38         Empoli       Atalanta  0.2500  0.3750  0.3750  0.6250  0.7500  0.6250
39          Lazio     Fiorentina  0.4100  0.2650  0.3250  0.6750  0.5900  0.7350

If you don't want to use pandas, you can easily write a loop to put entries in a database.

sql = "INSERT INTO sbostats (home, visitor, p1, pX, p2, p1x, px2, p12) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
for game in load_file("test.txt"):
    mycursor.execute(sql, tuple(game.values()))