tranquil trellis Jan 17, 2019, 3:42 PM

#

What's the problem?

gilded narwhal Jan 17, 2019, 5:19 PM

#

you can create and a write lines to a text file like so:

with open('some_file.txt', 'w') as file:
    for i in range(10):
        for j in range(10):
            print('i:', i, 'j:', j, file = file)
            
            # or alternatively:
            file.write(f'i: {i} j: {j}\n')

#

a few other things come to mind.
if you have lots of rows in your database fetchall wouldnt be the best option for iterating through them all.
instead you can iterate through them one by one.

one way (and the simplest way) this can be done is by iterating over the cursor:

cursor.execute('SELECT * FROM Customer')
for row in cursor:
    # do things here

#

you can also go one step further and unpack the row right there in your loop:

for user, password, balance, postcode in cursor:
    # do things

#

@spare geode

harsh pulsar Jan 17, 2019, 5:29 PM

#

one by one will be slow for other reasons

#

fetchall will be the fastest if you can fit the whole result in memory

#

otherwise you should use fetchmany with a batch size

spare geode Jan 17, 2019, 5:29 PM

#

def textfile(Admin):
    file = open("testfile.txt","w")
    print ("This shows ALL data including Stock that isnt in store anymore!")
    with sqlite3.connect("EMP.db") as db:
        cursor = db.cursor()
        cursor.execute('SELECT * FROM Customer')
        data = cursor.fetchall()
        iterate=0
        for x in (data):
            file.write(f"User: {data[iterate][0]}  Password: {data[iterate][1]} Balance: {data[iterate][2]} Overdraft: {data[iterate][3]} Postcode: {data[iterate][4]}\n")
            iterate+=1

#

Just opened the file then replaced the print with write

#

Works alright

#

ty for help ❤

hazy mango Jan 17, 2019, 5:44 PM

#

cursor.execute("""CREATE TABLE IF NOT EXISTS results(n0 INTEGER, n1 INTEGER, n2 INTEGER, n3 INTEGER, n4 INTEGER, n5 INTEGER,
n6 INTEGER, n7 INTEGER, n8 INTEGER, n9 INTEGER, n10 INTEGER""")```What's wrong with this? Says error near `INTEGER`

#

    n6 INTEGER, n7 INTEGER, n8 INTEGER, n9 INTEGER, n10 INTEGER""")
sqlite3.OperationalError: near "INTEGER": syntax error```

#

(@me upon response please)

ionic pecan Jan 17, 2019, 5:48 PM

#

missing closing )

hazy mango Jan 17, 2019, 5:55 PM

#

thx

harsh pulsar Jan 17, 2019, 6:31 PM

#

the world needs a good sql syntax checker

#

even the almighty postgres has absolutely crap syntax error reporting

hazy mango Jan 17, 2019, 6:33 PM

#

    n0_fail = cursor.execute("""SELECT * FROM results WHERE n0 = 0""").fetchall()
    n0_success = cursor.execute("""SELECT * FROM results WHERE n0 != 0""").fetchall()

    n1_fail = cursor.execute("""SELECT * FROM results WHERE n1 = 0""").fetchall()
    n1_success = cursor.execute("""SELECT * FROM results WHERE n1 != 0""").fetchall()

    n2_fail = cursor.execute("""SELECT * FROM results WHERE n2 = 0""").fetchall()
    n2_success = cursor.execute("""SELECT * FROM results WHERE n2 != 0""").fetchall()```Is there a better way of doing this (rather than having to do everything separately, it retrieves all info and stores in appropriate variable)

harsh pulsar Jan 17, 2019, 6:34 PM

#

you could append a column indicating whether each of the n variables is 0

#

and then download the whole thing and do filtering client side

#

if the data isnt too big to fit in memory that will be simpler than doing a single query. and if you use pandas it will be pretty fast

hazy mango Jan 17, 2019, 6:35 PM

#

How exactly would that work? (I'm really new to databases)

harsh pulsar Jan 17, 2019, 6:41 PM

#

actually wait hold on

#

just include the N variables in the output

#

and filter

#

literally just `select * from results', load into pandas, and filter

hazy mango Jan 17, 2019, 6:43 PM

#

idk anything about pandas and filtering

#

So that doesn't really help either

#

@harsh pulsar

harsh pulsar Jan 17, 2019, 6:43 PM

#

well you dont need to use pandas then

#

but if you just wanna know how to filter a list thats a topic for one of the help channels.

hazy mango Jan 17, 2019, 6:49 PM

#

    data = cursor.execute("""SELECT * FROM results""").fetchall()
    print("".join(str(data)), sep="\n")```How to get it to print on different lines?

#

This still prints it all on one line

#

It currently prints py [(0, 0, 1, 0, 2, 1, 1, 1, 1, 1, 2), (0, 1, 1, 0, 3, 0, 1, 1, 0, 1, 2), (1, 1, 1, 1, 0, 2, 0, 1, 1, 1, 1), (0, 0, 1, 0, 1, 1, 1, 1, 0, 3, 2), (1, 0, 1, 0, 3, 1, 0, 1, 1, 2, 0), (2, 1, 1, 0, 0, 1, 1, 0, 2, 1, 1), (1, 0, 0, 0, 2, 1, 0, 2, 3, 1, 0), (0, 2, 1, 0, 1, 0, 0, 1, 3, 0, 2), (1, 0, 1, 2, 2, 0, 1, 2, 1, 0, 0), (1, 2, 1, 2, 0, 1, 0, 1, 1, 1, 0)]instead of ```py
(0, 0, 1, 0, 2, 1, 1, 1, 1, 1, 2)
(0, 1, 1, 0, 3, 0, 1, 1, 0, 1, 2)

e.t.c.```

#

@harsh pulsar

#

Doing print("\n".join(...)) doesn't work either

#

That prints ```py
[
(
0
,

0
,

1
,

0
,

2
,

1
,

2
)
#e.t.c.```

slate spire Jan 17, 2019, 6:53 PM

#

you don't need to do str(data), that transforms it into a string, which iterates through it's characters

hazy mango Jan 17, 2019, 6:54 PM

#

I do need it?

#

    print("\n".join(data))
TypeError: sequence item 0: expected str instance, tuple found```

slate spire Jan 17, 2019, 6:54 PM

#

lemme rephrase that

#

you're using it wrong

#

the problem is that you're casting the whole list to str, when you really want to cast each element to str

#

for the latter you want to do map(str, data)

hazy mango Jan 17, 2019, 6:56 PM

#

    print('\n'.join(map(str, data)))``` thanks :~)

slate spire Jan 17, 2019, 6:56 PM

#

no probs

#

glad it worked

hazy mango Jan 17, 2019, 7:02 PM

#

def get_results():
    data = cursor.execute("""SELECT * FROM results""").fetchall()
    print('\n'.join(map(str, data)))
    d0 = [x for x in data[0] if x == 0]
    d1 = [x for x in data[1] if x == 0]
    d2 = [x for x in data[2] if x == 0]
    d3 = [x for x in data[3] if x == 0]
    d4 = [x for x in data[4] if x == 0]
    d5 = [x for x in data[5] if x == 0]
    d6 = [x for x in data[6] if x == 0]
    d7 = [x for x in data[7] if x == 0]
    d8 = [x for x in data[8] if x == 0]
    d9 = [x for x in data[9] if x == 0]

    print("\n")
    print(len(d0))
    print(len(d1))
    print(len(d3))
    print(len(d4))
    print(len(d5))
    print(len(d6))
    print(len(d7))
    print(len(d8))
    print(len(d9))```Feel like this is still too complicated

harsh pulsar Jan 17, 2019, 7:03 PM

#

i think you have ti fliipped

#

data is a list of rows

hazy mango Jan 17, 2019, 7:03 PM

#

ik I can make the print better, but I feel like the d0, d1 e.t.c. is too complicated

harsh pulsar Jan 17, 2019, 7:03 PM

#

each element of data is 1 row

hazy mango Jan 17, 2019, 7:03 PM

#

oh yeah

#

🤦

#

So how would I do it by column?

harsh pulsar Jan 17, 2019, 7:06 PM

#

def get_colnames(cursor):
    """ Get column names for cursor's result set """
    return [coldesc[0] for coldesc in cursor.description]

cursor.execute("""SELECT * FROM results""")
colnames = get_colnames(cursor)
data = [dict(zip(colnames, row)) for row in cursor.fetchall()]

print([row for row in data if row['n0'] == 0])

#

something like that @hazy mango

#

let me know if that works for you. i might have made a mistake or a typo

hazy mango Jan 17, 2019, 7:08 PM

#

[{'n0': 0, 'n1': 0, 'n2': 1, 'n3': 0, 'n4': 2, 'n5': 1, 'n6': 1, 'n7': 1, 'n8': 1, 'n9': 1, 'n10': 2}, {'n0': 0, 'n1': 1, 'n2': 1, 'n3': 0, 'n4': 3, 'n5': 0, 'n6': 1, 'n7': 1, 'n8': 0, 'n9': 1, 'n10': 2}, {'n0': 0, 'n1': 0, 'n2': 1, 'n3': 0, 'n4': 1, 'n5': 1, 'n6': 1, 'n7': 1, 'n8': 0, 'n9': 3, 'n10': 2}, {'n0': 0, 'n1': 2, 'n2': 1, 'n3': 0, 'n4': 1, 'n5': 0, 'n6': 0, 'n7': 1, 'n8': 3, 'n9': 0, 'n10': 2}]```is the output

#

I don't think that's right...?

#

@harsh pulsar

ionic pecan Jan 17, 2019, 7:15 PM

#

if your columns are named n0..n10, then it is right

hazy mango Jan 17, 2019, 7:16 PM

#

but that doesn't give me the info I want

#

I'm looking for a better way of doing this

#

https://paste.pythondiscord.com/oletohutin.py

#

Better being shorter

ionic pecan Jan 17, 2019, 7:18 PM

#

uhhhhhhhhhh

#

i feel like you're misunderstanding sql

#

what are n0..10 supposed to represent

hazy mango Jan 17, 2019, 7:19 PM

#

n0..n10 is the column names

#

I want to get all values of each column

#

If it is 0, then add to {column}fail

#

Otherwise add to {column}success

ionic pecan Jan 17, 2019, 7:20 PM

#

i get that they are the column names

hazy mango Jan 17, 2019, 7:20 PM

#

This is my table

📎 unknown.png

ionic pecan Jan 17, 2019, 7:20 PM

#

but what is the data in them supposed to represent

hazy mango Jan 17, 2019, 7:20 PM

#

The amount of times a number was generated in 10 attempts

#

Hence the sum of each row is 10

ionic pecan Jan 17, 2019, 7:21 PM

#

so the column name represents the number? or the row index represents the number

hazy mango Jan 17, 2019, 7:21 PM

#

So n0 means amount of times the number 0 was generated

#

n1 means amount of times the number 1 was generated

#

e.t.c.

#

The row index is how many times the 'test' (generating 10 numbers) has been executed

#

def saveResults(n0, n1, n2, n3, n4, n5, n6, n7, n8, n9, n10):
    cursor.execute("""INSERT INTO results(n0, n1, n2, n3, n4, n5, n6, n7, n8, n9, n10) VALUES (?,?,?,?,?,?,?,?,?,?,?)""", (n0,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10))
    db.commit()


def run():
    for a in range(10):
        n0 = 0
        n1 = 0
        n2 = 0
        n3 = 0
        n4 = 0
        n5 = 0
        n6 = 0
        n7 = 0
        n8 = 0
        n9 = 0
        n10 = 0
        for i in range(10):
            r = random.randint(0, 10)
            if  r == 0:
                n0+=1
            elif r == 1:
                n1+=1
            elif r == 2:
                n2+=1
            elif r == 3:
                n3+=1
            elif r == 4:
                n4+=1
            elif r == 5:
                n5+=1
            elif r == 6:
                n6+=1
            elif r == 7:
                n7+=1
            elif r == 8:
                n8+=1
            elif r == 9:
                n9+=1
            elif r == 10:
                n10+=1

        print(f"0: {n0}\t1: {n1}\t2: {n2}\t3: {n3}\t4: {n4}\t5: {n5}\t6: {n6}\t7: {n7}\t8: {n8}\t9: {n9}\t10: {n10}")
        saveResults(n0, n1, n2, n3, n4, n5, n6, n7, n8, n9, n10)```This is the code (might be easier for you to understand from code)

harsh pulsar Jan 17, 2019, 7:27 PM

#

@hazy mango uhh i just gave an example

#

that isnt the whole thing

#

yes that is the correct output for part of what you want. that gets you your data, and then the last line filters to show you only rows where n0 == 0. You still need to repeat that filtering process for every condition, but it saves you several round trips to the database.

hazy mango Jan 17, 2019, 7:30 PM

#

Yeah I see

#

I don't really get where I need to go from here though

#

Wait

#

I think I got it

#

Thanks :~)

#

How to print the amount of rows a database has?

#

(In my case 10)

#

@harsh pulsar

harsh pulsar Jan 17, 2019, 7:35 PM

#

if you already have the data just get the length of the list w/ your data in it

#

if you dont already have it, use a sql query

hazy mango Jan 17, 2019, 7:36 PM

#

yeah, I do

#

Thanks

#

:~)

#

def get_colnames(cursor):
    """ Get column names for cursor's result set """
    return [coldesc[0] for coldesc in cursor.description]


def get_results():
    cursor.execute("""SELECT * FROM results""")
    colnames = get_colnames(cursor)
    data = [dict(zip(colnames, row)) for row in cursor.fetchall()]
    num_columns = len(data)
    
    fail0 = len([row for row in data if row['n0'] == 0])
    fail1= len([row for row in data if row['n1'] == 0])
    fail2= len([row for row in data if row['n2'] == 0])
    fail3= len([row for row in data if row['n3'] == 0])
    fail4= len([row for row in data if row['n4'] == 0])
    fail5= len([row for row in data if row['n5'] == 0])
    fail6= len([row for row in data if row['n6'] == 0])
    fail7= len([row for row in data if row['n7'] == 0])
    fail8= len([row for row in data if row['n8'] == 0])
    fail9= len([row for row in data if row['n9'] == 0])
    fail10= len([row for row in data if row['n10'] == 0])

    success0 = len([row for row in data if row['n0'] != 0])
    success1 = len([row for row in data if row['n1'] != 0])
    success2 = len([row for row in data if row['n2'] != 0])
    success3 = len([row for row in data if row['n3'] != 0])
    success4 = len([row for row in data if row['n4'] != 0])
    success5 = len([row for row in data if row['n5'] != 0])
    success6 = len([row for row in data if row['n6'] != 0])
    success7 = len([row for row in data if row['n7'] != 0])
    success8 = len([row for row in data if row['n8'] != 0])
    success9 = len([row for row in data if row['n9'] != 0])
    success10 = len([row for row in data if row['n10'] != 0])
```Anything I can change here to make shorter? @harsh pulsar

harsh pulsar Jan 17, 2019, 8:04 PM

#

eh, depends on what youre trying to do

#

explicit is better than implicit, as they say

hazy mango Jan 17, 2019, 8:16 PM

#

True

harsh pulsar Jan 17, 2019, 8:24 PM

#

@hazy mango

colnames = ['n%i' % i for i in range(10)]
fails = {colname: [row for row in data if data[colname] == 0] for colname in colnames}
successes =  # exercise left for the reader

hazy mango Jan 17, 2019, 8:24 PM

#

Thanks

copper sphinx Jan 17, 2019, 10:33 PM

#

Ideas what's failing?

Jan 17 23:32:16 cloud-db-01 postgresql@10-main[1460]: Error: /usr/lib/postgresql/10/bin/pg_ctl /usr/lib/postgresql/10/bin/pg_ctl start -D /var/lib/postgresql/10/main -l /var/log/postgresql/postgresql-10-main.log -s -o  -c config_file="/etc/postgresql/10/main/postgresql.conf"  exited with status 1:
Jan 17 23:32:16 cloud-db-01 systemd[1]: postgresql@10-main.service: Can't open PID file /var/run/postgresql/10-main.pid (yet?) after start: No such file or directory
Jan 17 23:32:16 cloud-db-01 systemd[1]: postgresql@10-main.service: Failed with result 'protocol'.
Jan 17 23:32:16 cloud-db-01 systemd[1]: Failed to start PostgreSQL Cluster 10-main.

spare geode Jan 18, 2019, 12:39 AM

#

I think it cant find the file?

copper sphinx Jan 18, 2019, 1:10 AM

#

yeah, it's a bug in Ubuntu.

ionic pecan Jan 18, 2019, 7:04 AM

#

there should be RuntimeDirectory in the service file

#

if not you can add that with systemctl edit

hazy mango Jan 18, 2019, 9:10 PM

#

    fails = {colname: [row for row in data if data[colname] == 0] for colname in colnames}
TypeError: list indices must be integers or slices, not str``` @harsh pulsar error from code you gave me yesterday

patent glen Jan 18, 2019, 9:12 PM

#

you probably want row[colname]

hazy mango Jan 18, 2019, 9:15 PM

#

thanks

#

    print(f"No.\t|\tFails\t|\tSuccesses\n"
          f"0\t|\t{fail0}\t|\t{success0}\n"
          f"1\t|\t{fail1}\t|\t{success1}\n"
          f"2\t|\t{fail2}\t|\t{success2}\n"
          f"3\t|\t{fail3}\t|\t{success3}\n"
          f"4\t|\t{fail4}\t|\t{success4}\n"
          f"5\t|\t{fail5}\t|\t{success5}\n")```Is there a better way of doing this? A way of doing this in one line using a list comp or something?

gilded narwhal Jan 18, 2019, 9:22 PM

#

you could either change your approach to storing fails and successes in lists which you can then index in a loop

hazy mango Jan 18, 2019, 9:22 PM

#

fails is stored in a list

gilded narwhal Jan 18, 2019, 9:22 PM

#

or you could try locals()[f'fail{n}']

#

oh ok. well if you put successes in a list then you can do this:

for i in range(5):
    print(i, fails[i], '|', successes[i], sep='\t')

hazy mango Jan 18, 2019, 9:24 PM

#

Also, I don't like how I'm getting success. py success0 = len([row for row in data if row['n0'] != 0])

#

That means it treats any amount of successes as 1 success

#

So I can succeed say 7 times per loop, but it will count that as 1

gilded narwhal Jan 18, 2019, 9:25 PM

#

you could switch that to an any() with just a generator instead of a list

#

any(True for row in data if row['n0'] != 0)

#

something like that

hazy mango Jan 18, 2019, 9:27 PM

#

I don't think I'm explaining properly.

What I mean is it says here that n0,n1 and n2 succeed 6 times

📎 unknown.png

#

But n0 succeeded 2+1+1+2+2+1 (9)

#

And n1 succeeded 1+2+2+2+1+1 (9)

#

And n2 succeeded 1+1+2+1+1+1 (7)

#

But because the amount of numbers that != 0 is 6, it says 6 successes

gilded narwhal Jan 18, 2019, 9:29 PM

#

any will return a boolean

slate spire Jan 18, 2019, 9:29 PM

#

do you how many are != 0 or the sum of the non-zero numbers

hazy mango Jan 18, 2019, 9:30 PM

#

What I want is the sum of the non-zero numbers, what I have is how many are != 0

gilded narwhal Jan 18, 2019, 9:30 PM

#

ah ok. sum(row). there

hazy mango Jan 18, 2019, 9:31 PM

#

Doesn't work

#

Because it returns a dict

gilded narwhal Jan 18, 2019, 9:31 PM

#

oh right it's a dict. sum(row.values())

hazy mango Jan 18, 2019, 9:32 PM

#

    success0 = sum([row.values() for row in data if row['n0'] != 0])
TypeError: unsupported operand type(s) for +: 'int' and 'dict_values'```

#

    success0 = sum([row for row in data if row['n0'] != 0])
TypeError: unsupported operand type(s) for +: 'int' and 'dict'```

gilded narwhal Jan 18, 2019, 9:32 PM

#

no, just sum(row.values()) by itself.

slate spire Jan 18, 2019, 9:32 PM

#

woah woah woah woah woah

#

ok in this instance you want something like um, sum of all row['n0'] right?

gilded narwhal Jan 18, 2019, 9:33 PM

#

my understanding is that they want the sum of all non-zero values in a row. and each row is a dict.

#

that about right?

hazy mango Jan 18, 2019, 9:34 PM

#

^

slate spire Jan 18, 2019, 9:34 PM

#

yeah, but i assume each rowdict was like {'n0': 5, 'n1': 1, ...}...?

hazy mango Jan 18, 2019, 9:35 PM

#

{'n0': 2, 'n1': 1, 'n2': 1, 'n3': 0, 'n4': 1, 'n5': 1, 'n6': 2, 'n7': 1, 'n8': 0, 'n9': 1, 'n10': 0} {'n0': 0, 'n1': 2, 'n2': 1, 'n3': 3, 'n4': 0, 'n5': 0, 'n6': 1, 'n7': 0, 'n8': 1, 'n9': 1, 'n10': 1} {'n0': 0, 'n1': 0, 'n2': 0, 'n3': 1, 'n4': 3, 'n5': 0, 'n6': 2, 'n7': 1, 'n8': 1, 'n9': 2, 'n10': 0} {'n0': 1, 'n1': 0, 'n2': 2, 'n3': 0, 'n4': 1, 'n5': 0, 'n6': 2, 'n7': 2, 'n8': 1, 'n9': 1, 'n10': 0} {'n0': 1, 'n1': 2, 'n2': 1, 'n3': 3, 'n4': 1, 'n5': 0, 'n6': 0, 'n7': 1, 'n8': 0, 'n9': 0, 'n10': 1} {'n0': 0, 'n1': 2, 'n2': 1, 'n3': 0, 'n4': 0, 'n5': 0, 'n6': 3, 'n7': 0, 'n8': 1, 'n9': 1, 'n10': 2} {'n0': 0, 'n1': 0, 'n2': 0, 'n3': 1, 'n4': 0, 'n5': 1, 'n6': 1, 'n7': 2, 'n8': 3, 'n9': 2, 'n10': 0} {'n0': 2, 'n1': 0, 'n2': 0, 'n3': 1, 'n4': 0, 'n5': 0, 'n6': 2, 'n7': 0, 'n8': 1, 'n9': 1, 'n10': 3} {'n0': 2, 'n1': 1, 'n2': 1, 'n3': 1, 'n4': 0, 'n5': 1, 'n6': 0, 'n7': 4, 'n8': 0, 'n9': 0, 'n10': 0} {'n0': 1, 'n1': 1, 'n2': 0, 'n3': 1, 'n4': 1, 'n5': 2, 'n6': 0, 'n7': 1, 'n8': 0, 'n9': 2, 'n10': 1}```is the value of `data`.
```py
[2, 0, 0, 1, 1, 0, 0, 2, 2, 1]```is the values of `data['n0']`

#

I think what I want to do is sum(data['n0'])

#

But that doesn't work because data['n0'] is a list

gilded narwhal Jan 18, 2019, 9:37 PM

#

sum(row['n0'] for row in data). is that what you're looking for? sum of all n0 values in each row?

#

and so on for n1, n2, etc?

hazy mango Jan 18, 2019, 9:38 PM

#

    success0 = [sum(row['n0']) for row in data]
TypeError: 'int' object is not iterable```

gilded narwhal Jan 18, 2019, 9:38 PM

#

paranthesis goes on the end, after data

hazy mango Jan 18, 2019, 9:39 PM

#

Yep

#

That works

#

:~)

gilded narwhal Jan 18, 2019, 9:39 PM

#

row['n0'] for row in data is a generator comprehension

#

sweet

hazy mango Jan 18, 2019, 9:39 PM

#

So

#

Now I've got it printing

#

[9]

#

I want it to print just 9

#

Can I just do like success0[0]?

gilded narwhal Jan 18, 2019, 9:40 PM

#

im confused

#

why are you putting the sum in a list?

hazy mango Jan 18, 2019, 9:40 PM

#

I don't want it in a list

gilded narwhal Jan 18, 2019, 9:40 PM

#

then remove the square brackets.

slate spire Jan 18, 2019, 9:41 PM

#

you don't need the [] around the sum

hazy mango Jan 18, 2019, 9:41 PM

#

Yeah, sorry. I needed it before I moved the ) to the correct position

gilded narwhal Jan 18, 2019, 9:41 PM

#

right, sum is just a function.

hazy mango Jan 18, 2019, 9:43 PM

#

See I'm getting so confused over what I need to do here for the next part

#

This is my table...

📎 unknown.png

#

Wait, is rows going up+down or left+right?

gilded narwhal Jan 18, 2019, 9:45 PM

#

rows are horizontal, columns are vertical

hazy mango Jan 18, 2019, 9:45 PM

#

Ok, so

#

Each ROW is 10 generations of a random number

#

Each COLUMN is the number

#

What I want to do, is find the probability of a number being chosen by the random module by reading from this, but I'm getting really confused with the maths behind it

#

I think I need to divide each ROW by 10, and then divide the sum of all COLUMNS by the number of COLUMNS?

#

So chance of getting a 0 would be ((2/10) + (0/10) + (0/10) + (1/10) + (1/10) + (0/10) + (0/10) + (2/10) + (2/10) + (1/10))/10

#

Which is 0.09, so the probability of a 0 is 0.09

#

Is that maths right?

gilded narwhal Jan 18, 2019, 9:51 PM

#

that doesn't look right at all to me.

hazy mango Jan 18, 2019, 9:51 PM

#

As a percentage it would be 9% (0.09x100)

gilded narwhal Jan 18, 2019, 9:51 PM

#

0.09 means 9 percent. meaning 9 out of every 100. you have 100 numbers in that table and there are more than 9 zeros.

hazy mango Jan 18, 2019, 9:52 PM

#

No there isn't

#

there IS 9 zeros

#

📎 unknown.png

#

2+0+0+1+1+0+0+2+2+1 = 9

gilded narwhal Jan 18, 2019, 9:53 PM

#

oh i see, n0 represents the number of times the generator generated 0

hazy mango Jan 18, 2019, 9:53 PM

#

^ yeah, sorry

#

So is the maths right then?

gilded narwhal Jan 18, 2019, 9:53 PM

#

im not really sure. stats and maths in general isnt my strong suit.

hazy mango Jan 18, 2019, 9:54 PM

#

It's not mine either xD

#

But it seems right

#

Since, there's 10 possibilities

#

So the percentage would be about 10% for each number

#

And 9% is pretty much 10% xD

#

Yeah, I've done that for all of them

#

And it adds up to 100 so it must be right

#

Thanks @gilded narwhal :~)

gilded narwhal Jan 18, 2019, 9:59 PM

#

👍

vestal geyser Jan 18, 2019, 10:06 PM

#

Hey, weird question incoming since I started with databases some days ago.
is there a way to compare the first number of a BigInt with a variable?
Like the LEFT Function but with a BigInt

hazy mango Jan 18, 2019, 10:12 PM

#

    print(f"No.\t|\tSuccess\n"
          f"0\t|\t{success0}%\n"
          f"1\t|\t{success1}%\n"
          f"2\t|\t{success2}%\n"
          f"3\t|\t{success3}%\n"
          f"4\t|\t{success4}%\n"
          f"5\t|\t{success5}%\n"
          f"6\t|\t{success6}%\n"
          f"7\t|\t{success7}%\n"
          f"8\t|\t{success8}%\n"
          f"9\t|\t{success9}%\n"
          f"10\t|\t{success10}%"
          )```
The result of the above code is below.  How can I change it so that things are better in-line with each other? I thought `\t` would do this, but apparently not

📎 unknown.png

harsh pulsar Jan 19, 2019, 12:07 AM

#

@vestal geyser can you cast to string first? seems silly but possibly the best way

vestal geyser Jan 19, 2019, 12:11 AM

#

is there a way via sql?

harsh pulsar Jan 19, 2019, 4:42 AM

#

CAST ?

vestal geyser Jan 19, 2019, 9:56 AM

#

Ah yes perfect, thank you!

copper sphinx Jan 19, 2019, 12:20 PM

#

Does anyone of you know if there's a async compability library for accessing Microsoft SQL or should I just use http://www.pymssql.org/en/stable/ ?

viral crag Jan 19, 2019, 2:48 PM

#

I had a quick look but I couldn't find anything

#

you could probably make use of the executors though, if you really needed that

#

https://docs.python.org/3/library/asyncio-eventloop.html#asyncio.loop.run_in_executor

#

@copper sphinx

copper sphinx Jan 19, 2019, 2:50 PM

#

Needed what? You mean Microsoft SQL as I think that code would be blocking?

viral crag Jan 19, 2019, 2:50 PM

#

asyncio support

#

I mean I assume that's what you're using anyway

copper sphinx Jan 19, 2019, 2:50 PM

#

Umm, no.

viral crag Jan 19, 2019, 2:51 PM

#

Then what are you using?

#

trio, curio? gevent?

copper sphinx Jan 19, 2019, 2:51 PM

#

Main database is done using asyncpg, but I would need to get access to Microsoft SQL too.

viral crag Jan 19, 2019, 2:51 PM

#

okay yes, you're using asyncio

copper sphinx Jan 19, 2019, 2:51 PM

#

Ah, you meant that okey.

viral crag Jan 19, 2019, 2:51 PM

#

Then yeah, the executor method is probably what you want

#

Extract whatever you're aiming to do into a bunch of functions

#

https://docs.python.org/3/library/concurrent.futures.html#concurrent.futures.ThreadPoolExecutor

#

Here's an example of using the executor directly

#

it's a little convoluted, though

#

but essentially you factor your database operations out into functions and submit them to an executor

#

submitting them will give you a Future

#

and then you should be able to await that Future

copper sphinx Jan 19, 2019, 2:54 PM

#

Unfortunately, I have no idea how that would actually work.

viral crag Jan 19, 2019, 2:54 PM

#

Well, for lack of a better example, let's say you're a company that sells widgets and you want to put each widget sale into your MSSQL database

#

so you might have a function that adds each sale to the database, eg

#

def add_widget(widget_name, widget_amount):
    # ...

#

and in that function you add your row to the database

#

then later on, you want to do this asynchronously, so you might...

#

executor = ThreadPoolExecutor()

async def my_function():
    await executor.submit(add_widget, "widget a", 4)

copper sphinx Jan 19, 2019, 2:56 PM

#

Ooh, okey that helped for me to understand it better. :)

viral crag Jan 19, 2019, 2:56 PM

#

note that we pass the function directly to submit, followed by its arguments

#

instead of calling it ourselves

#

the executor will do that for you

copper sphinx Jan 19, 2019, 2:57 PM

#

Yes, I noticed.

viral crag Jan 19, 2019, 2:57 PM

#

That's the approach I'd recommend

copper sphinx Jan 19, 2019, 2:57 PM

#

Alright, thank you for the help. :)

viral crag Jan 19, 2019, 2:58 PM

#

No problem, good luck \o/

#

I have a question for this channel myself, as it happens

#

I'm working on a little finance tracking app to train myself up with Qt, but I'm stuck on data storage - I think I want to go SQLite, but I'm wondering if it's better to store transactions under accounts in one big transactions table, or whether it would be better to have a table per account instead

#

and if the latter, how I might get that to work with something like peewee

ionic pecan Jan 19, 2019, 3:16 PM

#

i would create an accounts table and reference the account ID in the transaction entries

#

But note that sqlite is a bit messy when it comes to fk relationships by default; you need to enable them explicitly for sqlite to validate the constraints

viral crag Jan 19, 2019, 3:16 PM

#

hmm, I wonder if peewee will do that for me

#

Alright, that's what I was expecting to hear since peewee and other ORMs aren't really designed to deal with dynamic models

#

Does sqlite actually handle giant tables reasonably well?

#

Actually I've gotta do some baking now, I'll check back later

ionic pecan Jan 19, 2019, 3:46 PM

#

for anything that's supposed to handle "giant tables" i wouldn't even bother with sqlite

viral crag Jan 19, 2019, 4:09 PM

#

@ionic pecan The problem is what else you'd want to use though, for local file storage of this type of data

#

The structure does very much suit sql

ionic pecan Jan 19, 2019, 4:11 PM

#

well then you don't really have any choice

viral crag Jan 19, 2019, 4:12 PM

#

Hmm, I guess so

#

Well, thanks

#

Maybe I should do something like splitting into files per year or something

tropic bloom Jan 19, 2019, 4:15 PM

#

sorry if im interupting

#

i have a few queries re connecting to databases

viral crag Jan 19, 2019, 4:15 PM

#

Go for it, gotta get back to baking

tropic bloom Jan 19, 2019, 4:16 PM

#

i was told avoid pyodbc because of auth token requirements connecting to my work db

#

we use ms sql, and (still) on CRM 2011

#

regardless - i get the error that my sql isnt a valid query

#

so are there any other alternatives to integrating sql directly or the script file into a py function?

#

i was told look at py-LINQ

#

issue is i already have, say, 100+ different sql queries and would just want to call them, read the output into a pandas DF and work from there

copper sphinx Jan 19, 2019, 4:19 PM

#

"5. List all product names and the value of each product in the warehouse. Rename the calculated attribute as 'ValueInStock'"

This returns only one, and I have no ideas why?

SELECT
    ProductName, sum(UnitPrice) as ValueInStock
FROM 
    products
WHERE
    products.UnitsInStock >= 100

viral crag Jan 19, 2019, 4:20 PM

#

@tropic bloom pymssql maybe?

tropic bloom Jan 19, 2019, 4:20 PM

#

group by productname

#

?

viral crag Jan 19, 2019, 4:20 PM

#

That's what samip there is using

tropic bloom Jan 19, 2019, 4:21 PM

#

pymssql looks like it uses pyodbc conx as well

#

or that its a dependancy

#

i cant really test it as im out of work rn but was enquiring

#

cursor.execute("""
IF OBJECT_ID('persons', 'U') IS NOT NULL
    DROP TABLE persons```

#

instead of hardcoding sql

#

cant i just call a script in the directory

viral crag Jan 19, 2019, 4:22 PM

#

Are you asking for a solution that doesn't require you to write SQL?

#

Because that'll probably need a big restructure

tropic bloom Jan 19, 2019, 4:22 PM

#

i dont mind copying in the sql

copper sphinx Jan 19, 2019, 4:22 PM

#

@viral crag Wait, what about me?

viral crag Jan 19, 2019, 4:23 PM

#

I mean yeah if you have some powershell or bash script or something you could just run it

#

@copper sphinx You were answered

copper sphinx Jan 19, 2019, 4:23 PM

#

Ah.

viral crag Jan 19, 2019, 4:23 PM

#

..but you likely want an ORM instead of just running scripts

tropic bloom Jan 19, 2019, 4:23 PM

#

samip try groupby it should work

#

heres the scenario

#

im checking one HUGE table

#

for records marked as 'new'

#

each SQL script uses different filters for different locations

copper sphinx Jan 19, 2019, 4:24 PM

#

Agent Rho, thank you. That's what I was missing. :)

tropic bloom Jan 19, 2019, 4:24 PM

#

which then spits out a list. what i want, is for that list to do into a DF, then write to CSV. rinse and repeat for locations.

#

so, 1 py doc, 10 SQL queries acted on

viral crag Jan 19, 2019, 4:25 PM

#

Right okay

#

That sounds simple enough

tropic bloom Jan 19, 2019, 4:25 PM

#

i cant even get past the 'not valid sql query' issue

viral crag Jan 19, 2019, 4:25 PM

#

The problem you have is that you need some kind of auth token?

#

I haven't worked with mssql in yonks, btw

tropic bloom Jan 19, 2019, 4:25 PM

#

seems so

viral crag Jan 19, 2019, 4:26 PM

#

Can you get that token from the SQL server admin tools?

tropic bloom Jan 19, 2019, 4:26 PM

#

we need it for our AI priority assignment

#

i could

#

but im too new i think they dont like me asking too many questions

#

my boss told me use LINQ

#

:/

#

ill try pymssql

#

if i can find the error message ill post it

viral crag Jan 19, 2019, 4:27 PM

#

Linq isn't really.. That's a C# thing

tropic bloom Jan 19, 2019, 4:28 PM

#

py-linq is a port

viral crag Jan 19, 2019, 4:28 PM

#

Yeah but either way you need to connect to the database

tropic bloom Jan 19, 2019, 4:32 PM

#

eh sorry

#

im not exactly helping minus hte errors. ill try some other queries tomorrow

frozen fossil Jan 19, 2019, 7:04 PM

#

i have a sqlite database with a table it has a Int in a row called ids how can i check if 1234 is in that row? so if 1234 is in ids do something

viral crag Jan 19, 2019, 7:12 PM

#

Try to select that row

#

If you don't get a row back, then it doesn't exist

frozen fossil Jan 19, 2019, 7:25 PM

#

thork but I don't want to check if the row exists i wanna check if 1234 is in that

viral crag Jan 19, 2019, 7:28 PM

#

You still have to do it with a selection

#

It depends on the SQL dialect

#

But I have to wonder what your use-case for this actually is

frozen fossil Jan 19, 2019, 7:39 PM

#

I wanna check if a id is already in my db

viral crag Jan 19, 2019, 7:40 PM

#

why do you have to check the start then?

#

just check the whole thing?

frozen fossil Jan 19, 2019, 7:48 PM

#

Wym check the whole thing

viral crag Jan 19, 2019, 7:52 PM

#

ah right okay

#

I misread

#

so what do you have to work with already, just the ID?

frozen fossil Jan 19, 2019, 7:54 PM

#

Id and some other rows

#

Name id balance

viral crag Jan 19, 2019, 7:55 PM

#

so you have an ID and a bunch of rows, and you want to find the row with that specific ID?

frozen fossil Jan 19, 2019, 8:08 PM

#

either I don't understand how they are called or the row is called ID and it's an int

#

And I Insert many different ids to the row ID where I wanna check if that ID is already in use

viral crag Jan 19, 2019, 8:09 PM

#

The only thing I can tell you from that is that you will have to either run a select query against the table, or iterate over each row that you already have in memory with a for loop

copper sphinx Jan 19, 2019, 8:18 PM

#

DeprecationWarning: Using or importing the ABCs from 'collections' instead of from 'collections.abc' is deprecated, and in 3.8 it will stop working
  import pymssql

What does people think of this? Should I be using pymssql then?

quasi holly Jan 19, 2019, 9:52 PM

#

Is there a way for my bot and my friend's bot to use one database somehow if hosted on different machines?

copper sphinx Jan 20, 2019, 12:21 AM

#

Yes, it's possible but not recommended. @quasi holly

spare geode Jan 20, 2019, 12:35 AM

#

I recommened having a text file

#

And reading and writing into it

#

That can then be posted wherever

slate spire Jan 20, 2019, 12:39 AM

#

wait, why?

spare geode Jan 20, 2019, 12:44 AM

#

Having a text file?

#

Surely its easier than sending the database over and over

viral crag Jan 20, 2019, 12:46 AM

#

Usually databases aren't just a local file

#

You'd have a database server and connect to that or abstract it away with a webapp

spare geode Jan 20, 2019, 12:46 AM

#

DB browser , you can connect that?

viral crag Jan 20, 2019, 12:46 AM

#

No file sharing required.

#

No idea what DB browser is.

spare geode Jan 20, 2019, 12:47 AM

#

Form of database

#

Good for Sql

copper sphinx Jan 20, 2019, 1:00 AM

#

Ideas?

Code:

# Auhtor: Sami "samip5/samip537" Mäntysaari
# Purpose: This file is for storing Microsoft SQL related tasks and queries.

import config
import pyodbc
import asyncio
import concurrent.futures

mssql_host = config.mssql_host
mssql_database = config.mssql_database
mssql_user = config.mssql_user
mssql_pass = config.mssql_password
driver = '{ODBC Driver 17 for SQL Server}'


def get_stats(user_id):
    cnxn = pyodbc.connect(
        'DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + mssql_host + ';DATABASE=' + mssql_database + ';UID=' + mssql_user + ';PWD=' + mssql_pass)
    cursor = cnxn.cursor()
    cursor.fetch("""
    select count(*) cnt from discord_messages dm
inner join discord_channels dc on dm.channel_id = dc.channel_id
where [user_id] = $1
    """, user_id)
    return cnxn.commit()


async def get_stats_executor(user_id):
    loop = asyncio.get_running_loop()
    with concurrent.futures.ThreadPoolExecutor() as pool:
        result = await loop.run_in_executor(pool, get_stats(user_id))
        return result

Traceback:

Traceback (most recent call last):
  File "Z:\Varasto\Programming\Python\Parempi-Bot\venv\lib\site-packages\discord\ext\commands\core.py", line 62, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\Sami\Documents\Parempi-Bot\cogs\profile.py", line 14, in dev
    await mssql_db.get_stats_executor(user_id)
  File "C:\Users\Sami\Documents\Parempi-Bot\cogs\utils\mssql_db.py", line 31, in get_stats_executor
    result = await loop.run_in_executor(pool, get_stats(user_id))
  File "C:\Users\Sami\Documents\Parempi-Bot\cogs\utils\mssql_db.py", line 18, in get_stats
    'DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + mssql_host + ';DATABASE=' + mssql_database + ';UID=' + mssql_user + ';PWD=' + mssql_pass)
pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

#

Or what could be a problem with using pymssql:

Traceback (most recent call last):
  File "src\pymssql.pyx", line 636, in pymssql.connect
  File "src\_mssql.pyx", line 1957, in _mssql.connect
  File "src\_mssql.pyx", line 676, in _mssql.MSSQLConnection.__init__
  File "src\_mssql.pyx", line 1683, in _mssql.maybe_raise_MSSQLDatabaseException
_mssql.MSSQLDatabaseException: (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (<censored>.database.windows.net:1433)\n')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "Z:\Varasto\Programming\Python\Parempi-Bot\venv\lib\site-packages\discord\ext\commands\core.py", line 62, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\Sami\Documents\Parempi-Bot\cogs\profile.py", line 14, in dev
    await mssql_db.get_stats_executor(user_id)
  File "C:\Users\Sami\Documents\Parempi-Bot\cogs\utils\mssql_db.py", line 29, in get_stats_executor
    result = await loop.run_in_executor(pool, get_stats(user_id))
  File "C:\Users\Sami\Documents\Parempi-Bot\cogs\utils\mssql_db.py", line 16, in get_stats
    conn = pymssql.connect(mssql_host, mssql_user, mssql_pass,mssql_database)
  File "src\pymssql.pyx", line 642, in pymssql.connect
pymssql.OperationalError: (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (<censored>.database.windows.net:1433)\n')

spare geode Jan 20, 2019, 1:18 AM

#

Right i have a slight issue

#

I have an embed message and it prints but it shows the link to the photo

#

Is there a way i could stop that?

#

Takes the photo from Database

silver vortex Jan 20, 2019, 6:12 AM

#

Hi, i'm using pymongo and have some questions about it, if I insert a document into the database, then later add another key and value into the data i'm inserting, would it update the existing documents with the key and value? I know it's possible in NodeJS with mongoose

tropic bloom Jan 20, 2019, 10:00 AM

#

@viral crag I managed to connect at last

#

Pymssql was the trick

viral crag Jan 20, 2019, 10:00 AM

#

Good stuff

tropic bloom Jan 20, 2019, 10:01 AM

#

Although I do get a collections.abc error

viral crag Jan 20, 2019, 10:04 AM

#

Traceback?

tropic bloom Jan 20, 2019, 10:07 AM

#

Using or importing the ABCs from 'collections' is depreciated

#

I can't copy it off my work machine

#

But that's the jist

viral crag Jan 20, 2019, 10:09 AM

#

That's not an error, that's a warning

#

Also the word is deprecated :P

tropic bloom Jan 20, 2019, 10:10 AM

#

Yes

#

Thay

#

iPhone loves to think it's fixing my text

viral crag Jan 20, 2019, 10:11 AM

#

It's telling you that the approach you're using is no longer supported and may be removed in a later version

tropic bloom Jan 20, 2019, 10:12 AM

#

Yup

#

I'm scared of it 🤣

#

Even if I put from collections.abc import iterable it pops up

#

🤔

viral crag Jan 20, 2019, 10:13 AM

#

Probably because it's in a different module now, likely just abc

tropic bloom Jan 20, 2019, 2:52 PM

#

im debating the worthiness of pycharm 🤔

#

given its use to R and SQL in the same IDE

spare geode Jan 20, 2019, 3:33 PM

#

https://pokemondb.net/pokedex/all
is there a way i can iterate all through these and put it all into a db/

Pokémon Pokédex: list of Pokémon with stats

ionic pecan Jan 20, 2019, 3:34 PM

#

yes

spare geode Jan 20, 2019, 3:36 PM

#

Could u explain how i would go around it?

tropic zealot Jan 20, 2019, 3:37 PM

#

Only problem is that the Pokemon Database isn't actually something you can pull from.

#

https://pokemondb.net/about

In general, no. All written content, site design and data display/layouts have had a huge amount of research and effort put into them to make as easy to read as possible. Do not steal our content!```

About Pokémon Database

#

Instead, they attempt to redirect you either the PokeAPI (https://pokeapi.co/) or the Veekun Pokedex (https://github.com/veekun/pokedex) for your scraping needs.

tropic bloom Jan 20, 2019, 3:41 PM

#

herse a pymssql question

#

instead of `cursor.execute("""SQL QUERY HERE""")

#

can i not call a class

spare geode Jan 20, 2019, 3:42 PM

#

@tropic zealot ah so i wouldnt be able to take the images and names

#

yikes

tropic zealot Jan 20, 2019, 3:43 PM

#

Not from there, no, since they kinda forbid it.
However, the Veekun github is looking kinda promising, or if you're into REST APIs, I've worked a tiny bit with PokeAPI.

spare geode Jan 20, 2019, 3:43 PM

#

havent Worked with like anything par discordpy and databases

#

https://pokedex.org/

Pokedex.org

A mini-encyclopedia of Pokémon species, types, evolutions, and moves.

#

Much easier

spare geode Jan 20, 2019, 4:06 PM

#

📎 unknown.png

#

how would i get that?

#

📎 unknown.png

ionic pecan Jan 20, 2019, 5:29 PM

#

try https://pokeapi.co/

PokéAPI

An open RESTful API for Pokémon data

gleaming frost Jan 20, 2019, 5:51 PM

#

That's mad

dire light Jan 20, 2019, 6:13 PM

#

@viral crag its @tropic bloom but on my work account lol

#

would you see any problems with this

#

df_MDIV = pd.read_sql(OL_MDIV, conn)

OL_MDIV = FreshOL + MenaDIV

MDIV_list = list()
for x in range(len(df_MDIV.index)):
    MDIV_list.append('A1')

MDIV2 = pd.DataFrame()
MDIV2['HC3']=MDIV_list

frames_MDIV_Fresh_OL = [df_MDIV, MDIV2]
MDIV_Fresh_OL = pd.concat(frames_MDIV_Fresh_OL, axis=1)

print('MDIV_Fresh_OL = ' + str(df_MDIV.shape[0]))
MDIV_Fresh_OL.to_csv('MDIV_Fresh_OL.csv', encoding='utf-8', index=False)```

#

where the SQL is literally a string = in this case, OL_MDIV

viral crag Jan 20, 2019, 6:19 PM

#

Nothing immediately obvious, but these variable names are incredibly hard to read

dire light Jan 20, 2019, 6:21 PM

#

I am aware

#

theres going to be upwards of 30 at least

#

any way I can possibly add in class/functions to this, or not really given the amount of variables

quick hill Jan 20, 2019, 7:50 PM

#

im starting to learn POSTGRESQL and is there a good tutoral for learning how it works

#

with python

gray lodge Jan 20, 2019, 9:26 PM

#

Is there a way to remove port binds from docker? I installed influxdb and need to remove before reinstalling it, however it can't be created because a port is in use. I tried several commands to list ports, but the container is gone and no ports are showing up. Is there any good documentation about docker ports?

#

The bind is on tcp 0.0.0.0:8086

viral crag Jan 20, 2019, 9:35 PM

#

That's a #414737889352744971 question, but you probably need to remove the network docker crated for that container

gray lodge Jan 20, 2019, 9:35 PM

#

I removed it and the only running container is not related.

viral crag Jan 20, 2019, 9:35 PM

#

You may also consider docker prune -af which will remove all non-running containers and images

#

Finally, make sure something else on your system isn't using that port

gray lodge Jan 20, 2019, 9:36 PM

#

Nothing is

#

Docker also doesn't recognize prune

viral crag Jan 20, 2019, 9:37 PM

#

Did you check your ports with eg netstat?

#

netstat -tulpn | grep port

gray lodge Jan 20, 2019, 9:39 PM

#

I have 2 established connections using localhost:8086 for tcp6

viral crag Jan 20, 2019, 9:41 PM

#

Are they listening?

gray lodge Jan 20, 2019, 9:42 PM

#

Established

viral crag Jan 20, 2019, 9:45 PM

#

Then I have no idea

gray lodge Jan 20, 2019, 9:46 PM

#

Thanks for helping though

#

I may just start over

#

I got it by running:

netstat -ltnp | grep -w ':8086'
kill -9 [PID]

#

Where PID is from the first command

ornate isle Jan 20, 2019, 10:57 PM

#

@gray lodge you do docker image prune, docker container prune and docker network prune. if the containers are not running they shouldnt be blocking the ports unless a docker network is active, and docker network list following by inspecting any networks should take care of that

#

ive never really seen issues before with ports still being blocked without the containers running, so something somewhere is using them

#

i personally tend to bind containers to nondefault host ports so i never block a non-container instance of an application or confuse myself

gray lodge Jan 21, 2019, 12:33 AM

#

Thank you for that information. I am still new to this, but this should help me learn docker.

quiet ermine Jan 21, 2019, 1:47 AM

#

for websites, would you ever need anything else than sqlite unless you are planning to make a big website?

ornate isle Jan 21, 2019, 5:42 AM

#

@quiet ermine so the fundamental problem with sqlite is that it's file-based. it's a single file.

#

this means you can't share it between multiple app/server instances, it can't be accessed by multiple clients at once, it is not a server, etc

#

so generally speaking, sqlite is great during dev, but you almost never want it in production.

#

that said, chances are you are using an ORM or some database library that can switch between sql dialects easily

#

lots of people use sqlite for local dev but postgres in production

#

which db libs and ORMs can usually handle nicely as long as you arent using custom stuff that is specific to one of those db systems

#

so you can absolutely run a "big" site with sqlite, but you couldnt distribute the app. and that is a fundamental aspect of modern apps tehse days

#

there is the rqlite project, which is a distributed sqlite server, and for a couple years ive been curious to use it, but ive only just lightly played around with it: https://github.com/rqlite/rqlite

GitHub

rqlite/rqlite

The lightweight, distributed relational database built on SQLite. - rqlite/rqlite

#

uses raft for consensus

#

but to be honest, you probably want to be running postgres in production. use sqlite locally if you want. or postgres in a docker container you dont have to deal with

#

i totally understand the ease factor that comes with sqlite but the same general peace of mind can be achieve with real databases if you use containers or managed db instances, etc

copper sphinx Jan 21, 2019, 7:22 AM

#

Umm, how well does people know sqlalchemy here? I'm trying to build a API for my database, but I'm not entirely sure how I should reference my tables.

copper sphinx Jan 21, 2019, 9:26 AM

#

Okey, I fixed that problem, but now I'm hitting my head to a wall as I'm unable to figure out how I filter the results so I only get what I want, like use WHERE, but with SQLalchemy.

quiet ermine Jan 21, 2019, 9:55 AM

#

👌 @ornate isle I'll use SQLite for testing and probably a bit of production (for very small projects, will probably be the first ones I properly do)

tropic bloom Jan 21, 2019, 12:05 PM

#

Use DATABASENAME
Select a b c
From TABLENAME
Where (filter on a) and (filter on b) and (getdate()-14 on c)

Order by a c b

#

@copper sphinx that's a general SQL query

#

Oracle, mssql etc all use the same structure

#

Ish

copper sphinx Jan 21, 2019, 12:06 PM

#

Umm, that was not my question.

tropic bloom Jan 21, 2019, 12:06 PM

#

Your question was where does "where" go

#

?

copper sphinx Jan 21, 2019, 12:07 PM

#

Not exactly as I meant it as how do I use it with SQLalcemy.

tropic bloom Jan 21, 2019, 12:09 PM

#

Oh

#

Use the .filter() method

copper sphinx Jan 21, 2019, 12:11 PM

#

If I have this

from flask import request
from flask_restful import Resource
from model import db, Messages, MessagesSchema

messages_schema = MessagesSchema(many=True)
message_schema = MessagesSchema()


class MessageResource(Resource):
    def get(self):
        messages = Messages.query.all()
        messages = messages_schema.dump(messages).data
        return {'status': 'success', 'data': messages}, 200

Where would I put the filter?

dire light Jan 21, 2019, 12:12 PM

#

https://www.pythoncentral.io/sqlalchemy-vs-orms/

Python Central

Xiaonuo Gantan

Python's SQLAlchemy vs Other ORMs | Python Central

Overview of Python ORMs As a wonderful language, Python has lots of ORM libraries besides SQLAlchemy. In this article, we are going to take a look at several popular alternative ORM libraries to better understand the big picture of the Python ORM landscape. By writing a scrip...

copper sphinx Jan 21, 2019, 12:12 PM

#

Umm?

#

Some context?

#

@dire light

tropic bloom Jan 21, 2019, 12:17 PM

#

Scroll to bottom

#

It shows query being called

#

Followed by filter

copper sphinx Jan 21, 2019, 12:18 PM

#

Ah, I see.

tropic bloom Jan 21, 2019, 12:18 PM

#

Is that not possible here?

#

Sorry

#

I prefer good ol SQL

#

😊

copper sphinx Jan 21, 2019, 12:19 PM

#

It should be possible yes.

copper sphinx Jan 21, 2019, 2:09 PM

#

I have a question, if someone could direct me to the right place, can I execute the full query directly with SQLAlchemy and if yes, how do I do so with using the db.model approach?

viral crag Jan 21, 2019, 2:11 PM

#

You're asking whether you an execute an SQL query directly with SQLAlchemy?

#

The point of SQLA is that you don't have to write SQL

#

That also gives you the advantage of being able to support multiple database types with minimal code changes

#

You should look into the way SQLA does query building, and make use of that

copper sphinx Jan 21, 2019, 2:13 PM

#

So what would be the correct way to do this then?

select count(*) cnt from discord_messages dm
inner join discord_channels dc on dm.channel_id = dc.channel_id
where [user_id] = '130685606345965568'

#

As I'm pretty much aware how I should do it with normal SQL queries, but not with SQLA.

viral crag Jan 21, 2019, 2:13 PM

#

it's something like...

#

wait

#

what is user_id

#

is that supposed to be dm.user_id?

copper sphinx Jan 21, 2019, 2:16 PM

#

It's a columm in my database.

viral crag Jan 21, 2019, 2:16 PM

#

session.query(Message.id).where(Message.user_id == "whatever").join(Channel, Channel.id == Message.id).count()

#

something like this I think

#

is this for a Discord bot?

copper sphinx Jan 21, 2019, 2:17 PM

#

No, the database is from discord data though.

viral crag Jan 21, 2019, 2:17 PM

#

Ah okay

copper sphinx Jan 21, 2019, 2:17 PM

#

This is for a API for the said database.

viral crag Jan 21, 2019, 2:17 PM

#

more info on the query API: https://docs.sqlalchemy.org/en/latest/orm/query.html

copper sphinx Jan 21, 2019, 2:18 PM

#

I checked the documentation out, but I'm stuck with understanding how I should do this as I'm currently using it like this:

messages_schema = MessagesSchema(many=True)
message_schema = MessagesSchema()


class MessageResource(Resource):
    def get(self):
        value = request.args.get('user_id')
        if value is not None:
            messages = Messages.query.filter(f'discord_messages.user_id = {value}')
            messages = messages_schema.dump(messages).data
            return {'status': 'success', 'data': messages}, 200
        else:
            messages = Messages.query.all()
            messages = message_schema.dump(messages).data
            return {'status': 'success', 'data': messages}, 200

viral crag Jan 21, 2019, 2:19 PM

#

f'discord_messages.user_id = {value}' well this is definitely not right

copper sphinx Jan 21, 2019, 2:19 PM

#

Well, that part still does work.

viral crag Jan 21, 2019, 2:19 PM

#

You should be using your model

#

Messages.user_id == value

copper sphinx Jan 21, 2019, 2:20 PM

#

Oh, wow, so I'm overcomplicating things.

viral crag Jan 21, 2019, 2:20 PM

#

You should read over the page I linked you

#

or at least ctrl+f for the things you're trying to do

copper sphinx Jan 21, 2019, 2:20 PM

#

I have done so already.

viral crag Jan 21, 2019, 2:20 PM

#

sure, but there's a filter example right on that page

#

📎 unknown.png

copper sphinx Jan 21, 2019, 2:27 PM

#

That

messages = Messages.query(Messages.message_id).where(Messages.user_id == value)\
                .join(Messages.Channel, Messages.Channel.id == Messages.message_id).count()

Gives me an error like TypeError: 'BaseQuery' object is not callable.

viral crag Jan 21, 2019, 2:27 PM

#

Since you're querying the model directly, you don't need the Messages.message_id

#

in the query call

#

and I think count might work differently..

#

although

#

what is this join about?

#

you're doing a count of messages

#

so do you even need the join?

copper sphinx Jan 21, 2019, 2:29 PM

#

Might be unnecessary at this point.

viral crag Jan 21, 2019, 2:29 PM

#

Probably what you need is more like

session.query(func.count(Messages.message_id)).where(Messages.user_id == value)

copper sphinx Jan 21, 2019, 2:32 PM

#

I don't have anything imported which that session comes off of..

viral crag Jan 21, 2019, 2:32 PM

#

that's an SQLalchemy session

copper sphinx Jan 21, 2019, 2:32 PM

#

Which is not imported.

#

from flask import request
from flask_restful import Resource
from model import db, Messages, MessagesSchema
from sqlalchemy import func

viral crag Jan 21, 2019, 2:33 PM

#

yes, you have to make them

#

# application starts
Session = sessionmaker()

# ... later
engine = create_engine('sqlite:///foo.db')
Session.configure(bind=engine)

sess = Session()

#

You can put that somewhere importable

#

generally you do want to be using sessions

#

they allow you to make use of transactions

#

transactions basically are like, sets of changes and queries

#

if you have an exception mid-transaction, the entire transaction should be rolled back to preserve your data's integrity

#

https://docs.sqlalchemy.org/en/latest/orm/session_transaction.html

copper sphinx Jan 21, 2019, 2:41 PM

#

I think that correct documentation would be http://flask-sqlalchemy.pocoo.org/2.3/

viral crag Jan 21, 2019, 2:42 PM

#

You're still using base sqlalchemy under that, but there might be a session in there somewhere, sure

#

db.session

#

yep

copper sphinx Jan 21, 2019, 2:45 PM

#

Yep, so it's already there but "hidden" ish.

viral crag Jan 21, 2019, 2:46 PM

#

Well either way, you have access to it

copper sphinx Jan 21, 2019, 2:46 PM

#

Yup.

#

AttributeError: 'BaseQuery' object has no attribute 'where' :(

#

messages = db.session.query(func.count(Messages.message_id)).where(Messages.user_id == value)

viral crag Jan 21, 2019, 2:48 PM

#

You could try .filter instead, it's basically the same thing

#

but that's pretty odd

copper sphinx Jan 21, 2019, 2:51 PM

#

That does not work like it should though.

#

2019-01-21 16:50:46,859 INFO sqlalchemy.engine.base.Engine SELECT count(discord_messages.message_id) AS count_1 
FROM discord_messages 
WHERE discord_messages.user_id = %(user_id_1)s
2019-01-21 16:50:46,859 INFO sqlalchemy.engine.base.Engine {'user_id_1': '222394927978381312'}```

viral crag Jan 21, 2019, 2:52 PM

#

I mean, I did ask if what you wanted was the count of matching messages

#

That's why we removed the join

#

so that does look correct

copper sphinx Jan 21, 2019, 2:53 PM

#

So it needs the join apperently.

viral crag Jan 21, 2019, 2:53 PM

#

Are you getting an incorrect count?

copper sphinx Jan 21, 2019, 2:53 PM

#

I'm not getting any count.

viral crag Jan 21, 2019, 2:53 PM

#

Are you actually doing anything with the return value of that call?

copper sphinx Jan 21, 2019, 2:55 PM

#

messages = db.session.query(func.count(Messages.message_id)).filter(Messages.user_id == value)
            messages = messages_schema.dump(messages).data
            return {'status': 'success', 'data': messages}, 200

This should return the count, right?

viral crag Jan 21, 2019, 2:55 PM

#

Not sure what this line is for messages = messages_schema.dump(messages).data

#

I don't think you need it if what you want is the count

#

considering messages should just be a number at that point

copper sphinx Jan 21, 2019, 2:56 PM

#

If I take that out, it errors with typerror.

TypeError: Object of type BaseQuery is not JSON serializable

viral crag Jan 21, 2019, 2:56 PM

#

okay, well why don't you print that and see what it is

#

Ah right, yeah, you need to add a specifier like .first() I think

copper sphinx Jan 21, 2019, 2:58 PM

#

It spits out

SELECT count(discord_messages.message_id) AS count_1 
FROM discord_messages 
WHERE discord_messages.user_id = %(user_id_1)s

viral crag Jan 21, 2019, 2:58 PM

#

in this case you probably want .one()

#

at the end

#

that will return exactly one result, or raise an exception

copper sphinx Jan 21, 2019, 2:59 PM

#

Yes, that works with one().

viral crag Jan 21, 2019, 2:59 PM

#

Good good

#

it's been a while since I've used SQLA to be honest

#

got most of that from skimming the first link

copper sphinx Jan 21, 2019, 3:00 PM

#

But thank you for the help. :)

viral crag Jan 21, 2019, 3:01 PM

#

No problem

copper sphinx Jan 21, 2019, 3:01 PM

#

I have been hitting my head to a wall already for some time.

obsidian leaf Jan 21, 2019, 3:55 PM

#

this is my table for all users -
def user(c):

               first text,
               last text,
               username text,
               password text,
               dob date,
               email text,
               type text,
               pic text
               )""")``` and i also have a table for those whose types are "Student" which is def student(c):
   ```c.execute("""CREATE TABLE student (
               username text,
               yeargroup integer
               )""")```
i was wondering how i would merge the 2 together while keeping all the other users who are not type: Student . Thanks in advance 😃

viral crag Jan 21, 2019, 4:01 PM

#

Generally, you wouldn't merge them

#

However in this case all you would do is put those fields into the first table

#

and let them be nullable

#

you already have a type field

copper sphinx Jan 21, 2019, 10:07 PM

#

class CountMessagesByChannel(Resource):
    def get(self):
        value = request.args.get('user_id')
        if value is not None:
            count = db.session.query(func.count(Messages.message_id, Channels.channel_name).where(Messages.user_id == value)
                                     .join(Channels.channel_id == Messages.channel_id)).one()
            return {'status': 'success', 'data': count}, 200
        else:
            return {'status': 'failed', 'data': 'This is not how it works'}, 400

What am I doing wrong now?

TypeError: __init__() takes from 1 to 2 positional arguments but 3 were given

viral crag Jan 21, 2019, 10:08 PM

#

Full traceback required

copper sphinx Jan 21, 2019, 10:09 PM

#

Traceback at: https://paste.pythondiscord.com/qahicihuci.py

viral crag Jan 21, 2019, 10:09 PM

#

Too many arguments to func.count

copper sphinx Jan 21, 2019, 10:10 PM

#

So how would I transform this query to SQLA?

select count(*) cnt, channel_name from discord_messages dm
inner join discord_channels dc on dm.channel_id = dc.channel_id
where [user_id] = '130685606345965568'
group by channel_name

viral crag Jan 21, 2019, 10:10 PM

#

you actually appear to be missing a closed paren

#

well you just need two counts

#

you have db.session.query(func.count(Messages.message_id, Channels.channel_name).where(Messages.user_id == value).join(Channels.channel_id == Messages.channel_id)).one()

#

what you probably want is db.session.query(func.count(Messages.message_id), func.count(Channels.channel_name)).where(Messages.user_id == value).join(Channels.channel_id == Messages.channel_id).one()

#

but your formatting makes this confusing

#

you've got way too much on a single line

#

so,

#

we need to split

copper sphinx Jan 21, 2019, 10:12 PM

#

Ah, so I can't do with one func.count.

viral crag Jan 21, 2019, 10:13 PM

#

let's format this better

#

you have ```py
db.session.query(
func.count(Messages.message_id, Channels.channel_name)
.where(Messages.user_id == value)
.join(Channels.channel_id == Messages.channel_id)
).one()

#

there are two problems with this

#

firstly, you tried to combine two parameters in func.count

#

secondly, you're doing the whole thing inside db.session.query

copper sphinx Jan 21, 2019, 10:14 PM

#

Which is a bad thing?

viral crag Jan 21, 2019, 10:14 PM

#

well yes, it's incorrect

copper sphinx Jan 21, 2019, 10:15 PM

#

So if not inside db.session.query, then how?

viral crag Jan 21, 2019, 10:15 PM

#

after it

#

db.session.query(
    func.count(Messages.message_id), func.count(Channels.channel_name)
).where(
    Messages.user_id == value
).join(
    Channels.channel_id == Messages.channel_id
).one()

copper sphinx Jan 21, 2019, 10:15 PM

#

Oh, wow.

viral crag Jan 21, 2019, 10:15 PM

#

that's one possible way of formatting it

#

here's another one

#

(db.session.query(func.count(Messages.message_id), func.count(Channels.channel_name))
    .where(Messages.user_id == value)
    .join(Channels.channel_id == Messages.channel_id)
    .one())

#

You get the idea

copper sphinx Jan 21, 2019, 10:18 PM

#

Interestingly enough, there's still no .where. :/

viral crag Jan 21, 2019, 10:18 PM

#

Then use .filter :P

#

it's probably something to do with flask-sqlalchemy

copper sphinx Jan 21, 2019, 10:19 PM

#

Probably, and also my query is apperently not working as it spits out AttributeError: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'selectable'.

viral crag Jan 21, 2019, 10:19 PM

#

that's not a full traceback

copper sphinx Jan 21, 2019, 10:20 PM

#

This is: https://paste.pythondiscord.com/umoyohujok.py

viral crag Jan 21, 2019, 10:20 PM

#

that'll be the join

#

and, indeed

#

it is

#

(db.session.query(func.count(Messages.message_id), func.count(Channels.channel_name))
    .join(Channels)
    .filter(Channels.channel_id == Messages.channel_id, Messages.user_id == value)
    .one())

#

So you join on a table rather than an expression

#

and then filter by your join condition

copper sphinx Jan 21, 2019, 10:23 PM

#

Oh, now that gave me much better error.

viral crag Jan 21, 2019, 10:26 PM

#

Oh? :P

copper sphinx Jan 21, 2019, 10:27 PM

#

sqlalchemy.exc.InvalidRequestError: Could not find a FROM clause to join from. Tried joining to <class 'model.Channels'>, but got: Can't find any foreign key relationships between 'discord_messages' and 'discord_channels'. I had forgotton to include a PK. Darng

viral crag Jan 21, 2019, 10:30 PM

#

whoops :P

copper sphinx Jan 21, 2019, 10:35 PM

#

Indeed, no wonder it wouldn't work.

spare geode Jan 21, 2019, 10:49 PM

#

Ok so i have a semi decent bot for discord

#

I wanna make it so each server has its own database

#

Or table?

viral crag Jan 21, 2019, 10:50 PM

#

no you don't

#

:P

spare geode Jan 21, 2019, 10:50 PM

#

Why nuut 😭😭

viral crag Jan 21, 2019, 10:51 PM

#

That's poor database design and will ultimately result in slowdowns and hard-to-manage schema

spare geode Jan 21, 2019, 10:51 PM

#

So ur saying to just let 3 servers compete for pokemon

#

I predict a riot

viral crag Jan 21, 2019, 10:51 PM

#

That's not what I said.

#

Servers have IDs, you know

#

No reason you can't have a server_id column and filter on it

spare geode Jan 21, 2019, 10:52 PM

#

I know i was joking 😅 , and i know but how would i go around having 800 pokemon for each server?

#

Actually yeah that could be good

viral crag Jan 21, 2019, 10:52 PM

#

I'm not really sure what your modus operandi is here, but assuming you have a set of pretedermined pokemon

#

you have a pokemon table with all of those in there, as well as an ID primary key that's generated for them

#

and then your "catches" are in a different table

#

referring to those pokemon only by ID

spare geode Jan 21, 2019, 10:53 PM

#

I have every pokemon , rank and if theyre owned by the user

#

So for each server id have to create 800 pokemon

viral crag Jan 21, 2019, 10:53 PM

#

No you wouldn't

#

If they're not owned, they aren't in the catches table, are they

spare geode Jan 21, 2019, 10:54 PM

#

NoNo i want it so if one user in one server owns it

#

Another server user can too

viral crag Jan 21, 2019, 10:54 PM

#

yes, I know

spare geode Jan 21, 2019, 10:54 PM

#

From another server

viral crag Jan 21, 2019, 10:54 PM

#

that doesn't contradict what I said

#

hold on, I'm going to have to draw this for you, aren't I

spare geode Jan 21, 2019, 10:55 PM

#

I think so :3

#

📎 image0.png

viral crag Jan 21, 2019, 10:57 PM

#

📎 unknown.png

#

something like this

spare geode Jan 21, 2019, 10:59 PM

#

Ok yeah im understanding now

viral crag Jan 21, 2019, 10:59 PM

#

if a pokemon isn't owned, then it won't be in the catches table for that server ID

spare geode Jan 21, 2019, 10:59 PM

#

Makes alot of sense

viral crag Jan 21, 2019, 10:59 PM

#

you don't need a flag for it in that case.

spare geode Jan 21, 2019, 11:04 PM

#

So if pokemon is in catches with the server id that theyre in, then it will come up its owned by the user id and cant be claimed again

viral crag Jan 21, 2019, 11:04 PM

#

that's the idea

#

You'll also want to look into joins so you can get data from both tables at the same time

spare geode Jan 21, 2019, 11:06 PM

#

Yeah would be easier than extracting it

#

Ill have a look at it, tysm 😃

#

Much harder to do than it looks 😂🙈 (Making a discord bot with databases)

viral crag Jan 21, 2019, 11:13 PM

#

I'd suggest an ORM but that's not something you really want in a Discord bot

copper sphinx Jan 21, 2019, 11:22 PM

#

Okey, I'm hitting my head to brick with this again. I checked that my query now works directly in the database, so why won't it in my SQLA?

Traceback: https://paste.pythondiscord.com/akinexotoh.py

#

Query for reference:

select count(*) cnt, channel_name from discord_messages dm
inner join discord_channels dc on dm.channel_id = dc.channel_id
where user_id = '380865985479049216'
group by channel_name

viral crag Jan 21, 2019, 11:28 PM

#

well do you have any relationship()s in your models?

copper sphinx Jan 21, 2019, 11:30 PM

#

Umm, no.

viral crag Jan 21, 2019, 11:31 PM

#

well that'll be it then

copper sphinx Jan 21, 2019, 11:33 PM

#

Well, I have no idea how I should define them.

#

And yes, looking at the docs.

viral crag Jan 21, 2019, 11:33 PM

#

look harder :P

#

Don't skim

#

You should be looking at https://docs.sqlalchemy.org/en/latest/orm/relationships.html

#

yes, it's a lot of reading unfortunately

#

it's an important concept though

copper sphinx Jan 21, 2019, 11:46 PM

#

channel_name = relationship("Channels", uselist=False, back_populates="discord_messages") This is annoying.

#

Still the same error.

copper sphinx Jan 22, 2019, 12:09 AM

#

Maybe a fresh night sleep will help, I hope.

copper sphinx Jan 22, 2019, 12:31 AM

#

I used this to help me out: http://visualalchemist.prahladyeri.com

#

:D

cyan steeple Jan 22, 2019, 10:17 AM

#

what database do you recommend for min first database that I want to learn I know little of mongo db continuous in it or will I go to another databa?

dull scarab Jan 22, 2019, 10:22 AM

#

A normal sql db to play around with?

#

sqlite is already shipped with your python installation so you could easily use that, if you want sql based

terse stump Jan 22, 2019, 11:26 AM

#

I am a sql guy, so correct me if I am wrong, but I have the impression that NoSQL databases are easier to learn.

viral crag Jan 22, 2019, 11:27 AM

#

They have their own merits and problems

#

Because they use expressive languages though, the drivers emulate the query language

#

So it's more or less the same regardless of what language you're actually using

terse stump Jan 22, 2019, 11:37 AM

#

are the merits and problems related to how they work or how easy it is to learn?

#

when i did my database course at uni half the class was struggling with the whole sql concept

#

there is also a strong bias from my part here since I only work with sql

viral crag Jan 22, 2019, 11:39 AM

#

Really it depends what kind of person you are

#

If you hate declarative query languages or you prefer working with stuff like map and reduce then nosql can be quite nice

terse stump Jan 22, 2019, 11:40 AM

#

I have never thought about it in that way.. i like that..

viral crag Jan 22, 2019, 11:40 AM

#

Nosql is often just enterprise map and reduce :P

dull scarab Jan 22, 2019, 11:40 AM

#

The satisfaction of writing a complex sql query is still fun though

terse stump Jan 22, 2019, 11:40 AM

#

yeah it totally is..

viral crag Jan 22, 2019, 11:40 AM

#

I don't enjoy it

dull scarab Jan 22, 2019, 11:40 AM

#

I do c:

terse stump Jan 22, 2019, 11:40 AM

#

I hate writing sql

viral crag Jan 22, 2019, 11:40 AM

#

But that's why we have ORMs

dull scarab Jan 22, 2019, 11:40 AM

#

Probably yeah

terse stump Jan 22, 2019, 11:41 AM

#

let me show you something

dull scarab Jan 22, 2019, 11:41 AM

#

my only encounter with nosql is pydis/site so far

#

and i prefered sql over that

viral crag Jan 22, 2019, 11:41 AM

#

Rethinkdb is a bitch

terse stump Jan 22, 2019, 11:41 AM

#

📎 unknown.png

viral crag Jan 22, 2019, 11:42 AM

#

It was amusing that I had to write an API wrapper around the already rich and object-oriented driver

dull scarab Jan 22, 2019, 11:42 AM

#

That's a neat query GWqlabsSpyEyes

viral crag Jan 22, 2019, 11:42 AM

#

Is that all SQL? Fuck that

#

Haha

terse stump Jan 22, 2019, 11:42 AM

#

this is one of the smalles ones

#

it fits in one screen

viral crag Jan 22, 2019, 11:42 AM

#

You can keep it

dull scarab Jan 22, 2019, 11:43 AM

#

You write columns and what not in norwegian (!?)

terse stump Jan 22, 2019, 11:43 AM

#

I am forced to do that

#

I did not make the databse

#

📎 unknown.png

dull scarab Jan 22, 2019, 11:43 AM

#

Very shame.

#

o.o

terse stump Jan 22, 2019, 11:44 AM

#

📎 unknown.png

#

like this. this works, and I am never touching it again

dull scarab Jan 22, 2019, 11:46 AM

#

My queries are not even close to that :P

#

SELECT id, money FROM users
    WHERE guild_id = $1
    ORDER BY money desc
limit $2;``` 😎

#

but then again my dbs are hobby level and not very complicated or big.

terse stump Jan 22, 2019, 11:51 AM

#

this is an enterprise level databased, at the moment it has around 50gb of entries

dull scarab Jan 22, 2019, 11:51 AM

#

Yeah, mine has ... 1000 entries?

#

actually 35,000. Forgot it got approved by discordbot list and just yoinked the entire userlist there

stray night Jan 22, 2019, 1:58 PM

#

can any one help me in.....How to create setup.py file that will contain all dependancy of my project...?? i am using flask

carmine heart Jan 22, 2019, 2:07 PM

#

@stray night I don't think this question really fits this channel. Also, please refrain from asking your question in multiple channels simultaneously, as that defeats the purpose of having multiple channels.

vocal oxide Jan 22, 2019, 2:29 PM

#

@stray night have the setup.py file run pip to install your requirements.txt file

#

anyone have experience with sqlalchemy as it pertains to connecting to existing tables? Using pyodbc I can run a table query no problem but with SQLalchemy it can't even find the table at all

carmine heart Jan 22, 2019, 3:10 PM

#

@vocal oxide They've received help in one of the other channels they've asked their question in.

fluid nova Jan 22, 2019, 5:51 PM

#

can anyone help with a script im trying to make that involves two excel sheets and some python?

ionic pecan Jan 22, 2019, 6:13 PM

#

yes, but im not sure what that has to do with databases

spare geode Jan 22, 2019, 6:45 PM

#

Guessing he feels like EXCEL would class as the database

fluid nova Jan 22, 2019, 6:50 PM

#

is it not?

#

or... could it not be?

spare geode Jan 22, 2019, 6:50 PM

#

I wouldnt class it as

fluid nova Jan 22, 2019, 6:50 PM

#

" You know nothing John Snow"

#

lol

spare geode Jan 22, 2019, 6:51 PM

#

Im not experienced transfering from excel so ill try look it up for u

fluid nova Jan 22, 2019, 6:52 PM

#

ohh you dont have to go through that trouble... that's what i;m doing hehe

spare geode Jan 22, 2019, 6:52 PM

#

https://www.sitepoint.com/using-python-parse-spreadsheet-data/ might be good 😃

SitePoint

Shaumik Daityari

Using Python to Parse Spreadsheet Data — SitePoint

Shaumik takes a quick look at two Python modules that you can use to parse and extract data from spreadsheets.

fluid nova Jan 22, 2019, 6:52 PM

#

i mean unless you want to learn yourself hehe

#

ohh nice

#

awesome thank you

spare geode Jan 22, 2019, 6:52 PM

#

No problem , enjoy

fluid nova Jan 22, 2019, 6:53 PM

#

man i need another monitor..... . or 3

#

even with a window tile manager

#

its insane

spare geode Jan 22, 2019, 6:53 PM

#

aha yeah i have the same problem

fluid nova Jan 22, 2019, 6:53 PM

#

just insane

#

no where to put stuff

ornate isle Jan 22, 2019, 7:55 PM

#

excel sheet -> export to csv -> import dicts with csvkit or similar

hybrid juniper Jan 23, 2019, 3:18 AM

#

When people make GUI programs in Python that need database access, do they usually open a new db connection every time they write something? Or do they keep the connection around for whenever it's needed?

patent glen Jan 23, 2019, 4:08 AM

#

with a Real Database, you can generally do a connection pool to let you write the code as if it opens a new db connection each time, without the cost of actually making and closing connections

#

with sqlite3 it probably doesn't matter much either way

tropic bloom Jan 23, 2019, 4:47 AM

#

@fluid nova have a look at pandas

#

I currently use it to read into SQL, then append and write to csv

stray night Jan 23, 2019, 6:36 AM

#

@vocal oxide do you check the sql connection string here you need to provid servername,tablename,usernameand password so it will connect to particular table..

tropic bloom Jan 23, 2019, 1:57 PM

#

Thanks to the folks here I was able to fix some bugs and v1 of our scraper is going live on our company's AWS servers

#

❤️

craggy coyote Jan 23, 2019, 3:41 PM

#

anyone versed in sqlalchemy?

Sqlalchemy has properties you can declare on classes (example using flask sqlalchemy)

class User(db.Model):
    first_name = db.Column(db.String(26))
    last_name = db.Column(db.String(26))
    is_real == db.Column(db.Integer)
    @property

     real_user(self):
        return self.is_real == 1

but if you query that property

db.session.query(User).filter(User.real_user == True).all()

you get nothing because full_name is a property not any sort of sql expression.

e.g. <type 'property'>

question is there a way to disable that so it throws an error so people can't query of property. Seems like it should be a built in behavior because hybrid_properties are querably but regular properties aren't.

fallow tide Jan 23, 2019, 5:21 PM

#

is apache cassandra good

full geyser Jan 23, 2019, 5:22 PM

#

that sounds familiar

#

the nosql db?

#

its god

#

*good

#

can i also suggest looking into aerospike if you're going to use it for anything big

#

https://hackernoon.com/trying-aerospike-as-data-store-in-kurio-99c82b9a7180

Hacker Noon

Trying Aerospike as Data store in Kurio – Hacker Noon

First Impression and A Little Story When Trying Aerospike in Kurio

fallow tide Jan 23, 2019, 5:27 PM

#

oh ok

#

i've never heard of aerospike, will look into it

#

actually, i just realized that discord uses apache cassandra

#

i was going to use it for storing passwords on my website

tropic bloom Jan 23, 2019, 7:02 PM

#

@viral crag want to see the variable nightmare im facing

copper sphinx Jan 24, 2019, 12:55 PM

#

Ideas?

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not create SSL context: library has no ciphers ?

torn sphinx Jan 25, 2019, 10:21 AM

#

Hi, guys. Do you have any link to Python ETL best practices?

weary flax Jan 25, 2019, 10:41 AM

#

mycursor.execute("SELECT amount, serial, name FROM produkter_product WHERE serial = '{0}'".format(identification))

I'm trying to grab some data from MySQL database

#

but its case sensitive

#

and i'd like to get the result from both FAR and far

#

nvm fixed it

quasi holly Jan 25, 2019, 2:39 PM

#

If I want to have multiple bots accessing one db, which db should I use? I am currently using SQLite but that isn't good for multiple hosts

viral crag Jan 25, 2019, 2:39 PM

#

Postgresql

quasi holly Jan 25, 2019, 2:45 PM

#

I need psycopg2 to control it, right?

viral crag Jan 25, 2019, 2:49 PM

#

You probably want asyncpg

quasi holly Jan 25, 2019, 2:50 PM

#

Alright install postgre now and will look into asyncpg

eager nest Jan 25, 2019, 5:06 PM

#

Hey all, using MySQL and python and trying to change a column in my database without having to redo the whole thing. basically the users did a weird and made their dates in mmddyy format and I want to change it to yyyymmdd format.

I believe that the way I had it set up (I tried to use slicing) was changing the variable I was using, so then I couldn't get the day and year parts once I slicing the variable for the month. 😦

I'm not sure how this is working under the hood but based on my print statements, that is is my guess. Is there a way for me to set this up so that I don't change the original variable in trying to pull out this information?

and bigger picture: is there a better method than slicing because another issue I'm having is...

well to get all the problematic fields, I'm using fetchone or fetchall and that gives me a tuple or tuple of tuples and I can't turn a tuple into an integer to add 2000 to it. maybe instead I try to turn it into a string and then concat str(20) + yy...

ornate isle Jan 25, 2019, 5:41 PM

#

@quasi holly are the bots written in async code? if not, you dont need asyncpg. and probably dont want it. you want a connection pool

ionic pecan Jan 25, 2019, 9:03 PM

#

The PostgreSQL documentation specifies that logical and streaming replication can both be enabled concurrently. However, it is not possible to create a publication on logical replication from the master on the slave when streaming replication is enabled, since the slave is set to read-only mode, and CREATE SUBSCRIPTION cannot be used inside read-only transactions. How is this meant, that the master can replicate both via streaming replication and logical replication at the same time?

ornate storm Jan 26, 2019, 9:15 AM

#

Hi guys, I'm writing a web scraper for real estate websites and I was wondering, what is better to do when an estate is offered with a phone number or email but the name of the person or agency is missing... Would you advice to store this missing information as an empty string or "anononymous" would be better? Thanks

tropic bloom Jan 26, 2019, 12:49 PM

#

store it as null

#

as in leave it blank

#

when you go to update a row itll cause less issues

#

not that it matters at all but yeh

hazy mango Jan 26, 2019, 3:23 PM

#

What's the quickest database when it comes to storing loads of data and retrieving that data?

viral crag Jan 26, 2019, 3:24 PM

#

Haha, it's never quite that simple

hazy mango Jan 26, 2019, 3:24 PM

#

And when I say loads I mean over a million items

viral crag Jan 26, 2019, 3:24 PM

#

Postgresql is the database we mostly recommend to people

#

MariaDB is also quite capable

hazy mango Jan 26, 2019, 3:24 PM

#

I'm currently using sqlite so is postresql quicker?

viral crag Jan 26, 2019, 3:25 PM

#

Sqlite is kind of unsuitable for a lot of large applications

hazy mango Jan 26, 2019, 3:25 PM

#

yeah, I've found that

viral crag Jan 26, 2019, 3:25 PM

#

The others require you to run and connect to a database server

#

It's totally worth it though

hazy mango Jan 26, 2019, 3:26 PM

#

Then I'll probs use postgresql

#

Is there any websites that have a good introduction to it? @viral crag

viral crag Jan 26, 2019, 3:29 PM

#

I don't know off the top of my head, but some libraries you can use include psycopg2 and asyncpg

hazy mango Jan 26, 2019, 3:29 PM

#

👌

#

How to create a host on your own PC? @viral crag

#

I tried doing localhost but that raises an error

tropic zealot Jan 26, 2019, 3:44 PM

#

What kind of error are you coming up with?

hazy mango Jan 26, 2019, 3:44 PM

#

    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not connect to server: Connection refused (0x0000274D/10061)
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 5432?
could not connect to server: Connection refused (0x0000274D/10061)
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?```

viral crag Jan 26, 2019, 3:45 PM

#

Your postgresql server is not running

#

You need to install postgresql and start it

hazy mango Jan 26, 2019, 3:46 PM

#

How do I do that?

#

Is that this? https://www.postgresql.org/download/windows/

viral crag Jan 26, 2019, 3:47 PM

#

Yrp

hazy mango Jan 26, 2019, 3:52 PM

#

I ran the installer and it's now installing Microsoft Visual C++ 2013 Redistributable (x86) - 12.0.40660???

#

And a load of other stuff as well - guess they're requirements?

#

@viral crag

viral crag Jan 26, 2019, 3:56 PM

#

Yes they are

hazy mango Jan 26, 2019, 3:56 PM

#

Ah, ok

#

Guess I gotta play the waiting game 😦

#

@viral crag Stack Builder has come up with a load of options saying what do I want to install - what needs to be installed?

#

@tropic zealot

viral crag Jan 26, 2019, 4:21 PM

#

You need postgresql (the server)

#

pgadmin may also be useful to you

hazy mango Jan 26, 2019, 4:21 PM

#

I'm on the part after that

#

I'd show a screenshot but my screenshot software isn't working

#

I've got loads of click-downs Add-ons, tools and utilities Database Drivers Database Server Registration-required and trial products Replication Solutions Spatial Extensions Web Development

#

Says I have to install at least one thing

viral crag Jan 26, 2019, 4:25 PM

#

I think pgadmin is in the first one

hazy mango Jan 26, 2019, 4:25 PM

#

I'm on the page after that

viral crag Jan 26, 2019, 4:26 PM

#

Also, the database server

hazy mango Jan 26, 2019, 4:26 PM

#

In database server it shows a load of versions and then one version with (installed) next to it

viral crag Jan 26, 2019, 4:26 PM

#

Then you're good, just close that

hazy mango Jan 26, 2019, 4:26 PM

#

Kk

#

So now I've installed it, how do I run it? @viral crag

viral crag Jan 26, 2019, 4:29 PM

#

There should be something in your start menu, otherwise go to the services section in your control panel and start it there

hazy mango Jan 26, 2019, 4:31 PM

#

Don't know what you're talking about...

#

Nothing in start menu and idk what you mean by services section in your control panel

#

wait, I think I got it

#

I'm getting really confused... I thought this would be easy but apparently not

#

All I want to do is convert sqlite to postgresql

#

Think I might just leave it because this is too confusing

viral crag Jan 26, 2019, 4:58 PM

#

There's a bit of work in it

#

It does use a different dialect of sql

open fiber Jan 26, 2019, 11:02 PM

#

Hello o/

viral crag Jan 26, 2019, 11:03 PM

#

\o olleH

#

Yep

open fiber Jan 26, 2019, 11:05 PM

#

Sweet

#

I'm making a application which requires a bulk upsert, However mid upsert it throws erros at me.

queen gull Jan 26, 2019, 11:35 PM

#

i need help on databases

fringe tiger Jan 26, 2019, 11:50 PM

#

@queen gull maybe show us what you need first?

queen gull Jan 26, 2019, 11:50 PM

#

you were just in the same channel as me...

fringe tiger Jan 26, 2019, 11:51 PM

#

But I didn't read it

#

Just post the question don't ask to post it will be faster

queen gull Jan 26, 2019, 11:51 PM

#

i want to put the content of a message into the database

#

they said

#

it needs another column

#

I dont really get what I'm supposed to do

fringe tiger Jan 26, 2019, 11:52 PM

#

:/ so basically I need to do all the work 😦

queen gull Jan 26, 2019, 11:52 PM

#

No

#

I already have some of the code

fringe tiger Jan 26, 2019, 11:53 PM

#

paste is your friend

queen gull Jan 26, 2019, 11:53 PM

#

@bot.command()
async def setuid(ctx, *, words):
    author_id = str(ctx.message.author.id)

    await bot.pg_con.fetch("SELECT * FROM users WHERE user_id = $1", author_id)
    await bot.pg_con.execute("UPDATE users SET user_id = words", author_id)

#

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: UndefinedColumnError: column "words" does not exist

fringe tiger Jan 26, 2019, 11:53 PM

#

Yes I see

#

Show me how you created database

#

and how you manipulated it

#

(added columns)

queen gull Jan 26, 2019, 11:54 PM

#

📎 Screenshot_28.png

#

📎 Screenshot_29.png

#

@fringe tiger

fringe tiger Jan 27, 2019, 12:00 AM

#

Welp this is the problem await bot.pg_con.execute("UPDATE users SET user_id = words", author_id)

queen gull Jan 27, 2019, 12:01 AM

#

yeah

#

I dont really know

#

how to make the words

#

or the message content

#

In the database

#

i also tried this

#

await bot.pg_con.execute("INSERT INTO users (user_id) VALUES (words)", author_id)

#

that didnt work either

#

huh

#

i saw a message flash

hearty iron Jan 27, 2019, 5:58 AM

#

I was trying to install RethinkDb on a fresh debian instance(from google cloud) which has around 1.7gb memory and 10gb storage. Everything till configure went great but i got this error while running the make command!

📎 Screenshot_2019-01-27-11-22-09.png

patent glen Jan 27, 2019, 6:38 AM

#

@queen gull what is your database schema? it's not clear what words is for.

#

you probably want something like py await bot.pg_con.execute("INSERT INTO users(user_id, words) VALUES($1, $2)", author_id, words) or ```py
await bot.pg_con.execute("UPDATE users SET words=$2 WHERE user_id=$1", author_id, words)

#

if you only have one column i'm not sure how you intend to associate the text with the author id

ionic pecan Jan 27, 2019, 12:37 PM

#

@hearty iron why not use the rethinkdb packages

hearty iron Jan 27, 2019, 12:44 PM

#

What is it @ionic pecan?

ionic pecan Jan 27, 2019, 12:45 PM

#

see https://rethinkdb.com/docs/install/debian/

hearty iron Jan 27, 2019, 12:46 PM

#

I tried the first one too but that said package not found. I got a solution for it thougj

#

wget https://github.com/srh/rethinkdb/releases/download/v2.3.6.srh.1/rethinkdb_2.3.6.srh.1.0stretch_amd64.deb && sudo dpkg -i rethinkdb_2.3.6.srh.1.0stretch_amd64.deb
if you're missing something do sudo apt-get install -f

#

It worked^^

novel axle Jan 27, 2019, 3:10 PM

#

A={'Jeju': 0, 'Pangyo': 1, 'Seoul': 2, 'NewYork': 3, 'LA': 4}
I have a number (for example 3)
and I'd like to pop the items from A(for example A={'Jeju': 0, 'Pangyo': 1, 'Seoul': 2, 'LA': 4}

dull scarab Jan 27, 2019, 3:17 PM

#

Is this db related?

ionic pecan Jan 27, 2019, 3:18 PM

#

No

dull scarab Jan 27, 2019, 3:18 PM

#

None the less use dict.pop(key, default)

quiet ermine Jan 27, 2019, 6:01 PM

#

for small websites, why not use SQLite?

dull scarab Jan 27, 2019, 6:06 PM

#

What if you need to scale it up?

quiet ermine Jan 27, 2019, 8:27 PM

#

like the website would only ever be small, upto 10k visits per day

#

you could just use sqlite's native transfer option that can do to something like postgres

proven grove Jan 28, 2019, 12:27 AM

#

What is wrong with my syntax?

#

"SELET * FROM blacklist WHERE word = %s OR author = %s;"

#

(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELET * FROM blacklist WHERE word = 'fuck' OR author = 293555442670370817' at line 1")

viral crag Jan 28, 2019, 12:28 AM

#

I believe MySQL uses LIKE, not =

#

but your SELECT is typo'd

#

you have SELET

proven grove Jan 28, 2019, 12:28 AM

#

I've been working with = for a while

#

oh

#

I'm

#

stupid

viral crag Jan 28, 2019, 12:28 AM

#

happens :P

proven grove Jan 28, 2019, 12:28 AM

#

Thankss

patent glen Jan 28, 2019, 12:36 AM

#

LIKE is for pattern matching, = is fine for exact matches

viral crag Jan 28, 2019, 12:37 AM

#

Ah right

proven grove Jan 28, 2019, 1:09 AM

#

Again I need help :')

#

C:\Users\Administrator\AppData\Local\Programs\Python\Python36\lib\site-packages\aiomysql\cursors.py:456: Warning: Truncated incorrect DOUBLE value: '39-80690'

#

I know it's because I'm comparing the same value twice but I need to

#

sql_query = "SELECT * FROM reports WHERE case_number = %s OR agency_qir = %s;"
await crsr.execute(sql_query, (id, id))

viral crag Jan 28, 2019, 1:13 AM

#

No, a double is a decimal number

#

The value you gave is an invalid decimal so it gets shortened

copper sphinx Jan 28, 2019, 7:24 AM

#

asyncpg's fetch will eat up memory like crazy ar am I mistaken?

dull scarab Jan 28, 2019, 7:27 AM

#

It shouldn't by itself afaik

#

How much are you fetching, and what are you doing with the data

copper sphinx Jan 28, 2019, 7:34 AM

#

https://github.com/oomessis/Parempi-Bot/blob/master/cogs/profile.py

GitHub

oomessis/Parempi-Bot

Contribute to oomessis/Parempi-Bot development by creating an account on GitHub.

#

Most of the database code is in that cog.

dull scarab Jan 28, 2019, 7:41 AM

#

Cant go indepth with just a phone. Couldn't see anything that stood out other than that youre doing a lot of repedetive queries and keeping connections open for a while for a lot of them

#

Maybe you could restructure the db in a way so that you could fetch all those values with one query?

#

Also noticed you used fetch for update statements, those should probably be executes

copper sphinx Jan 28, 2019, 7:50 AM

#

I have no idea how I would parse the output if it would be from one query.

copper sphinx Jan 28, 2019, 9:12 AM

#

Could someone help me out here with the memory problem?

quiet ermine Jan 28, 2019, 6:33 PM

#

what are some usecases for nosql?

eager nest Jan 28, 2019, 9:09 PM

#

SQL: How complicated is it to insert a column of integers that are ordered based on another column in the same table being ordered ascending?

raw onyx Jan 28, 2019, 11:06 PM

#

im currently wondering if i should use a databse for my discord bot, and im wondering what database should i use. i know sqlite3 is already built in to python, but i was wondering if there are others which are better, more secure, or any other reason
any recommendations for databases to use?

quiet ermine Jan 29, 2019, 12:25 AM

#

just stick with sqlite for it

#

unless its getting a billion pings per day, its stable

#

(also easier to setup & even though sqlite isnt as secure as some other, larger things, the user will never have direct acess to the database, only through a couple of bot commands)

raw onyx Jan 29, 2019, 12:38 AM

#

okay, i'll stick to sqlite3 then

proven grove Jan 29, 2019, 4:06 AM

#

sql_query = "INSERT INTO rooms (uid, name, date, plot, recommendations, note, advertising, id-1, id-2, author, created, url) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);" what's wrong with my syntax?

#

ERROR:(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1, id-2, author, created, url) VALUES (245, 'yes', 'off', 'I'M DONE WIT' at line 1")

#

Please help :'''D

#

Nvm, id-1 is not a valid name I think

wispy fable Jan 29, 2019, 8:00 AM

#

- is minus, so yeah, you are correct.

#

Does anybody know if there's a way to insert a row into an sqlite database and have some of values filled in with the tables default values and the other values user-supplied?

#

Although based on what I've gathered from various tutorials and the sqlite docs, it seems to be all or nothing for default values.

📎 unknown.png

hazy mango Jan 29, 2019, 8:04 AM

#

If you know what the default value is you can just pass that in?

wispy fable Jan 29, 2019, 8:05 AM

#

That's what I was going to do instead, tbh.

#

¯_(ツ)_/¯

hazy mango Jan 29, 2019, 8:06 AM

#

One sec

#

Look at how I create the table and then how add_login() works https://repl.it/@ScottRobinson1/SQLite3-Login-V1

repl.it

ScottRobinson1

SQLite3 Login V1

Powerful and simple online compiler, IDE, interpreter, and REPL. Code, compile, and run code in 30+ programming languages: Clojure, Haskell, Kotlin (beta), QBasic, Forth, LOLCODE, BrainF, Emoticon, Bloop, Unlambda, JavaScript, CoffeeScript, Scheme, APL, Lua, Python 2.7, Ruby,...

#

(I think this is what you're after)

wispy fable Jan 29, 2019, 8:08 AM

#

You just pass in a NULL and it uses the default instead?

#

I'll find out the fun way in five seconds

hazy mango Jan 29, 2019, 8:09 AM

#

Yeah I think so

wispy fable Jan 29, 2019, 8:11 AM

#

That seems to not work, I'll just put the defaults into the code instead.

hazy mango Jan 29, 2019, 8:12 AM

#

Yeah, sorry. Perhaps try using ? in the format part (where I have username, password...)

wispy fable Jan 29, 2019, 8:12 AM

#

whoawhoa wait a second

#

you're specifying the columns too

#

hm, one sec

dull scarab Jan 29, 2019, 8:13 AM

#

I think it uses default when you dont specify the column

hazy mango Jan 29, 2019, 8:14 AM

#

^

dull scarab Jan 29, 2019, 8:14 AM

#

Db columns - A B C

Insert into db(A B) values (2, 5)```

wispy fable Jan 29, 2019, 8:14 AM

#

When I don't specify the columns,it expects data for all solumns, regardless of whether there's a default value or not

#

but if I specify the column then I only need data for the specified columns

dull scarab Jan 29, 2019, 8:14 AM

#

And have C default to something

wispy fable Jan 29, 2019, 8:15 AM

#

exactly

#

That's what I just figured out lol

dull scarab Jan 29, 2019, 8:15 AM

#

If that doesn't work I'm blank

wispy fable Jan 29, 2019, 8:15 AM

#

It does work lol

dull scarab Jan 29, 2019, 8:15 AM

#

Neat

wispy fable Jan 29, 2019, 8:15 AM

#

Thanks @hazy mango!

hazy mango Jan 29, 2019, 8:15 AM

#

Np 😃

wispy fable Jan 29, 2019, 8:16 AM

#

now, to go back to my coffin

#

👻

hazy mango Jan 29, 2019, 8:17 AM

#

:p

novel axle Jan 29, 2019, 8:21 AM

#

Why has python made changing to my copy variable/list/dictionary affects the original one?

dull scarab Jan 29, 2019, 8:24 AM

#

Uh, what does that have to do with databases

#

!free

delicate fieldBOT Jan 29, 2019, 8:24 AM

#

**Looking for a free help channel?**

The following channels look free:

**1. #help-falafel inactive for 2h49m21s

#help-coconut inactive for 1h31m3s
#help-kiwi inactive for 1h15m30s
#help-croissant inactive for 20m30s

**
These channels aren't guaranteed to be free, so use your best judgement and check for yourself.

novel axle Jan 29, 2019, 9:06 AM

#

sorry.

neat hamlet Jan 29, 2019, 6:02 PM

#

!free°

#

!free

delicate fieldBOT Jan 29, 2019, 6:02 PM

#

**Looking for a free help channel?**

The following channels look free:

**1. #help-chestnut inactive for 44m12s

#help-falafel inactive for 38m6s
#help-kiwi inactive for 25m58s
#help-croissant inactive for 22m22s
#help-grapes inactive for 19m44s

**
These channels aren't guaranteed to be free, so use your best judgement and check for yourself.

obsidian leaf Jan 29, 2019, 9:11 PM

#

        c.execute("""CREATE TABLE :tablename (
                   username text,
                   predictedgrade varchar(2),
                   cag varchar(2));""",
                  {"tablename":self.currentClass.id})

this gives me the error

Traceback (most recent call last):
  File "E:\School-Database\main.py", line 778, in create_
    {"tablename":self.currentClass.id})
sqlite3.OperationalError: near ":tablename": syntax error

even though if i do a print(self.currentClass.id) the line before it returns a string ie. "Table1"

ionic pecan Jan 29, 2019, 9:19 PM

#

you cant substitute tablenames like that

#

param substitution usually only works on values

obsidian leaf Jan 29, 2019, 9:24 PM

#

ok is there a way i can substitute a variable in for it? @ionic pecan

ionic pecan Jan 29, 2019, 9:39 PM

#

you shouldnt dynamically create database tables generally

#

what are you trying to accomplish

obsidian leaf Jan 29, 2019, 9:48 PM

#

ive worked around it now - but everytime a user created a class i was hoping to create a separate table designated to that lesson that would store grades etc for each student

torn sphinx Jan 30, 2019, 9:07 AM

#

hi

#

I have two questions

#

what is a columnar storage.. can someone brief me with an example

#

and.. why are csv, avro,json considered alternative data sources

sterile ferry Jan 30, 2019, 4:18 PM

#

hey guys, this is an sqlalchemy as well as a more general db and ORM question, so say I have: people (pkey int id, name) companies (pkey int id, name) employees (int person_id, int company_id, salary, pkey (person_id, company_id))so the employees is it's own model as well as many-to-many secondary table for people and companies. Would you suggest in this case to keep using composite key (the assumption that my models have singular keys makes many things easier in code) or add a surrogate key (either autoincrement or based on person_id, company_id)?

harsh pulsar Jan 30, 2019, 8:59 PM

#

create a sqlite database and start messing around

#

or mysql or postgres

#

sqlite

torn sphinx Jan 31, 2019, 12:52 AM

#

Well

#

i like rasmpi

quasi saddle Jan 31, 2019, 1:21 PM

#

how do i drop my index from a pandas dataframe im concating a bunch of dataframes to excel and i get random index when i print the dataframe to excel

#

nvm

#

i needed to have drop=True

digital bobcat Jan 31, 2019, 1:45 PM

#

Is it possible to have a tree like structure (like nested dictionaries) saved in a database?

#databases

e.t.c.```