#databases | Python | Page 72

storm hawk Sep 23, 2019, 9:37 PM

#

yep

ivory turtle Sep 23, 2019, 9:37 PM

#

So you want ANOTHER table

#

linking users to guilds

storm hawk Sep 23, 2019, 9:37 PM

#

oh

ivory turtle Sep 23, 2019, 9:37 PM

#

same procedure

storm hawk Sep 23, 2019, 9:37 PM

#

hm

#

ok

#

one sec

#

ok

#

done

#

by the way, thanks for helping me with this. takes a lot of patience i presume.

#

now, how the hell do i go about everything else is the question.

ivory turtle Sep 23, 2019, 9:41 PM

#

No problem man, I remember having to go through this myself as well...
It gets confusing.

#

now, how the hell do i go about everything else is the question.
Depends on what 'everything else' is 😛

storm hawk Sep 23, 2019, 9:45 PM

#

like

#

querying

#

and

#

just

#

adding items, etc

#

more importantly, using the references (foreign keys)

ivory turtle Sep 23, 2019, 9:47 PM

#

Looking up material for that

storm hawk Sep 23, 2019, 9:47 PM

#

ah

ivory turtle Sep 23, 2019, 9:47 PM

#

Cause I'm not actually sure if they automatically update for that, since I use an ORM

#

And it's all automatic there 😛

storm hawk Sep 23, 2019, 10:14 PM

#

@ivory turtle i’m takin a break

#

my brain hurt

ivory turtle Sep 23, 2019, 10:25 PM

#

Fair enough 🙂

torn sphinx Sep 24, 2019, 2:52 AM

#

Looking for help on off-topic mySQL stuff

stable pilot Sep 24, 2019, 6:28 AM

#

hey guys

#

can you guys help me

#

what is wrong with this ```@client.command()
async def auth(ctx, playerAuth: str):
mycursor = mydb.cursor()
sql = "SELECT COUNT(*) FROM playeraccount WHERE playerAuth = %s"
mycursor.execute(sql, (playerAuth,))
myresult = mycursor.fetchone()
for x in myresult:
print(x)

if myresult[0] > 0:
    mycursor = mydb.cursor()
    check = "SELECT ingamePass FROM playeraccount WHERE playerAuth = %s"
    mycursor.execute(check, (playerAuth,))
    myresult2 = mycursor.fetchone()
    for x2 in myresult2:
        print(x2)
    await ctx.send(f'We have found your account in our system, your ingame Password is ""{myresult2[0]}"')

else:
    await ctx.send('Your Account does not exist in our files please try again later.')```

torn sphinx Sep 24, 2019, 10:07 AM

#

I get this error :d

  File "C:\Users\Crossfire StationXd\PycharmProjects\untitled1\cogs\leveling.py", line 99, in on_message
    SQL.execute(f'update Experience set Server_Name = {server_name} where Server_ID = ? and Member_ID = ?', (server_id, author_id))
sqlite3.OperationalError: near "Of": syntax error

rich trout Sep 24, 2019, 10:08 AM

#

What happens when I make my server name 1; drop table Experience; --?

torn sphinx Sep 24, 2019, 10:08 AM

#

what? :d

#

I am sorry,but I do not understand your question.. 😅

rich trout Sep 24, 2019, 10:09 AM

#

SQL.execute(f'update Experience set Server_Name = 1; drop table Experience')

#

is what happens

cobalt cipher Sep 24, 2019, 10:09 AM

#

It's called SQL injections

torn sphinx Sep 24, 2019, 10:09 AM

#

what does drop table do? 🤔

cobalt cipher Sep 24, 2019, 10:10 AM

#

It deletes table

#

All of it

torn sphinx Sep 24, 2019, 10:10 AM

#

what is called SQL injetions? 😱 scary

#

I can not let that happen\

#

But how does that relate? 🤔

rich trout Sep 24, 2019, 10:10 AM

#

The ? syntax that you use for the other inputs prevents it

torn sphinx Sep 24, 2019, 10:11 AM

#

GOOD 😄

#

I heard about that is a security good

#

Sorry English :d

rich trout Sep 24, 2019, 10:11 AM

#

If you replace your other format string input with a ? and an appropriate parameter, I suspect your issue will go away

#

it seems like you're just dropping the server name in

torn sphinx Sep 24, 2019, 10:11 AM

#

😮

rich trout Sep 24, 2019, 10:11 AM

#

Which would lead to sql like this:

#

update Experience set Server_Name = Of Kinds...

torn sphinx Sep 24, 2019, 10:11 AM

#

oh so if I do Server_Name = ? instead of {server_name} you mean?

rich trout Sep 24, 2019, 10:11 AM

#

and since Of is a keyword, that's resulting in a syntax error

#

yes

torn sphinx Sep 24, 2019, 10:11 AM

#

https://tenor.com/view/nickyoung-questionmarks-what-excuseme-huh-gif-4486363

Tenor

????

▶ Play video

rich trout Sep 24, 2019, 10:12 AM

#

It's a lack of quotes that was your original problem, but the solution is to pass parameters properly instead

torn sphinx Sep 24, 2019, 10:12 AM

#

Oky,I will try to do this because I think I understand you 😂

rich trout Sep 24, 2019, 10:12 AM

#

good luck

torn sphinx Sep 24, 2019, 10:12 AM

#

Don't say that 😮

#

makes it sound hard xD

rich trout Sep 24, 2019, 10:13 AM

#

:P

torn sphinx Sep 24, 2019, 10:13 AM

#

what about this:

SQL.execute(f'select Exp from Experience where Server_ID="{server_id}" and Member_ID="{author_id}"')
```?
could this be SQL injected?

rich trout Sep 24, 2019, 10:14 AM

#

Yes

torn sphinx Sep 24, 2019, 10:14 AM

#

0.0

rich trout Sep 24, 2019, 10:14 AM

#

..

#

no, actually

torn sphinx Sep 24, 2019, 10:14 AM

#

-_-

#

CHANGE YOUR MIND XD You are confusing me 😭

rich trout Sep 24, 2019, 10:14 AM

#

given that ID's are set by discord, and guaranteed to be numbers, that code should not cause problems

#

But I would still suggest using ? syntax there

torn sphinx Sep 24, 2019, 10:15 AM

#

how do I do in select?

#

just same?

rich trout Sep 24, 2019, 10:15 AM

#

yes

torn sphinx Sep 24, 2019, 10:15 AM

#

= ? and after values(?) ?

rich trout Sep 24, 2019, 10:15 AM

#

Yes

#

something like

torn sphinx Sep 24, 2019, 10:15 AM

#

😅 I = try that

rich trout Sep 24, 2019, 10:16 AM

#

SELECT * FROM Experience WHERE server_name = ? and user_id = ?

torn sphinx Sep 24, 2019, 10:16 AM

#

yes

#

and after just ,(server_id, author_id)

#

no values

#

right?

#

because it is not in the parenthesis

rich trout Sep 24, 2019, 10:17 AM

#

UPDATE users SET user_name = ? WHERE user_id = ?

#

right

torn sphinx Sep 24, 2019, 10:17 AM

#

:d

#

but how do I put something in the '?' ?

rich trout Sep 24, 2019, 10:17 AM

#

you pass it into the execute command

torn sphinx Sep 24, 2019, 10:18 AM

#

something like this:

SQL.execute(f'update Experience set Server_Name =? where Server_ID = ? and Member_ID = ?', (server_name, server_id, author_id))
```?

rich trout Sep 24, 2019, 10:18 AM

#

cursor.execute("UPDATE users SET user_name = ? WHERE user_id = ? ", (user_name, user_id))

torn sphinx Sep 24, 2019, 10:18 AM

#

yeeeeee

rich trout Sep 24, 2019, 10:18 AM

#

yes

torn sphinx Sep 24, 2019, 10:18 AM

#

That is what I was asking

#

arigatou

rich trout Sep 24, 2019, 10:18 AM

#

👍

torn sphinx Sep 24, 2019, 10:19 AM

#

oh

#

so where it is only 1 thingy instead of 2 like
(user_name, user_id))
I don't need parenthesis

#

makes sense

rich trout Sep 24, 2019, 10:21 AM

#

Perhaps not, but it's been my experience that you'd have to wrap it in a proper tuple-- (user_name,)

#

There's also an alternative named syntax too:

torn sphinx Sep 24, 2019, 10:21 AM

#

no I mean

rich trout Sep 24, 2019, 10:21 AM

#

cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})

torn sphinx Sep 24, 2019, 10:21 AM

#

I get the green underline saying 'you don't need that' basically

rich trout Sep 24, 2019, 10:22 AM

#

If you missed the extra ,, then you probably would

torn sphinx Sep 24, 2019, 10:22 AM

#

yeye but there are 2 objects there

#

I did not 🤔

rich trout Sep 24, 2019, 10:22 AM

#

hm

#

shrug

torn sphinx Sep 24, 2019, 10:23 AM

#

1 sec

#

dude

#

took me 5 minutes to get this text thingy to stay so I can take a photo of it lol

#

📎 unknown.png

rich trout Sep 24, 2019, 10:27 AM

#

You're missing the trailing comma

#

:P

torn sphinx Sep 24, 2019, 10:27 AM

#

A WHAT

#

HOW

rich trout Sep 24, 2019, 10:27 AM

#

(thing**,**)

torn sphinx Sep 24, 2019, 10:27 AM

#

REALLY?

rich trout Sep 24, 2019, 10:27 AM

#

yep..

torn sphinx Sep 24, 2019, 10:27 AM

#

I need that? 😮

rich trout Sep 24, 2019, 10:27 AM

#

yes

torn sphinx Sep 24, 2019, 10:27 AM

#

even if I will not be putting anything else inside?

rich trout Sep 24, 2019, 10:27 AM

#

!e ```python
print(repr(("a")))
print(repr(("a",)))

delicate fieldBOT Sep 24, 2019, 10:27 AM

#

@rich trout Your eval job has completed with return code 0.

001 | 'a'
002 | ('a',)

torn sphinx Sep 24, 2019, 10:27 AM

#

🤔

rich trout Sep 24, 2019, 10:28 AM

#

!e ```python
print(type(("a",)))

delicate fieldBOT Sep 24, 2019, 10:28 AM

#

@rich trout Your eval job has completed with return code 0.

<class 'tuple'>

torn sphinx Sep 24, 2019, 10:28 AM

#

😮

#

oky thanks

rich trout Sep 24, 2019, 10:28 AM

#

👍

torn sphinx Sep 24, 2019, 10:28 AM

#

I learn something new everyday 😄

#

THANKS It works 😄

#

Juses! I have a lot of those 😅 to fix

stable pilot Sep 24, 2019, 11:25 AM

#

hey guys
why doesnt this work ?

    val = (ctx.author.id, ctx.author.name, username, location, isp, creation_date)
    async with conn.cursor() as mycursor:
        await mycursor.execute(sql, val)
        await conn.commit()
    print('created')
    await ctx.send('Your request has been sent through')
    await conn.close()```
it doesnt put anything my database
put it prints the values ..

tawny sail Sep 24, 2019, 12:00 PM

#

u missed the , after creation_date

#

@stable pilot

patent glen Sep 24, 2019, 2:07 PM

#

that doesn't seem like it should be necessary - a comma after the last item in a tuple is optional and only required for single-element tuples.

#

how do you tell it didn't put anything in the database?

pliant oxide Sep 24, 2019, 2:10 PM

#

Guys, someone knows SQL?

#

I have to modify that VIEW

#

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`%` 
    SQL SECURITY DEFINER
VIEW `eskycrawler_dev`.`offers_viewer` AS
    SELECT 
        `eskycrawler_dev`.`offers`.`price` AS `price`,
        `out_flights`.`flight_number` AS `departure_number`,
        `out_airports`.`name` AS `departure_airport`,
        `out_cities`.`name` AS `departure_city`,
        `out_countries`.`name` AS `departure_country`,
        FROM_UNIXTIME((`out_flights`.`from_date` / 1000)) AS `departure_date`,
        `in_flights`.`flight_number` AS `arrival_number`,
        `in_airports`.`name` AS `arrival_airport`,
        `in_cities`.`name` AS `arrival_city`,
        `in_countries`.`name` AS `arrival_country`,
        FROM_UNIXTIME((`in_flights`.`to_date` / 1000)) AS `arrival_date`,
        `eskycrawler_dev`.`offers`.`url` AS `offer_url`
    FROM
        ((((((((`eskycrawler_dev`.`offers`
        JOIN `eskycrawler_dev`.`flights` `out_flights` ON ((`eskycrawler_dev`.`offers`.`outbound_flight_id` = `out_flights`.`id`)))
        JOIN `eskycrawler_dev`.`flights` `in_flights` ON ((`eskycrawler_dev`.`offers`.`inbound_flight_id` = `in_flights`.`id`)))
        JOIN `eskycrawler_dev`.`airports` `out_airports` ON ((`out_flights`.`from_airport_id` = `out_airports`.`id`)))
        JOIN `eskycrawler_dev`.`airports` `in_airports` ON ((`in_flights`.`from_airport_id` = `in_airports`.`id`)))
        JOIN `eskycrawler_dev`.`cities` `out_cities` ON ((`out_airports`.`city_id` = `out_cities`.`id`)))
        JOIN `eskycrawler_dev`.`cities` `in_cities` ON ((`in_airports`.`city_id` = `in_cities`.`id`)))
        JOIN `eskycrawler_dev`.`countries` `out_countries` ON ((`out_cities`.`country_id` = `out_countries`.`id`)))
        JOIN `eskycrawler_dev`.`countries` `in_countries` ON ((`in_cities`.`country_id` = `in_countries`.`id`)))
    ORDER BY FROM_UNIXTIME((`out_flights`.`from_date` / 1000)) , FROM_UNIXTIME((`in_flights`.`to_date` / 1000))

#

I got OFFERS main table. That view get all offers and show informations instead of all foreign keys id.

#

just converts id to information from other tables

#

I got a problem, because I have to add one more thing. My Offers table got new column "search_id" foreign key from table SEARCHES.

#

📎 unknown.png

#

this is SEARCHES table

#

Now in VIEW I have to get all offers only with the last search.

#

How to do that?

#

Get last id of searches
Get offers only with that search_id

torn sphinx Sep 24, 2019, 2:38 PM

#

SQL.execute(f'select Member_ID from Staff_List where Server_ID = ?', (server_ID))
ValueError: parameters are of unsupported type

#

WUT IS WRONG HERE :d

harsh pulsar Sep 24, 2019, 2:39 PM

#

@torn sphinx (server_ID) is the same as server_ID

#

you want (server_ID,)

#

or [server_ID]

torn sphinx Sep 24, 2019, 2:39 PM

#

what do you mean

#

id doesn't matter what name it is

#

right

#

WOW apparently it does???

#

why

harsh pulsar Sep 24, 2019, 2:41 PM

#

@torn sphinx the params must be a sequence of some kind, i.e. a tuple or list

tawny sail Sep 24, 2019, 2:42 PM

#

Maybe a comma at the end of that tuple?

torn sphinx Sep 24, 2019, 2:42 PM

#

just a coma can change that?

#

😮

#

anyways thanks

harsh pulsar Sep 24, 2019, 2:42 PM

#

of course

tawny sail Sep 24, 2019, 2:42 PM

#

End of the first element

torn sphinx Sep 24, 2019, 2:42 PM

#

wow

#

epic

#

thanks again!

tawny sail Sep 24, 2019, 2:43 PM

#

Did it work

torn sphinx Sep 24, 2019, 2:43 PM

#

I am fixing this 5 minutes before I need to go to the dentist hahaha

#

yes it did,much love! have a nice day

tawny sail Sep 24, 2019, 2:43 PM

#

Nice

torn sphinx Sep 24, 2019, 4:02 PM

#

I need help..

#

So I have database filled out like this:

#

📎 unknown.png

#

BUT

#

when I run this line of code:

#

📎 unknown.png

#

I get this printed out:

#

📎 unknown.png

#

and that is 1 ID only..

#

I am confusion

tawny sail Sep 24, 2019, 4:09 PM

#

u do fetchone()

#

so u get 1

#

do fetchall()

torn sphinx Sep 24, 2019, 4:09 PM

#

WOW REALLY?

#

NO WONDER MY CODE WAS BULLSHIT

tawny sail Sep 24, 2019, 4:10 PM

#

lol

torn sphinx Sep 24, 2019, 4:10 PM

#

not funny xd

tawny sail Sep 24, 2019, 4:10 PM

#

sry

torn sphinx Sep 24, 2019, 4:10 PM

#

😂

#

thanks

tawny sail Sep 24, 2019, 4:11 PM

#

yw

torn sphinx Sep 24, 2019, 4:37 PM

#

SQL.execute('insert into Staff_List(Server_ID, Server_Name, Member_ID) values(?,?,?)', (server_ID, server_name, member_ID,))
sqlite3.OperationalError: database is locked

#

I added a timeout time

#

nope still that error

#

and by that I mean

db = sqlite3.connect(os.path.join(DIR, "staff.db"), timeout=5)

frozen fossil Sep 24, 2019, 4:45 PM

#

does anybody know how to import mysql connector python

torn sphinx Sep 24, 2019, 4:50 PM

#

not me

tawny sail Sep 24, 2019, 4:50 PM

#

@frozen fossil did u install it?

frozen fossil Sep 24, 2019, 4:51 PM

#

i did

#

dev:~ raizo$ pip3 show mysql-connector-python
Name: mysql-connector-python
Version: 8.0.17
Summary: MySQL driver written in Python
Home-page: http://dev.mysql.com/doc/connector-python/en/index.html
Author: Oracle and/or its affiliates
Author-email: UNKNOWN
License: GNU GPLv2 (with FOSS License Exception)
Location: /usr/local/lib/python3.7/site-packages
Requires: protobuf
Required-by: 
dev:~ raizo$

tawny sail Sep 24, 2019, 4:52 PM

#

did u look into the docs?

frozen fossil Sep 24, 2019, 4:52 PM

#

yes it says import mysql.connector

#

but that gives me unable to import package

tawny sail Sep 24, 2019, 4:53 PM

#

u have multiple python versions?

#

@torn sphinx ur using pycharm?

torn sphinx Sep 24, 2019, 4:56 PM

#

Yes,I am,sir!

tawny sail Sep 24, 2019, 4:56 PM

#

ur using the green triangle to run the bot?

torn sphinx Sep 24, 2019, 4:56 PM

#

yes 😳

#

why?

#

I have always used that!

tawny sail Sep 24, 2019, 4:56 PM

#

whats ur working dir?

torn sphinx Sep 24, 2019, 4:56 PM

#

:d

#

C:\Users\Crossfire StationXd\PycharmProjects\untitled1\cogs

#

this,sir

#

why

frozen fossil Sep 24, 2019, 4:58 PM

#

yeah i have these

📎 Bildschirmfoto_2019-09-24_um_18.57.49.png

tawny sail Sep 24, 2019, 4:58 PM

#

in which have u installed the package and which one are u using to run the program

#

@torn sphinx go to edit config and see the working dir there

torn sphinx Sep 24, 2019, 4:59 PM

#

🤔

frozen fossil Sep 24, 2019, 4:59 PM

#

its in /usr/local/lib/python3.7/site-packages

#

and im trying to run 3.7.4

torn sphinx Sep 24, 2019, 4:59 PM

#

where can I find that?

#

📎 laughing_emoji.PNG

frozen fossil Sep 24, 2019, 5:00 PM

#

both versions dont work of 3.7.4

tawny sail Sep 24, 2019, 5:00 PM

#

ahh choose the last option

frozen fossil Sep 24, 2019, 5:00 PM

#

ThonkTriangle not working

tawny sail Sep 24, 2019, 5:00 PM

#

@torn sphinx left side of the green triangle,expand

#

try all raizo

torn sphinx Sep 24, 2019, 5:00 PM

#

owo

#

ye

#

C:\Users\Crossfire StationXd\PycharmProjects\untitled

#

this is,sir!

#

but the database is not in that directory 😂

#

should it be? 🤔

tawny sail Sep 24, 2019, 5:01 PM

#

inside it in some folder

torn sphinx Sep 24, 2019, 5:01 PM

#

??

#

it is

tawny sail Sep 24, 2019, 5:01 PM

#

try running the command again, for which u got error

torn sphinx Sep 24, 2019, 5:01 PM

#

in cogs

#

okay

tawny sail Sep 24, 2019, 5:02 PM

#

i have never got that kind of error,lol

torn sphinx Sep 24, 2019, 5:02 PM

#

oh okay

#

I have a questio,sir

#

fck

#

@tawny sail

tawny sail Sep 24, 2019, 5:02 PM

#

OperationalError: database is locked errors indicate that your application is experiencing more concurrency than sqlite can handle in default configuration. This error means that one thread or process has an exclusive lock on the database connection and another thread timed out waiting for the lock the be released.

torn sphinx Sep 24, 2019, 5:02 PM

#

what does this mean:
DIR = os.path.dirname(__file__)?

#

0.0

#

I have this:

DIR = os.path.dirname(__file__)
db = sqlite3.connect(os.path.join(DIR, "staff.db"), timeout=5)
#db.execute("PRAGMA busy_timeout = 30000")
SQL = db.cursor()

#

but it did not change the directory..

tawny sail Sep 24, 2019, 5:03 PM

#

use regular path insted of OS , im not the best with os module, lol

torn sphinx Sep 24, 2019, 5:04 PM

#

?

tawny sail Sep 24, 2019, 5:04 PM

#

show me ur project structure

torn sphinx Sep 24, 2019, 5:04 PM

#

I am asking if you maybe know if I can replace 'file' with the other path?

#

WHAT? 😦

#

_ file _

tawny sail Sep 24, 2019, 5:04 PM

#

folder order n stuff

#

we need someone else who is good at os module

torn sphinx Sep 24, 2019, 5:05 PM

#

📎 unknown.png

#

this is THE main folder,oky?

tawny sail Sep 24, 2019, 5:06 PM

#

where is the db located at

torn sphinx Sep 24, 2019, 5:06 PM

#

and THIS is where the database is

#

📎 unknown.png

#

staff.db

tawny sail Sep 24, 2019, 5:06 PM

#

u can just do sqlite3.connect("cogs/staff.db")

torn sphinx Sep 24, 2019, 5:07 PM

#

YEEE

#

I will try that

#

but I put the whole path in

#

because in the past it bugged on me

tawny sail Sep 24, 2019, 5:07 PM

#

ur working dir is untitled so just doing cogs/staff.db should work

#

if ur using that green triangle run button

torn sphinx Sep 24, 2019, 5:08 PM

#

nah,I want to be secure XD

#

yes,I am

tawny sail Sep 24, 2019, 5:08 PM

#

cool

#

working now?

torn sphinx Sep 24, 2019, 5:11 PM

#

not yet,BUT I get 0 errors 😄

tawny sail Sep 24, 2019, 5:11 PM

#

remove the timeout

#

i dont think u need it

torn sphinx Sep 24, 2019, 5:11 PM

#

nono,I think my code is bad

tawny sail Sep 24, 2019, 5:11 PM

#

i belive u set timeout on servers

torn sphinx Sep 24, 2019, 5:11 PM

#

I did

#

why?

#

I got the same error - database locked

#

so that fixed it

#

before

tawny sail Sep 24, 2019, 5:12 PM

#

u added timeout and it fixed?

torn sphinx Sep 24, 2019, 5:12 PM

#

HOLY SHIT it worked 😮

#

yes

tawny sail Sep 24, 2019, 5:12 PM

#

okay im confused

#

i need to brush up some db stuff

torn sphinx Sep 24, 2019, 5:16 PM

#

again

  SQL.execute('insert into Staff_List(Server_ID, Server_Name, Member_ID) values(?,?,?)', (server_ID, server_name, member_ID,))
sqlite3.OperationalError: database is locked

I JUST ADDED A 'RETURN' TO THE CODE

#

😡

#

😭

#

SQL.execute(f'select Member_ID from Staff_list where Server_ID = ?', (server_ID,))
            member_dbID = SQL.fetchall()
            print(member_dbID)
            for i in member_dbID:
                print(i[0])
                if i[0] == member_ID:
                    await ctx.send("This member is already on the staff list.")
                    return
                if not i[0] == member_ID:
                    SQL.execute('insert into Staff_List(Server_ID, Server_Name, Member_ID) values(?,?,?)', (server_ID, server_name, member_ID,))
                    db.commit()
                    await ctx.send("Member has been added to the list.")

This my code

#

This for loop may be the cause of it :/

tawny sail Sep 24, 2019, 5:17 PM

#

are u closing connection? (dunno if its related but just wanna make sure)

#

u should close db connection

torn sphinx Sep 24, 2019, 5:19 PM

#

no?

#

how do I do that

#

I never do that 😂

tawny sail Sep 24, 2019, 5:19 PM

#

...

#

<conn.object>.close()

torn sphinx Sep 24, 2019, 5:19 PM

#

._.

tawny sail Sep 24, 2019, 5:20 PM

#

what have u named ur connection object

torn sphinx Sep 24, 2019, 5:20 PM

#

db

tawny sail Sep 24, 2019, 5:20 PM

#

db.close()

torn sphinx Sep 24, 2019, 5:21 PM

#

WHAT is this?
staff.db-journal

#

a blank file

#

also not working

#

locked again

tawny sail Sep 24, 2019, 5:22 PM

#

remove comma after member_ID and try

torn sphinx Sep 24, 2019, 5:22 PM

#

0.o0.o

#

but

tawny sail Sep 24, 2019, 5:23 PM

#

im not sure whats going on :/

torn sphinx Sep 24, 2019, 5:23 PM

#

that bad

#

still not working

#

Can I send the whole cog?

tawny sail Sep 24, 2019, 5:23 PM

#

lemme get someone who is better at this

#

paste it some(paste bin or i belive pydis has something like that)\

torn sphinx Sep 24, 2019, 5:24 PM

#

what do you mean

#

!paste

delicate fieldBOT Sep 24, 2019, 5:24 PM

#

Pasting large amounts of code

If your code is too long to fit in a codeblock in discord, you can paste your code here:
https://paste.pydis.com/

After pasting your code, save it by clicking the floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

torn sphinx Sep 24, 2019, 5:24 PM

#

this?

tawny sail Sep 24, 2019, 5:24 PM

#

yea

torn sphinx Sep 24, 2019, 5:24 PM

#

cool

#

https://paste.pydis.com/epejihoxon.py

#

there,sir

tawny sail Sep 24, 2019, 5:26 PM

#

please dont create tables in commands

torn sphinx Sep 24, 2019, 5:26 PM

#

I JUST edited that

#

it was at the top

tawny sail Sep 24, 2019, 5:26 PM

#

have a seperate python file where u do table creating n stuff

torn sphinx Sep 24, 2019, 5:26 PM

#

HOW

#

DO I IMPORT

#

😦

#

I always got errors while tring that

tawny sail Sep 24, 2019, 5:27 PM

#

make a file called sql_setup.py and run python script for creating table in db

#

and create table only once(i mean run only once)

torn sphinx Sep 24, 2019, 5:27 PM

#

so like setup a function that when called creates a table?

tawny sail Sep 24, 2019, 5:27 PM

#

yea

torn sphinx Sep 24, 2019, 5:27 PM

#

and how will I only know only once?

tawny sail Sep 24, 2019, 5:27 PM

#

u should do it once cuz u cannot make duplicate table

torn sphinx Sep 24, 2019, 5:27 PM

#

🤔 makes sense

tawny sail Sep 24, 2019, 5:27 PM

#

if u do again, u will get table already exists error

#

line 36 use else

torn sphinx Sep 24, 2019, 5:29 PM

#

something like this:

    DIR = os.path.dirname("C:\\Users\\Crossfire StationXd\\PycharmProjects\\untitled1\\")
    db = sqlite3.connect(os.path.join(DIR, "staff.db"), timeout=5)
    SQL = db.cursor()

    SQL.execute('create table if not exists Staff_List("ID" integer not null primary key autoincrement,"Server_ID" integer, "Server_Name" text, "Member_ID" integer)')
    db.commit()
    return SQL
```?

#

the returning thingy xd

#

also I tried using else's,elif's

tawny sail Sep 24, 2019, 5:30 PM

#

no that i not what i ment

torn sphinx Sep 24, 2019, 5:30 PM

#

I know

#

I asked a different question there

tawny sail Sep 24, 2019, 5:30 PM

#

https://github.com/RohanJnr/Gator_Bot_no_Gspread/blob/master/sqlite_setup.py
u need a different file like this

GitHub

RohanJnr/Gator_Bot_no_Gspread

A discord bot used extensively for clash of clans purpus. - RohanJnr/Gator_Bot_no_Gspread

torn sphinx Sep 24, 2019, 5:30 PM

#

😮

#

HOW do I ..

#

do I ummm just connected to the database everytime I rune the 'addstaff'command?

#

so everything else except the creating table part?

tawny sail Sep 24, 2019, 5:32 PM

#

do everything except create table

#

u create table once in life

torn sphinx Sep 24, 2019, 5:32 PM

#

ye

#

epic!

#

makes sense BECAUSE

#

it creates once

#

not for every server,ye

tawny sail Sep 24, 2019, 5:32 PM

#

https://paste.pydis.com/oxucofagir.py

#

copy this code and try

#

@harsh pulsar can u look into this issue, im not able to solve it

#

about db being locked

torn sphinx Sep 24, 2019, 5:35 PM

#

also I never copy-paste code

#

yo this no work

else not i[0] == member_dbID:

tawny sail Sep 24, 2019, 5:38 PM

#

elif

torn sphinx Sep 24, 2019, 5:38 PM

#

ye the 'else' part

#

ye

tawny sail Sep 24, 2019, 5:38 PM

#

use do else:

torn sphinx Sep 24, 2019, 5:38 PM

#

oky

#

it was like that

#

before I went to change it again :/

tawny sail Sep 24, 2019, 5:39 PM

#

the problem is still there?

#

db locked?

torn sphinx Sep 24, 2019, 5:39 PM

#

ummm YE

#

nothing changed pretty much

#

except the making table thing

tawny sail Sep 24, 2019, 5:40 PM

#

use this

db = sqlite3.connect("cogs/staff.db")
SQL = db.cursor()```

torn sphinx Sep 24, 2019, 5:40 PM

#

👺

tawny sail Sep 24, 2019, 5:40 PM

#

n try

torn sphinx Sep 24, 2019, 5:40 PM

#

bro

#

I will change os chdir then

tawny sail Sep 24, 2019, 5:41 PM

#

dont use os

torn sphinx Sep 24, 2019, 5:41 PM

#

bro

#

it will go again instied cogs

#

wait wt.f

#

it is not in cogs anymore

#

I moved it to the main folder lol

tawny sail Sep 24, 2019, 5:41 PM

#

then

db = sqlite3.connect("staff.db")
SQL = db.cursor()```

torn sphinx Sep 24, 2019, 5:41 PM

#

ye

#

already did,lets see

#

ErRor

#

same

#

I removed os

tawny sail Sep 24, 2019, 5:43 PM

#

did u put the code which i gave?

torn sphinx Sep 24, 2019, 5:43 PM

#

No

#

I went through it

#

I will go again

tawny sail Sep 24, 2019, 5:43 PM

#

i dont understand how can working with db be so complex, i never had these issues 🤔

#

id say move this issue to one of the help channels, more activity there, sorry im not able to solve this

torn sphinx Sep 24, 2019, 5:45 PM

#

:d

#

Thank you ❤

harsh pulsar Sep 24, 2019, 5:45 PM

#

@torn sphinx i suggest maybe moving a little more carefully

torn sphinx Sep 24, 2019, 5:46 PM

#

:d

harsh pulsar Sep 24, 2019, 5:46 PM

#

instead of "trying things" maybe spend some time reading documentation and understanding how things work and are intended to work

#

from your questions here it seems like you just throw down some code that looks like it might work

tawny sail Sep 24, 2019, 5:46 PM

#

i dont understand why its locked

harsh pulsar Sep 24, 2019, 5:46 PM

#

which is fine, but eventually becomes frustrating both for you and the people trying to help you

tawny sail Sep 24, 2019, 5:46 PM

#

never experienced it before

harsh pulsar Sep 24, 2019, 5:46 PM

#

@tawny sail can you state the issue

tawny sail Sep 24, 2019, 5:47 PM

#

he is having db being locked for some reason

torn sphinx Sep 24, 2019, 5:47 PM

#

😦

tawny sail Sep 24, 2019, 5:47 PM

#

can u paste ur code again, so salt can look

torn sphinx Sep 24, 2019, 5:47 PM

#

1minute

#

I mean I did spent my whole day on this ,salt

#

if that counts about being careful

tawny sail Sep 24, 2019, 5:48 PM

#

i still dont understand why u need that timeout

torn sphinx Sep 24, 2019, 5:49 PM

#

I already told you,sir 😦

#

It fixed 1 error in the past: 'database locked'

tawny sail Sep 24, 2019, 5:49 PM

#

okay

harsh pulsar Sep 24, 2019, 5:49 PM

#

share the code

#

and the full error traceback

torn sphinx Sep 24, 2019, 5:49 PM

#

https://paste.pydis.com/nuyovewazo.py

#

there,sir

#

full error code:
https://paste.pydis.com/pimonipuzi.py

pliant oxide Sep 24, 2019, 5:51 PM

#

Hi, how to select only offers with the greatest "search_id" value? (It should return only last found offers)

📎 unknown.png

#

*sorry for cut off

harsh pulsar Sep 24, 2019, 5:51 PM

#

@pliant oxide groupby and max?

tawny sail Sep 24, 2019, 5:52 PM

#

yes DESC order

pliant oxide Sep 24, 2019, 5:53 PM

#

but how that SQL should be looked

tawny sail Sep 24, 2019, 5:53 PM

#

get the max value and another query to filter out all values = that max value

pliant oxide Sep 24, 2019, 5:53 PM

#

hmm

harsh pulsar Sep 24, 2019, 5:54 PM

#

@torn sphinx writing to a sqlite database locks the database. it looks like either you're attempting to do concurrent writes, or you're failing to release something after writing

#

since discord.py is inherently async, you will need to implement some kind of access control around the database

#

which will require a more "structured" application if you want to stay sane

tawny sail Sep 24, 2019, 5:54 PM

#

SELECT MAX(ur col name) from table name i belive

pliant oxide Sep 24, 2019, 5:55 PM

#

yes, it gives me a max value

#

but how to use it then

tawny sail Sep 24, 2019, 5:56 PM

#

so now

pliant oxide Sep 24, 2019, 5:56 PM

#

SELECT MAX(search_id) FROM eskycrawler_dev.offers;

tawny sail Sep 24, 2019, 5:56 PM

#

try it

torn sphinx Sep 24, 2019, 5:56 PM

#

:d

harsh pulsar Sep 24, 2019, 5:56 PM

#

do you have a primary key @pliant oxide

pliant oxide Sep 24, 2019, 5:56 PM

#

yes, even 3 primary keys xd

#

📎 unknown.png

#

whoa, don't remove msg xD

harsh pulsar Sep 24, 2019, 5:57 PM

#

@pliant oxide what are you grouping by, to find the max?

#

or you just want the max in the whole table

torn sphinx Sep 24, 2019, 5:59 PM

#

how do you mean access control? I am not a fucking computer scientist 😭

pliant oxide Sep 24, 2019, 5:59 PM

#

The problem is: I got OFFERS table where I store my offers. In the first search all founded offers got search_id = 1. When I start program again (second search) it should search_id = 2.
And... I just care only about last founded offers

torn sphinx Sep 24, 2019, 5:59 PM

#

I apologise for my language

harsh pulsar Sep 24, 2019, 5:59 PM

#

@torn sphinx i'm still looking don't worry

#

let me get back to you in a bit on this

torn sphinx Sep 24, 2019, 5:59 PM

#

oh my GOD

#

IT WORKED

#

ALL I HAVE TO DO IS CLOSE MY DATABASE BROWSER

#

🤦

harsh pulsar Sep 24, 2019, 6:00 PM

#

oh LOL

#

yeah

#

so

torn sphinx Sep 24, 2019, 6:00 PM

#

wai

#

wait

harsh pulsar Sep 24, 2019, 6:00 PM

#

did you forget to save in the db browser

torn sphinx Sep 24, 2019, 6:00 PM

#

before you leave

#

I thought I saved it..

harsh pulsar Sep 24, 2019, 6:00 PM

#

it was probably holding a lock open

torn sphinx Sep 24, 2019, 6:00 PM

#

anyways

harsh pulsar Sep 24, 2019, 6:00 PM

#

no im still going to look at your code

#

there are a few places for improvement here

#

first of all that chdir is scary and not needed

torn sphinx Sep 24, 2019, 6:01 PM

#

How do I get rid of the FOR LOOP things? 😦 it prints to the server both

Member has been added to the list.
This member is already on the staff list.

#

I have a 'return' if the member is added to the list

pliant oxide Sep 24, 2019, 6:01 PM

#

@tawny sail

SELECT * FROM eskycrawler_dev.offers WHERE search_id == SELECT MAX(search_id) FROM eskycrawler_dev.offers;```
that doesn't work ;c

torn sphinx Sep 24, 2019, 6:01 PM

#

thansk salt,I apprecaite it

#

but I think I do need the os.chdir

#

BECAUSE

harsh pulsar Sep 24, 2019, 6:01 PM

#

@pliant oxide that doesnt make sense and isnt valid syntax

torn sphinx Sep 24, 2019, 6:02 PM

#

last time I did not have it,the database got saved to the /cogs folder

harsh pulsar Sep 24, 2019, 6:02 PM

#

SELECT * FROM eskycrawler_dev.offers WHERE search_id == MAX(search_id)

@pliant oxide

#

@torn sphinx you can put the full path in the .connect

#

not just a filename

torn sphinx Sep 24, 2019, 6:02 PM

#

but I shall remove that os.chdir sthingy

#

what do you mean just not a filename? 0.o

harsh pulsar Sep 24, 2019, 6:03 PM

#

sqlite3.connect("C:\\Users\\Crossfire StationXd\\PycharmProjects\\untitled1\\staff.db")

you can write that

torn sphinx Sep 24, 2019, 6:03 PM

#

dude

#

what is that \staff.db ?

#

you said no filename.. 😂

harsh pulsar Sep 24, 2019, 6:03 PM

#

the filename from your code

#

no

#

i said not just a filename

torn sphinx Sep 24, 2019, 6:03 PM

#

._.

tawny sail Sep 24, 2019, 6:03 PM

#

@pliant oxide that's why I deleted it that time, sorry, I guess I'm using orm and sqlalchemy more these days 🙁

#

Salt I think he can provide only the dB name cuz his working dir is set to untitled1

torn sphinx Sep 24, 2019, 6:05 PM

#

I am more safe with
db = sqlite3.connect("C:\\Users\\Crossfire StationXd\\PycharmProjects\\untitled1\\staff.db", timeout=5)

tawny sail Sep 24, 2019, 6:05 PM

#

Okay

#

Sry

torn sphinx Sep 24, 2019, 6:05 PM

#

I am talking from experience 😂

#

but thank you for the worries 🙂

tawny sail Sep 24, 2019, 6:06 PM

#

I myself couldn't understand :/, dint be of any help

torn sphinx Sep 24, 2019, 6:06 PM

#

https://discordapp.com/channels/267624335836053506/342318764227821568/626115886729920515

#

Thanks anyways,you did help me @tawny sail ,you got salt into helping me 😅

harsh pulsar Sep 24, 2019, 6:06 PM

#

@torn sphinx have you considered setting uniqueness constraints on your table?

torn sphinx Sep 24, 2019, 6:07 PM

#

no,but I have been considering saving up for a brain transplatation..

#

:d

harsh pulsar Sep 24, 2019, 6:07 PM

#

what code did you run to create the table?

torn sphinx Sep 24, 2019, 6:07 PM

#

I never heard of 'uniqueness constraints'

harsh pulsar Sep 24, 2019, 6:07 PM

#

oh i see

tawny sail Sep 24, 2019, 6:07 PM

#

I told him have a separate file to create table once

torn sphinx Sep 24, 2019, 6:07 PM

#

SQL.execute('create table if not exists Staff_List("ID" integer not null primary key autoincrement,"Server_ID" integer, "Server_Name" text, "Member_ID" integer)')
        db.commit()
        db.close()
``` SIR

#

Yes,I did make one, Iceman! 🙂

tawny sail Sep 24, 2019, 6:07 PM

#

Nice

harsh pulsar Sep 24, 2019, 6:08 PM

#

why are you re-creating the table whenever staffhelp is called

torn sphinx Sep 24, 2019, 6:08 PM

#

But I will only use it NEVER again xd

#

I am not,sir!

harsh pulsar Sep 24, 2019, 6:08 PM

#

    @commands.command()
    async def staffhelp(self, ctx):
        author = ctx.message.author
        author_ID = ctx.message.author.id
        server_name = str(ctx.guild)
        server_ID = ctx.guild.id

        db = sqlite3.connect(self.staff_dbfile, timeout=5)
        SQL = db.cursor()

        SQL.execute('create table if not exists Staff_List("ID" integer not null primary key autoincrement,"Server_ID" integer, "Server_Name" text, "Member_ID" integer)')
        db.commit()

torn sphinx Sep 24, 2019, 6:08 PM

#

what

harsh pulsar Sep 24, 2019, 6:08 PM

#

oh yeah, so quick change

#

class StaffCommand(commands.Cog):
    staff_dbfile = "C:\\Users\\Crossfire StationXd\\PycharmProjects\\untitled1\\staff.db"

torn sphinx Sep 24, 2019, 6:08 PM

#

O.O

#

😱

#

thanks

harsh pulsar Sep 24, 2019, 6:08 PM

#

now you can get the filename from self.staff_dbfile

torn sphinx Sep 24, 2019, 6:09 PM

#

I already checked,but I misssed it somehow

#

the filename?

#

oh

tawny sail Sep 24, 2019, 6:09 PM

#

Know oop?

torn sphinx Sep 24, 2019, 6:09 PM

#

yyyyyyyyyyyyyyyyyyyyyyyyyyyeaaah?

#

I remember those...

tawny sail Sep 24, 2019, 6:09 PM

#

Okay good

torn sphinx Sep 24, 2019, 6:09 PM

#

I do

#

..

tawny sail Sep 24, 2019, 6:10 PM

#

U can access the file location with the name mentioned by salt

torn sphinx Sep 24, 2019, 6:10 PM

#

But I have not used them more or less

#

OH

#

so you mean

#

I can get a variable?

#

from self.staff_dbfile.VAR?

tawny sail Sep 24, 2019, 6:11 PM

#

So now dB = sqlite3.connect(self.staff_dbfile)

torn sphinx Sep 24, 2019, 6:11 PM

#

._.

tawny sail Sep 24, 2019, 6:11 PM

#

Okay I gotta go now, geting late for me :/, almost mid night, I guess salt will be here

#

Sorry

#

Thanks @harsh pulsar n sry for the tag before

torn sphinx Sep 24, 2019, 6:12 PM

#

Thank you,Iceman for your hours! 😄

#

Bless you

#

But that did not help me solve https://discordapp.com/channels/267624335836053506/342318764227821568/626115886729920515 :d

harsh pulsar Sep 24, 2019, 6:14 PM

#

i'm getting there

#

be patient

torn sphinx Sep 24, 2019, 6:14 PM

#

oh

#

Sorry! 😅 I thought you were gone somewhere

harsh pulsar Sep 24, 2019, 6:14 PM

#

i also recommend removing server_name from your table

#

it doesn't help and it can go out of date at any time

#

you can always get the server name with self.client.get_guild(server_ID).name

torn sphinx Sep 24, 2019, 6:14 PM

#

I know,but it is used just so I know what to edit if needed

#

and it is updated on every event on_message

#

I = stupid,but with lots of time I = smort :3

#

sometimes*

#

you strugling?

📎 laughing_emoji.PNG

#

If you are taking your time,no problem xD I am just joking around,I appreciate any help! I am chilling meanwhile anyways soo 😄

#

..please reply

obsidian iron Sep 24, 2019, 6:36 PM

#

using sqlite3 how do i count up how many times a specific value is present

torn sphinx Sep 24, 2019, 6:37 PM

#

🤔

#

autoincrement

obsidian iron Sep 24, 2019, 6:37 PM

#

whats that

torn sphinx Sep 24, 2019, 6:37 PM

#

here an example:

        db.commit()
        db.close()

#

so for every row

#

the ID from the table adds a +1

#

so for example:46
ID SERVER_ ID SERVER_NAME MEMBER_id
1 57636345346 SERVER-1 3463463
2 3474554636345 server-2 34637345

pliant oxide Sep 24, 2019, 6:39 PM

#

@tawny sail

SELECT * FROM eskycrawler_dev.offers WHERE search_id = MAX(search_id)

Still that doesn't work

#

I maybe found an answer

#

https://stackoverflow.com/questions/39086549/mysql-invalid-use-of-group-function-with-max?rq=1

Stack Overflow

MySQL Invalid use of group function with max()

I am trying to select a boolean value where order.lastUpdated is older than 30 minutes ago, but I get the error:
SQL state [HY000]; error code [1111]; Invalid use of group function;
nested

#

but I don't understand that

ivory turtle Sep 24, 2019, 6:40 PM

#

That's actually not what derek was asking lmao
@torn sphinx

pliant oxide Sep 24, 2019, 6:40 PM

#

@ivory turtle maybe you know how to fix it?

ivory turtle Sep 24, 2019, 6:40 PM

#

@obsidian iron sql has a count() query

#

match it against a condition

#

boom

torn sphinx Sep 24, 2019, 6:40 PM

#

I am trying my best @ivory turtle 😦 I really hoped it was the answer

ivory turtle Sep 24, 2019, 6:41 PM

#

@pliant oxide gimme a sec, multitasking

obsidian iron Sep 24, 2019, 6:41 PM

#

great. thank you!

pliant oxide Sep 24, 2019, 6:41 PM

#

it's ok, I can wait 🙂

torn sphinx Sep 24, 2019, 6:41 PM

#

wait it worked @obsidian iron ?

obsidian iron Sep 24, 2019, 6:41 PM

#

no, i said thanks for the idea

torn sphinx Sep 24, 2019, 6:41 PM

#

oh :d

harsh pulsar Sep 24, 2019, 6:41 PM

#

@torn sphinx what's the purpose of the check in staffhelp, you just want to see if the member is in the staff list?

obsidian iron Sep 24, 2019, 6:41 PM

#

im going to see if it works i just said thanks for the help

torn sphinx Sep 24, 2019, 6:42 PM

#

YEAHS!

#

😄

#

oh thanks for saying thanks,derek! 🙂

pliant oxide Sep 24, 2019, 6:44 PM

#

Ok, I think I found a solution

obsidian iron Sep 24, 2019, 6:45 PM

#

also i got one big question

#

im creating a discord bot and need to add database functionality

torn sphinx Sep 24, 2019, 6:45 PM

#

yeah?

obsidian iron Sep 24, 2019, 6:45 PM

#

is closing the database NECESSARY if the bot is on 24/7?

torn sphinx Sep 24, 2019, 6:45 PM

#

it is good to close it

obsidian iron Sep 24, 2019, 6:46 PM

#

considering i'll be using definitions to add to the database, if need be where would i close it

torn sphinx Sep 24, 2019, 6:46 PM

#

but if it is necasseraly 🤔 I may not know

harsh pulsar Sep 24, 2019, 6:46 PM

#

@torn sphinx https://paste.pydis.com/gulizafeqo.py

#

i commented out the last function because that part is your homework

#

note i added a new function at the end which creates a table using a UNIQUE constraint

obsidian iron Sep 24, 2019, 6:46 PM

#

@torn sphinx no i was referring to the count().. sorry

#

just reread

#

earlier on

torn sphinx Sep 24, 2019, 6:47 PM

#

cool

#

@obsidian iron no problem 🙂

#

yeah the last function was the first thing that cought my eye

harsh pulsar Sep 24, 2019, 6:50 PM

#

you will probably want to keep this as a reference https://www.sqlite.org/lang.html

#

same with https://docs.python.org/3/library/sqlite3.html

torn sphinx Sep 24, 2019, 6:53 PM

#

bookmarked

obsidian iron Sep 24, 2019, 6:54 PM

#

anybody know if i should close the database connection in a python bot

#

and if im using definitions for functions where should i close it considering the bot is online 24/7 and at no point will i need to stop database functionality

harsh pulsar Sep 24, 2019, 6:55 PM

#

eh

#

not necessary i think

#

but you might need some functionality to restart the db connection if it craps out

ivory turtle Sep 24, 2019, 6:57 PM

#

@obsidian iron In answer to your earlier question

using sqlite3 how do i count up how many times a specific value is present

SELECT COUNT(column_name) FROM table_name WHERE condition;

#

@pliant oxide done multi tasking, lemme check

pliant oxide Sep 24, 2019, 6:57 PM

#

I know already

ivory turtle Sep 24, 2019, 6:57 PM

#

oh, great

#

🙂

obsidian iron Sep 24, 2019, 6:58 PM

#

awesome, lunar thanks

#

also have any idea on my 2nd question about the .close?

pliant oxide Sep 24, 2019, 6:58 PM

#

SELECT * FROM eskycrawler_dev.offers WHERE search_id = (SELECT MAX(search_id) FROM eskycrawler_dev.offers)

@ivory turtle I had to make nested SELECT

#

;f

ivory turtle Sep 24, 2019, 6:58 PM

#

Didn't read it, mind reposting your 2nd quetsions?

obsidian iron Sep 24, 2019, 6:58 PM

#

anybody know if i should close the database connection in a python bot
and if im using definitions for functions where should i close it considering the bot is online 24/7 and at no point will i need to stop database functionality

ivory turtle Sep 24, 2019, 6:58 PM

#

@pliant oxide I see, that's some funky SQL stuff eh ;P

#

Ah

#

Yes, so normally

#

You'd init a db

#

And start connection whenever you need to fetch/store

#

then close

obsidian iron Sep 24, 2019, 6:59 PM

#

i see

ivory turtle Sep 24, 2019, 6:59 PM

#

Mainly for the purpose of safe coding iirc

obsidian iron Sep 24, 2019, 7:00 PM

#

could i do something like that

#

def add_infraction(connection,imember,ireason):
    with connection:
        c.execute("INSERT INTO infractions VALUES (:member, :reason)",{'member':imember,'reason':ireason})

add_infraction(sqlite.connect("infractions.db"),"test","test")

ivory turtle Sep 24, 2019, 7:00 PM

#

I believe I read that the correct handling of opening/closing connections happens outside of the function, but so far I've seen most people close/open connections inside functions

#

I'm honestly not too well versed in sqlite, but it doesn't look wrong to me 👀

obsidian iron Sep 24, 2019, 7:01 PM

#

and then i'd be able to close it inside the function right? @ivory turtle

ivory turtle Sep 24, 2019, 7:02 PM

#

yes

#

https://stackoverflow.com/questions/9561832/what-if-i-dont-close-the-database-connection-in-python-sqlite

Stack Overflow

What if I don't close the database connection in Python SQLite

I am doing something like this...

conn = sqlite3.connect(db_filename)

with conn:
cur = conn.cursor()
cur.execute( ... )
with automatically commits the changes. But the docs say nothing a...

#

This sums it up quite nicely I think

#

Also, I wouldn't pass connection as an arg, seeing as that'd allow it to be dynamic, when you very much want that function to work in the same database always?
~~Or maybe you want to switch databases for that one function eh?~~

torn sphinx Sep 24, 2019, 7:04 PM

#

how do you do that text? :p

#

😮

#

crossed out one

harsh pulsar Sep 24, 2019, 7:04 PM

#

~~crossed out~~

ivory turtle Sep 24, 2019, 7:04 PM

#

Double tilde on both side

torn sphinx Sep 24, 2019, 7:04 PM

#

~~test~~

#

NOOL

#

~~So this should be crossed out!~~

#

epic!

#

thanks

#

wow

#

I will absorb knowledge like a spunge from salt rock lamp's code they sent me

#

I was suspicious about if a try block can have an 'else:' statement

obsidian iron Sep 24, 2019, 7:07 PM

#

search up discord markdown

torn sphinx Sep 24, 2019, 7:08 PM

#

oh thanks! 😄

#

I can use that for my discord bot now

harsh pulsar Sep 24, 2019, 7:08 PM

#

!e

def do_thing(x):
    print(f'INPUT: {x!r}')
    try:
        int(x)
    except ValueError:
        print('An error occurred')
    else:
        print('An error did not occur')
    finally:
        print('This part always executes')

do_thing('3')
print()
do_thing('a')

delicate fieldBOT Sep 24, 2019, 7:08 PM

#

@harsh pulsar Your eval job has completed with return code 0.

001 | INPUT: '3'
002 | An error did not occur
003 | This part always executes
004 | 
005 | INPUT: 'a'
006 | An error occurred
007 | This part always executes

ivory turtle Sep 24, 2019, 7:10 PM

#

Question actually regarding exception types

obsidian iron Sep 24, 2019, 7:12 PM

#

lunar, replying back to your first response

#

would i store that in a variable?

ivory turtle Sep 24, 2019, 7:12 PM

#

!e

emoji = "👀"
try:
    emoji_id = await commands.EmojiConverter().convert(ctx, emoji)
    emoji_id = emoji_id.id
except Exception as e:
    print(e)

delicate fieldBOT Sep 24, 2019, 7:12 PM

#

Sorry, but you may only use this command within #bot-commands.

ivory turtle Sep 24, 2019, 7:12 PM

#

oh.

obsidian iron Sep 24, 2019, 7:12 PM

#

or would i execute it? i'm new to databases as a whole

ivory turtle Sep 24, 2019, 7:12 PM

#

I'm not allowed to eval here huh

obsidian iron Sep 24, 2019, 7:13 PM

#

would i use .execute(..)

ivory turtle Sep 24, 2019, 7:13 PM

#

@obsidian iron Which response of mine? The part where I mentioned not wanting to dynamically parse a connection ?

#

👀

obsidian iron Sep 24, 2019, 7:13 PM

#

how would i get the response?

#

in a variable

ivory turtle Sep 24, 2019, 7:13 PM

#

Oh, the result?

obsidian iron Sep 24, 2019, 7:13 PM

#

var = c.execute()

#

yes

ivory turtle Sep 24, 2019, 7:13 PM

#

From your query?

#

basically what you just said yes

#

var = c.execute()

#

iirc

#

but lemme dig in sqlite docs 😮

obsidian iron Sep 24, 2019, 7:14 PM

#


SELECT COUNT(column_name) FROM table_name WHERE condition;

#

i realised i pasted the wrong one earlier

ivory turtle Sep 24, 2019, 7:15 PM

#

still same answer

#

make conn execute that query

#

store in var

#

Judging from this example from the docs

#

def select_all_tasks(conn):
    """
    Query all rows in the tasks table
    :param conn: the Connection object
    :return:
    """
    cur = conn.cursor()
    cur.execute("SELECT * FROM tasks")
 
    rows = cur.fetchall()
 
    for row in rows:
        print(row)

#

I'd assume you could also just

def select_all_tasks(conn):
    """
    Query all rows in the tasks table
    :param conn: the Connection object
    :return:
    """
    cur = conn.cursor()
    cur.execute("SELECT COUNT(column_name) FROM table_name WHERE condition; ")
 
    result = cur
 
    print(result)

#

Seeing as you only got 1 result

#

although you might have to use cur.fetchone()

#

(Depending on the type of returned result)

obsidian iron Sep 24, 2019, 7:20 PM

#

one more thing lunar!

#

for the conditions

#

does it differ from python conditions

#

say i'd be looking for the number 5

#

WHERE 5

#

right? @ivory turtle

ivory turtle Sep 24, 2019, 7:21 PM

#

WHERE column_name = 5

#

iirc

#

ah, single =

obsidian iron Sep 24, 2019, 7:21 PM

#

one more thing

ivory turtle Sep 24, 2019, 7:22 PM

#

That confused me.

obsidian iron Sep 24, 2019, 7:22 PM

#

how do i refer to parameters inside sql statements if they're just strings

ivory turtle Sep 24, 2019, 7:22 PM

#

many different methods tbf

#

All with their pros and cons

#

I mainly use fstring

#

same as when you'd use the print function

obsidian iron Sep 24, 2019, 7:22 PM

#

that works

ivory turtle Sep 24, 2019, 7:22 PM

#

print(f"{variable_name} is of type {type(variable_name)}")

#

Works

#

print('{} {}'.format(variable_name, 'two'))

also works

#

iirc

#

https://pyformat.info/

obsidian iron Sep 24, 2019, 7:24 PM

#

    cur = conn.cursor()
    cur.execute("SELECT COUNT(column_name) FROM table_name WHERE condition; ")
 
    result = cur

ivory turtle Sep 24, 2019, 7:24 PM

#

More on .format here

obsidian iron Sep 24, 2019, 7:24 PM

#

for this i got the object

#

instead of the value

#

yeah i keep getting the cursor object

ivory turtle Sep 24, 2019, 7:25 PM

#

c.execute(qry)
y=c.fetchone()[0]
print y

#

Did a quick SO search

#

This should return one result from conn with position 0 (not sure if that part is neccesary for you, since you should only have one result)

obsidian iron Sep 24, 2019, 7:26 PM

#

awesome, that works

ivory turtle Sep 24, 2019, 7:27 PM

#

Awesome

obsidian iron Sep 24, 2019, 7:27 PM

#

thank you very much, appreciated

ivory turtle Sep 24, 2019, 7:27 PM

#

No problem man, good learning moments for me too

pliant oxide Sep 24, 2019, 7:28 PM

#

@ivory turtle do you have some time?

#

I got strange idea

ivory turtle Sep 24, 2019, 7:29 PM

#

Sure

#

Shoot

pliant oxide Sep 24, 2019, 7:30 PM

#

I got a crawler which run search every few hours. I get flight offers from website and store in OFFERS table with important primary key "search_id" which reference in what search it found that offer.

#

For example:

📎 unknown.png

#

it's the offers from 1st and 2nd search

ivory turtle Sep 24, 2019, 7:32 PM

#

pl 👀

#

polski eh

pliant oxide Sep 24, 2019, 7:32 PM

#

Haha

ivory turtle Sep 24, 2019, 7:32 PM

#

c:

#

Okay so, crawler checks flight offers

pliant oxide Sep 24, 2019, 7:33 PM

#

ok, będzie trochę łatwiej, ale nikt nas nie zrozumie

ivory turtle Sep 24, 2019, 7:33 PM

#

Yeaaah, sorry

#

I tried to learn polish with polish friend of mine ages ago

#

Didn't work out

pliant oxide Sep 24, 2019, 7:33 PM

#

haha, ok, don't worry; let's continue in english

ivory turtle Sep 24, 2019, 7:33 PM

#

可以，我会说一点儿中文 😉

#

Took other language instead

pliant oxide Sep 24, 2019, 7:33 PM

#

; o

#

Ich weiss es nicht

ivory turtle Sep 24, 2019, 7:34 PM

#

Keine ahnung oder was ;P

pliant oxide Sep 24, 2019, 7:34 PM

#

How to SELECT only new offers. <- so we have to SELECT all last found offers (for example search_id=2) and remove offers from search_id=1

#

I just want to know only new offers

ivory turtle Sep 24, 2019, 7:34 PM

#

Deutch war auch gans nicht schwierig

#

Okay yes

pliant oxide Sep 24, 2019, 7:35 PM

#

I don't understand that much Deutch xd

ivory turtle Sep 24, 2019, 7:35 PM

#

English, focus focus

#

Dw dw, we're pursueing higher level programming languages anyways

#

Not like, actual languages :^)

#

So, you want to select new offers

#

The 'newest' only?

#

Or anything that's newer than x hours

pliant oxide Sep 24, 2019, 7:36 PM

#

I mean, the newest offers are those which got the greatest "search_id".

#

But...

ivory turtle Sep 24, 2019, 7:37 PM

#

Two distinct options of course;

For the offers released within x hours from now;
-Store datetime of moment when released
-Check condition against that with datetime.now

For only the actual newest,
-While selecting sort by ascending/descending, limit selection to top 10

#

Greatest search ID?

#

oh yes of course

#

So

pliant oxide Sep 24, 2019, 7:37 PM

#

I want to select only NEW offers. So offers that are on search_id = 2, but not on search_id = 1.

ivory turtle Sep 24, 2019, 7:37 PM

#

Order table by search_id, descending

#

limit selection to top 10

pliant oxide Sep 24, 2019, 7:37 PM

#

nah nah

ivory turtle Sep 24, 2019, 7:37 PM

#

actually

#

No

#

🤔

#

Lemme think on this for a mo

hallow cloud Sep 24, 2019, 7:38 PM

#

what exactly do you wanna do

pliant oxide Sep 24, 2019, 7:38 PM

#

But... do you understand what I want to achieve?

hallow cloud Sep 24, 2019, 7:38 PM

#

what do you classify as new

ivory turtle Sep 24, 2019, 7:38 PM

#

Yes, I think I understand

pliant oxide Sep 24, 2019, 7:39 PM

#

Get offers with the greatest search_id (last found offers) and then remove from those offers with "greatest search_id - 1"

ivory turtle Sep 24, 2019, 7:39 PM

#

Basically for column search_id, get the highest current value, then select everything matching that value for same column

#

Right?

#

then remove from those offers with "greatest search_id - 1"

Huh?

pliant oxide Sep 24, 2019, 7:39 PM

#

SELECT * FROM eskycrawler_dev.offers WHERE search_id = (SELECT MAX(search_id) FROM eskycrawler_dev.offers)

This gives me last found offers

#

Now

ivory turtle Sep 24, 2019, 7:39 PM

#

Okay, yes, what's the next step?

pliant oxide Sep 24, 2019, 7:40 PM

#

I want to remove from that SELECT offers which were in the search_id-1

#

Ah, how to explain that xD

#

Example:

#

@ivory turtle @hallow cloud

#

fuck

#

wait

#

📎 unknown.png

#

Now it's better

#

You see?

ivory turtle Sep 24, 2019, 7:44 PM

#

yes

#

But why only those 3 results

#

and not all with search_id = 2

pliant oxide Sep 24, 2019, 7:44 PM

#

Because they're "new"

ivory turtle Sep 24, 2019, 7:44 PM

#

👀

pliant oxide Sep 24, 2019, 7:44 PM

#

You got Paris in search 1

#

so we have to remove Paris from search 2

#

the same with Szczecin

ivory turtle Sep 24, 2019, 7:45 PM

#

ooh

#

Like that

#

Okay okay

#

Now I see

pliant oxide Sep 24, 2019, 7:45 PM

#

yes xD

#

great!

ivory turtle Sep 24, 2019, 7:45 PM

#

Duplicate entries from older column

#

Is there anything that makes them unique?

#

URI's ?

pliant oxide Sep 24, 2019, 7:45 PM

#

I don't understand question

#

This is my real table OFFERS:

📎 unknown.png

#

If it helps

harsh pulsar Sep 24, 2019, 7:47 PM

#

what database engine is this @pliant oxide

pliant oxide Sep 24, 2019, 7:47 PM

#

MySQL

ivory turtle Sep 24, 2019, 7:47 PM

#

Okay so what I meant is

#

If you get search results

#

And those with search_id 1 and 2 are the same

#

Can they be matched against each other

#

Without confusing them with other similar search results?

#

Are they, in a way, unique ?

pliant oxide Sep 24, 2019, 7:48 PM

#

We can say that results are the same if "outbound_flight_id" and "inbound_flight_id" pair is the same (that pair is unique).

ivory turtle Sep 24, 2019, 7:49 PM

#

hmm

#

So either some very intricate single query

#

Or just multiple queries

#

I'm leaning towards the latter tbh lol

#

Salt, thoughts?

harsh pulsar Sep 24, 2019, 7:50 PM

#

wait how does that even make sense

pliant oxide Sep 24, 2019, 7:50 PM

#

?

harsh pulsar Sep 24, 2019, 7:50 PM

#

MAX(search_id) returns exactly 1 number

pliant oxide Sep 24, 2019, 7:50 PM

#

Ok, but what's the question?

harsh pulsar Sep 24, 2019, 7:51 PM

#

https://discordapp.com/channels/267624335836053506/342318764227821568/626141722996637696

#

how can this output possibly result from that query?

pliant oxide Sep 24, 2019, 7:51 PM

#

I don't know the query.

#

I only know what I want to achieve

harsh pulsar Sep 24, 2019, 7:51 PM

#

?

SELECT *
FROM eskycrawler_dev.offers
WHERE search_id = MAX(search_id)

pliant oxide Sep 24, 2019, 7:52 PM

#

this is the starting point, we still didn't remove Paris and Szczecin records

#

and I don't know how to do it

harsh pulsar Sep 24, 2019, 7:52 PM

#

and how do you know you want to remove Paris?

pliant oxide Sep 24, 2019, 7:52 PM

#

If Paris was in MAX(search_id) - 1 I remove it

harsh pulsar Sep 24, 2019, 7:53 PM

#

ah i see

#

how do you know which record "paris" is

#

what is the common identifier in your data

#

inbound/outbound flight id?

pliant oxide Sep 24, 2019, 7:54 PM

#

yes, that pair is unique

harsh pulsar Sep 24, 2019, 7:55 PM

#

SELECT t1.*
FROM (
  SELECT *
  FROM eskycrawler_dev.offers
  WHERE search_id = MAX(search_id)
) AS t1
JOIN (
  SELECT *
  FROM eskycrawler_dev.offers
  WHERE search_id = MAX(search_id) - 1
) AS t2 ON
  t1.inbound_flight_id <> t2.inbound_flight_id AND
  t1.outbound_flight_id <> t2.outbound_flight_id

pliant oxide Sep 24, 2019, 7:55 PM

#

whoa

#

t1 is?

#

ag

#

*ah

#

I see

harsh pulsar Sep 24, 2019, 7:56 PM

#

hmmmm wait

#

hm

#

let me see

#

1 sec

pliant oxide Sep 24, 2019, 7:57 PM

#

I'm waiting

#

I think in your solution I also get "London" in results (I don't wnat London in results):

📎 unknown.png

harsh pulsar Sep 24, 2019, 7:59 PM

#

yeah thats what i realized

pliant oxide Sep 24, 2019, 8:00 PM

#

Still we can duplicate ourselves. Just SELECT again MAXed search_id

harsh pulsar Sep 24, 2019, 8:03 PM

#

oh yeah mysql is garbage and doesnt allow you to write MAX in WHERE anyway

pliant oxide Sep 24, 2019, 8:03 PM

#

Yes, that's why I have to write:

SELECT * FROM eskycrawler_dev.offers WHERE search_id = (SELECT MAX(search_id) FROM eskycrawler_dev.offers)

#

that works

#

btw, can you explain me, what does "t1.*" means?

harsh pulsar Sep 24, 2019, 8:04 PM

#

im not sure if its even valid mysql syntax

#

it was just shorthand for "use t1 to prefix everything"

pliant oxide Sep 24, 2019, 8:04 PM

#

hah, ok

harsh pulsar Sep 24, 2019, 8:07 PM

#

do you want to exclude all earlier searches or just max-1

obsidian iron Sep 24, 2019, 8:07 PM

#

@ivory turtle

#

hey my dude

#

i was wondering

#

hm.

#

how do i explain

#

hm

#

say i have three sets of records

#

id: 5, level: 6

#

id: 5, level : 8

#

id 5:, level: 10

pliant oxide Sep 24, 2019, 8:09 PM

#

@harsh pulsar exclude just max-1

obsidian iron Sep 24, 2019, 8:09 PM

#

how do i loop through each record, checking if the id is 5 and making each of the levels 2 for example

pliant oxide Sep 24, 2019, 8:09 PM

#

I think it's even easier to exlude just max-1

ivory turtle Sep 24, 2019, 8:10 PM

#

@obsidian iron

UPDATE table_name SET level = 2 WHERE id = 5

#

?

obsidian iron Sep 24, 2019, 8:10 PM

#

yeah, that works thank y

#

ou

#

will it change each of them to 2?

ivory turtle Sep 24, 2019, 8:10 PM

#

yes

obsidian iron Sep 24, 2019, 8:10 PM

#

great, thats what i needed

ivory turtle Sep 24, 2019, 8:10 PM

#

All rows where id = 5

hallow cloud Sep 24, 2019, 8:16 PM

#

oh you want to only show results which had not been seen before in other id's

#

that's all you needed to say 😛

pliant oxide Sep 24, 2019, 8:18 PM

#

@hallow cloud yes xd do you know how to do it?

#

I think @harsh pulsar is doing something

hallow cloud Sep 24, 2019, 8:18 PM

#

it shouldn't be too hard

harsh pulsar Sep 24, 2019, 8:19 PM

#

im fiddling with a correlated subquery but its not quite right

pliant oxide Sep 24, 2019, 8:19 PM

#

btw, I have to do that in one query

#

because I will use it in the VIEW

hallow cloud Sep 24, 2019, 8:20 PM

#

select all names and then remove all the names that are in all other id's except the specified one

pliant oxide Sep 24, 2019, 8:20 PM

#

it only sounds like it's easy ;f

harsh pulsar Sep 24, 2019, 8:21 PM

#

this is as far as i got, i gotta get back to work. it's not working right though

select *
from offers o
where
  o.search_id = (select max(search_id) from offers)
  and
  not exists (
    select 1
    from offers o1
    where o1.search_id = (select max(search_id) - 1 from offers)
  )

narrow hull Sep 24, 2019, 8:21 PM

#

@harsh pulsar check #help-coconut

harsh pulsar Sep 24, 2019, 8:21 PM

#

?

#

please don't do that

pliant oxide Sep 24, 2019, 8:22 PM

#

what do tou mean "it's not working"? just syntax? or logic?

harsh pulsar Sep 24, 2019, 8:22 PM

#

https://www.db-fiddle.com/f/f29VDiG4wMghj4p1fafGux/0

its giving 0 results instead of 1

DB Fiddle - SQL Database Playground

An online SQL database playground for testing, debugging and sharing SQL snippets.

narrow hull Sep 24, 2019, 8:22 PM

#

what?

pliant oxide Sep 24, 2019, 8:22 PM

#

hm

hallow cloud Sep 24, 2019, 8:22 PM

#

i don't think it's hard, im a bit rusty in with my sql but lemme give it a twirl:

select  names from offers
where offers.name in (
    select name
    from offers
) and offers.name not in (
    select name
    from offers
    where offers.id!=ID)

#

something like this

#

(probably would throw a syntax erorr but you get the jist of it)

#

am i thinking in the right way

pliant oxide Sep 24, 2019, 8:24 PM

#

Ok guys, I got some idea, I will try to fix your codes to work and paste it just for information

hallow cloud Sep 24, 2019, 8:40 PM

#

oh..you litteraly use id to sort by date, as in id represents the chronological order of the items

#

yikes

#

use time formats for that next time please 😛

violet cairn Sep 24, 2019, 8:49 PM

#

If a table is dynamically modified with a schema modifying transaction while everything is running, is there a way to keep the graphql schema up to date?

pliant oxide Sep 24, 2019, 8:56 PM

#

@hallow cloud what? I'm using timestamp

hallow cloud Sep 24, 2019, 8:57 PM

#

wdym arent u searching by search_ids

pliant oxide Sep 24, 2019, 8:58 PM

#

it's my searches table

#

📎 unknown.png

#

but in that case I described, I'm using search_id

#

to get the right order

#

belive me, it's fine ;p

hallow cloud Sep 24, 2019, 9:11 PM

#

roite, you got a solution now :P?

proven wagon Sep 24, 2019, 9:20 PM

#

I have a list of 4 ids (BIGINT) That can be in one of 4 colums in a table, how can i check for this?

#

Do i just need to do a really long check?

patent glen Sep 24, 2019, 9:32 PM

#

uh what are the four columns?

#

but yeah, your options are basically "check each column separately with OR" or "redesign your schema", and I'd give serious thought to the latter

proven wagon Sep 24, 2019, 9:32 PM

#

creator, member1, member2, member3

patent glen Sep 24, 2019, 9:33 PM

#

what if it needs more than 3 members

proven wagon Sep 24, 2019, 9:33 PM

#

it doesent

#

wont ever

patent glen Sep 24, 2019, 9:33 PM

#

ok

proven wagon Sep 24, 2019, 9:33 PM

#

will always be 4 members

stable pilot Sep 24, 2019, 10:23 PM

#

hey guys
i have this code

  sql = "INSERT INTO passwordrecovery (discordID, discordName, ingameName, countryCreated, internetProvider, dateCreated) VALUES (%s, %s, %s, %s, %s, %s)"
  val = (ctx.author.id, ctx.author.name, username, location, isp, creation_date)

  try:
      async with get_conn as conn:
          async with conn.cursor() as cur:
              await cur.execute(sql, val)
      await conn.commit()
      print('created')
      await ctx.send('Your request has been sent through')
      await conn.close()

  except Exception as e:
      print(e)
      raise```
when i print exception
```__aexit__```
i get this
and whatever im doing
its not putting it in my database
am i doing something wrong ?

silent wyvern Sep 24, 2019, 11:53 PM

#

Has anyone gotten the error "WinError 10013" when trying to connect to a MySQL database that is hosted on your own pc? I have a few python files trying to connect to it to input information and read from it but when I run my init file I get that error

#

OSError: [WinError 10013] An attempt was made to access a socket in a way forbidden by its access permissions Full error message I'm given

patent glen Sep 25, 2019, 12:08 AM

#

that error is normally associated with a port already being in use... are you sure that's at the point where it's connecting to mysql, not where it's setting up the web server?

#

@silent wyvern

#

are you able to get a traceback

silent wyvern Sep 25, 2019, 12:10 AM

#

server_address = ('localhost', 3306) this is what I'm using to define the connection to the server, Am I not supposed to use the same port as what I setup the server to use? this is my first time using MySQL so I'm still learning this stuff

#

I decided to add the database to it last minute so I'm just trying to get it connected

patent glen Sep 25, 2019, 12:11 AM

#

no i mean

#

are you building a web app, that you access through the browser

silent wyvern Sep 25, 2019, 12:11 AM

#

no I'm not

patent glen Sep 25, 2019, 12:11 AM

#

what kind of application are you building

#

are you able to get a traceback for what line the error is happening on

silent wyvern Sep 25, 2019, 12:12 AM

#

Yeah I can post the whole thing

#

  File "C:/Users/mattt/PycharmProjects/Test/Automatic Youtube Uploader/YouTube Bot Server/initserver.py", line 53, in <module>
    init()
  File "C:/Users/mattt/PycharmProjects/Test/Automatic Youtube Uploader/YouTube Bot Server/initserver.py", line 40, in init
    socketserver.startServer()
  File "C:\Users\mattt\PycharmProjects\Test\Automatic Youtube Uploader\YouTube Bot Server\socketserver.py", line 21, in startServer
    socket.bind(server_address)
OSError: [WinError 10013] An attempt was made to access a socket in a way forbidden by its access permissions

#

The errors are coming from socket_address which is what I have set as my MySQL hostname and port to define them

patent glen Sep 25, 2019, 12:17 AM

#

ok so you are building a server

silent wyvern Sep 25, 2019, 12:17 AM

#

yeah

patent glen Sep 25, 2019, 12:17 AM

#

yeah, you don't want to be using the mysql port for your python script's server's address

#

and...... automatic youtube uploading sounds like probably something we can't help with, sorry (though i can't think of why you'd need to run a server for that)

#

but creating a socket server has nothing to do with connecting to mysql so there's no reason you'd use the same port for that

torn sphinx Sep 25, 2019, 12:57 AM

#

quick question

#

inner join vs left join

#

is it really different for this case:

#

A is a subset of B.. I'm trying to join A with B because B has more fields but I only want to complete the information available for common field between A and B

stable pilot Sep 25, 2019, 1:27 AM

#

hey guys i am just wondering how cna i send a gmail from python, but doing so securely

harsh pulsar Sep 25, 2019, 1:32 AM

#

https://stackabuse.com/how-to-send-emails-with-gmail-using-python/

Stack Abuse

How to Send Emails with Gmail using Python

There are quite a few ways to send email with Python, whether it be through a 3rd party library like with boto and SES, or through an email protocol like SMTP. While the subject of using Python to send emails may seem like it's been done to death, there are

hallow cloud Sep 25, 2019, 7:49 AM

#

make sure to enable the less secure connections on your gmail account

#

otherwise you could accidentally lock your account for 24h

toxic rune Sep 25, 2019, 8:24 AM

#

📎 unknown.png

#

Hello this is kind of a dumb question but my brain is fried right now. I have the schemas I posted above. I have multiple entries with the same identifier on GameClass but different locale so the primary key is locale, identifier. The issue is it's not letting me set up relationship because GameClass.identifier is not unique.

This is how the GameClass table data looks like.

 id | locale | identifier  |      class
----+--------+-------------+------------------
    | en-US  | knight      | Knight
    | es-AR  | knight      | Caballero

How can I set up the relationship between those two (GameArtifact.exclusive and GameClass.identifier) columns?

pure scroll Sep 25, 2019, 8:36 AM

#

it depends on what kind of relation you have, one to one, one to many, many to many

toxic rune Sep 25, 2019, 8:41 AM

#

well i think of it, one exclusive can be output in multiple languages

#

the issue is if i try to set the reference on gameartifact to gameclass it will complain it's not unique. I feel like I'm missing something there lol.

pure scroll Sep 25, 2019, 9:11 AM

#

yeah, foreignkey constraint should either target a unique key or a primary key (which is unique anyways)

#

so you can either reference game artifact in game class table

#

but I guess this is not desired

#

our just not use foreignkey constraint, what for are you going to use it?

#

of course there is a third option to use additional table that references all the gameclasses for a given game artifact

toxic rune Sep 25, 2019, 9:16 AM

#

yea that isnt desired. well every artifact (and another table, which i didnt work on yet) have a class (each with their unique identifier), which i assume in terms of how a database is designed should have a relationship to the gameclass table which holds the identifier and it's translation in multiple different locales

#

~~would be far easier if i didn't have the locale column lol~~

tawny sail Sep 25, 2019, 12:28 PM

#

should i download postgresql from terminal or enterpriseDB?

torn sphinx Sep 25, 2019, 1:12 PM

#

@harsh pulsar I am on your code from yesterday now..
I have an error ...this is kinda a rookie error if you ever worked with discord.py:

    await ctx.send("Please try this command in a server.")
    ^
SyntaxError: 'await' outside async function```

ivory turtle Sep 25, 2019, 1:15 PM

#

Guys, thouhts on a many-to-many relationship with only 1 table involded?
IE;
Table filled with users, however duplicate users exist since they belong to different guilds (so different unique PK, but identical user_id)

Users can gift eachother gifts, but within a set limit, ergo me wanting to store the transactions;

intermediary table
column 1 and 2 being Foreign keys of course, but both pointing to primary key of the user table, other columns holding datetime stamps of the transactions and whatnot.

Question being, is this even possible? table having a many-to-many relationship with itself? If not, is there a better way to do this by chance?

#

@torn sphinx Error says it all. You need to use await inside an async function

torn sphinx Sep 25, 2019, 1:17 PM

#

It is not my code,just telling the output to salt

#

I know that

ivory turtle Sep 25, 2019, 1:18 PM

#

I see, sorry misread that.

torn sphinx Sep 25, 2019, 1:18 PM

#

what do you think,I started programming using Discord yesterday? no,30 days ago 😂

ivory turtle Sep 25, 2019, 1:18 PM

#

Same difference tbh 🙂

torn sphinx Sep 25, 2019, 1:18 PM

#

No problem,literally can happen to everyone 😅

#

😒

ivory turtle Sep 25, 2019, 1:18 PM

#

I've seen people work with it for years and make the same mistakes still lol

torn sphinx Sep 25, 2019, 1:20 PM

#

Thanks 😅

#

I know it is a easy fix,though

#

unless it HAS to be outside function to simply work fast

#

yeah,I think they put that block of code in the wrong spot

#

because they are calling it by self.function
but it not even inside the class 😅

#

or just to teach me 🤔

torn sphinx Sep 25, 2019, 2:46 PM

#

It does not add the database @harsh pulsar