#databases | Python | Page 44

rancid bronze Apr 13, 2018, 5:27 AM

#

hey yall - i want to have a table that has information about an entity and i want users to be able to supply categories that they can associate with these entities -- how should I model the categories?

#

should I have an entities table and a categories table that has (entity_id, category name) that would just be a list of the associations between categories or..?

#

thanks!

viral crag Apr 13, 2018, 8:29 AM

#

Yeah, so what you're referring to is a join table

#

It depends on the kind of relationship you need to have with your data

#

If you need to be able to go from entity to category, and also from category to entity, and there might be more than one relationship for either of those situations, then you do need a join table

#

a join table is basically just an extra table with three columns

#

a primary key ID, and two IDs - one each for each table you're associating

#

so ID, entity ID, category ID

#

if you're using an ORM, that's likely to be quite easy to do in it

rancid bronze Apr 13, 2018, 2:24 PM

#

Ohh that makes total sense

#

Thanks

#

Can you look over some models for me in a bit? @viral crag

astral blaze Apr 14, 2018, 9:13 AM

#

coming from the PHP-world, just started with python: which DB-engine would u suggest using?
MongoDB? NoSQL?

viral crag Apr 14, 2018, 9:14 AM

#

Depends entirely on your use-case

#

I like RethinkDB

astral blaze Apr 14, 2018, 9:14 AM

#

just to keep it short:
I wanna check some routine every 5 mins and insert / update some records everytime.

#

not talking about many records... lets say 100 or so

viral crag Apr 14, 2018, 9:15 AM

#

That's not what I meant

#

It depends how strict your schema enforcement needs to be, and how data in your tables relates to data from other tables

astral blaze Apr 14, 2018, 9:16 AM

#

not strict as all, only 1 table probably without any relations

#

at least for now - it might expand later, but just for now

#

i'll check out RethinkDB

#

thanks

vagrant jewel Apr 14, 2018, 9:25 AM

#

@astral blaze mariadb or mysql works fine for simple stuff

astral blaze Apr 14, 2018, 9:27 AM

#

i'm familiar with mysql, just wasn't sure if it is the "way to go" with python

vagrant jewel Apr 14, 2018, 9:30 AM

#

works for me 😉

#

check out pymysql, super simple stuff

astral blaze Apr 14, 2018, 9:30 AM

#

will do, thanks for the input 😃

ornate abyss Apr 14, 2018, 1:09 PM

#

I mean if it's only one table just use sqlite

torn sphinx Apr 15, 2018, 5:27 PM

#

hey just wanted to say SQLbolt is helping me learn SQL a lot

#

it's somewhat mobile friendly so i get on it while im on the toilet too

viral crag Apr 15, 2018, 5:28 PM

#

Classy

vestal apex Apr 15, 2018, 7:42 PM

#

(you're more likely to get responses if you give the details up front, unfortunately we humans are lazy and if people feel like they have to extract the situation out of you they're less likely to bother with helping)

#

maybe you could provide us with some details like your operating system, the method you're using, and the particular troubles

stoic pendant Apr 15, 2018, 7:45 PM

#

haha no problem

#

please could someone help me figure out the issue i'm having with installing mysql server on windows 7?
during the configuration part of the install, it's failing to initialize database. i get warnings, but no actual errors that i can identify. actually when i installed this earlier today i managed to get to the 'starting server' portion of the config but it said it was taking too long and i didn't understand why. i decided to reinstall and now i'm getting this initializing database failed error message.
this is the .ini that people seem to ask for on stackoverflow altho it doesn't include the log which i'm not sure how to find.
https://pastebin.com/TfphSDHn

i'm going to restart my pc and do a clean install of mysql server again and see what happens, but this will be the 3rd time i've tried it this way...

Pastebin

# Other default tuning values # MySQL Server Instance Configurati...

stoic pendant Apr 15, 2018, 8:43 PM

#

yeah, a fresh install did it, annoyingly. i have learned nothing except that i hate mysql 😂

vestal apex Apr 15, 2018, 8:59 PM

#

F

torn sphinx Apr 17, 2018, 1:11 AM

#

what do you guys think of Peewee as an ORM?

ionic pecan Apr 17, 2018, 5:29 AM

#

seems nice

viral crag Apr 17, 2018, 9:07 AM

#

It's not great, but it works

near cradle Apr 17, 2018, 10:05 AM

#

it's kind of like django, right?

#

I don't know peewee but the django ORM is okay, so I guess I'd feel the same about peewee

#

I've heard they're more or less the same.

ionic pecan Apr 17, 2018, 10:08 AM

#

Why would I choose Peewee over SQLAlchemy though? SQLAlchemy seems far more mature

viral crag Apr 17, 2018, 10:42 AM

#

Peewee is smaller and comes with a migration framework

#

there's also peewee-async

#

but to be honest, SQLA has a nicer api, and alembic is way better than peewee's migration features

versed coyote Apr 18, 2018, 2:08 AM

#

Just leaving this here: https://www.influxdata.com/

InfluxData

InfluxData (InfluxDB) | Time Series Database Monitoring & Analytics

Open Source Time Series DB Platform for Metrics & Events (Time Series Data). Creator of the TICK Stack: InfluxDB, Telegraf, Chronograf & Kapacitor. Providing real-time analytics, IoT applications, and DevOps Monitoring. Try a Free 14-Day Trial.

young flame Apr 19, 2018, 2:03 AM

#

Hello, I am looking for a nice way to log live data to something other than a csv file. Any ideas on the best way to get started with databases like a simple tutorial

viral crag Apr 19, 2018, 1:08 PM

#

If you're logging live data, are you sure a database is what you want?

#

I mean, what you're asking for is a bit ambiguous

#

Are you storing time-series data? Or are you talking about aggregating log messages?

steel slate Apr 19, 2018, 4:55 PM

#

why not store them as a file with an about.txt that holds the 12 variables?

young flame Apr 19, 2018, 6:19 PM

#

@viral crag I am storing 120 currency pairs with 12 variables each approximately. My current saving method is to just save my latest dataframe once it reaches a count of 24 hours
However for backtesting purposes I like to review past data in graphs instead of raw data. I was hoping if I store my data in a database vs a csv excel file I would be able to have a gui for the database so I can easily view historic data

#

@steel slate I was using csv files before and it works. However I just set it to save once it hits a count of 24 hours. Im not sure how to update a csv file with the last line of a dataframe

viral crag Apr 19, 2018, 6:21 PM

#

So you're storing.. What exactly?

#

That didn't really answer my question

#

Transactions? Totals?

young flame Apr 19, 2018, 6:22 PM

#

Live stock ticker data
"e": "24hrTicker", # Event type
"E": 123456789, # Event time
"s": "BNBBTC", # Symbol
"p": "0.0015", # Price change
"P": "250.00", # Price change percent
"w": "0.0018", # Weighted average price
"x": "0.0009", # Previous day's close price
"c": "0.0025", # Current day's close price
"Q": "10", # Close trade's quantity
"b": "0.0024", # Best bid price
"B": "10", # Bid bid quantity
"a": "0.0026", # Best ask price
"A": "100", # Best ask quantity
"o": "0.0010", # Open price
"h": "0.0025", # High price
"l": "0.0010", # Low price
"v": "10000", # Total traded base asset volume
"q": "18", # Total traded quote asset volume
"O": 0, # Statistics open time
"C": 86400000, # Statistics close time
"F": 0, # First trade ID
"L": 18150, # Last trade Id
"n": 18151 # Total number of trades

#

I also store current coin counts and calculated netWorth in lists. Also trade positions

desert ice Apr 20, 2018, 12:36 PM

#

gdude, what he wants is to store live-streamed data from a stock-exchange/brokerage/data-provider which is to be used for later research on the data. Maybe there is real-time calculations + plotting involved too

#

Ideally one would want a csv like file for each ticker and also for each day or week or month ( as maybe convenient). Smaller chunks are easier. I have no idea about databases. So not sure if databases are better to text dumps

viral crag Apr 20, 2018, 3:06 PM

#

Databases can be queried, which is the point

#

It sounds like what you want is something like rdb

#

A timeseries database with incremental backoff over time

desert ice Apr 20, 2018, 3:51 PM

#

A little off-topic. After a little background study, it appears that the data he posted is level 2 data compared to level 1 which is just the prices. Some basic reading suggests people mostly use level 2 data for HFT algorithm trading. Pretty expensive given that they say HFT is only workable if you have a workstation close to the exchange i.e. high real estate costs leading to high price for your hired e-space. If those are for crypto-coins rather than real world currency-pairs ( USD-JPY or USD-EUR) you speak of, then it is a different game I guess

broken belfry Apr 20, 2018, 4:05 PM

#

definitely crypto:
"s": "BNBBTC", # Symbol Indicated Binance Coin to Bitcoin trading.

young flame Apr 20, 2018, 10:19 PM

#

its crypto. And its not hft, its algo trading

#

@desert ice you explained what i meant pretty well

#

my bot is alreary in papet and live trading however it was just in level 1 data tgrough api calls. The next stagr of development is to go to level 2 websocket live stresm

torn sphinx Apr 22, 2018, 8:59 AM

#

How can I build a database with sqlite with information about songs like singer, titel, date of release etc. ?

silent mauve Apr 22, 2018, 9:04 AM

#

i'm sorry i can't help you

broken linden Apr 22, 2018, 9:06 AM

#

@torn sphinx just do it like you would do it in normal sql databases, if you dont know what those are/how they work do a bit googling, basic sql is really easy to learn

torn sphinx Apr 22, 2018, 9:40 AM

#

I don't find good tutorials

#

can anyone create a little database and send it?

broken linden Apr 22, 2018, 9:41 AM

#

https://www.w3schools.com/sql/

torn sphinx Apr 22, 2018, 9:41 AM

#

so I could learn from this

broken linden Apr 22, 2018, 9:41 AM

#

a little database

#

sec

torn sphinx Apr 22, 2018, 9:42 AM

#

but it says at this site you sent that this tutorial is for mysql, .... but not for sqlite

broken linden Apr 22, 2018, 9:43 AM

#

sql syntax is quite similar

#

there are only very specifc things which are different

#

INSERT INTO, SELECT FROM, UPDATE and ALTER should always work in all dbs

#

and if you are using sqlite i doubt you need much more if any

#

and once you learnt basic syntax you just go for https://docs.python.org/3/library/sqlite3.html

torn sphinx Apr 22, 2018, 9:47 AM

#

okay thanks

#

so do you have a database?

broken linden Apr 22, 2018, 9:48 AM

#

i can come up with some sql to make one

#

CREATE TABLE Players
(
    "id" SERIAL PRIMARY KEY NOT NULL,
    "nick" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "progress" TEXT DEFAULT '{}' NOT NULL,
    "alliance" int DEFAULT 0 NOT NULL,
    "bio" TEXT
);

#

although i am not sure if sqlite has the "TEXT" datatype

torn sphinx Apr 22, 2018, 9:52 AM

#

so this is just a table right?

broken linden Apr 22, 2018, 9:54 AM

#

yes

torn sphinx Apr 22, 2018, 9:56 AM

#

it says invalid syntax when I run it

broken linden Apr 22, 2018, 9:57 AM

#

does it

#

well i guess thats too postgresql then

#

CREATE TABLE humans (date text, name text, age real)

torn sphinx Apr 22, 2018, 10:03 AM

#

postgresql?

broken linden Apr 22, 2018, 10:05 AM

#

another sql database

torn sphinx Apr 22, 2018, 10:07 AM

#

ah

#

something with TABLE is wrong

broken linden Apr 22, 2018, 10:11 AM

#

take a look at this https://docs.python.org/3.6/library/sqlite3.html @torn sphinx

torn sphinx Apr 22, 2018, 2:38 PM

#

This is so long

torn sphinx Apr 23, 2018, 2:16 PM

#

so is anyone in here good with sqlite and can create databases with sqlite?

#

I really need your help

barren mesa Apr 23, 2018, 2:20 PM

#

I wouldn't say I'm really good, but I've used it quite a bit

#

I can probably help

torn sphinx Apr 23, 2018, 2:21 PM

#

it'll be enough for sure

ornate abyss Apr 23, 2018, 4:10 PM

#

literally read the docs linked

heavy warren Apr 23, 2018, 4:22 PM

#

Hi. Can someone help me to put together a request for sqlLite?

DB is very simple. One column with numeric User_Id where each user has his own table with numbers
Looks like
[
00001 [001, 002, 003 ...]
00002 [001, 002 ...]
00003 [001, 003, 005 ...]
...
]
Is it the right choice of DB structure in terms of performance?

I need to fill out the database with data structured in the same way in local variables.
Users should not be duplicated in dB. In case if user already exists, simply add the numbers to his table.

That's how I suppose to do it.

    for u in user:
        users_cursor.execute ("INSERT INTO users [id] VALUES [", u[0],"] IF NOT EXISTS")
        
        for num in u [1]:
            users_cursor.execute ("INSERT", num, "IN .... IF NOT EXISTS WHERE id =", u[0])

Is there any option to add everything in one line?

(I'm not that much experienced with databases, so I would be grateful for help)

near cradle Apr 23, 2018, 6:11 PM

#

instead of looping over .execute, you should just compile these lines into a single string with semicolons seperating each instruction and then execute when you're done looping. that way you're just making one database interaction instead of len(users) * len(u[1]) database interactions.

#

this is a bigger problem with other databases, though

#

sqllite is usually a local file, but if you were using a remote database, for example, using this approach might mean having to send data over a network 1000 times instead of 1.

#

I'm afraid I don't know any truer ways of making it one line off the top of my head, though.

ornate abyss Apr 23, 2018, 7:10 PM

#

Each user has their own table, what?

heavy warren Apr 23, 2018, 7:22 PM

#

there could be a lot of numbers related to each user, I thought to use string instead of table, but it doesn't seem to be the right solution

#

any better ideas?

ornate abyss Apr 23, 2018, 7:32 PM

#

A table per user isn't a good idea

#

I'm unsure as to what you're doing to suggest a better alternative mind

heavy warren Apr 23, 2018, 8:12 PM

#

A condition is easy accessibility of all numbers of the specified user.
I can put them all in one column, with a duplicated user id. I do not know if this method is better. In the long term, one more table will be needed, where each number of the user is assigned another array of numbers. And also an additional info.

Like:

u1, somedata, [num1, num2, num3...]
somedata somedata somedata
[subnum1 [subnum1 [subnum1
subnum2 subnum2 subnum2
subnum3 subnum3 subnum3
... ] ...] ...]

u2, somedata, [num1, num2, num3...]
somedata somedata somedata
[subnum1 [subnum1 [subnum1
subnum2 subnum2 subnum2
subnum3 subnum3 subnum3
... ] ...] ...]

...

Each sequence should be easily accessible by root numbers. Including specified "somedata"
Also the entire sequences of this structure should be easily transferred to the program memory, in the same form.

To be honest, I have no idea which solution is the best.
In the python it's a 3-dimensional list. It takes to much memory, so cannot be handled without periodic interaction with database.

wispy fable Apr 24, 2018, 1:57 AM

#

Each row of a table can hold as much data as you like

#

You're working with softwaren not paper and pen, so your limitations should only be a concern when you actually find them

heavy warren Apr 24, 2018, 1:01 PM

#

The point is to easily get the exact right data, What if string could reach over million symbols?

#

then I have to parse all that huge string in python just to discard most of this data?

torn sphinx Apr 24, 2018, 1:57 PM

#

Can anyone explain how to use the join command to connect tables with each other?

heavy warren Apr 24, 2018, 2:37 PM

#

Looks like sqlite uses Hdd cache file for Execute operation, before to attach data to db file with Commit.
Is that so?
Is there any way to force it use ram memory instead?

ionic pecan Apr 26, 2018, 1:44 PM

#

Anybody know some SQLAlchemy? I'm trying to figure out the best way to return a list of a single column, e.g. ```py
session.query(MyModel.column).distinct().somehow_get_a_list()

Right now I get a nested list of tuples, no matter how I query. There must be some SQLAlchemy way to do this. ```py
(Pdb) session.query(Follow.follows).distinct().all()
[('volcyy',), ('vao92',)]

I've got this flatten-the-result-list pattern quite often here, so I'd be happy for any suggestions how it's possible to simplify this.

viral crag Apr 26, 2018, 1:44 PM

#

x[0] for x in y?

#

I think there is a proper way to do it in SQLA, but I'm not sure how it works

ionic pecan Apr 26, 2018, 1:45 PM

#

Yeah that's the short form of what I currently have, but I feel like there must be the right way™

ionic pecan Apr 26, 2018, 2:36 PM

#

https://pgexercises.com/ This is great

loud spade Apr 27, 2018, 12:22 AM

#

[SQLite3] What do you call it when you create a table and one var is numbers and karakters? Like c.execute("CREATE TABLE IF NOT EXISTS DiscordOffers(user TEXT, datestamp TEXT, id REAL, confirmed REAL)")

#

where user is like MK#7906

ionic pecan Apr 27, 2018, 6:48 AM

#

to my knowlege that's not a data type in sqlite

#

if you want to store users, it's probably better to store them by ID, since the ID cannot change while username / discriminator can

loud spade Apr 27, 2018, 9:46 AM

#

Yeah I thought of that too :p

#

@ionic pecan can you use an if else statement with c.execute? sorry to ask, but i am new to sqlite3 :p

ionic pecan Apr 27, 2018, 9:49 AM

#

not sure what you mean, can you show some code ?

loud spade Apr 27, 2018, 9:49 AM

#

I’ll do that in a bit, I’m currently on my phone xd

ionic pecan Apr 27, 2018, 9:54 AM

#

👍

loud spade Apr 27, 2018, 10:17 AM

#

@ionic pecan

id = ctx.message.author.id #discord thingie, so something like 2309328592385209
c.execute("SELECT * FROM DiscordUsers WHERE id=user")
for row in c.fethcall():
    print(row)

how do you use an if and else statement here? Like if it has an outcome, that it will say like yes or something

ionic pecan Apr 27, 2018, 10:17 AM

#

like if there is one user with the given ID?

loud spade Apr 27, 2018, 10:17 AM

#

yeah

ionic pecan Apr 27, 2018, 10:22 AM

#

you probably want fetchone() instead of fetchall() https://docs.python.org/3.6/library/sqlite3.html#sqlite3.Cursor.fetchone

#

then you can do e.g.

c.execute("SELECT * FROM DiscordUsers WHERE id=user")
result = c.fetchone()
if result is not None:
    # result is the queried user's row
else:
    # result not found

#

also, I assume that the WHERE id=user is just a placeholder, but keep in mind that you should generally use parameter substitution

loud spade Apr 27, 2018, 10:54 AM

#

Ah

#

Okay

#

Thank you!

ionic pecan Apr 27, 2018, 10:56 AM

#

👍

dusty scroll Apr 27, 2018, 12:59 PM

#

I'm trying to use pymysql to add the values of a certain field up for 60k+ records, is there a faster way of doing this rather than going through all of the entries and adding the values up?

unreal tartan Apr 27, 2018, 1:00 PM

#

executemany should be used for bulk inserts https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html

dusty scroll Apr 27, 2018, 1:00 PM

#

i'm doing a bulk select

unreal tartan Apr 27, 2018, 1:00 PM

#

So yes, you first have to make a list of tuples of all the stuff you wish to insert.

dusty scroll Apr 27, 2018, 1:00 PM

#

so just use executemany too?

unreal tartan Apr 27, 2018, 1:00 PM

#

What do you mean with a bulk select?

dusty scroll Apr 27, 2018, 1:01 PM

#

I have 60K+ records in a table currently, I need to get the value of a column from each individual record

unreal tartan Apr 27, 2018, 1:02 PM

#

okay so that's a normal select and you loop over the rows right?

dusty scroll Apr 27, 2018, 1:18 PM

#

yes

ionic pecan Apr 27, 2018, 5:13 PM

#

c.execute("SELECT * FROM DiscordOffers WHERE user=user") will always return the same

#

where user=user literally selects any DiscordOffer where user=user

#

completely independent of what's happening around it

#

you wanna pass the user into the execute

#

I think it's ```py
c.execute("SELECT * FROM DiscordOffers WHERE user=?", (user,))

but I could be wrong, you should re-check the docs to be sure

loud spade Apr 27, 2018, 5:15 PM

#

I am sorry, but I forgot what ? did and (user,))

#

I know user is a var

#

but what is the usage of it?

ionic pecan Apr 27, 2018, 5:17 PM

#

by using ? and passing (user,) as the second parameter, you're essentially putting the value of user into that WHERE user= statement

loud spade Apr 27, 2018, 5:17 PM

#

oh

#

like .format ?

ionic pecan Apr 27, 2018, 5:17 PM

#

safely, that is

#

nope, was about to mention that

#

imagine this, you have an app where users submit comments

#

comment = await get_comment_from_user()
c.execute("INSERT INTO comments VALUES ({}, {})".format(user, comment))

#

okay now, this is bad, why? you're just inserting untrusted user stuff into your database

#

imagine comment was something like 'hello'); DROP TABLE comments;

#

then the SQL could be something like

INSERT INTO comments VALUES (
    91032123,  -- user id
    'hello'); DROP TABLE comments;
)

#

do you see the issue? 😛

loud spade Apr 27, 2018, 5:21 PM

#

ah

#

yes

ionic pecan Apr 27, 2018, 5:21 PM

#

that's why you use ? for substituting parameters. you don't need to worry about that then

loud spade Apr 27, 2018, 5:21 PM

#

you can literally input code from the inside 'hacking' it

#

I think I saw that on yt too

#

where someone talked about that

ionic pecan Apr 27, 2018, 5:22 PM

#

yeah

#

basically, don't do that

#

super important

#

but yeah. now back to your question, now you should get the DiscordOffer corresponding to the user

loud spade Apr 27, 2018, 5:22 PM

#

it worked btw

#

just tried it

#

thanks again!

#

+respects

ionic pecan Apr 27, 2018, 5:29 PM

#

finger_gun_dank no problem

tawny sail Apr 27, 2018, 5:29 PM

#

hey guys,i was just wondering about what is the best database to use for my programming ? i use MySQL ,the workbench n all,but in youtube while browzing tutorials,u see people use various others which i dint know about..so just a little thought on this

steel slate Apr 27, 2018, 5:31 PM

#

@tawny sail are you going to have a million or more rows in your database?

tawny sail Apr 27, 2018, 5:31 PM

#

not more than 40 or so

steel slate Apr 27, 2018, 5:31 PM

#

then it won't matter much to you

tawny sail Apr 27, 2018, 5:31 PM

#

oh

steel slate Apr 27, 2018, 5:32 PM

#

SQLite would be fine for you

tawny sail Apr 27, 2018, 5:32 PM

#

i did work on that,but i shifted to mysql later on lol

steel slate Apr 27, 2018, 5:34 PM

#

I don't see any reason to change it back if you already have something in place

#

small scale stuff isn't going to have a major impact unless you have been asked to evaluate efficiency or justify design choices

loud spade Apr 27, 2018, 8:46 PM

#

@ionic pecan sorry to disturb (again), but is this a REAL or a TEXT? <discord.message.Message object at 0x103a72130>

torn sphinx Apr 27, 2018, 8:47 PM

#

thats a message object

#

do you want to get the content of that message?

loud spade Apr 27, 2018, 8:48 PM

#

no not like that. I am trying to get a function that I can use client.delete_message(message_object)

#

and you cannot use the id

#

of the message

torn sphinx Apr 27, 2018, 8:48 PM

#

you can

#

theres something that retrieves the message object from id

#

http://discordpy.readthedocs.io/en/latest/api.html#discord.Client.get_message

loud spade Apr 27, 2018, 8:49 PM

#

really?

#

lemme check this out

#

no, but the ID is known

#

it just needs to delete the message

#

it saves the message object in a database

#

and on request it deletes the message

torn sphinx Apr 27, 2018, 8:51 PM

#

....?

#

idek what youre saying

loud spade Apr 27, 2018, 8:51 PM

#

like

#

if you do this

#


message_id = await client.say("test".format(ctx.message))

message_id_code = message_id.id

print(message_id)
print(message_id_code)

>>> <discord.message.Message object at 0x103a72130>
>>> 848948498895895898

You can't use message_id_code as a parameter in delete_message()

torn sphinx Apr 27, 2018, 8:54 PM

#

thats why i said use client.get_message

#

it builds message objects from id

loud spade Apr 27, 2018, 8:54 PM

#

OH

#

I am sooo dumb

torn sphinx Apr 27, 2018, 8:54 PM

#

lol

#

but yeah

loud spade Apr 27, 2018, 8:54 PM

#

thanks!

#

F

#

+respect

torn sphinx Apr 27, 2018, 8:55 PM

#

but if you want to do that youll have to store both channel ids and server ids i think..

#

lemme see

#

actually nvm

#

only channel id

#

so youll want both py message_object = await client.say("whatever") message_id = message_object.id message_channel_id = message_object.channel.id

#

message_id and message_channel_id

#

and then when you want to get that message object you can do py channel = client.get_channel(message_channel_id) message_object = await client.get_message(channel, message_id)

loud spade Apr 27, 2018, 9:05 PM

#

thanks

#

ill look at it :p

#

@torn sphinx when I try to store the ID in SQLite3, I get a number like 4.3953257433176474e+17

#

is REAL for floats?

torn sphinx Apr 27, 2018, 9:17 PM

#

REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

yeah looks like

loud spade Apr 27, 2018, 9:17 PM

#

what is then the best to store it?

torn sphinx Apr 27, 2018, 9:18 PM

#

i dont know much about dbs, but maybe BIGINT

loud spade Apr 27, 2018, 9:18 PM

#

lemme try

torn sphinx Apr 27, 2018, 9:20 PM

#

or tbh you can store it as a string cuz ids are strings in the async version of discord.py

loud spade Apr 27, 2018, 9:21 PM

#

well, BIGINT worked 😄

torn sphinx Apr 27, 2018, 9:21 PM

#

ah ok

#

im not sure if itll be a str or an int when you retrieve it from the db, but make sure you pass strings whenever an id is required

loud spade Apr 27, 2018, 9:23 PM

#

if I retrieve it from the database it is this: ('359012789852831744', '2018-04-27', 439536454150914059)

#

so all good

torn sphinx Apr 27, 2018, 9:24 PM

#

ah ok so make sure you convert it to a string for the get_member and get_message stuff

loud spade Apr 27, 2018, 9:24 PM

#

isn't it to retrieve the id like this:

#

async def done(ctx):
    try:

        name = ctx.message.author.id
        c.execute("SELECT * FROM DiscordOffers WHERE user =?", (name,))
        for row in c.fetchall():
            print(id)
        conn.commit()
        await client.say("{} I have succesfully closed your offer.".format(ctx.message.author.mention))

    except:
        raise

#

i used print to look if it works

#

frick

#

I get <built-in function id>

torn sphinx Apr 27, 2018, 9:26 PM

#

yeah because you dont use id anywhere else

#

maybe you mean row?

loud spade Apr 27, 2018, 9:27 PM

#

If i use row, I will get ('359012789852831744', '2018-04-27', 439536454150914059), what I don't, what I think. Since the ID is only relevant in this function

torn sphinx Apr 27, 2018, 9:28 PM

#

i think * will do everything in that row

#

so use column names instead

loud spade Apr 27, 2018, 9:29 PM

#

it kinda works

#

but only

#

I get this

#

(439536454150914059,)

torn sphinx Apr 27, 2018, 9:30 PM

#

its a tuple so youll have to do [0]

#

but wouldnt it be better to store it as text?

loud spade Apr 27, 2018, 9:31 PM

#

why that?

torn sphinx Apr 27, 2018, 9:31 PM

#

because thats an id right

#

for discord.py (async branch) the ids are strings

#

but on rewrite theyre ints

loud spade Apr 27, 2018, 9:32 PM

#

well, the outcome is 439536454150914059

#

so I think no problemo

torn sphinx Apr 27, 2018, 9:32 PM

#

no problem but storing it as a string will just save you from casting it to a str

#

also you need both the message id and channel id to retrieve the message again

loud spade Apr 27, 2018, 9:33 PM

#

channel ID is always equal to the same channel

#

@torn sphinx just save it as TEXT you mean?

#

or

torn sphinx Apr 27, 2018, 9:41 PM

#

yes\

loud spade Apr 27, 2018, 9:42 PM

#

lemme try

#

thank you for taking your time to help me btw

#

I appreciate it!

torn sphinx Apr 27, 2018, 9:42 PM

#

np

glass bough Apr 27, 2018, 11:16 PM

#

So I'm trying to make a mix of a database and a GUI, I will have songs and details about them. I am very new to python but I want to learn, this is a project I've been stuck on and I want some help, I have tried a few times, I can give those as examples, but they are very disorganized and sloppy as again I am inexperienced. I am 14 so if you can't handle the annoying voice you don't have to try to explain it. Thanks

#

hopefully you can teach me something further about classes and some basic concepts that I can't quite grasp from youtube videos

broken linden Apr 27, 2018, 11:22 PM

#

first of all ages isnt really important here, im 15 even the server owner is

if you are going for a databse plus GUI mix maybe take a look at sqlite and the sqlite3 module from the python standard library and for a farely simple GUI take a look at tkinter, from the standard library too

or do you have any specific questions

glass bough Apr 27, 2018, 11:28 PM

#

I've been attempting to use those two exactly

#

I'm just struggling apparently

#

its mostly some slightly more complex pieces of tkinter. It might not actually be complex, but it was difficult for me

versed coyote Apr 27, 2018, 11:43 PM

#

@glass bough You may find this useful: http://appjar.info/

Python library for building GUIs, using tkinter toolkit

The easiest way to build tkinter GUIs in Python.

#

GUI library designed for people who are learning

#

Though this is off-topic for databases channel 😛

loud spade Apr 28, 2018, 12:19 AM

#

@glass bough Tkinter is fairly easy to understand, only getting the input from textboxes sucks ass

#

I couldn't get that working

glass bough Apr 28, 2018, 12:21 AM

#

lol

broken linden Apr 28, 2018, 12:21 AM

#

nearly everything from tkinter is what you expect every GUI framework to have in some form now days tbh

loud spade Apr 28, 2018, 12:22 AM

#

@broken linden bist du Deutsch?

broken linden Apr 28, 2018, 12:22 AM

#

indeed

loud spade Apr 28, 2018, 12:22 AM

#

:0

#

das ist sehr schön

broken linden Apr 28, 2018, 12:23 AM

#

trotzdem schreiben die Server Regeln aber Englisch vor ¯_(ツ)_/¯

loud spade Apr 28, 2018, 12:23 AM

#

I know, but just wanted to test you :0

glass bough Apr 28, 2018, 12:24 AM

#

I think I understand most of it, I just don't know how to make my code clear and to use some more advanced pieces to make it make more sense, using that appJar thing is like relearning it a bit

loud spade Apr 28, 2018, 12:24 AM

#

@broken linden deleting a row in SQLite3 is like c.execute("DELETE FROM DiscordOffers WHERE id=?", (name,)) right?

broken linden Apr 28, 2018, 12:24 AM

#

its like that in every sql dialect

loud spade Apr 28, 2018, 12:25 AM

#

@glass bough maybe try to learn the basics of Python

#

@broken linden I am having a problemos lol

#

😅

#

look

broken linden Apr 28, 2018, 12:25 AM

#

i mean

#

i am not sure about sqlite3 cause i never used it

#

but in my database projects i usually have

loud spade Apr 28, 2018, 12:26 AM

#

it causes some sort of 404 error

broken linden Apr 28, 2018, 12:26 AM

#

stuff like

glass bough Apr 28, 2018, 12:26 AM

#

I know a bit of python, but some of the things that people put in these chats confuse the hell out of me

broken linden Apr 28, 2018, 12:26 AM

#

def showID_to_name(ID):
    app.config["CUR"].execute('SELECT title FROM "shows" WHERE id = %s',
                              (ID, ))
    return app.config["CUR"].fetchall()

#

and yes

#

that new line is needed

loud spade Apr 28, 2018, 12:27 AM

#

is that MySQL?

broken linden Apr 28, 2018, 12:27 AM

#

that is postgresql

#

but they work nearly the same at this very basic syntax

loud spade Apr 28, 2018, 12:27 AM

#

but could you look at my code please meine Curry wurst?

#

bitte?

#

xd

broken linden Apr 28, 2018, 12:28 AM

#

so you get some sort of 404

#

could you show me?

loud spade Apr 28, 2018, 12:28 AM

#

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: NotFound: NOT FOUND (status code: 404): Unknown Message

#

wait maybe I know it xd

#

nope

#

scheisse

broken linden Apr 28, 2018, 12:29 AM

#

is the code you showed inside a cog or not?

#

(if you dont know what a cog is i am sure it isnt)

loud spade Apr 28, 2018, 12:30 AM

#

I choose the second option

#

lmao

#

but this is the right code

#

but it gives an error

#

#close offer
@client.command(description = "close an offer", pass_context = True)
@commands.has_any_role("Verified")
async def done(ctx):
    try:

        name = ctx.message.author.id
        c.execute("SELECT id FROM DiscordOffers WHERE user =?", (name,))
        for row in c.fetchall():
            message_id = row[0]

            channel = discord.utils.get(client.get_all_channels(), name="offers")
            message_object = await client.get_message(channel, message_id)

            await client.delete_message(message_object)
            c.execute("DELETE * FROM DiscordOffers WHERE user=name")



            conn.commit()
        await client.say("{} I have succesfully closed your offer.".format(ctx.message.author.mention))
        

    except:
        await client.say("{} You do not have any open offers!".format(ctx.message.author.mention))
        raise

broken linden Apr 28, 2018, 12:30 AM

#

im not sure but

#

you have to put a @ command there dont you?

#

oh

#

sorry

loud spade Apr 28, 2018, 12:31 AM

#

it is that you use like !done

broken linden Apr 28, 2018, 12:31 AM

#

GOD DAMN IT

loud spade Apr 28, 2018, 12:31 AM

#

and that acts like done()

#

but then in discord

broken linden Apr 28, 2018, 12:31 AM

#

yeah

loud spade Apr 28, 2018, 12:31 AM

#

oh

#

wait wut

broken linden Apr 28, 2018, 12:31 AM

#

i think you gotta put the command decorator there

#

im not sure though

#

like

#

@client.command(pass_context=True)
async def test(ctx):
  pass

loud spade Apr 28, 2018, 12:32 AM

#

already have that?

broken linden Apr 28, 2018, 12:32 AM

#

oh

#

hmmmmmmmm

loud spade Apr 28, 2018, 12:32 AM

#

I think it is with the database, but not sure

broken linden Apr 28, 2018, 12:33 AM

#

is there some this exception was a direct cause of xyz thingy?

loud spade Apr 28, 2018, 12:33 AM

#

xyz thingy?

#

@torn sphinx could you help me?

broken linden Apr 28, 2018, 12:34 AM

#

sometimes python spits out two exceptions

loud spade Apr 28, 2018, 12:34 AM

#

@broken linden no discrespect tho, meine liebe herr

broken linden Apr 28, 2018, 12:34 AM

#

that doesnt sound like correct german o_O

loud spade Apr 28, 2018, 12:34 AM

#

Traceback (most recent call last): File "/Library/Frameworks/Python.framework/Versions/3.5/lib/python3.5/site-packages/discord/ext/commands/bot.py", line 846, in process_commands yield from command.invoke(ctx) File "/Library/Frameworks/Python.framework/Versions/3.5/lib/python3.5/site-packages/discord/ext/commands/core.py", line 374, in invoke yield from injected(*ctx.args, **ctx.kwargs) File "/Library/Frameworks/Python.framework/Versions/3.5/lib/python3.5/site-packages/discord/ext/commands/core.py", line 54, in wrapped raise CommandInvokeError(e) from e discord.ext.commands.errors.CommandInvokeError: Command raised an exception: NotFound: NOT FOUND (status code: 404): Unknown Message

torn sphinx Apr 28, 2018, 12:34 AM

#

it means it didnt find the message

loud spade Apr 28, 2018, 12:34 AM

#

@broken linden could be true, cuz i am not a native xd

torn sphinx Apr 28, 2018, 12:34 AM

#

make sure the message isnt deleted

loud spade Apr 28, 2018, 12:35 AM

#

oh

#

@torn sphinx you are my savior in needs lmao

#

I love you so fucking much

#

YES

#

It worked

#

@torn sphinx ❤ ❤ ❤

#

btw

#

maybe handy for you

#

using channel = discord.utils.get(client.get_all_channels(), name="name of your channel") to get the channel is easier if the channel name is known

torn sphinx Apr 28, 2018, 12:41 AM

#

that works altho i think get_server is faster

#

esp if you have a lot of servers

#

or get_channel i mean

loud spade Apr 28, 2018, 12:42 AM

#

Yeah, but my bot is specifically designed for one server so idk

#

xd

glass bough Apr 28, 2018, 12:51 AM

#

Well, using that sortof guiding tool (appJar) this has been incredibly simple

#

atleast for the basic parts of it that is

#

Those being being able to add to the table, now I just have to have a simple way to find specific details and pick out those songs

#

this is great:D

lavish ferry Apr 28, 2018, 4:57 AM

#

how can I fix the error No module named database ?

gusty spindle Apr 28, 2018, 4:58 AM

#

by installing a module named database, or making sure you are typing the right name

lavish ferry Apr 28, 2018, 4:58 AM

#

what the command to install database ?

gusty spindle Apr 28, 2018, 4:59 AM

#

I've never heard of it, but I'd imagine that you'd have to use pip install ... or python -m pip install ...

lavish ferry Apr 28, 2018, 5:01 AM

#

I will try

gusty spindle Apr 28, 2018, 5:02 AM

#

if you want to actually use a database module in python, I'd recommend starting with sqlite3 in the standard library

sturdy orbit Apr 28, 2018, 6:29 AM

#

I'm creating a league of legends discord bot and wanting to store user data, what database do you guys recommend i use? (could potentially be on a LOT of servers)

ionic pecan Apr 28, 2018, 6:32 AM

#

i use sqlite for mine since i'm too lazy to set up something else

#

postgresql is fairly popular

sturdy orbit Apr 28, 2018, 6:33 AM

#

its not necessarily going to be VAST amounts of data, just more speed of access i think

viral crag Apr 28, 2018, 6:34 AM

#

Postgresql is the best sql database

#

We personally use rethinkdb for our database

sturdy orbit Apr 28, 2018, 6:34 AM

#

nods yupyup

viral crag Apr 28, 2018, 6:34 AM

#

But that's not an sql database

sturdy orbit Apr 28, 2018, 6:34 AM

#

ive tinkered with rethink before

#

sod it, i'll give it a try

#

rethinkdb it is

#

thanks gdude

viral crag Apr 28, 2018, 6:37 AM

#

I think you'll like it

#

But remember, it has no access control

#

Don't expose the port

sturdy orbit Apr 28, 2018, 6:38 AM

#

nods

#

yupyup

#

thanks buddy

broken linden Apr 28, 2018, 9:31 AM

#

I am currently trying to get some simple database application to work, im running postgresql and created my main table with

CREATE TABLE "Users" (
  "time" TIMESTAMP NOT NULL,
  "count" INT NOT NULL,
  "invite" VARCHAR(20) NOT NULL
);

however when my python code comes to the insertion point and tries to execute

 INSERT INTO Users VALUES (1524907749.9109762,2392,'jQtfh66')

i get
psycopg2.ProgrammingError: relation "users" does not exist

#

creating the db with lowercase name fixed it

torn sphinx Apr 28, 2018, 12:32 PM

#

How can you create a column in a "main" table which includes the ID's of other tables?

loud spade Apr 28, 2018, 7:46 PM

#

@torn sphinx Having a little trouble 😅

link: https://pastebin.com/Ju8My7u1

I have marked where it is causing errors, could you look at what is wrong?

Pastebin

#tipping @client.command(description = "tip users", pass_context ...

#

just search for !!!!!! problem is here !!!!!!

torn sphinx Apr 28, 2018, 7:48 PM

#

just one thing i noticed

#

put try: except: on the problematic line

#

not the whole thing

loud spade Apr 28, 2018, 7:49 PM

#

oh

torn sphinx Apr 28, 2018, 7:49 PM

#

or else you dont know where the error came from

loud spade Apr 28, 2018, 7:49 PM

#

okay

#

yeah

torn sphinx Apr 28, 2018, 7:49 PM

#

but

loud spade Apr 28, 2018, 7:49 PM

#

sorry

#

xd

torn sphinx Apr 28, 2018, 7:49 PM

#

whats the problem youre having?

loud spade Apr 28, 2018, 7:49 PM

#

i get multiple errors tbh

#

but it keeps changing

#

for instance

#

the error I get with this code is : Traceback (most recent call last): File "/Library/Frameworks/Python.framework/Versions/3.5/lib/python3.5/site-packages/discord/ext/commands/bot.py", line 846, in process_commands yield from command.invoke(ctx) File "/Library/Frameworks/Python.framework/Versions/3.5/lib/python3.5/site-packages/discord/ext/commands/core.py", line 374, in invoke yield from injected(*ctx.args, **ctx.kwargs) File "/Library/Frameworks/Python.framework/Versions/3.5/lib/python3.5/site-packages/discord/ext/commands/core.py", line 54, in wrapped raise CommandInvokeError(e) from e discord.ext.commands.errors.CommandInvokeError: Command raised an exception: UnboundLocalError: local variable 'receiving' referenced before assignment

torn sphinx Apr 28, 2018, 7:50 PM

#

oh i see

#

you need to define receiving before you use it in the for loop

#

also this

#

for row in result_receiver:
                receiving = row[0]``` lets say you do define receiving before you used it

#

itll change in each row

loud spade Apr 28, 2018, 7:52 PM

#

it searches for the AMOUNT tho?

#

but

#

just assign before the loop and reassign in the loop?

#

or

#

poke?

#

@torn sphinx sorry to ping

torn sphinx Apr 28, 2018, 8:04 PM

#

you can do like

#

receiving = None before the loop

#

but cant you just reverse the list and get the first item

loud spade Apr 28, 2018, 8:08 PM

#

how do you mean? sorry for the dumb question.....

torn sphinx Apr 28, 2018, 8:12 PM

#

ok well

#

explain what that for loop is supposed to do

loud spade Apr 28, 2018, 8:14 PM

#

so that code gets executed repeatably right?

torn sphinx Apr 28, 2018, 9:43 PM

#

ok i have a little more time now

#

so you want to keep repeatedly getting the first item in each row?

#

until it reaches the end?

#

isnt that the same as getting the first item in the reversed list

loud spade Apr 28, 2018, 9:50 PM

#

@torn sphinx I have fixed it 😅

torn sphinx Apr 28, 2018, 9:50 PM

#

ah ok

loud spade Apr 28, 2018, 9:50 PM

#

Thanks tho

#

I really appreciate you and what you are doing

torn sphinx Apr 28, 2018, 9:50 PM

#

btw it might be better to ask more general questions in one of the help channels

#

and thanks 😅

loud spade Apr 28, 2018, 9:51 PM

#

What do you mean with general ?

#

*questions

torn sphinx Apr 28, 2018, 9:51 PM

#

just regular questions

#

more people will see it there probably

loud spade Apr 28, 2018, 9:55 PM

#

oh

#

okay

#

btw

#

could I show you how I fixed it?

#

so you can see if I did it right or not

torn sphinx Apr 28, 2018, 9:55 PM

#

sure

loud spade Apr 28, 2018, 9:55 PM

#

nice

#

one sec

#

xd

#

okay

#

here it is :p

#

https://pastebin.com/y3PsmFyj

Pastebin

#tipping @client.command(description = "tip users", pass_context ...

ionic pecan Apr 28, 2018, 10:03 PM

#

the loop is kinda weird, you overwrite the variable every time

#

maybe you just wantto fetch the first result?

loud spade Apr 28, 2018, 10:06 PM

#

could be yeah

#

could you give the line number where it starts 'overwriting' ?

ionic pecan Apr 28, 2018, 10:09 PM

#

14

#

if you have multiple results, sendingtip will be set to the last one

loud spade Apr 28, 2018, 10:11 PM

#

thanks

#

ill edit that in a bit xd

#

love you Volcyy

#

❤

#

and poke

ionic pecan Apr 28, 2018, 10:12 PM

#

also, not entirely sure, does c.fetchall() ever return None?

#

or does it return an empty sequence when it doesn't get anything

loud spade Apr 28, 2018, 10:13 PM

#

c.fetchall() gives []

#

if you do this:


result = c.fetchall()
print(result)
>>> []

ionic pecan Apr 28, 2018, 10:15 PM

#

what's that if result is None check then?

#

maybe you want if not result to check if the result is empty?

glass bough Apr 28, 2018, 10:17 PM

#

okay, this is a kind of mixed chat question, if I take something from a database and want to print it as a label on a GUI, say all the parts of a row in a table, how would I do this? I am using tkinter and sqlite3

loud spade Apr 28, 2018, 10:18 PM

#

@ionic pecan yeah forgot that :c

lavish ferry Apr 29, 2018, 1:32 AM

#

someone know where can I find my token from MongoDB ?

viral crag Apr 29, 2018, 9:15 AM

#

Token..?

#

MongoDB doesn't use authentication by default

#

To set that up, you have to explicitly set the passwords

#

So there's nowhere to "get" the "token"

#

You're expected to not lose the password

broken linden Apr 29, 2018, 9:16 AM

#

Maybe he means his discord token

viral crag Apr 29, 2018, 9:16 AM

#

He said that wasn't it in another channel

broken linden Apr 29, 2018, 9:16 AM

#

Oh welp

small dirge May 1, 2018, 3:06 AM

#

need some help with my mongodb program. It's using flask-pymongo(basically flask). This is probably the easiest question, but googled for like 25 min and haven't found anything that really makes sense. Basically I'm trying to search through the users collection, and query/find a document that has a certain email field and grab the documents string that has the api key in it and set it to a variable. Anyone know how I would do this?

viral crag May 1, 2018, 10:02 AM

#

Wait so you have a document

#

What is the form of that document? Does it contain both email and api key? @small dirge

small dirge May 1, 2018, 9:36 PM

#

yes @viral crag

viral crag May 1, 2018, 9:36 PM

#

yeah, so you just get the document by the email address

#

and you'll get the whole document

#

including the api key

glass bough May 2, 2018, 12:48 AM

#

How could I set a limitation so I am unable to add something that is already in any of the columns in a database

#

to prevent duplicates

wheat meadow May 2, 2018, 3:06 AM

#

@glass bough you would have to querry the DB for the results and check to see if it's already there

ionic pecan May 2, 2018, 5:41 AM

#

@glass bough I would use a constraint

#

Are you using an ORM or raw SQL?

fresh vale May 2, 2018, 9:34 AM

#

Ok, I have another problem

#

I used and coppied code that insarting data into SQL base

#

I added my query and edited args, but nothing else

#

  File "C:\Python35\lib\site-packages\discord\client.py", line 307, in _run_event
    yield from getattr(self, event)(*args, **kwargs)
  File "C:\Users\saaswww\Desktop\bonkobot\bot.py", line 112, in on_message
    rules_confirm(message.author.id)
  File "C:\Users\saaswww\Desktop\bonkobot\bot.py", line 63, in rules_confirm
    cursor.close()
UnboundLocalError: local variable 'cursor' referenced before assignment```

viral crag May 2, 2018, 9:36 AM

#

you're using cursor before you assign it

fresh vale May 2, 2018, 9:36 AM

#

I now it

#

But i defined it

#

    query = "INSERT INTO users(Discord_ID,rules) " \
        "VALUES(%s,%s)"
    args = (Discord_ID, 1) 
    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)
 
        cursor = conn.cursor()
        cursor.execute(query, args)
 
        if cursor.lastrowid:
            print('last insert id', cursor.lastrowid)
        else:
            print('last insert id not found')
 
        conn.commit()
    except Error as error:
        print(error)
 
    finally:
        cursor.close()
        conn.close()```

viral crag May 2, 2018, 9:37 AM

#

You defined it within the try

#

but you're using it outside of the try block

fresh vale May 2, 2018, 9:37 AM

#

How to make it global?

viral crag May 2, 2018, 9:37 AM

#

No

#

You need to define it outside of the try

fresh vale May 2, 2018, 9:38 AM

#

in rules_confirm() ?

viral crag May 2, 2018, 9:38 AM

#

Yeah

fresh vale May 2, 2018, 9:44 AM

#

yyyyy

#

    try:
        dbconfig = read_db_config()
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()
        cursor.execute("SELECT rules FROM users WHERE Discord_ID= `%s`")
        args=(DiscordID)
        row = cursor.fetchone()
 
        while row is not None:
            print(row)
            row = cursor.fetchone()
 
    except Error as e:
        print(e)
 
    finally:
        cursor.close()
        conn.close()
def rules_confirm(Discord_ID):
    conn = ""
    cursor = ""
    query = "INSERT INTO users(Discord_ID,rules) " \
        "VALUES(%s,%s)"
    args = (Discord_ID, 1) 
    try:
        cursor = conn.cursor()
        db_config = read_db_config()
        cursor.execute(query, args)
        conn = MySQLConnection(**db_config)
        if cursor.lastrowid:
            print('last insert id', cursor.lastrowid)
        else:
            print('last insert id not found')
 
        conn.commit()
    except Error as error:
        print(error)
 
    finally:
        cursor.close()
        conn.close()```

#

I tryied to fix it by defining empty vartibiles, but:

#

  File "C:\Python35\lib\site-packages\discord\client.py", line 307, in _run_event
    yield from getattr(self, event)(*args, **kwargs)
  File "C:\Users\saaswww\Desktop\bonkobot\bot.py", line 112, in on_message
    rules_confirm(message.author.id)
  File "C:\Users\saaswww\Desktop\bonkobot\bot.py", line 63, in rules_confirm
    cursor.close()
AttributeError: 'str' object has no attribute 'close'```

viral crag May 2, 2018, 9:46 AM

#

well now you're defining cursor as a string

#

same thing for conn

#

you've messed up your line ordering

#

look at your try block

#

first line, get a cusor from the connection

#

second line, get the db config

#

third line, execute a query on the cursor

#

fourth line, create a connection using the db config

#

that's what you're doing, and yeah that definitely won't work

#

you need to define your db config, connection and cursor at the top of that function, in that order

fresh vale May 2, 2018, 10:20 AM

#

    db_config = read_db_config()
    conn = MySQLConnection(**db_config)
    cursor = conn.cursor()
    query = "INSERT INTO users(Discord_ID,rules) " \
        "VALUES(%s,%s)"
    args = (Discord_ID, 1) 
    try:
        cursor.execute(query, args)
        if cursor.lastrowid:
            print('last insert id', cursor.lastrowid)
        else:
            print('last insert id not found')
 
        conn.commit()
    except Error as error:
        print(error)
 
    finally:
        cursor.close()
        conn.close()```

#

Now NameError: name 'read_db_config' is not defined

viral crag May 2, 2018, 10:22 AM

#

Well, then I guess you didn't define it

fresh vale May 2, 2018, 10:23 AM

#

A

#

I know

#

I don`t imorted this function 🤦

ashen moth May 2, 2018, 10:58 AM

#

Okay so this is more SQL than Python, but my web server is throwing me an operational error which doesn't happen elsewhere. This is the code:

params3 = ("TRUE", discord_date_to_unix(datetime.utcnow()), "FALSE", discord_id)
        udb.execute("UPDATE UserMainTable "
                    "SET (IsRegistered, RegisterDate, SendRegistrationReminders) "
                    "= (?,?,?) "
                    "WHERE DiscordID is ?", params3)```

#

And here's the error:

#

File "/dobby3/DobbyCommands.py", line 369, in register_user_account_in_db
    if int(res2[0]) == 0:
        params3 = ("TRUE", discord_date_to_unix(datetime.utcnow()), "FALSE", discord_id)
        udb.execute("UPDATE UserMainTable "
                    "SET (IsRegistered, RegisterDate, SendRegistrationReminders) "
                    "= (?,?,?) "
                    "WHERE DiscordID is ?", params3)
sqlite3.OperationalError: near "(": syntax error```

#

Hold up, messed up the formatting

#

There ya go

viral crag May 2, 2018, 10:59 AM

#

what SQL dialect is this?

ashen moth May 2, 2018, 11:00 AM

#

SQLite 3

viral crag May 2, 2018, 11:00 AM

#

I think it's SET VALUES, not just SET

ashen moth May 2, 2018, 11:00 AM

#

I had some issues getting it to work actually

#

Had to install the dev package and recompile Python

#

I'll give it a go, thanks

#

@viral crag Now it's the same error, except that it's displaying near "VALUES"

#

I wonder if it's the brackets actually

ionic pecan May 2, 2018, 11:11 AM

#

Is is a thing in SQL?

#

It's a bit further there, but I recall always using = for this

viral crag May 2, 2018, 11:13 AM

#

oh, it'd be LIKE I think

#

I'm not sure honsetly, the error you have is an SQL syntax error though

ionic pecan May 2, 2018, 11:13 AM

#

https://sqlite.org/lang_update.html I'm checking here but I don't see anything wrong

ashen moth May 2, 2018, 11:13 AM

#

I found it. For whatever reason the SQLite version is a right stickler when it comes to formatting

#

I can't use SET c1, c2, c3 = ?,?,?

#

Instead I have to use

#

SET c1 = ?, c2 = ?, c3 = ?

#

On my desktop/laptop it works fine though

ionic pecan May 2, 2018, 11:14 AM

#

Judging from the docs the above should work fine though

#

📎 unknown.png

#

and expr can be

📎 unknown.png

ashen moth May 2, 2018, 11:15 AM

#

Hmm

#

It might be to do with the issue I had with it before

#

https://stackoverflow.com/questions/42766063/python3-6-import-sqlite3-error

python3.6 import sqlite3 error

I installed Python3.6 on ubuntu16.04 and installed sqlite3. When in python2, I can import sqlite successfully, but in python3 I got an import error. I tried many methods from Google, but it still...

glass bough May 2, 2018, 11:36 AM

#

@ionic pecan I am using slite3 I assume that applies to normal SQL, but I'm very inexperienced with some of this stuff

ionic pecan May 2, 2018, 11:38 AM

#

https://www.w3schools.com/sql/sql_constraints.asp you should check these out

#

the pages after it explain more about it

glass bough May 2, 2018, 11:21 PM

#

it's still sortof confusing

#

because I don't know where I would put that in my code

#

well I just made it so that when I add something, it takes the name input and deletes the rows with that name and then adds the new one

#

Does this make sense to do?

#

Or is there a better method

glass bough May 2, 2018, 11:55 PM

#

Well, I figured it out in a better way

glass bough May 3, 2018, 1:42 AM

#

so I ended with the constraint of INSERT OR IGNORE

#

but instead of ignoring, is there a chance I can have it tell me when something is repeated

ionic pecan May 3, 2018, 5:49 AM

#

I think you can use ON CONFLICT

#

also that's not a constraint

#

a constraint would be something like name VARCHAR(30) UNIQUE

#

you need to put these constraints to your table creation

ornate abyss May 3, 2018, 9:27 AM

#

Just do an insert and catch the exception

#

Then you know it's repeated

glass bough May 3, 2018, 12:18 PM

#

I put unique in the table part with my insert and ignore in the main body

hexed epoch May 3, 2018, 3:47 PM

#

select from table a row where username == variable can someone translate this to proper postgresql

runic tundra May 3, 2018, 4:24 PM

#

SELECT *
FROM table_name
where username = variable;

barren swallow May 3, 2018, 9:26 PM

#

I have a MySQL database running and I'm using the mysql.connector.
I need to insert millions of rows into a table, what is most efficient (fastest) way to do so?

#

I've tried just

sql = "INSERT INTO users VALUES (%s, %s, %s, %s)"
cursor.execute(sql, (data[0], data[1], data[2], data[3]))

But for one million inserts it takes 30-40 minutes

runic tundra May 3, 2018, 10:00 PM

#

https://stackoverflow.com/questions/6159087/mysql-is-taking-too-long-to-insert-rows
https://dba.stackexchange.com/questions/61029/inserting-60-000-rows-taking-too-long
@barren swallow do either of these help? Basically.. how are you inserting each row? does each one have an insert into statement?

Stack Overflow

mysql is taking too long to insert rows

I have one page where users can import their contacts. Initially it was working fine upto 3000 contacts but when I tried to import 10000 contacts it started taking too much time and now the situati...

Database Administrators Stack Exchange

Inserting 60 000 rows taking too long

I'm trying to insert about 60 000 rows on my database on localhost (windows) it is now taking about an hour and still inserting. And after a while it says that maximum timeout is over.
Shouldn't it...

barren swallow May 3, 2018, 10:01 PM

#

I'm currently testing out executemany function

#

but loading 15 million rows into memory isn't ideal so I will need to code it to do 1K at a time or something like that

ionic pecan May 3, 2018, 10:03 PM

#

From where are you getting the rows?

#

If it's a large file, you can use mmap to slowly traverse it instead of reading all of it by once https://docs.python.org/3/library/mmap.html#module-mmap

barren swallow May 3, 2018, 10:05 PM

#

ATM I'm using

with open('x.txt', encoding="utf8") as f:
    for row in f:

#

Apparently it just loads it row by row

runic tundra May 3, 2018, 10:05 PM

#

what is in the text file? comma seperated rows?

barren swallow May 3, 2018, 10:06 PM

#

Something like that, yes

ionic pecan May 3, 2018, 10:06 PM

#

Hmm

runic tundra May 3, 2018, 10:07 PM

#

ok if you want something fast and easy convert the text file into a csv and do LOAD DATA FROM INFILE '<filepath>/x.csv' INTO TABLE users

barren swallow May 3, 2018, 10:07 PM

#

hmm

runic tundra May 3, 2018, 10:07 PM

#

otherwise, you want to group rows and do the insert as batches

#

because the executemany is doing an insert every time which is slow

barren swallow May 3, 2018, 10:08 PM

#

So for importing large quantities of rows import is always a better choice?

runic tundra May 3, 2018, 10:09 PM

#

as far as i know yes, as long as you don't need to manipulate the data inbetween

fluid rose May 3, 2018, 10:09 PM

#

Hi

barren swallow May 3, 2018, 10:10 PM

#

Well, I actually need.

runic tundra May 3, 2018, 10:11 PM

#

ok in that case, what you need to do if you want speed is to create an insert statement that inserts lots of rows at once. your executemany statement is adding overhead to every single row you are adding

barren swallow May 3, 2018, 10:11 PM

#

I should parse the file using Python and create a csv file and import the csv file?

runic tundra May 3, 2018, 10:12 PM

#

that could work

small dirge May 4, 2018, 5:00 AM

#

Hey guys, a bit confused

#

Using mongodb, should I be creating a new collection for every user?

#

atm i've been creating new documents for each user

vestal apex May 4, 2018, 5:03 AM

#

it depends on what all you're storing

#

well...

#

no it should pretty much just be documents

#

what makes you think you'd need individual collections?

small dirge May 4, 2018, 5:06 AM

#

not sure, i just have to store a lot of data

#

it would be a lot of fields for each user

#

@vestal apex

vestal apex May 4, 2018, 5:09 AM

#

well you can break user info across multiple collections, but sometimes you just have lots of fields

#

if you've got a considerable number of fields that would work well in, say, a table

#

you might want to consider a relational database

small dirge May 4, 2018, 5:11 AM

#

my project is just starting though, and i want to be able to change and work with things very easily which is why I chose mongodb

#

i don't want to be forced to use a structure right away

barren swallow May 4, 2018, 9:16 PM

#

I was having problems with importing large quantities of data into a MySQL database earlier.
I found the solution, mysqlimport.exe is the best solution. With it I was able to import 100k rows in less than a second

hexed epoch May 5, 2018, 11:48 AM

#

lol that sounds like an ad and half

ionic pecan May 6, 2018, 2:31 PM

#

Question regarding foreign keys.

CREATE TABLE mute (
    expiry DATETIME, 
    active BOOLEAN, 
    infraction_id INTEGER NOT NULL, 
    PRIMARY KEY (infraction_id), 
    CHECK (active IN (0, 1)), 
    FOREIGN KEY(infraction_id) REFERENCES infraction (id) ON DELETE CASCADE
);

This is my schema. When I delete the referenced infraction, the mute doesn't seem to delete. My understanding is that ON DELETE CASCADE would also delete the mute, but it doesn't. What would I use instead?

#

Maybe this is an issue with how I execute the delete. Using SQLAlchemy:

        query = infraction_db.delete().where(and_(
            infraction_db.c.id == id_,
            infraction_db.c.guild_id == ctx.guild.id
        ))
        result = await self.bot.db.execute(query)

runic tundra May 6, 2018, 3:20 PM

#

@ionic pecan have you fixed this?

ionic pecan May 6, 2018, 3:21 PM

#

Yes

#

Turns out SQLite doesn't support Foreign Keys without enabling it

#

so that just failed silently, which annoyed me a slight bit, so I use pgsql now

#

thanks though

runic tundra May 6, 2018, 3:22 PM

#

ok sweet

#

interesting to know

orchid charm May 6, 2018, 7:28 PM

#

Info on Databases?

#

I wanna know how to connect to one, and write to one

gusty spindle May 6, 2018, 7:31 PM

#

depends on what scenario you've got and what you plan to do @orchid charm

orchid charm May 6, 2018, 7:32 PM

#

I just want a Profile system

#

Where i can write stuff in a Specific Cagetories so the bot can grab the info

#

Like if i typed !profile add weapon splattershot It would write to a Weapons Cagetory Splattershot

#

@gusty spindle Also what do you mean by Scenario

#

Do you mean what i am gonna use?

#

If so i have no idea whatsoever

gusty spindle May 6, 2018, 7:35 PM

#

i'm not too sure about DB's, but i know some are better at smaller-scale projects and others are better at much larger-scale things

orchid charm May 6, 2018, 7:36 PM

#

I just want a profile system like this

📎 Screen_Shot_2018-05-06_at_12.36.21_PM.png

#

The Bot is not in Github so i can't look at what the Person who devved this bot did with it

dull scarab May 6, 2018, 10:12 PM

#

What's the correct datatype for storing strings converted to bytes in postgres?

#

reason = Padding.pad(bytes(reason, "utf-8"), 16)
enc_msg = self.aes.encrypt(reason)``` kinda message

orchid charm May 6, 2018, 10:40 PM

#

What do i do if i wanted a Sqlite3 discord bot to check if something exists in a db and do something if it does not exist in the db

#

Like i got 3 Columns, UID, IGN, WEAPON

#

How would i make the bot check UID for a Specific value in UID

#

Cool

#

👍

#

So like this?

c.execute('SELECT UID from Profile where name = ?', (name,))

gusty spindle May 6, 2018, 10:53 PM

#

should work

#

but why don't you try and see? ^^

orchid charm May 6, 2018, 11:06 PM

#

Cause bot.run is not working

#

I am currently working on it

#

It worked this time, so time to see if that thing works

#

It gives me a error

#

"name" is not defined

#

@gusty spindle Do you know what i am doing incorrectly?

#

Wait think i fixed it

gusty spindle May 6, 2018, 11:12 PM

#

hmm

orchid charm May 6, 2018, 11:29 PM

#

@gusty spindle Yo do you know what everything in that thing means

#

I want it to look for the Author of the messages ID in Table Profile Column UID

dull scarab May 6, 2018, 11:30 PM

#

are you using postgresql ?

orchid charm May 6, 2018, 11:30 PM

#

No, SQlite3

dull scarab May 6, 2018, 11:30 PM

#

For a discord bot?

orchid charm May 6, 2018, 11:30 PM

#

Yeah

dull scarab May 6, 2018, 11:31 PM

#

Hmm, haven't used sqlite. Most say postgresql works best with discord bots using asyncpg

orchid charm May 6, 2018, 11:31 PM

#

I am using asyncio

dull scarab May 6, 2018, 11:32 PM

#

That's what asyncpg is, an async api for postgres

gusty spindle May 6, 2018, 11:32 PM

#

sqlite3 is good for beginners ^^

orchid charm May 6, 2018, 11:32 PM

#

Anyways, am i having it search correctly

#

c.execute('SELECT UID from Profile where UID = ?', (ctx.message.author.id,))
    rows = c.fetchall()
    if (ctx.message.author.id,) not in rows:
        print('Profile Created!')
    else:
        print(ctx.message.author.name + ' You already have a Profile!')

#

I just want it to see if the Message authors ID is in Table Profile Column UID

#

And if it does it does something, and if it does not it does something else

dull scarab May 6, 2018, 11:34 PM

#

Does it always print profile created?

#

Actually, nvm. I miss read your if

gusty spindle May 6, 2018, 11:35 PM

#

you just need to do if ctx.message.author.id not in rows i think

#

oh wait i don't know

dull scarab May 6, 2018, 11:35 PM

#

Isn't rows a list of rows

#

Not Ids

#

As in you have to loop through the rows and check the id column for each row?

orchid charm May 6, 2018, 11:39 PM

#

📎 Screen_Shot_2018-05-06_at_3.21.47_PM.png

#

This is my DB

dull scarab May 6, 2018, 11:39 PM

#

c.execute('SELECT UID from Profile where UID = ?', (ctx.message.author.id,))
rows = c.fetchall()
for row in rows:
    if ctx.message.author.id != row["UID"]:
        print("Profile Created!")
    else:
        print("Already exists")```

#

Try looping over the rows

#

Actually, you're already checking if the id exists in your query.

#

Just check if rows is None or not

#

fetchall()

Fetches all (remaining) rows of a query result, returning a list. Note that the cursor’s arraysize attribute can affect the performance of this operation. An empty list is returned when no rows are available.

orchid charm May 6, 2018, 11:41 PM

#

Oh

#

So i don't need c.execute('')

dull scarab May 6, 2018, 11:42 PM

#

How will you check if the id is in the database if you don't query it?

orchid charm May 6, 2018, 11:42 PM

#

¯_(ツ)_/¯

dull scarab May 6, 2018, 11:43 PM

#

c.execute('SELECT UID from Profile where UID = ?', (ctx.message.author.id,))
rows = c.fetchall()
if not rows:
    print("Profile Created!")
else:
    print("Already exists")```

orchid charm May 6, 2018, 11:43 PM

#

So i use that?

dull scarab May 6, 2018, 11:44 PM

#

Well, like the method documentation says. It returns an empty list if theres no rows from the query. Your query is asking for a row with a specific ID, which I assume is a primary or atleast unique Key in your database

#

So if it finds a result, you'll only get 1 row, else an empty list. If it's empty you don't have any rows in your db with that Id, hence it doesn't exist

orchid charm May 6, 2018, 11:46 PM

#

Well the thing you gave me worked

#

👍

#

I put my User ID into my Database and tested it

dull scarab May 6, 2018, 11:46 PM

#

And you understand why?

orchid charm May 6, 2018, 11:47 PM

#

Mhm, pretty sure i get it

dull scarab May 6, 2018, 11:47 PM

#

feelsthumbsupman

orchid charm May 7, 2018, 3:51 AM

#

How do i use a database in another folder

#

Sqlite 3

#

so sqlite3.connect

#

But i do the directory it is in instead of name?

#

Ah

#

Thought so

#

How do i grab DB info and post it in chat with the bot

#

How do i grab it

#

Example of doing that

orchid charm May 7, 2018, 4:43 AM

#

@modest haven Well what if there are multiple of the same thing

#

Cause there is gonna be stuff like Weapon in there which multiple will be the same

#

Yeah

#

That is what i was saying

#

How exactly do i do that

#

🤔

#

@modest haven

#

Also wow took me like 10 seconds for the ping to send 🤔

#

@modest haven

📎 Screen_Shot_2018-05-06_at_9.50.23_PM.png

#

That is what a Default DB looks like

#

For what i am gonna be doing

#

So it would need to find the Line with the matching UID then grab from that line IGN, and Weapon then set it to some variable

orchid charm May 7, 2018, 5:18 AM

#

@modest haven So like this?

bot.c.execute('SELECT Weapon from Profile where UID = ?', (ctx.message.author.id))

orchid charm May 7, 2018, 6:03 AM

#

Also what am i doing wrong with this
self.bot.c.execute('SELECT Weapon from Profile where UID = ?', (ctx.message.author.id))
I just want it to grab the line with your username and take from that line weapons

#

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/discord/ext/commands/core.py", line 62, in wrapped
    ret = yield from coro(*args, **kwargs)
  File "/Users/shanehawkins/Desktop/DiscordBot/cogs/member.py", line 30, in profile
    self.bot.c.execute('SELECT Weapon from Profile where UID = ?', (ctx.message.author.id))
ValueError: parameters are of unsupported type

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/discord/ext/commands/bot.py", line 886, in invoke
    yield from ctx.command.invoke(ctx)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/discord/ext/commands/core.py", line 514, in invoke
    yield from injected(*ctx.args, **ctx.kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/discord/ext/commands/core.py", line 71, in wrapped
    raise CommandInvokeError(e) from e
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ValueError: parameters are of unsupported type

#

That is the Error when i use the command that it is linked to

#

@commands.command()
    async def profile(self, ctx):
        self.bot.c.execute('SELECT Weapon from Profile where UID = ?', (ctx.message.author.id))
        Weapon = self.bot.c.fetchone
        WeaponID = Weapon[0]
        await ctx.send('Favorite Weapon {}'.format(Weapon[0]))

This is the command

ionic pecan May 7, 2018, 6:04 AM

#

i believe that you need to pass it an iterable of parameters

#

right now you pass it a single one

orchid charm May 7, 2018, 6:04 AM

#

How do i do that Exactly

ionic pecan May 7, 2018, 6:04 AM

#

you need to add a comma at the end of ctx.message.author.id to make it a tuple, e.g. (ctx.message.author.id,)

#

that's my guess, the traceback is not entirely obvious

orchid charm May 7, 2018, 6:04 AM

#

Why do i keep forgetting to do that

ionic pecan May 7, 2018, 6:05 AM

#

GWcmeisterPeepoShrug

orchid charm May 7, 2018, 6:05 AM

#

Ok new error

#

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/discord/ext/commands/core.py", line 62, in wrapped
    ret = yield from coro(*args, **kwargs)
  File "/Users/shanehawkins/Desktop/DiscordBot/cogs/member.py", line 32, in profile
    WeaponID = Weapon[0]
TypeError: 'builtin_function_or_method' object is not subscriptable

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/discord/ext/commands/bot.py", line 886, in invoke
    yield from ctx.command.invoke(ctx)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/discord/ext/commands/core.py", line 514, in invoke
    yield from injected(*ctx.args, **ctx.kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/discord/ext/commands/core.py", line 71, in wrapped
    raise CommandInvokeError(e) from e
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: 'builtin_function_or_method' object is not subscriptable

#

    @commands.command()
    async def profile(self, ctx):
        self.bot.c.execute('SELECT Weapon from Profile where UID = ?', (ctx.message.author.id,))
        Weapon = self.bot.c.fetchone
        WeaponID = Weapon[0]
        await ctx.send('Favorite Weapon {}'.format(Weapon[0]))

#

That is what i get from the command now when i use it

#

The goal of this command is to Grab from the line that has your User ID in it whatever Weapons is

ionic pecan May 7, 2018, 6:06 AM

#

fetchone is a method, not an attribute

orchid charm May 7, 2018, 6:07 AM

#

So what do i do to fix

ionic pecan May 7, 2018, 6:07 AM

#

you call it

orchid charm May 7, 2018, 6:07 AM

#

Define "Calling it"

#

Just putting self.bot.c.fetchone[0]

ionic pecan May 7, 2018, 6:07 AM

#

this should give you an idea ```py

def add(a: int, b: int):
... return a + b
...
add
<function add at 0x10cb7af28>
add(2, 3)
5

#

fetchone is a method on the cursor

orchid charm May 7, 2018, 6:08 AM

#

🤔

#

So what am i supposed to do with that

#

@ionic pecan Also where do i put that

ionic pecan May 7, 2018, 6:11 AM

#

nowhere
Basically, what you're doing is WeaponID = self.bot.c.fetchone[0]

#

and fetchone is a method

#

you want to access index 0 on the result of fetchone, not the function itself

orchid charm May 7, 2018, 6:11 AM

#

O

#

    @commands.command()
    async def profile(self, ctx):
        self.bot.c.execute('SELECT Weapon from Profile where UID = ?', (ctx.message.author.id,))
        self.bot.c.fetchone
        await ctx.send('Favorite Weapon {}'.format())

#

@ionic pecan How do i access Index 0 then

#

Brain.exe has stopped working, its like 11:00PM PT

runic tundra May 7, 2018, 6:16 AM

#

i think you want WeaponID = self.bot.c.fetchone()[0]

orchid charm May 7, 2018, 6:18 AM

#

cool that actually worked

#

👍

#

Thanks Dom

runic tundra May 7, 2018, 6:18 AM

#

so that calls fetchone, and then you access it with the [0]

#

just so you know what its doing

orchid charm May 7, 2018, 6:18 AM

#

Now all i gotta do is figure out how to write like that

#

So i can write to the Weapon Cagetory that has the UID with my user id in it

runic tundra May 7, 2018, 6:20 AM

#

is it an insert or an update you want to do

orchid charm May 7, 2018, 6:20 AM

#

Update

#

I want to update Weapon from None to something else

runic tundra May 7, 2018, 6:20 AM

#

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

#

thats the general syntax

#

i will let you figure it out

#

let me know if you need help

orchid charm May 7, 2018, 6:21 AM

#

New error

#

I manually wrote to the Weapon Column Splattershot pro

#

Now if i use the command i get this

#

Ignoring exception in command profile:
Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/discord/ext/commands/core.py", line 62, in wrapped
    ret = yield from coro(*args, **kwargs)
  File "/Users/shanehawkins/Desktop/DiscordBot/cogs/member.py", line 31, in profile
    WeaponID = self.bot.c.fetchone()[0]
TypeError: 'NoneType' object is not subscriptable

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/discord/ext/commands/bot.py", line 886, in invoke
    yield from ctx.command.invoke(ctx)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/discord/ext/commands/core.py", line 514, in invoke
    yield from injected(*ctx.args, **ctx.kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/discord/ext/commands/core.py", line 71, in wrapped
    raise CommandInvokeError(e) from e
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: 'NoneType' object is not subscriptable

#

    @commands.command()
    async def profile(self, ctx):
        self.bot.c.execute('SELECT Weapon from Profile where UID = ?', (ctx.message.author.id,))
        WeaponID = self.bot.c.fetchone()[0]
        await ctx.send('Favorite Weapon {}'.format(WeaponID))

ionic pecan May 7, 2018, 6:22 AM

#

fetchone returns None

#

probably because it didn't find anything with the query

#

and you try to access None at index 0

runic tundra May 7, 2018, 6:24 AM

#

result = self.bot.c.fetchone()
if(result != None):
    WeaponID = result [0]
else:
    #error case here

#

@orchid charm bit of error checking

orchid charm May 7, 2018, 6:24 AM

#

So where do i put that

runic tundra May 7, 2018, 6:25 AM

#

delete WeaponID = self.bot.c.fetchone()[0] and put that instead

#

im not sure what your error case is because idk what your program does

#

can just have it printing something for debug purposes

orchid charm May 7, 2018, 6:26 AM

#

Error case here

#

What do i put in that

runic tundra May 7, 2018, 6:26 AM

#

if you can't think of anyting put print("Error fetching weapon id result") or something

orchid charm May 7, 2018, 6:26 AM

#

this Cog is for a Profile system

#

The user Writes information to the DB

#

Then when they type !profile the bot displays it in a embed

#

But for now no embeds

runic tundra May 7, 2018, 6:27 AM

#

ok so in the case there is no weapon id result (database doesn't have one) what do you want it to do?

#

send a message? leave something blank?

#

that is what the error case is for

orchid charm May 7, 2018, 6:37 AM

#

Unset

#

I want it to say instead of a Weapon Unset

#

@runic tundra

runic tundra May 7, 2018, 6:38 AM

#

you want it to say that in the database?

orchid charm May 7, 2018, 6:39 AM

#

Yeah

#

If there is nothing there it is Unset

runic tundra May 7, 2018, 6:40 AM

#

ok so where i put #error case here put a sql update statement

#

self.bot.c.execute("UPDATE Profile SET Weapon='Weapon Unset' WHERE UID=?", (ctx.message.author.id,))

#

@orchid charm give that a go

orchid charm May 7, 2018, 6:45 AM

#

    @commands.command()
    async def profile(self, ctx):
        self.bot.c.execute('SELECT Weapon from Profile where UID = ?', (ctx.message.author.id,))
        result = self.bot.c.fetchone()
        if(result != None):
            WeaponID = result [0]
        else:
            self.bot.c.execute("UPDATE Profile SET Weapon='Weapon Unset' WHERE UID=?", (ctx.message.author.id,))
        await ctx.send('Favorite Weapon {}'.format(WeaponID))

#

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/discord/ext/commands/core.py", line 62, in wrapped
    ret = yield from coro(*args, **kwargs)
  File "/Users/shanehawkins/Desktop/DiscordBot/cogs/member.py", line 36, in profile
    await ctx.send('Favorite Weapon {}'.format(WeaponID))
UnboundLocalError: local variable 'WeaponID' referenced before assignment

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/discord/ext/commands/bot.py", line 886, in invoke
    yield from ctx.command.invoke(ctx)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/discord/ext/commands/core.py", line 514, in invoke
    yield from injected(*ctx.args, **ctx.kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/discord/ext/commands/core.py", line 71, in wrapped
    raise CommandInvokeError(e) from e
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: UnboundLocalError: local variable 'WeaponID' referenced before assignment

#

@runic tundra

runic tundra May 7, 2018, 6:47 AM

#

add WeaponID='Weapon Unset' just above the the sql select statement

orchid charm May 7, 2018, 6:51 AM

#

Which one, the else statement? @runic tundra

#

Ok did it

#

Now time to try it when i set the Weapon Value

runic tundra May 7, 2018, 6:52 AM

#

na the first select statement

#

@orchid charm

orchid charm May 7, 2018, 6:52 AM

#

? @runic tundra

runic tundra May 7, 2018, 6:53 AM

#

what don't you understand

#

you asked where to put it i said above the select statement

orchid charm May 7, 2018, 6:53 AM

#

Ah

#

    @commands.command()
    async def profile(self, ctx):
        WeaponID='Weapon Unset'
        self.bot.c.execute('SELECT Weapon from Profile where UID = ?', (ctx.message.author.id,))
        result = self.bot.c.fetchone()
        if(result != None):
            WeaponID = result [0]
        else:
            self.bot.c.execute("UPDATE Profile SET Weapon='Weapon Unset' WHERE UID=?", (ctx.message.author.id,))
        await ctx.send('Favorite Weapon {}'.format(WeaponID))

runic tundra May 7, 2018, 6:54 AM

#

yeah that looks good

#

just need to test it

orchid charm May 7, 2018, 6:54 AM

#

But the thing is i set the DB

runic tundra May 7, 2018, 6:54 AM

#

you need to try one with a weapon set and one that has nothing

#

and see if it works

orchid charm May 7, 2018, 6:55 AM

#

📎 Screen_Shot_2018-05-06_at_11.54.45_PM.png

#

📎 Screen_Shot_2018-05-06_at_11.55.31_PM.png

#

@runic tundra

runic tundra May 7, 2018, 6:56 AM

#

wait

#

urr ok so you need to do some debugging

#

give me a sec

#

    @commands.command()
    async def profile(self, ctx):
        WeaponID='Weapon Unset'
        self.bot.c.execute('SELECT Weapon from Profile where UID = ?', (ctx.message.author.id,))
        result = self.bot.c.fetchone()
        if(result != None):
            WeaponID = result[0]
        else:
            await ctx.send('UID from discord {}'.format(ctx.message.author.id))
            self.bot.c.execute("UPDATE Profile SET Weapon='Weapon Unset' WHERE UID=?", (ctx.message.author.id,))
        await ctx.send('Favorite Weapon {}'.format(WeaponID))

#

@orchid charm try that and see what it sends

#

if the userid matches the one in the database

orchid charm May 7, 2018, 7:03 AM

#

So replace my command with that

#

📎 Screen_Shot_2018-05-07_at_12.04.15_AM.png

#

@runic tundra

#

That is what i get now

runic tundra May 7, 2018, 7:08 AM

#

the user ids are correct but the select is retuning None

#

i gotta go do stuff so i cant help right now hopefully someone else can help

orchid charm May 7, 2018, 7:08 AM

#

Also when Weapon is Null it says None

#

Ok well it actually worked this time

#

I reset my DB to default and it functioned Properly

orchid charm May 7, 2018, 7:35 AM

#

Ok, new Question
How do i set Names value to argument

#

📎 Screen_Shot_2018-05-06_at_11.54.45_PM.png

#

It is Currently Null

#

So how do i set it to a Argument

gusty spindle May 7, 2018, 7:49 AM

#

@orchid charm that should be very easy to find in a google search or on the sqlite3 docs

orchid charm May 7, 2018, 7:50 AM

#

What would i even search, How to Update Table Column where something exists?

gusty spindle May 7, 2018, 7:50 AM

#

yup

#

specify sqlite3 in there though

orchid charm May 7, 2018, 3:45 PM

#

        self.bot.c.execute("UPDATE Profile SET FC=? WHERE UID=?", (arg1, arg2, arg3, ctx.message.author.id,))

#

How do i make it SET FC= to Arg1, Arg2, and Arg3

ornate abyss May 7, 2018, 3:53 PM

#

That doesnt make sense

orchid charm May 7, 2018, 3:54 PM

#

Ok just did a Different method

ornate abyss May 7, 2018, 3:55 PM

#

?

orchid charm May 7, 2018, 3:55 PM

#

3 Different Columns

#

Each one is set to the specified arg

ornate abyss May 7, 2018, 3:55 PM

#

Yea, thatll work

orchid charm May 7, 2018, 3:55 PM

#

FC1 is set to Arg1, FC2 is set to Arg 2, ect.

#

How do i make it require it to be numbers

#

And how do i make it require each arg to be 4 Characters long

ornate abyss May 7, 2018, 3:56 PM

#

Youd do that with python

orchid charm May 8, 2018, 3:41 PM

#

@bot.event
async def on_message():
    self.bot.c.execute('UPDATE Profile SET XP=XP+5 WHERE UID=?', (ctx.message.author.id,))
    self.bot.conn.commit()

#

Ignoring exception in on_message
Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/discord/client.py", line 224, in _run_event
    yield from coro(*args, **kwargs)
TypeError: on_message() takes 0 positional arguments but 1 was given

#

What am i doing wrong

steel slate May 8, 2018, 3:41 PM

#

what do you think you are doing?

#

on_message() takes no arguments and apparently on line 224 of client.py you called it with an argument

#

you forgot to do on_message(self):

orchid charm May 8, 2018, 3:42 PM

#

Ohhh

steel slate May 8, 2018, 3:43 PM

#

#python-discussion

orchid charm May 8, 2018, 3:43 PM

#

This was a Database question too so i put it in here just incase it had to do with my DB, also incase i do something wrong with my DB

viral crag May 8, 2018, 3:44 PM

#

that was a discord.py question

#

it's on_message(message)

steel slate May 8, 2018, 3:47 PM

#

ok, I was joking anyway

#

#303906096458891264 then

orchid charm May 8, 2018, 8:12 PM

#

@bot.event
async def on_message(message):
    async with aiosqlite.connect('profiles.db') as db:
        await db.execute('UPDATE PROFILE SET XP=XP+5 where UID = {}'.format(message.author.id))

What exactly am i doing wrong, it is not adding 5 to XP
XP by default is 0

#

Also using aiosqlite now

#

NVM, i forgot to commit

#

Ok, now i need to figure out how to make the bot check if the users XP is 100, and if it is add 1 to level

#

Or to be more clear, whenever the users xp is a multiple of 100 it adds one level

#

So i become LV 1 at 100 EXP, 2 at 200 EXP ect.

gusty spindle May 8, 2018, 8:24 PM

#

just make their level equal to their EXP // 100

orchid charm May 8, 2018, 8:24 PM

#

Oh?

gusty spindle May 8, 2018, 8:24 PM

#

well

#

EXP // 100 + 1

#

60 // 100 == 0, 632 // 100 == 6 etc

orchid charm May 8, 2018, 8:25 PM

#

so i Select EXP

#

Then how would i check if its a multiple of 100

gusty spindle May 8, 2018, 8:25 PM

#

can you not use google?

orchid charm May 8, 2018, 8:25 PM

#

¯_(ツ)_/¯

gusty spindle May 8, 2018, 8:26 PM

#

"python number has multiple" should give you a result

orchid charm May 8, 2018, 8:33 PM

#

@bot.event
async def on_message(message):
    bot.db = await aiosqlite.connect('profiles.db')
    await bot.db.execute('UPDATE PROFILE SET XP=XP+5 where UID = {}'.format(message.author.id))
    await bot.db.commit()
    await bot.db.execute('SELECT XP from Profile where UID = ?', (message.author.id,))
    result = bot.db.fetchone()
    if (result x%100==0):
        await bot.send('You levelled up!')

#

@gusty spindle So like that?

gusty spindle May 8, 2018, 8:34 PM

#

i suppose, yup ^^

orchid charm May 8, 2018, 8:36 PM

#

@bot.event
async def on_message(message):
    bot.db = await aiosqlite.connect('profiles.db')
    await bot.db.execute('UPDATE PROFILE SET XP=XP+5 where UID = {}'.format(message.author.id))
    await bot.db.commit()
    await bot.db.execute('SELECT XP from Profile where UID = ?', (message.author.id,))
    result = bot.db.fetchone()
    if (result == x%100==0):
        await bot.send('You levelled up!')

#

Ignoring exception in on_message
Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/discord/client.py", line 224, in _run_event
    yield from coro(*args, **kwargs)
  File "./main.py", line 37, in on_message
    bot.db = await aiosqlite.connect('profiles.db')
TypeError: object Connection can't be used in 'await' expression

#

@gusty spindle That is what happens now

#

It worked before i added result

gusty spindle May 8, 2018, 8:36 PM

#

i have no idea how to use aiosqlite but try removing the await

orchid charm May 8, 2018, 9:48 PM

#

I am gonna migrate to PostgreSQL

#

@gusty spindle

@bot.event
async def on_message(message):
        await db.execute('UPDATE PROFILE SET XP=XP+5 where UID = {}'.format(message.author.id))
        await db.commit()
        await db.execute('SELECT XP from Profile where UID = ?', (message.author.id,))
        row = await bot.db.fetchone()
    if (row == x%100==0):
        await bot.send('You levelled up!')

#

  File "./main.py", line 43
    if (row == x%100==0):
                        ^
IndentationError: unindent does not match any outer indentation level

#

What is wrong

#

Or wait, i think i know

#

Nvm

#

@gusty spindle Oh yeah i got this error
https://hastebin.com/dabahelevi.sql

#

What does it mean exactly

gusty spindle May 8, 2018, 10:00 PM

#

i have no idea how to use postgresql databases, sorry

orchid charm May 8, 2018, 10:00 PM

#

Oof

#

What databases do you know how to use then XD

#

Well if anyone knows what this means and how to fix ping me
https://hastebin.com/dabahelevi.sql

viral crag May 8, 2018, 10:37 PM

#

@orchid charm Something wrong with your credentials object

#

Make sure that's the correct way to do what you're doing by reading the docs

orchid charm May 8, 2018, 10:37 PM

#

credentials = {
    "user": "(Not Showing You)",
    "password": "(Not Showing You)",
    "database": "profiles.db",
    "host:": "127.0.0.1",
    }

@viral crag

#

Those are my Credentials

viral crag May 8, 2018, 10:38 PM

#

So are you supposed to pass a dict into that function?

orchid charm May 8, 2018, 10:38 PM

#

?

viral crag May 8, 2018, 10:38 PM

#

I would have expected a connection url

orchid charm May 8, 2018, 10:38 PM

#

a connection url?

viral crag May 8, 2018, 10:39 PM

#

A connection url.

orchid charm May 8, 2018, 10:39 PM

#

How do i get one

viral crag May 8, 2018, 10:39 PM

#

You make one

orchid charm May 8, 2018, 10:39 PM

#

How XD

viral crag May 8, 2018, 10:39 PM

#

It'll be something like uhh

#

"aiopg://username:password@host:port/database"

#

Something like that

orchid charm May 8, 2018, 10:40 PM

#

O

viral crag May 8, 2018, 10:40 PM

#

Unless you just meant to unpack that dict

orchid charm May 8, 2018, 10:40 PM

#

I just wanted that dict to be my credentials

viral crag May 8, 2018, 10:41 PM

#

In which case you needed **credentials

orchid charm May 8, 2018, 10:41 PM

#

Ohh

#

so ** in front of credentials

viral crag May 8, 2018, 10:41 PM

#

In the function call

#

Basically that unpacks the dict into keyword arguments for the function

orchid charm May 8, 2018, 10:41 PM

#

Ohh

#

TypeError: connect() got an unexpected keyword argument 'host:'

#

@viral crag So i just remove host?

#

Ok, it worked

#

but password authentication failed

viral crag May 8, 2018, 10:47 PM

#

No, don't remove host, remove the colon in the host key

orchid charm May 8, 2018, 10:47 PM

#

o

viral crag May 8, 2018, 10:47 PM

#

Alright well, wrong username and password :P

orchid charm May 8, 2018, 10:48 PM

#

How do i change it then

#

@viral crag

#

Cause i don't know what i set it to

viral crag May 8, 2018, 10:50 PM

#

You'll have to look that up

#

I don't know the reset process and I'm going to bed anyway

orchid charm May 8, 2018, 11:45 PM

#

Ya know what, fuck postgres

#

Too stupidly complicated to set up on my mac

gusty spindle May 9, 2018, 12:22 AM

#

by the way you seem to be asking a lot of questions which you can answer with google.

lyric stag May 9, 2018, 12:32 AM

#

postgres is dead simple to set up on mac

#

probably the easiest of all the OSs

#

https://postgresapp.com/

Postgres.app

Postgres.app – the easiest way to get started with PostgreSQL on...

Postgres.app is a full featured PostgreSQL installation packaged as a standard Mac app.

orchid charm May 9, 2018, 7:08 AM

#

@lyric stag Also, sqlite3 was easier to install 👍

#

Also i ran Nadekos before this so i already was running sqlite

lyric stag May 9, 2018, 7:09 AM

#

check out that link I posted, it's really easy

orchid charm May 9, 2018, 7:09 AM

#

Hopefully it works this time, i might have to uninstall alot of stuff cause of it conflicting with everything

#

Also wow i am suprised anyone is up rn

#

Its Midnight (in PT)

lyric stag May 9, 2018, 7:15 AM

#

i'm moving out of my uni for the summer

orchid charm May 9, 2018, 7:17 AM

#

?

#

Also how do i connect to a postgresql server with discord.py

hollow tiger May 9, 2018, 7:19 AM

#

aight i'm getting started on mongodb cuz i need that json like shit

orchid charm May 9, 2018, 7:20 AM

#

Json db?

#

I did not even know that was possible

hollow tiger May 9, 2018, 7:21 AM

#

yea lol someone told me about it in this server

#

I think it's perfect for what I'm using

orchid charm May 9, 2018, 7:21 AM

#

Json would be for a smaller bot

hollow tiger May 9, 2018, 7:22 AM

#

but I'm completely new to databases

orchid charm May 9, 2018, 7:22 AM

#

Sqlite would work better probably

viral crag May 9, 2018, 7:22 AM

#

I would advise not using mongo

#

Try rethinkdb

orchid charm May 9, 2018, 7:22 AM

#

^

hollow tiger May 9, 2018, 7:22 AM

#

rethinkdb?

viral crag May 9, 2018, 7:22 AM

#

Also, you're wrong @orchid charm

hollow tiger May 9, 2018, 7:22 AM

#

lmao

orchid charm May 9, 2018, 7:22 AM

#

Nom

hollow tiger May 9, 2018, 7:22 AM

#

oof

orchid charm May 9, 2018, 7:22 AM

#

Sqlite best DB ever confirmed 2018

viral crag May 9, 2018, 7:22 AM

#

JSON files are no good for larger applications, yes

#

These things are far from JSON files

orchid charm May 9, 2018, 7:22 AM

#

Don't you need to apply everything at once to a json file?