#databases | Python | Page 45

viral crag May 9, 2018, 7:23 AM

#

Yeah which is why you use a database instead

hollow tiger May 9, 2018, 7:23 AM

#

i use a json file but i'm converting to some document database thing

orchid charm May 9, 2018, 7:23 AM

#

Yeah, use a db bruh

viral crag May 9, 2018, 7:23 AM

#

There's no reason the database can't use JSON or BSON

orchid charm May 9, 2018, 7:23 AM

#

I got a friend with a bot called Spykebot that runs on mongodb

#

Its in multiple thousand splatoon discords

hollow tiger May 9, 2018, 7:23 AM

#

my current json file is like 8632 lines long lmao

viral crag May 9, 2018, 7:23 AM

#

Mongo is infamous for being unreliable

hollow tiger May 9, 2018, 7:23 AM

#

unreliable?

orchid charm May 9, 2018, 7:24 AM

#

My friend still does not know how the fuck it still is running

#

But it did get corrupted a month ago

hollow tiger May 9, 2018, 7:24 AM

#

idk y it makes u do whatever port and connection shit if ur connecting to a local db

orchid charm May 9, 2018, 7:24 AM

#

And all data was lost

viral crag May 9, 2018, 7:24 AM

#

It does stuff like temporarily losing data during upgrades and not sharding correctly

#

It's also a bit unsafe because it doesn't warn you about system problems

#

For example, if you run out of disk space, it just silently drops any data you give it

hollow tiger May 9, 2018, 7:25 AM

#

damn lmao

#

mongodb

#

what a g

#

rethink db

#

one thing i'm confused about is whatever port and stuff

#

Why are there things related to hosts and shit

#

if i'm running the db locally?

viral crag May 9, 2018, 7:32 AM

#

Because it has to know that still?

#

Every database does, some of them just default to localhost

hollow tiger May 9, 2018, 7:34 AM

#

idk i'm just so uninformed about everything rn

viral crag May 9, 2018, 7:35 AM

#

No kidding :P

hollow tiger May 9, 2018, 7:37 AM

#

rethinkdb.errors.ReqlTimeoutError: Could not connect to localhost:28015, operation timed out.

#

woooooot

viral crag May 9, 2018, 7:40 AM

#

Well yeah

#

That's the mongodb port

#

rethinkdb runs on 8080 by default

hollow tiger May 9, 2018, 7:58 AM

#

wait wat

#

what should i do

#

wooot

viral crag May 9, 2018, 7:59 AM

#

Install rethinkdb and run it?

hollow tiger May 9, 2018, 7:59 AM

#

i did

viral crag May 9, 2018, 7:59 AM

#

So then connect to it using the right port

hollow tiger May 9, 2018, 7:59 AM

#

uh

#

is it r.connect( "localhost", 8080).repl()?

#

rethinkdb.errors.ReqlTimeoutError: Could not connect to localhost:8080, operation timed out.

#

wooot

viral crag May 9, 2018, 8:21 AM

#

you still need to actually run the database software

#

it's just rethinkdb in a terminal

ornate abyss May 9, 2018, 12:20 PM

#

@viral crag mongodb is a lot better than it once was

viral crag May 9, 2018, 12:20 PM

#

you say that, but I use it

ornate abyss May 9, 2018, 12:20 PM

#

So?

viral crag May 9, 2018, 12:21 PM

#

so I know exactly what the problems I have with it are

ornate abyss May 9, 2018, 12:21 PM

#

Compared to a few years ago, it is a lot better

viral crag May 9, 2018, 12:22 PM

#

are you trying to say that because it has improved over the last few years, my issues are invalid?

#

they are current issues

ornate abyss May 9, 2018, 12:22 PM

#

"Mongo is infamous for being unreliable" isn't as true as it once was

viral crag May 9, 2018, 12:22 PM

#

well, it's still both infamous and unreliable, so

ornate abyss May 9, 2018, 12:22 PM

#

Whatever you say

torn sphinx May 9, 2018, 12:31 PM

#

it's still unreliable in comparison to anything else used in the industry

#

¯_(ツ)_/¯

viral crag May 9, 2018, 12:32 PM

#

sounds infamous enough to me

hollow tiger May 9, 2018, 3:58 PM

#

I tried running it but it didn’t let me exit out of it

#

Do I need to tmux or some shit?

viral crag May 9, 2018, 4:03 PM

#

No? You can ctrl+c it like anything else

hollow tiger May 9, 2018, 4:08 PM

#

Idk I’m still learning lol

#

It’s ctrl+c on Linux?

ionic pecan May 9, 2018, 4:10 PM

#

It is, yes

#

https://stackoverflow.com/questions/800521/linuxlike-ctrl-c-keyboardinterrupt-for-the-windows-cmd-line

Stack Overflow

Linuxlike Ctrl-C (KeyboardInterrupt) for the Windows cmd line?

I've been forced into using a command line in windows and wondered if there were Linux-like keyboard shortcuts? I googled and didn't find what I was looking for.

Things like ^C, ^Z and such? Thank...

#

try Ctrl+ Break

#

if you run that in Windows?

viral crag May 9, 2018, 4:30 PM

#

It works on windows for me too

hollow tiger May 9, 2018, 6:49 PM

#

uh

#

when i did ctrl+c....

#

**```fix
Server got SIGINT from pid 0, uid 0; shutting down...
Shutting down client connections...
All client connections closed.
Shutting down storage engine... (This may take a while if you had a lot of unflushed data in the writeback cache.)
Storage engine shut down.

#

doesn't that mean it disconnected?

ionic pecan May 9, 2018, 6:53 PM

#

sounds like it shut down, which is what you had in mind 🤔 or is it?

hollow tiger May 9, 2018, 6:53 PM

#

i was trying to do some python stuff

#

and it caused a localhost error

#

so iwas told to actually connect to it

#

"you still need to actually run the database software
it's just rethinkdb in a terminal"

#

so i ran rethinkdb

#

and then i wanted to stay connected to it so that i can test the db in python3

ionic pecan May 9, 2018, 6:57 PM

#

oh like, you want to run both rethinkdb and your bot?

hollow tiger May 9, 2018, 6:58 PM

#

yeah

#

so that i can access my db while using the bot

#

if u know what i mean

ionic pecan May 9, 2018, 6:58 PM

#

Yeah

#

Either you run rethinkdb in background (but I have no idea how that works on Windows), or you just open a second console window

hollow tiger May 9, 2018, 6:58 PM

#

do I start another tmux session?

ionic pecan May 9, 2018, 6:59 PM

#

oh, is this on some remote system?

#

you can run rethinkdb in background without tmux

#

rethinkdb & https://www.digitalocean.com/community/tutorials/how-to-use-bash-s-job-control-to-manage-foreground-and-background-processes

DigitalOcean

How To Use Bash's Job Control to Manage Foreground and Background ...

In this guide, we'll talk about how bash, the Linux system, and your terminal come together to offer process and job control. In a previous guide, we discussed how...

hollow tiger May 9, 2018, 6:59 PM

#

it's a vps

#

oh

#

rethinkdb &?

ionic pecan May 9, 2018, 7:00 PM

#

Yeah

hollow tiger May 9, 2018, 7:00 PM

#

uh

#

📎 Screen_Shot_2018-05-09_at_12.00.29_PM.png

#

how do i exit it?

gusty spindle May 9, 2018, 7:01 PM

#

ctrl+c closes 99% of things on the command line

ionic pecan May 9, 2018, 7:01 PM

#

its running

#

but it just spams your console

hollow tiger May 9, 2018, 7:01 PM

#

do i just close out of my console and open another one?

ionic pecan May 9, 2018, 7:01 PM

#

check the article I linked

#

in short, you can use fg to bring rethinkdb back to foreground, then quit it, and then launch it again and redirect its output to something like /dev/null

hollow tiger May 9, 2018, 7:03 PM

#

how do i put it to the background so that i can run another command?

ionic pecan May 9, 2018, 7:04 PM

#

check the article i linked

hollow tiger May 9, 2018, 7:05 PM

#

uh

#

it says "You can start a background process by appending an ampersand character ("&") to the end of your commands. This tells the shell not to wait for the process to complete, but instead to begin execution and to immediately return the user to a prompt. The output of the command will still display in the terminal (unless redirected), but you can type additional commands as the background process continues."

#

oh shit

#

can I do the same with my discord bot?

ionic pecan May 9, 2018, 7:06 PM

#

you can do that with most processes i believe

orchid charm May 9, 2018, 8:35 PM

#

Info on postgresql db

#

Im giving it another chance

#

Like what do i do to set everything up in it, like connecting to the db, ect.

#

Also info on commiting

#

Cause there are literally, no guides on how to do this with discord.py

#

Every single damn one is for .js

vestal apex May 9, 2018, 8:59 PM

#

I'm gonna imagine there's lots of guides online for setting up postgres

#

and... committing?

orchid charm May 9, 2018, 9:00 PM

#

Used sqlite3 before this

#

Committing = Saving to DB

#

@vestal apex

vestal apex May 9, 2018, 9:01 PM

#

except for the part where it's often called inserting

#

or updating

orchid charm May 9, 2018, 9:01 PM

#

So its in postgres called updating / inserting

#

Instead of update / insert + commit

vestal apex May 9, 2018, 9:01 PM

#

gotcha

orchid charm May 9, 2018, 9:01 PM

#

Anyways do you know how to use asyncpg

#

So i can start using postgres

vestal apex May 9, 2018, 9:02 PM

#

it looks like they have pretty clear docs https://magicstack.github.io/asyncpg/current/usage.html

orchid charm May 9, 2018, 9:03 PM

#

o shit they got docs

#

Welp, time to look into those for 1000000000 hours

vestal apex May 9, 2018, 9:03 PM

#

everything worth using has docs

obtuse lance May 9, 2018, 9:03 PM

#

^

#

^

vestal apex May 9, 2018, 9:03 PM

#

always Google the docs

obtuse lance May 9, 2018, 9:03 PM

#

Are the docs on Google Docs?

#

;p

orchid charm May 9, 2018, 9:04 PM

#

🤔

#

True Question = Google docs has google docs of how to use google docs?

obtuse lance May 9, 2018, 9:05 PM

#

https://imgs.xkcd.com/comics/manuals.png

orchid charm May 9, 2018, 9:08 PM

#

Fetchall equivilant?

obtuse lance May 9, 2018, 9:15 PM

#

sure?

orchid charm May 9, 2018, 9:17 PM

#

? @obtuse lance

#

Like, what is the Equivilant of sqlite3's fetchall in asyncpg

#

Ignoring exception in on_ready
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 30, in on_ready
    await connect()
  File "./main.py", line 13, in connect
    await conn.execute('''CREATE TABLE profile(UID, IGN, FC1, FC2, FC3, Weapon1, Weapon2, Weapon3, RM, SZ, TC, CB, XP)''')
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/asyncpg/connection.py", line 238, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 319, in query
asyncpg.exceptions.PostgresSyntaxError: syntax error at end of input

async def connect():
conn = await asyncpg.connect(**credentials)
await conn.execute('''CREATE TABLE profile(UID, IGN, FC1, FC2, FC3, Weapon1, Weapon2, Weapon3, RM, SZ, TC, CB, XP)''')

What am i doing wrong here also

#

🤔

torn sphinx May 9, 2018, 9:27 PM

#

maybe put a semicolon lol idk

#

also for that first question lemme read the docs

#

https://magicstack.github.io/asyncpg/current/api/index.html?highlight=fetch#asyncpg.connection.Connection.fetch

hollow tiger May 9, 2018, 11:46 PM

#

can you guys help get me started on how dbs work?

#

i'm using rethinkdb

#

and i'm getting really confused following https://rethinkdb.com/docs/guide/python/

runic tundra May 10, 2018, 12:28 AM

#

what exactly is confusing you

hollow tiger May 10, 2018, 12:28 AM

#

basically everything

#

idk what's the point of connecting to some server

#

the rethinkdb command

#

r.connect( "localhost", 28015).repl()

#

what does "localhost", 28015 mean and .repl()

#

y .run() is necessary

#

idk

vestal apex May 10, 2018, 12:41 AM

#

there's a server running on your computer that manages the database for anything else that wants to communicate with it

#

"localhost" is your computer's local address

#

28015 is probably the port rethink is running on

#

.repl() I imagine starts a Read Evaluate Print Loop, based on its name

torn sphinx May 10, 2018, 12:50 AM

#

and im guessing .run() allows the connection to be running perpetually until its closed

hollow tiger May 10, 2018, 1:01 AM

#

r.db("test").table_create("authors").run()

#

doesn't nosql mean theres no tables?.

deft badge May 10, 2018, 1:03 AM

#

no

#

It just means it's not SQL

#

As in there are not things like SELECT, DROP, WHERE, UPDATE etc.

#

there are tables, they work differently though

hollow tiger May 10, 2018, 1:03 AM

#

are they like a big json file?

#

but not a file but a database

deft badge May 10, 2018, 1:04 AM

#

That's how some work, yes

#

Rethink, for example, works in that manner

#

Every document is a JSON object

torn sphinx May 10, 2018, 1:06 AM

#

i withdraw my last comment i guess .run() just executes the line you call it on

hollow tiger May 10, 2018, 1:07 AM

#

well

#

that's

#

...

deft badge May 10, 2018, 1:07 AM

#

You have to specify a connection as well

#

You prepare a query by adding things to r.X().Y().Z()

#

once you are ready and you have your query ready you sent it off to the server using run

glass bough May 10, 2018, 1:08 AM

#

Ok, How do I prevent a blank input being added, such as when I try to insert and if a specific insertment is blank, then it does nothing

#

horribly worded, I'm very sorry

deft badge May 10, 2018, 1:08 AM

#

blank as in just space?

glass bough May 10, 2018, 1:08 AM

#

yea

#

or just submitted without any form of typing

#

like when I'm inserting it, it will prevent such from happening

deft badge May 10, 2018, 1:09 AM

#

looks like you may want str.isspace

glass bough May 10, 2018, 1:09 AM

#

and this does what?

deft badge May 10, 2018, 1:09 AM

#

>>> "hello".isspace()
False
>>> "hello world".isspace()
False
>>> " ".isspace()
True
>>> "                        ".isspace()
True

hollow tiger May 10, 2018, 1:09 AM

#

wait then how does create_table work if it's json?

glass bough May 10, 2018, 1:10 AM

#

so, If it were something like ""

#

would it be true?

deft badge May 10, 2018, 1:10 AM

#

@hollow tiger you can think of that as like a big array (the table) where the JSON objects (the documents) go

#

@glass bough Not for that, you'd need something like if input == "" or input.isspace(): # handle empty

glass bough May 10, 2018, 1:10 AM

#

oh, it returns false

#

ok, thanks:D

deft badge May 10, 2018, 1:11 AM

#

No problems :D

#

@hollow tiger Rethink doesn't actually communicate via JSON, that's just the way things are stored, opposed to a row (like in SQL)

glass bough May 10, 2018, 1:11 AM

#

so something like this

#

if date == '' or date.isspace():
break
else:

deft badge May 10, 2018, 1:12 AM

#

yep

glass bough May 10, 2018, 1:12 AM

#

thanks again

vestal apex May 10, 2018, 1:12 AM

#

or if not date.strip():

glass bough May 10, 2018, 1:19 AM

#

I did an oof

#

just inputed 3000 of the same thing

#

welp

deft badge May 10, 2018, 1:20 AM

#

that could be an issue

#

but theoretically

#

you could also remove 3000 of the same thing

glass bough May 10, 2018, 1:20 AM

#

yea, I made a while loop

deft badge May 10, 2018, 1:20 AM

#

amirite?

glass bough May 10, 2018, 1:20 AM

#

and that was the bad

#

ok, removed

#

alright

#

while not(date == '' or date.isspace()):
c.execute("INSERT INTO Runningtimesof2018(Date, Time, Place) VALUES(?, ?, ?)", (date, time, place))
break

#

would this make any sense

#

I'm almost certain the while statement is wrong

#

would I do if not

#

?

vestal apex May 10, 2018, 1:29 AM

#

I still vote for while date.strip():

deft badge May 10, 2018, 1:29 AM

#

Yeah date.strip does look nicer

#

also that makes sense, sort of

vestal apex May 10, 2018, 1:30 AM

#

(although what you wrote does look correct)

glass bough May 10, 2018, 1:30 AM

#

and this would have the same functionality

deft badge May 10, 2018, 1:30 AM

#

that loop will only run once, so it's basically acting as an if statement

vestal apex May 10, 2018, 1:30 AM

#

oh right the break

glass bough May 10, 2018, 1:31 AM

#

alright, I think I have it

orchid charm May 10, 2018, 3:07 AM

#

🤔

#

returning exception database is locked
Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/aiosqlite/core.py", line 136, in run
    result = fn()
sqlite3.OperationalError: database is locked
Ignoring exception in 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 "/Users/shanehawkins/Desktop/DiscordBot/cogs/member.py", line 182, in on_message
    await self.bot.db.commit()
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/aiosqlite/core.py", line 184, in commit
    await self._execute(self._conn.commit)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/aiosqlite/core.py", line 159, in _execute
    raise result
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/aiosqlite/core.py", line 136, in run
    result = fn()
sqlite3.OperationalError: database is locked

Why is doing !profile locking the database
https://hastebin.com/oqoqusamol.swift

#

Oh yeah here is a event that might have something to do with it
https://hastebin.com/usofebiyuz.py

#

🤔

#

Wait i might know why

#

It is doing two things at once

#

!profile counts as a message, so it is adding 5 points to my message, and the bots message

#

but !profile is also grabbing info from the DB

#

What would i do to fix it then?

#

Also how do i not award points to any messages the bot says

#

Or maybe if two things are happening at once instead of locking the database it does one at a time

#

Yo @vestal apex Do you know the answer to this, also what Databases that are similar to SQlite can handle multiple things at once without locking

vestal apex May 10, 2018, 3:47 AM

#

I'm afraid I don't know and don't have the resources to research at the moment

orchid charm May 10, 2018, 3:47 AM

#

Ah

orchid charm May 10, 2018, 4:04 AM

#

Ok, ima use aiosqlite in my member.py too

#

Also it is commiting on the parts where it needs to commit

#

    async def on_message(self, message):
        async with aiosqlite.connect('profiles.db') as self.bot.db:
            await self.bot.db.execute('UPDATE Profile SET XP=XP+5 where UID = {}'.format(message.author.id,))
            await self.bot.db.commit()
            cursor = await self.bot.db.execute('Select XP from Profile where UID = {}'.format(message.author.id,))
            row = await cursor.fetchone()
            print('{} has received 5 XP {} / 100(LVL UP)'.format(message.author.id, row[0]))
            if row[0] % 100 == 0:
                await message.channel.send('{} has Leveled up! You can view what level you are with !profile'.format(message.author.name))
                await self.bot.db.execute('UPDATE Profile SET Level=Level+1 where UID = {}'.format(message.author.id,))
                await self.bot.db.commit()

It needs to commit to save the Data on this one

#

https://hastebin.com/oqoqusamol.swift

#

This does not need to commit

#

Cause it is just selecting info

#

And putting it into a Variable

#

@modest haven The DB Browser app i am using i quit though

#

And it is doing two things at once is probably why it is locking

#

It is grabbing the data from the Database and inserting it into variables and at the same time awarding points

#

@modest haven Oh yeah also message.startswith is not working on it

    async def on_message(self, message):
        async with aiosqlite.connect('profiles.db') as self.bot.db:
            if message.startswith('!'):
                return
            else:
                await self.bot.db.execute('UPDATE Profile SET XP=XP+5 where UID = {}'.format(message.author.id,))
                await self.bot.db.commit()
                cursor = await self.bot.db.execute('Select XP from Profile where UID = {}'.format(message.author.id,))
                row = await cursor.fetchone()
                print('{} has received 5 XP {} / 100(LVL UP)'.format(message.author.id, row[0]))
                if row[0] % 100 == 0:
                    await message.channel.send('{} has Leveled up! You can view what level you are with !profile'.format(message.author.name))
                    await self.bot.db.execute('UPDATE Profile SET Level=Level+1 where UID = {}'.format(message.author.id,))
                    await self.bot.db.commit()

#

o

#

But all i gotta do is figure out how to make it do one thing at a time and this should be fixed

#

by the bot? or by people who are viewing it

#

Uh ok

#

Also do you know any other Databases that are similar to sqlite that can handle multiple things at once without locking

#

@modest haven

#

So mysql can handle multiple things at once? cool

#

Time to see if i like it

#

Yeah, i will probably recode my entire bot so i can use this

dull scarab May 10, 2018, 6:07 AM

#

Can you order a table based on 3 columns after a bit of math? ((col 1 + col 2)//col3) postgresql btw

#

Or would i have to add another column with that value

ionic pecan May 10, 2018, 8:28 AM

#

async sqlite just sounds like things will break

#

i use peewee_async with postgres https://github.com/05bit/peewee-async

GitHub

05bit/peewee-async

peewee-async - Asynchronous interface for peewee ORM powered by asyncio

hexed epoch May 10, 2018, 12:32 PM

#

aysncpg is also for postgres, fastest of asyncio postgresql connectors and easy to use

orchid charm May 10, 2018, 5:13 PM

#

https://hastebin.com/yugubaqipi.sql
https://hastebin.com/idijasoyot.py
This is a PostgreSQL error i am having, what do i do to fix?

ionic pecan May 10, 2018, 5:15 PM

#

iirc postgres is the system database for configuration and stuff by postgres

#

but I'm not sure

orchid charm May 10, 2018, 5:20 PM

#

🤔

#

Cause i have been trying to migrate

#

And all of this bullshit has been happening

orchid charm May 10, 2018, 6:09 PM

#

https://hastebin.com/eqilebupom.py
This command locks then closes my database, why?
https://hastebin.com/edorexopaj.sql

#

It might have something to do with it reopening connections alot

#

And if so how do i make it not do that XD

orchid charm May 10, 2018, 6:52 PM

#

Wow i figured it out

#

And apparently the peeps who guided me through levels and shit were completely wrong

#

👍

glass bough May 11, 2018, 12:05 AM

#

Ok, so I want to be able to pull everything that has a specific value in a specific row, such as the row being named "Event", and the input of the specific value being a variable

#

such as a string

#

my attempt is "SELECT * FROM table WHERE Event = ?"

#

then I would execute it in a for statement

#

loop*

#

might've just solved it

glass bough May 11, 2018, 3:05 AM

#

new problem, or questions I guess, how could I go through a database and sort it by a row

#

like given 7, 5, 6. It will sort each row in the order of 5,6,7

#

and replace it in the file

runic tundra May 11, 2018, 3:21 AM

#

you don't need to sort the database you can just sort when you query it

#

use ORDER BY

boreal creek May 11, 2018, 3:29 AM

#

Hi everyone. Thanks for having me in this group. Completely new to python.... and programming in general. Trying to teach myself everything here. So please bear with me as I may ask some really simple questions here haha.

#

So first thing I wanted to ask is that in my data, I have dates currently in this format:

#

4/2/2018 (Mon)

#

What's the best way to go about cleaning this?

#

I want to remove the (Day) from that entry and put it in a different column. Is there a quick command for that or would I have to do this manually?

runic tundra May 11, 2018, 3:33 AM

#

query the database for a row

a,b = date.split(" ")

now you have the date in a and the day in b
update the date value with a
update the empty day value with b

#

repeat

boreal creek May 11, 2018, 3:53 AM

#

@runic tundra Thanks!

dull scarab May 11, 2018, 9:12 AM

#

How would i create a new column in an existing database and update all rows with a value based on other columns?

runic tundra May 11, 2018, 9:25 AM

#

ALTER TABLE table_name
ADD column_name datatype;```

#

that will add a column

#

and you will have to write a for loop for updating the values

dull scarab May 11, 2018, 9:26 AM

#

just a quick utility script would do right

#

ty ty though

runic tundra May 11, 2018, 9:26 AM

#

yeah would work

#

is it sqlite or mysql or something?

dull scarab May 11, 2018, 9:31 AM

#

postgresql

glass bough May 11, 2018, 9:29 PM

#

@runic tundra

#

after it is ordered, I would need to select from it to specific rows such as SELECT * FROM table WHERE row = ?

#

after I have it sorted with order by

runic tundra May 12, 2018, 8:42 AM

#

You do the select and order by at the same time

#

After the where row = ? Put the order by

glass bough May 12, 2018, 12:29 PM

#

Thank you

boreal creek May 12, 2018, 6:57 PM

#

@runic tundra for date.split(""), is there a library that needs to be imported?

runic tundra May 12, 2018, 7:08 PM

#

No

glass bough May 12, 2018, 10:50 PM

#

How do I find all the column titles of a table

#

and print it out

#

well, maybe not

#

well, nvm

#

I got it

glass bough May 12, 2018, 11:53 PM

#

how would I put two inputs for a Query ex: sql = ("SELECT * FROM table WHERE ? = ?")

#

for row in c.execute(sql, (inp1, inp2,)):

#

is this how I would do it?

novel wharf May 13, 2018, 12:04 AM

#

I think each ? is replaced by an argument in your args list (inp1, inp2,)

glass bough May 13, 2018, 12:05 AM

#

so is it formatted correctly?

novel wharf May 13, 2018, 12:05 AM

#

So if you want multiple queries in one:
("SELECT * FROM table WHERE ? = ? AND ? = ?")

#

Well, does it run?

glass bough May 13, 2018, 12:05 AM

#

not exactly

#

no errors, but not working

#

as planned

#

its quite hard to explain

novel wharf May 13, 2018, 12:07 AM

#

You could try hardcoding it a little for now, just to see whether it's the query or the args list that isn't working.

#

Instead of ?, write the word you're searching for. Does it exist in the database?

glass bough May 13, 2018, 12:07 AM

#

its all filling it in a believe, but as I go to a for loop trying to print a row in that select information, it doesn't work

novel wharf May 13, 2018, 12:08 AM

#

Alternatively, try:
("SELECT * FROM table WHERE " + str(inp1) + " = " + str(inp2))

#

But you really should avoid this as it's open for SQL injection. The ? and args list should work as intended.

glass bough May 13, 2018, 12:10 AM

#

ok, so that worked

#

I have an idea for the old method that might fix it

novel wharf May 13, 2018, 12:10 AM

#

What worked?

glass bough May 13, 2018, 12:10 AM

#

using that above

novel wharf May 13, 2018, 12:12 AM

#

cursor.execute("INSERT INTO table VALUES (?, ?, ?)", (var1, var2, var3))```

#

It is strange because this should be acceptable syntax.

glass bough May 13, 2018, 12:12 AM

#

yea

#

thats how I added it into the table

novel wharf May 13, 2018, 12:12 AM

#

cursor.execute("INSERT INTO table VALUES (%s, %s, %s)", (var1, var2, var3))```

#

Prettier way of doing the pure string.

#

Try both of these, see which one that works.

glass bough May 13, 2018, 12:13 AM

#

in the entering section of my code?

novel wharf May 13, 2018, 12:13 AM

#

I guess~

glass bough May 13, 2018, 12:13 AM

#

("INSERT INTO Runningtimesof2018(Date, Time, Place, Event) VALUES(?, ?, ?, ?)", (date, time, place, event))

#

this is what I currently have

#

in that part tha tis

novel wharf May 13, 2018, 12:14 AM

#

Hey, that looks nice.

glass bough May 13, 2018, 12:14 AM

#

but thats not the section I'm having trouble with

novel wharf May 13, 2018, 12:16 AM

#

cursor.execute("SELECT * FROM table WHERE ?, ?", (var1, var2))```

glass bough May 13, 2018, 12:18 AM

#

wouldn't it be ? = ?

novel wharf May 13, 2018, 12:18 AM

#

Oh whoops.

glass bough May 13, 2018, 12:18 AM

#

but now it returns operation parameter must be str

novel wharf May 13, 2018, 12:21 AM

#

Ok, so it seems that it doesn't like that you pass arguments in a tuple as the second argument. It wants them individually.

#

cursor.execute("SELECT * FROM table WHERE ? = ?", var1, var2)

glass bough May 13, 2018, 12:22 AM

#

function takes at most 2 arguements (3 given)

novel wharf May 13, 2018, 12:22 AM

#

Nevermind. Misread.

glass bough May 13, 2018, 12:23 AM

#

we could hop into a call

novel wharf May 13, 2018, 12:23 AM

#

Can't talk.

glass bough May 13, 2018, 12:24 AM

#

I could send you the chunk of code I'm using

#

all I have is that there is a table where a column is named Date, and it has two parts with a value of 5.3

#

so as I input that, it takes those and fills them into the Query

novel wharf May 13, 2018, 12:28 AM

#

Paste the line where you call execute.

glass bough May 13, 2018, 12:28 AM

#

for row in c.execute("SELECT * FROM Runningtimesof2018 WHERE ? = ?", (Whattime, Whattime2)):

#

Whattime = Date

#

Whattime2 = 5.3

#

below the for loop it prints out the row

#

I can assure you that the table has a column named Date and a value in that column with the value of 5.3

novel wharf May 13, 2018, 12:32 AM

#

And this code works if you change it to..?

for row in c.execute("SELECT * FROM Runningtimesof2018 WHERE %s = %s" % (Whattime, Whattime2)):

glass bough May 13, 2018, 12:32 AM

#

it worked

#

I feel dumb

novel wharf May 13, 2018, 12:33 AM

#

What?

#

So the one with %s replacement works, but the ? one doesn't?

glass bough May 13, 2018, 12:33 AM

#

oh, missread

#

yea

#

assuming I typed the ? one properly

novel wharf May 13, 2018, 12:34 AM

#

Well, how important is it that it's have SQL injection safe? 😛

glass bough May 13, 2018, 12:34 AM

#

I don't know, its for personal use

#

I guess

#

do you have any idea of what could be wrong?

novel wharf May 13, 2018, 12:35 AM

#

The only problem is if you have a friend who knows how to mess up your life and tries it out.

glass bough May 13, 2018, 12:35 AM

#

but its for my personal use to track my growth through my running seasons

novel wharf May 13, 2018, 12:35 AM

#

Although you could actually make it a bit safer by checking the parameters first. Say, check that the first one is a string only, and the latter a valid number.

glass bough May 13, 2018, 12:35 AM

#

I should be ok

novel wharf May 13, 2018, 12:35 AM

#

Wait.

glass bough May 13, 2018, 12:35 AM

#

what?

novel wharf May 13, 2018, 12:36 AM

#

Go back to the ? one and change Whattime2 to str(Whattime2)

#

That can't possibly be it, but bah, who knows

#

It's just that if the string works, the ? should work, but there's something messing up

glass bough May 13, 2018, 12:37 AM

#

and it didn't work

novel wharf May 13, 2018, 12:38 AM

#

I've used sqlite3 myself and I'm very sure the ? method works, so it's really strange.

glass bough May 13, 2018, 12:38 AM

#

I'm so lost

#

I've used it before, and it has worked

novel wharf May 13, 2018, 12:38 AM

#

!!!!!!!!!!!!!!!!!!1

#

conn.commit()

#

You have to commit to insert the args into the query.

glass bough May 13, 2018, 12:39 AM

#

where would I do this?

#

after the for loop ends?

novel wharf May 13, 2018, 12:39 AM

#

cursor.execute("Insert into links (link,id) values (?,?)",(a,b))
conn.commit()

glass bough May 13, 2018, 12:39 AM

#

I have that

#

I know its in the db file because I have a viewer to check my work

#

just to make sure I'm doing it right

novel wharf May 13, 2018, 12:41 AM

#

Blah, I don't like SQL.

glass bough May 13, 2018, 12:41 AM

#

nobody should

#

this makes no sense

novel wharf May 13, 2018, 12:43 AM

#

for row in c.execute("SELECT * FROM Runningtimesof2018 WHERE Date = ?", (Whattime2)):

glass bough May 13, 2018, 12:43 AM

#

but the Date has to be changeable

#

to other things in the database columns

#

or do want me to just test with that?

novel wharf May 13, 2018, 12:44 AM

#

I'm just not finding any examples of anyone using ? as the key. Just the value.

#

And since only the value is what's sensitive here, you might actually have to insert the key into the string, not as an argument.

glass bough May 13, 2018, 12:45 AM

#

and this means what?

novel wharf May 13, 2018, 12:45 AM

#

The key is set in your code anyway so it's safe from injection, unless the user sends it.

glass bough May 13, 2018, 12:45 AM

#

ok

novel wharf May 13, 2018, 12:45 AM

#

for row in c.execute("SELECT * FROM Runningtimesof2018 WHERE %s = ?" % (Whattime), (Whattime2)):```

#

It means tossing away readability.

glass bough May 13, 2018, 12:46 AM

#

wait

#

for row in c.execute("SELECT * FROM Runningtimesof2018 WHERE Date = ?", (str(Whattime2))):

#

as I runth si

#

this

#

I get sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 3 supplied.

novel wharf May 13, 2018, 12:48 AM

#

Added some brackets.

glass bough May 13, 2018, 12:48 AM

#

where exactly

novel wharf May 13, 2018, 12:48 AM

#

Around Whattime and Whattime2

glass bough May 13, 2018, 12:48 AM

#

while I was just using Whattime2

#

it gave me that error

novel wharf May 13, 2018, 12:49 AM

#

That sounds like sqlite3 thought you had three ?

glass bough May 13, 2018, 12:49 AM

#

yea...

#

is it because its 5 . 3

novel wharf May 13, 2018, 12:51 AM

#

Mff.

glass bough May 13, 2018, 12:51 AM

#

it shouldn't right?

novel wharf May 13, 2018, 12:51 AM

#

Who even knows.

#

Does SQL even have floats? Isn't it INT only?

#

What is your Date set as in your scheme?

glass bough May 13, 2018, 12:52 AM

#

maybe thats my issue

novel wharf May 13, 2018, 12:52 AM

#

Is it a string?

glass bough May 13, 2018, 12:52 AM

#

Whattime2?

novel wharf May 13, 2018, 12:52 AM

#

Date

glass bough May 13, 2018, 12:53 AM

#

how can I tell?

novel wharf May 13, 2018, 12:53 AM

#

Do you have a SQL scheme?

glass bough May 13, 2018, 12:53 AM

#

its a TEXT

novel wharf May 13, 2018, 12:53 AM

#

Or how do you create the table?

glass bough May 13, 2018, 12:53 AM

#

oh my

novel wharf May 13, 2018, 12:53 AM

#

Ok ok.

#

So it's storing "5.3"

glass bough May 13, 2018, 12:53 AM

#

yes

novel wharf May 13, 2018, 12:53 AM

#

And you're most likely inserting a 5.3 float via the ?

glass bough May 13, 2018, 12:53 AM

#

but would converting it to a string matter?

novel wharf May 13, 2018, 12:54 AM

#

Although I suggested earlier you should insert str(Whattime2)

glass bough May 13, 2018, 12:54 AM

#

yea

novel wharf May 13, 2018, 12:54 AM

#

Give it a go

glass bough May 13, 2018, 12:54 AM

#

change what exactly?

novel wharf May 13, 2018, 12:54 AM

#

? = ?, (Whattime, str(Whattime2))

glass bough May 13, 2018, 12:54 AM

#

isn't that what I've been doing the entire time

#

for row in c.execute("SELECT * FROM Runningtimesof2018 WHERE Date = ?", (str(Whattime2))):

novel wharf May 13, 2018, 12:55 AM

#

Oof.

glass bough May 13, 2018, 12:55 AM

#

is it because its a TEXT

#

?

novel wharf May 13, 2018, 12:56 AM

#

I don't know what else it could be

#

Remove the args and change ? to 5.3

#

Just always select Date = 5.3

glass bough May 13, 2018, 12:56 AM

#

that works

#

for row in c.execute("SELECT * FROM Runningtimesof2018 WHERE Date = 5.3"):

#

that is

novel wharf May 13, 2018, 12:57 AM

#

Now you change Date to a ?, and add the (Whattime) as the args

#

Still always testing 5.3

glass bough May 13, 2018, 12:57 AM

#

it sais there is 4 supplied

#

for row in c.execute("SELECT * FROM Runningtimesof2018 WHERE ? = 5.3", (Whattime)):

#

let me convert it to a string

#

still sais 4 supplied as I type in Date

novel wharf May 13, 2018, 12:58 AM

#

Add a comma. (Whattime,)

glass bough May 13, 2018, 12:59 AM

#

that doesn't return it, but there are no errors

#

no return of the print statement

novel wharf May 13, 2018, 1:01 AM

#

I'd say that you cannot use ? for keys. Only values. Can't find a single example of a key injection

glass bough May 13, 2018, 1:01 AM

#

so I have to use %s?

novel wharf May 13, 2018, 1:01 AM

#

Before that, let's just test the opposite

#

Write Date = ?, and then ("5.3",)

glass bough May 13, 2018, 1:02 AM

#

that worked

#

and changing 5.3 to Whattime2 works

novel wharf May 13, 2018, 1:03 AM

#

Beautiful

glass bough May 13, 2018, 1:03 AM

#

just the Whattime part that doesn't work apparently

novel wharf May 13, 2018, 1:03 AM

#

Well, today we learned to not inject keys, I guess~

#

Are you using python3?

glass bough May 13, 2018, 1:04 AM

#

I am

novel wharf May 13, 2018, 1:04 AM

#

>>> name = 'Fred'
>>> age = 42
>>> f'He said his name is {name} and he is {age} years old.'```

#

I learned about f-strings only today. It's like christmas 🎁

#

Give this a try.

glass bough May 13, 2018, 1:05 AM

#

wait what?

novel wharf May 13, 2018, 1:05 AM

#

Instead of the ugly %s. No one likes those.

glass bough May 13, 2018, 1:05 AM

#

so my final result would be?

novel wharf May 13, 2018, 1:06 AM

#

f"SELECT * FROM Runningtimesof2018 WHERE {Whattime} = ?"

#

f at start and {Whattime}

#

Should rename those variables to Whatkey and Whatvalue or something~

glass bough May 13, 2018, 1:06 AM

#

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 0 supplied.

#

I will

novel wharf May 13, 2018, 1:07 AM

#

Well ok then

#

"SELECT * FROM Runningtimesof2018 WHERE {} = ?".format(Whattime)

glass bough May 13, 2018, 1:07 AM

#

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 0 supplied.

novel wharf May 13, 2018, 1:08 AM

#

Oh, that error has to do with the args

#

You NEED the comma at the end to make it a tuple

#

(Whattime2,)

#

Or you just change it to [Whattime2]

glass bough May 13, 2018, 1:09 AM

#

for row in c.execute("SELECT * FROM Runningtimesof2018 WHERE {} = ?".format(Whattime, Whattime2,)):

#

so like this?

novel wharf May 13, 2018, 1:09 AM

#

No. The format has to do with the {} and Whattime. The (Whattime2,) goes at the end into execute

glass bough May 13, 2018, 1:10 AM

#

for row in c.execute("SELECT * FROM Runningtimesof2018 WHERE {} = ?".format(Whattime), [Whattime2]):

#

this worked

#

did we just do it?

novel wharf May 13, 2018, 1:10 AM

#

query = "SELECT * FROM Runningtimesof2018 WHERE {} = ?".format(Whattime)
result = c.execute(query, [Whattime2])
for row in result:

glass bough May 13, 2018, 1:10 AM

#

what I did above got me the same answer

novel wharf May 13, 2018, 1:11 AM

#

Yee

#

But readability

glass bough May 13, 2018, 1:11 AM

#

so is this immune to injection attacks

#

oh

#

so just re format it like yours?

#

or would that still have an issue

novel wharf May 13, 2018, 1:11 AM

#

Only difference is that I split it into variables query/result

glass bough May 13, 2018, 1:11 AM

#

yea

#

so whats the issue with this now?

#

if any

novel wharf May 13, 2018, 1:12 AM

#

Anyway, it's injection safe as long as the Whattime key only comes from within your code. The user should definitely not request those.

glass bough May 13, 2018, 1:12 AM

#

ok

#

so great:D

novel wharf May 13, 2018, 1:12 AM

#

Does it work?

glass bough May 13, 2018, 1:12 AM

#

anyways, none of my friends use my computer

#

yea, as I mentioned above, it works using my and your final products

novel wharf May 13, 2018, 1:13 AM

#

🎵

#

Oof, what a mess.

glass bough May 13, 2018, 1:13 AM

#

thanks you so much for your patience

novel wharf May 13, 2018, 1:13 AM

#

Hehe, no worries.

glass bough May 13, 2018, 1:13 AM

#

I'm sorry for the inconvenience

novel wharf May 13, 2018, 1:13 AM

#

I honestly get stuck with issues like these all the time, and instead of just doing the cheap solution, I wanna know what's causing it.

#

Except it turns into hours of debugging.

glass bough May 13, 2018, 1:13 AM

#

well that payed out

novel wharf May 13, 2018, 1:14 AM

#

👌

glass bough May 13, 2018, 1:14 AM

#

wait, that took over an hour

#

holy shit

novel wharf May 13, 2018, 1:16 AM

#

So I'm doing this course called Algorithmic problem solving, and it's all about a writing code for a small problem and sending it to an online judge.

#

And it takes maybe an hour to write the code, and 4 hours to debug it because the online judge says it's spitting out the wrong result.

#

If that didn't give me enough patience, guh.

glass bough May 13, 2018, 1:19 AM

#

well, that was convenient for me

#

thanks again

orchid charm May 14, 2018, 4:46 PM

#

In SQLite how would i make a leaderboard system

#

Where it grabs every user and then displays the top 10 users in XP

terse fable May 14, 2018, 5:07 PM

#

well, with SQL

#

for how that kinda depends

orchid charm May 14, 2018, 5:08 PM

#

I got something that is sorta like it but it just auto creates a user account

#

async def check_all_members():
    bot.c.execute('SELECT UID from Profile')
    rows = bot.c.fetchall()
    for member in bot.get_all_members():
        if not any(row[0] == member.id for row in rows):
            bot.c.execute('INSERT INTO Profile (UID, XP, LEVEL, SCROLLS) VALUES(?, ?, ?, ?)',(member.id, 0, 0, 0))
            bot.conn.commit()
            print('{} has no Account! Account Created.'.format(member.id))

terse fable May 14, 2018, 5:10 PM

#

I guess you can use keywords like ORDER BY

orchid charm May 14, 2018, 5:11 PM

#

🤔

terse fable May 14, 2018, 5:11 PM

#

https://sqlbolt.com/lesson/filtering_sorting_query_results

SQLBolt - Learn SQL - SQL Lesson 4: Filtering and sorting Query re...

SQLBolt provides a set of interactive lessons and exercises to help you learn SQL

#

something like these

#

so in a way:

SELECT UID, xp (or *) from Profile
ORDER BY xp DESC
LIMIT 10 OFFSET 0;```

#

something like that

#

I'm still rather new on SQL itself so that's the best I can do

orchid charm May 14, 2018, 5:15 PM

#

Or could i make it re organize The DB

terse fable May 14, 2018, 5:15 PM

#

huh?

orchid charm May 14, 2018, 5:15 PM

#

    async def on_message(self, message):
        self.bot.c.execute('UPDATE Profile SET XP=XP+5 where UID = {}'.format(message.author.id,))
        self.bot.conn.commit()
        cursor = self.bot.c.execute('Select XP from Profile where UID = {}'.format(message.author.id,))
        row = cursor.fetchone()
        print('{} has received 5 XP {} / 100(LVL UP)'.format(message.author.id, row))
        if row[0] % 100 == 0:
            self.bot.c.execute('UPDATE Profile SET Level=Level+1 where UID = {}'.format(message.author.id,))
            cursor2 = self.bot.c.execute('Select Level from Profile where UID = {}'.format(message.author.id,))
            row2 = cursor2.fetchone()
            if row2[0] % 10 == 0:
                if not row2[0] >= 280:
                    self.bot.c.execute('Update Profile SET Scrolls=Scrolls+1 where UID = {}'.format(message.author.id,))
            self.bot.conn.commit()

#

So this event is where whenever someone chats they gain 5 XP

#

But what if i could make it move their row up if their XP is higher than another user

terse fable May 14, 2018, 5:17 PM

#

well

#

there's no so called "move row up" in RDBSM (or w/e its acronym is) IIRC

#

you just do the filter by SQL

orchid charm May 14, 2018, 5:18 PM

#

We could check and see what other DB services do

#

Cause they are usually all the same

#

Or similar

terse fable May 14, 2018, 5:18 PM

#

yeah I don't think any of those lets you change a position of a certain data physically

#

you can only achieve that via filtering

orchid charm May 14, 2018, 5:22 PM

#

Ok well i am gonna need to add a UNAME cagetory to my bot

#

So this will probably be a full DB reset

terse fable May 14, 2018, 5:23 PM

#

why not just grab user by user ID?

#

also you don't really need to reset a DB in order to add a new column

#

like there's a reason guild.get_member() exists

orchid charm May 14, 2018, 5:24 PM

#

So i could actually do it without needing a new column

terse fable May 14, 2018, 5:24 PM

#

yes

orchid charm May 14, 2018, 5:25 PM

#

Cause in my bot if a user already has a Account in the DB it won't make another or implement anything new

#

It only puts in the stuff it needs to function

terse fable May 14, 2018, 5:26 PM

#

you can just grab the username the next time a new message go through

#

also it's useless since it's editable

orchid charm May 14, 2018, 5:27 PM

#

What is

terse fable May 14, 2018, 5:27 PM

#

recording usernames in a database

#

an user ID is sufficient

#

oh right speaking of which

#

I'm considering writing a simple DSL to make my bot database-agnostic

#

is there any reason why I shouldn't combine create/update into one?

#

@woeful ivy ?

orchid charm May 14, 2018, 5:30 PM

#

self.bot.c.execute('SELECT XP from Profile ORDER BY xp DESC Limit 10 OFFSET 0')

terse fable May 14, 2018, 5:30 PM

#

something like that ye

orchid charm May 14, 2018, 5:30 PM

#

Would i need it to place into a variable the value?

terse fable May 14, 2018, 5:30 PM

#

or @light summit for the question above

orchid charm May 14, 2018, 5:30 PM

#

Cause it currently only selects

terse fable May 14, 2018, 5:30 PM

#

er

#

I don't quite grasp what do you mean by that

orchid charm May 14, 2018, 5:31 PM

#

        self.bot.c.execute('SELECT Weapon1 from Profile where UID = ?', (ctx.message.author.id,))
        result = self.bot.c.fetchone()
        if(result != None):
            WeaponID1 = result[0]

#

This is injecting the values it is taking into a Variable

light summit May 14, 2018, 5:31 PM

#

¿Qué?

orchid charm May 14, 2018, 5:31 PM

#

?

terse fable May 14, 2018, 5:32 PM

#

about why shouldn't I combine create/update into one for database

orchid charm May 14, 2018, 5:32 PM

#

Also what do you mean by that ngb

terse fable May 14, 2018, 5:32 PM

#

nah that's for thane

light summit May 14, 2018, 5:32 PM

#

Idk. I don't SQL

terse fable May 14, 2018, 5:32 PM

#

no like

#

I mean in general

light summit May 14, 2018, 5:33 PM

#

¯\_(õ.ô)_/¯

terse fable May 14, 2018, 5:33 PM

#

so not much good reasons to not combine create/update?

#

in terms of CRUD

#

@orchid charm also no, not really

#

I should ask DAPI

light summit May 14, 2018, 5:35 PM

#

Man, I'm not super knowledgeable db wise

terse fable May 14, 2018, 5:35 PM

#

rip

light summit May 14, 2018, 5:36 PM

#

I'm wanting to stop using Redis and start using either Rethink or something

terse fable May 14, 2018, 5:36 PM

#

~~do it already~~

light summit May 14, 2018, 5:36 PM

#

Dude, I've got so much shit going on right now

#

No way

#

Look at my sweet and sour

terse fable May 14, 2018, 5:37 PM

#

checks

#

damn that's quite a job

orchid charm May 14, 2018, 5:38 PM

#

If you seek answers ask god

📎 e0edc2a025ef1ad9670cc823946f493c--squid-girl-marie.jpg

light summit May 14, 2018, 5:38 PM

#

#ot0-fear-of-python

orchid charm May 14, 2018, 5:38 PM

#

Also I ~~Spot~~ A IFUNNY watermark

light summit May 14, 2018, 5:39 PM

#

Keep it chill, dude.

orchid charm May 14, 2018, 5:43 PM

#

    @commands.command()
    async def leaderboard():
        self.bot.c.execute('SELECT XP from Profile ORDER BY xp DESC Limit 10 OFFSET 0')
        lbresult = self.bot.c.fetchone()
        print(lbresult)

#

So like that?

terse fable May 14, 2018, 5:43 PM

#

er pretty much I guess

#

gotta run it yourself

orchid charm May 14, 2018, 5:45 PM

#

Ignoring exception in on_message
Traceback (most recent call last):
  File "/Users/shanehawkins/Library/Python/3.6/lib/python/site-packages/discord/ext/commands/core.py", line 371, in _parse_arguments
    next(iterator)
StopIteration

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/shanehawkins/Library/Python/3.6/lib/python/site-packages/discord/client.py", line 224, in _run_event
    yield from coro(*args, **kwargs)
  File "/Users/shanehawkins/Library/Python/3.6/lib/python/site-packages/discord/ext/commands/bot.py", line 920, in on_message
    yield from self.process_commands(message)
  File "/Users/shanehawkins/Library/Python/3.6/lib/python/site-packages/discord/ext/commands/bot.py", line 916, in process_commands
    yield from self.invoke(ctx)
  File "/Users/shanehawkins/Library/Python/3.6/lib/python/site-packages/discord/ext/commands/bot.py", line 886, in invoke
    yield from ctx.command.invoke(ctx)
  File "/Users/shanehawkins/Library/Python/3.6/lib/python/site-packages/discord/ext/commands/core.py", line 507, in invoke
    yield from self.prepare(ctx)
  File "/Users/shanehawkins/Library/Python/3.6/lib/python/site-packages/discord/ext/commands/core.py", line 471, in prepare
    yield from self._parse_arguments(ctx)
  File "/Users/shanehawkins/Library/Python/3.6/lib/python/site-packages/discord/ext/commands/core.py", line 374, in _parse_arguments
    raise discord.ClientException(fmt.format(self))
discord.errors.ClientException: Callback for leaderboard command is missing "self" parameter.

#

@terse fable That is my error now that i use that command

#

Wait

#

I think i know what i forgot

#

I forgot to add self as a arg

#

Hmm

#

Still same error?

terse fable May 14, 2018, 5:56 PM

#

¯_(ツ)_/¯

orchid charm May 14, 2018, 6:03 PM

#

Uhh ok it now prints 995

#

@terse fable

terse fable May 14, 2018, 6:04 PM

#

huh

#

uh

#

told you I don't really know that much about SQL

#

sorry mate

#

¯_(ツ)_/¯

orchid charm May 14, 2018, 6:15 PM

#

Well if anyone knows how to make a Leaderboard in Sqlite let me know

ornate abyss May 14, 2018, 7:41 PM

#

@orchid charm What do you mean by "leaderboard"?

orchid charm May 14, 2018, 7:41 PM

#

It lists the top 10 users with the most value in a specific column

#

Like this
Squint has 100000 XP
otheruser has 1243 XP
ect.

small gyro May 14, 2018, 8:57 PM

#

something like SELECT TOP 10 User, Experience...GROUP BY User ORDER BY Experience DESC?

#

not sqlite though

torn sphinx May 14, 2018, 11:26 PM

#

How can I make it to just print golds?

#

📎 unknown.png

#

Because now it shows this

#

📎 unknown.png

#

@bot.command(pass_context = True)
@owner_only()
async def mygolds(ctx):
    for item in db:
        await bot.say("You have {} golds.".format(item))```

vestal apex May 14, 2018, 11:40 PM

#

this is more of a question about how dictionaries work, I imagine

#

are you familiar with how dictionaries work?

torn sphinx May 14, 2018, 11:42 PM

#

no

vestal apex May 14, 2018, 11:45 PM

#

that would be the place to start, then. https://docs.python.org/3/tutorial/datastructures.html#dictionaries

#

take a look at that and let us know if you've got any questions

torn sphinx May 15, 2018, 12:00 AM

#

@bot.command(pass_context = True)
@owner_only()
async def mygolds(ctx):
    for id, golds in db:
        await bot.say("You have {} {} golds.".format(id, golds))```

#

The bot replies with You have id golds golds. lol

#

📎 unknown.png

#

How can I get the vlaue

#

and not the name

gusty spindle May 15, 2018, 12:33 AM

#

what is db @torn sphinx ?

glass bough May 15, 2018, 12:51 AM

#

How can I make a reference to all the columns in a Query, like SELECT * FROM table Where column = {}

#

nvm, I feel idiotic

gusty spindle May 15, 2018, 12:54 AM

#

what even were you asking haha

glass bough May 15, 2018, 12:54 AM

#

idk anymore, I did select *, then I realized, wait, I don't need to select all

gusty spindle May 15, 2018, 12:55 AM

#

yeah that confused me :D

glass bough May 15, 2018, 12:55 AM

#

just SELECT {} FROM table

#

I can't even

gusty spindle May 15, 2018, 1:02 AM

#

^^

torn sphinx May 15, 2018, 4:47 AM

#

@gusty spindle Database

gusty spindle May 15, 2018, 7:32 AM

#

@torn sphinx well.. yeah, obviously, but what was your variable?

torn sphinx May 15, 2018, 11:33 AM

#

@gusty spindle db = TinyDB('data.json')

torn sphinx May 15, 2018, 12:28 PM

#

📎 unknown.png

#

@bot.command(pass_context = True)
@owner_only()
async def mygolds(ctx):
    for asd in db:
        player = asd["id"]
        golds = asd["golds"]
        await bot.say("Member id: {}".format(player))
        await bot.say("Golds: {}".format(golds))```

#

📎 unknown.png

#

How can I make it show only my golds that match my id? and not all peoples golds

gusty spindle May 15, 2018, 2:53 PM

#

@torn sphinx you're iterating over every row in the DB right? you need to get the specific one you're looking for

crisp robin May 15, 2018, 6:01 PM

#

hey.. so with my bot, i have an xp system.. it's a code that my friend gave me.... it woks perfectly but what i want is to store the data on my database.. but i dont know much about database's. (I am using CouchDB) so far, the bot creates a document but i want the bot to see whether the user is on the database with the id and then if the user is on it, just update their xp , but if the user is not on the database, make a document with their id and their xp. then i want to grab the data back and when user does ~rank , i want the bot to send their xp thats on their document.
that was sooo hard to explain
how can i get the bot to check if the document is already there, then decide what to do

hexed epoch May 15, 2018, 6:11 PM

#

when you say document you mean row?

#

or entry?

broken linden May 15, 2018, 6:20 PM

#

no he is using couch db which uses documents instead of tables @hexed epoch

hexed epoch May 15, 2018, 6:21 PM

#

why does every user have a different table 🤔

broken linden May 15, 2018, 6:31 PM

#

you have to think about it like json

hexed epoch May 15, 2018, 6:31 PM

#

oh that makes sense

ornate abyss May 15, 2018, 10:39 PM

#

@crisp robin if you search for that particular user and nothing is returned you know they arent in the database, which in that case you can insert that user into the DB

crisp robin May 16, 2018, 2:56 PM

#

Is there any other dB I could use cus I'm struggling with couchdb.. and is tables easier than documents..

torn sphinx May 16, 2018, 3:33 PM

#

http://docs.python-guide.org/en/latest/scenarios/db/

orchid charm May 16, 2018, 3:34 PM

#

@crisp robin do you use Sqlite?

#

Cause i coded a XP system that works perfectly fine in my bot

#

    async def on_message(self, message):
        self.bot.c.execute('UPDATE Profile SET XP=XP+5 where UID = {}'.format(message.author.id,))
        self.bot.conn.commit()
        cursor = self.bot.c.execute('Select XP from Profile where UID = {}'.format(message.author.id,))
        row = cursor.fetchone()
        levelcursor = self.bot.c.execute('Select Level from Profile where UID = {}'.format(message.author.id))
        levelrow = cursor.fetchone()
        print('{} ({}) is level {} and is {} / 100 away from Leveling up'.format(message.author.id, message.author.name, levelrow, row))
        if row[0] % 100 == 0:
            self.bot.c.execute('UPDATE Profile SET Level=Level+1 where UID = {}'.format(message.author.id,))
            self.bot.c.execute('UPDATE Profile SET XP=0 where UID = {}'.format(message.author.id,))
            cursor2 = self.bot.c.execute('Select Level from Profile where UID = {}'.format(message.author.id,))
            row2 = cursor2.fetchone()
            if row2[0] % 10 == 0:
                if not row2[0] >= 280:
                    self.bot.c.execute('Update Profile SET Scrolls=Scrolls+1 where UID = {}'.format(message.author.id,))
            self.bot.conn.commit()

Just remove a small bit of this and it would work for what you wanna do

wraith terrace May 16, 2018, 3:36 PM

#

Hello, am i in the right chanel for infos how to generate textfiles ?

orchid charm May 16, 2018, 3:36 PM

#

I don't think so

#

This is for Databases

#

That store, read, and write info

wraith terrace May 16, 2018, 3:36 PM

#

Do you know where i can go ? Datascience ?

orchid charm May 16, 2018, 3:37 PM

#

I don't even know why you would want to gen text files

#

For a startup script?

wraith terrace May 16, 2018, 3:39 PM

#

i just want to creat textfiles when reading an array, i managed to creat the new files, and right into it

#

but im stuck, becaus i want th

#

*but im stuck because i want lines instead of columns

#

Data = open("data_test.txt", "r")
lines = Data.readlines()

i=0

#file = open("Imput.txt", "w")

for element in lines:
filename='data_%d.txt'%(i,)
file = open(filename, "w")

X0=i
X1=i+1
X16=i+16
X24=i+24
X28=i+28
X30=i+30
X31=i+31
X32=i+32

Imput = [lines[X0],lines[X1],lines[X16],lines[X24],lines[X28],lines[X30],lines[X31],lines[X32]]

#print (i)
print (Imput[0])
file.writelines(Imput)
i=i+1
file.close()

orchid charm May 16, 2018, 3:48 PM

#

The python help channels is what you should be asking this in then

wraith terrace May 16, 2018, 3:50 PM

#

ok, thanks ill leave you then

#

o7

crisp robin May 16, 2018, 6:12 PM

#

hey

feral glacier May 17, 2018, 5:30 AM

#

can someone help me to understand how mongodb atlas works?

it says Free MongoDB Cluster and also pay only for what you use, that means "is free under this limits"?

#

yes, is "free under this limits":

The free tier grants you 512 MB of storage for learning MongoDB, prototyping your application, and early development.

crisp robin May 17, 2018, 4:41 PM

#

hey.. which is the best and easiest database to use for my xp system for discord bot

ionic pecan May 17, 2018, 4:50 PM

#

easiest? sqlite

#

i migrated mine from sqlite to postgresql though

#

there's this which works with postgresql, and it's really nice https://github.com/05bit/peewee-async

GitHub

05bit/peewee-async

peewee-async - Asynchronous interface for peewee ORM powered by asyncio

#

i use it in my bot

crisp robin May 17, 2018, 4:57 PM

#

which is easier..

silver helm May 17, 2018, 4:58 PM

#

I need to know how I could access the file that holds the blocks, or read this file!

I've already seen something with Node + LevelWeb and also what else I'm trying to do would be with Python + plyvel

Who can help me???

Remembering that it is not by RPC I want to access the file that is written on the machine I think it would be the .ldb file

Thank you very much for helping me!

broken linden May 17, 2018, 4:59 PM

#

again

#

which blocks

silver helm May 17, 2018, 5:01 PM

#

Sorry, I thought you could post here!

packs a cryptomoeda wallet!

ionic pecan May 17, 2018, 5:01 PM

#

@crisp robin sqlite is easier to set up because you can just give it some filepath and it works, there's no sort of database creation or authentication involved

silver helm May 17, 2018, 5:01 PM

#

Blocks*

broken linden May 17, 2018, 5:02 PM

#

how is that related to databases?
gimme a sec

#

bot.tags["ask"]

delicate fieldBOT May 17, 2018, 5:02 PM

#

ask

Asking good questions will yield a much higher chance of a quick response:

• Don't ask to ask your question, just go ahead and tell us your problem.
• Try to solve the problem on your own first, we're not going to write code for you.
• Show us the code you've tried and any errors or unexpected results it's giving
• Keep your patience while we're helping you.

You can find a much more detailed explanation on our website.

silver helm May 17, 2018, 5:05 PM

#

It is related in some way with database, although it is not relational database, it is a way of storing data stepped

broken linden May 17, 2018, 5:06 PM

#

• Try to solve the problem on your own first, we're not going to write code for you.

• Show us the code you've tried and any errors or unexpected results it's giving

??

silver helm May 17, 2018, 5:30 PM

#

I did not ask for code
Help is not for code
Maybe i have not been clear enough, but what i would like guidance , i see a lot the internet , but i do not find anything good enough

My code:
import plyvel

db = plyvel.DB("/root/Downloads/DashCoin/000760.ldb", create_if_missing=False,)
db.put("key", "value")

for key, value in db:
print(key)
print(value)

Line error:
Traceback (most recent call last):
File "/root/PycharmProjects/DashCoin/AcessDB.py", line 7, in <module>
db = plyvel.DB("/root/Downloads/DashCoin/000760.ldb", create_if_missing=False,)
File "plyvel/_plyvel.pyx", line 247, in plyvel._plyvel.DB.init
File "plyvel/_plyvel.pyx", line 88, in plyvel._plyvel.raise_for_status
plyvel._plyvel.IOError: IO error: /root/Downloads/DashCoin/000760.ldb/LOCK: Not a directory

Process finished with exit code 1

real thistle May 17, 2018, 5:55 PM

#

how do you interact two tables in a database

#

like for example. I have budget in Plan table and I have cost in Meal table

#

how do I deduct the budget and cost

#

I.. am sorta draining off a bit xD

ornate abyss May 17, 2018, 7:00 PM

#

@real thistle do you have something that links the budget and the cost

real thistle May 17, 2018, 7:25 PM

#

I put MealID at the Plan table does it help @ornate abyss

ornate abyss May 17, 2018, 7:32 PM

#

@real thistle Yea, so you can do something like

SELECT Plan.mealID, (Plan.budget - Meal.cost) FROM Plan, Meal WHERE Plan.mealID = Meal.id

#

oops

real thistle May 17, 2018, 7:34 PM

#

@ornate abyss by storing it to a variable would it be a permanent so that a plan entry could show how much budget would be lost from that meal?

ornate abyss May 17, 2018, 7:34 PM

#

Im not sure what you mean

real thistle May 17, 2018, 7:34 PM

#

wait

ornate abyss May 17, 2018, 7:35 PM

#

Alright

real thistle May 17, 2018, 7:37 PM

#

my code runs like this
https://pastebin.com/2s9wgEQM

basically it's a meal planner that each food you decide to take on a week, your budget will be deducted from the meal cost.

Pastebin

import tkinter as tk from tkinter import * from tkinter import m...

#

should I make a new variable to store the changes in a plan entry so that it would appear how much the budget has been decreased from all the food in your weekly plan

ornate abyss May 17, 2018, 7:39 PM

#

Well im assuming each time they buy something its just added to the Meal table no?

#

So when they buy something, and its linked to a certain Plan itll just minus it already

real thistle May 17, 2018, 7:41 PM

#

@ornate abyss ah so it will be automatic? I need another function to attach a Plan to meals, right? I assume you looked into the code

ornate abyss May 17, 2018, 7:41 PM

#

i just skimmed over, nothing serious

#

I need another function to attach a Plan to meals why? they are linked already by the foreign key

#

ah so it will be automatic as long as they are in the database and linked to a Plan they will be subtracted from the budget, yes

real thistle May 17, 2018, 7:45 PM

#

@ornate abyss I mean, in the GUI, there is still no real connection to the meal itself. I think I need to add a drop-down menu or something that can inidcate which meals the plan will take

#

oh, that's it, a checkbox

#

wait, nvm

#

IDK if a checkbox would be proactive, on additions in the database...

orchid charm May 17, 2018, 7:52 PM

#

if levelrow < 5:

        levelcursor = self.bot.c.execute('Select Level from Profile where UID = {}'.format(ctx.message.author.id))
        levelrow = levelcursor.fetchone()

#

What am i doing wrong?

#

my "Level" is 58

#

Do i need to convert to a int or somethin

#

Its saying it ain't supported between tuple and int

ionic pecan May 17, 2018, 7:53 PM

#

fetchone() fetches a row, iirc

#

and a row is represented as a tuple

#

print levelrow to see what it is

orchid charm May 17, 2018, 7:54 PM

#

(58, )

#

That is what it shows me in my XP adder which is where i got this code from into this command

#

What would i do to fix this?

ionic pecan May 17, 2018, 7:56 PM

#

you get the int from the tuple

#

with index access

#

just like on a list

orchid charm May 17, 2018, 7:57 PM

#

so levelrow = int(levelrow[0]) ?

ionic pecan May 17, 2018, 7:57 PM

#

try it and see

orchid charm May 17, 2018, 7:58 PM

#

I think it is doing what it is supposed to

ionic pecan May 17, 2018, 7:59 PM

#

you probably don't need the int

orchid charm May 17, 2018, 7:59 PM

#

Cause it is doing nothing

ionic pecan May 17, 2018, 7:59 PM

#

given that Level is stored as an int

orchid charm May 17, 2018, 7:59 PM

#

Which is what it is supposed to for now

#

Cause my level is above 5

#

I will probably make a event for the hub now

#

Cause this "Hub" is gonna be infinately backable and forwardable

real thistle May 17, 2018, 11:25 PM

#

How do I create a dropdown menu from a set of data in one of my tables?

glass bough May 17, 2018, 11:50 PM

#

Do you mean accessing the table, or the dropdown part is stressing you

fluid root May 18, 2018, 12:24 AM

#

Anyone ever going along with mongoDB ?

real thistle May 18, 2018, 12:53 AM

#

@glass bough accessing the table

#

i've searched how dropdown works

glass bough May 18, 2018, 12:54 AM

#

I assume you would just query from whichever, or do you want an input?

real thistle May 18, 2018, 12:55 AM

#

what I want to do is to be able to link it up Since, I got the data part as the foreign key for another table. What I want is to add it to a Plan

#

wait

#

https://pastebin.com/2s9wgEQM

this is my code for now

Pastebin

import tkinter as tk from tkinter import * from tkinter import m...

#

I am planning to add another function, where i am able to add Meals to a Plan. So I thought a dropdown menu to add meals on a plan would be a great way to attach meals on it. The mealID is already linked to Plan table as a foreign key. I only need to add which specific meal in every plan

glass bough May 18, 2018, 1:03 AM

#

I'm not very experienced with this stuff, I hope someone else can help you

real thistle May 18, 2018, 1:04 AM

#

ahhh okay thanks anyway 😄

subtle kelp May 18, 2018, 1:18 AM

#

I usually use PyQt. Sorry man

real thistle May 18, 2018, 2:46 AM

#

how about this one

#

def mealplan():
        if (bdg.get() >= 0):
            cur.execute ('insert into Plan(mealID) values(?) where planID=(?)', (choption.get(), choptionu.get()))
            con.commit()
            budget = cur.execute('SELECT Plan.mealID, (Plan.budget - Meal.cost) FROM Plan, Meal WHERE Plan.mealID = Meal.id')
        else:
            messagebox.showinfo("You've stretched your budget", "Sorry! But your budget is not sufficient for you to add another food")
    filewin=Toplevel(root, bg="#39FF14")
    lblPlanID=tk.Label(filewin,text="Plan ID  ",bg="#39FF14", font="Arial 12 bold")
    lblPlanID.grid(row=1,column=1)
    optionu=[]
    cur.execute('SELECT * from Plan')
    con.commit()
    result=cur.fetchall()
    for row in result:
        optionu.append(row[0])
    optionub=ttk.Combobox(filewin, width=7, values = optionu)
    optionub.grid(row = 1, column = 2)
    option=[]
    lblMealID=tk.Label(filewin,text="Meal ID  ",bg="#39FF14", font="Arial 12 bold")
    lblMealID.grid(row=2,column=1)
    cur.execute('SELECT * from Meal')
    con.commit()
    result=cur.fetchall()
    for row in result:
        option.append(row[0])
    optionub=ttk.Combobox(filewin, width=7, values = option)
    optionub.grid(row = 2, column = 2)
    choptionu=StringVar()
    choptionu.set(optionu[0])
    choption=StringVar()
    choption.set(option[0])
    btnOK=tk.Button(filewin,text='OK',command=mealplan,bg="#FFFFFF",bd=3,height = 0, width = 8,font="Arial 10 bold")
    btnOK.grid(row=3,column=1)

#

getting near where error

#

anyone cthat can help me out here...

#

nvm

#

tried something out

crisp robin May 18, 2018, 7:33 PM

#

i need hellp
please
https://hastebin.com/gabicawova.py
i want to implement SQlite into there.. i tried many ways but it just isnt working out as well as i think it would.. i dont have much knowledge about Db's btw..

#

please mention me

orchid charm May 18, 2018, 9:10 PM

#

Read the Sqlite docs m8

ionic pecan May 18, 2018, 10:14 PM

#

you're really not someone who should tell other people to read docs

#

@crisp robin what have you tried already & what did you run into issues with?

rocky relic May 19, 2018, 12:27 PM

#

Hi. Most languages use strings to create SQL and interact with databases. I wish to create a DSL dialect to solve this issue. I'm stack on views. I'm not sure how to deal with 1:M situation. So I'm not sure my design will work and I need ideas. Any one wish to contribute?

primal olive May 20, 2018, 9:05 AM

#

Yo

#

Can someone give me a hand

vestal apex May 20, 2018, 9:06 AM

#

bot.tags['ask']

delicate fieldBOT May 20, 2018, 9:06 AM

#

ask

Asking good questions will yield a much higher chance of a quick response:

• Don't ask to ask your question, just go ahead and tell us your problem.
• Try to solve the problem on your own first, we're not going to write code for you.
• Show us the code you've tried and any errors or unexpected results it's giving
• Keep your patience while we're helping you.

You can find a much more detailed explanation on our website.

viral crag May 20, 2018, 9:07 AM

#

https://giphy.com/gifs/tom-hardy-the-drop-movie-KDfxIfV3DBIru

Giphy

primal olive May 20, 2018, 9:07 AM

#

Ok ok

#

So

#

There is a table called LDL_Cholesterol

#

And three categories within it (Classification, Min, Max)

#

I have no prior experience with SQL, would I do
SELECT LDL_Cholesterol
or
SELECT Classification from LDL_Cholesterol

I need to find two things under the Classification category.

ornate abyss May 20, 2018, 1:29 PM

#

The second will select Classification from every row

#

The first isn't a complete query

heavy warren May 20, 2018, 3:30 PM

#

when I work with DB containing simple strings, it works thousands times slower than numeric Ids Is there any way to automatically replace the equal words by equal numbers?

ornate abyss May 20, 2018, 5:44 PM

#

im unsure what you're asking..

silver pawn May 20, 2018, 5:50 PM

#

how can I make the json

#

on next line

#

I tried \n at end

#

and it didnt work

#

I just have one massive line of json

ionic pecan May 20, 2018, 5:56 PM

#

the JSON outputted by json.dump?

dull scarab May 20, 2018, 5:56 PM

#

Just as a side note based on your code; you're not allowed to store unencrypted data about discord users other than ID. Afaik

ornate abyss May 20, 2018, 5:58 PM

#

why was that asked in here :(

glass bough May 22, 2018, 12:50 AM

#

So I'm using ORDER BY column

#

but it isn't ordering it as I would like, I have the values 5.3, 5.10, 5.15, and 5.17 stored in this column

#

wait, I might be an idiot

#

every time I ask a question I swear

fair laurel May 22, 2018, 1:15 AM

#

I think that's called rubber duck troubleshooting

#

You explain the problem to the rubber duck, and by the time you're done, you've figured out the answer

vestal apex May 22, 2018, 2:41 AM

#

^ rubber duck debugging is our favorite

rancid bronze May 23, 2018, 3:45 AM

#

hey yall - do you know of any free db hosting?

wispy fable May 23, 2018, 3:45 AM

#

I know some pretty cheap VPS hosting?

rancid bronze May 23, 2018, 3:46 AM

#

free > cheap

#

idk

wispy fable May 23, 2018, 3:46 AM

#

by cheap I mean cents per month

rancid bronze May 23, 2018, 3:46 AM

#

eventually itll go on a vps

#

o dang

#

hmm i'll probably use heroku/cleardb until performance becomes an issue

#

then migrate ig

wispy fable May 23, 2018, 3:47 AM

#

check out google's vps hosting as well

#

They're pretty nice

rancid bronze May 23, 2018, 3:47 AM

#

link?

wispy fable May 23, 2018, 3:47 AM

#

and they have a super cool app that's fully functional (it even let's you use your vps via ssl)

#

just look up google cloud compute

#

https://cloud.google.com

Google Cloud

Google Cloud Computing, Hosting Services & APIs | Google Cloud

Google Cloud provides flexible infrastructure, end-to-security, modern productivity, and intelligent insights engineered to help your business thrive.

rancid bronze May 23, 2018, 3:49 AM

#

nice thanks

#

ill take a look

hexed epoch May 23, 2018, 4:46 AM

#

@wispy fable someone said a few cents a month? except if its not amazon where you have to use cc

wispy fable May 23, 2018, 5:20 AM

#

any debit card with visa works too

upbeat quartz May 24, 2018, 10:50 PM

#

Friend is looking for a good MySQL package, any recommendations from the experienced among you?

tawny sail May 25, 2018, 6:18 AM

#

MySQL community version is good, I use it with the workbench which makes it even easier

torn sphinx May 25, 2018, 9:17 AM

#

https://github.com/edgedb/edgedb I'm hyped

GitHub

edgedb/edgedb

edgedb - The next generation object-relational database. A @magicstack project.

ionic pecan May 25, 2018, 10:21 AM

#

looks interesting

#

WITH
  Alice := (SELECT User FILTER .login = "alice"),
  Bob := (SELECT User FILTER .login = "bob")

wouldn't the filter operation return multiple objects though..?

#

built-in migration support is definitely interesting imo but aside from that i dont see how a python database would be performant

torn sphinx May 25, 2018, 11:18 AM

#

@ionic pecan This is how: https://magic.io/blog/asyncpg-1m-rows-from-postgres-to-python

ionic pecan May 25, 2018, 11:18 AM

#

ohhh its async

#

oh I just read the A @magicstack project

#

that's pretty cool

dull scarab May 25, 2018, 11:41 AM

#

Oh, would it be on the same level as postgres for discord bots then?

ionic pecan May 25, 2018, 11:48 AM

#

what

dull scarab May 25, 2018, 11:52 AM

#

It*

#

But yeah, would it work well with d.py since its async

ionic pecan May 25, 2018, 12:07 PM

#

Oh, I was confused by same level

#

That depends on the client implementation

#

There is already an async postgres client impl though https://github.com/MagicStack/asyncpg

GitHub

MagicStack/asyncpg

asyncpg - A fast PostgreSQL Database Client Library for Python/asyncio.

#

or https://github.com/05bit/peewee-async if you're into ORMs

GitHub

05bit/peewee-async

peewee-async - Asynchronous interface for peewee ORM powered by asyncio

#

I use the latter

dull scarab May 25, 2018, 12:08 PM

#

Yeah, I'm using asyncpg

dull scarab May 26, 2018, 12:31 AM

#

Is there a injection for update if possible else insert ?

#

query = "SELECT * FROM members WHERE id = $1 AND guildid = $2;"
if await self.bot.db.fetchrow(query, member.id, ctx.guild.id):
    update = """UPDATE members SET 
        ap = $1, 
        aap = $2, 
        dp = $3, 
        level = $4, 
        class = $5, 
        gearpic = $6
        WHERE id = $7;"""
else:
    insert = """INSERT INTO members(
        id, 
        guild_id, 
        ap, 
        aap, 
        dp, 
        level, 
        class, 
        gearpic) VALUES ($1, $2, $3, $4, $5, $6, $7, $8);"""```I'm currently doing something like this, but feel like it could be done with one execution?

ionic pecan May 26, 2018, 8:30 AM

#

Some ORMs have an UPSERT, which is basically "insert or update". IIRC you're using PostgreSQL, so this post may provide some insight: https://stackoverflow.com/questions/17267417/how-to-upsert-merge-insert-on-duplicate-update-in-postgresql

Stack Overflow

How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

A very frequently asked question here is how to do an upsert, which is what MySQL calls INSERT ... ON DUPLICATE UPDATE and the standard supports as part of the MERGE operation.

Given that PostgreSQL

dull scarab May 26, 2018, 10:23 AM

#

okhandbutflipped

fierce summit May 27, 2018, 1:34 PM

#

Hello all. I need some suggestions on an error I'm getting. Thank you very much for any help you input you have.

Error: mysql.connector.errors.ProgrammingErrors: Not all parameters were used in the SQL statement.

        if cursor.rowcount == 0:
            add_sql = ("INSERT INTO stormdata "
                       "(StormName, StormID, AdvisoryNumber, Basin, AdvDateTime, Cat, Eye,"
                       " MoveDir, MoveSpeed, SLP, FA, PA, CD, WW, UserModified, AgencyID, CatNum) "
                       "VALUES (%s, %s, %s, %s, %s, %s, %d, %d, %d, %f, %s, %s, %s, %s, %d, %s, %d)")

            add_data = (
            self.name, self.storm_id, self.advisory_number, self.basin, self.sqldatetime, self.cat, self.eye,
            self.dir, self.speed, self.pressure, '', '', '', '', 0, 'NHC', self.catnum)

            # Execute the Query
            cursor.execute(add_sql, add_data)
            last_record_id = cursor.lastrowid
            print(last_record_id)

#

It looks like I did the markdown wrong for discord code highlighting. 😦

#

ok, fixed the markdown... used apostrophe instead of back-tick 😃

torn sphinx May 27, 2018, 2:18 PM

#

try making all of the values %s

fierce summit May 27, 2018, 2:24 PM

#

That did the trick! Thanks for your help. 😃

gusty spindle May 28, 2018, 5:47 PM

#

is there any proper way of getting the row from an sqlite cursor right after i've inserted it? i'm doing this:py cur.execute("INSERT INTO messages(name, message, created) VALUES (?, ?, ?)", data)and i need to get the resulting row's assigned primary key value. right now i'm re-searching the database with the data as conditions to get the primary key, but that seems impractical.

vestal apex May 28, 2018, 5:49 PM

#

apparently there's an optional extension you can use https://stackoverflow.com/questions/6242756/how-to-retrieve-inserted-id-after-inserting-row-in-sqlite-using-python

Stack Overflow

How to retrieve inserted id after inserting row in SQLite using Py...

How to retrieve inserted id after inserting row in SQLite using Python? I have table like this:

id INT AUTOINCREMENT PRIMARY KEY,
username VARCHAR(50),
password VARCHAR(50)
I insert a new row with

gusty spindle May 28, 2018, 5:50 PM

#

oh haha goodness knows how i didn't find that after searching for 10 minutes. thanks ^^

vestal apex May 28, 2018, 5:52 PM

#

o7

rain karma May 28, 2018, 9:44 PM

#

How would you for a feature like "Choose one/two/three of the following" in a database?

ionic pecan May 28, 2018, 10:25 PM

#

for column types?

#

i think some databases have an ENUM type, for others, you can use text and then add a constraint to check IN ('value', 'two', 'three');

rain karma May 28, 2018, 10:49 PM

#

Not exactly. For example: I have a list of classes to take to graduate.

📎 unknown.png

#

Here, the course guide says to take 4 DIGI classes, then choose 3 from the list below

#

How do I have add "rules" to the database showing that I need 3 courses

vestal apex May 29, 2018, 4:49 AM

#

@rain karma that doesn't really sound like something you can enforce at the DB level

rain karma May 29, 2018, 12:18 PM

#

If it were you how would you enforce it then? 🤔

#

Because I have to keep track of it somehow

rain karma May 29, 2018, 7:59 PM

#

@vestal apex

vestal apex May 29, 2018, 8:00 PM

#

at the application level I'd validate the user's input

#

just the same as you do with, say, usernames and passwords

#

"hey, you can't use that password it's two characters long"

#

"hey, that username is already taken, you can't use that either!"

#

"you need to choose 3 classes from the list below and you didn't! do that now please!"

rain karma May 29, 2018, 8:03 PM

#

So even with an extensive list of rules like that one, you would have them all stored in the application?

vestal apex May 29, 2018, 8:06 PM

#

I mean it hardly seems extensive, but... if I were writing a system like a class picking tool, I'd probably decide on a format I can use to write those rules down, and then have my application read rule configurations

#

another way to do it at the application level might be to interact with the DB using abstract models, although that seems a little rigid for this purpose

#

but at the DB level I am not aware of nor would I recommend a way to enforce those types of rules you listed

fair laurel May 29, 2018, 8:08 PM

#

Yep. The DB should just enforce things like "you have too many characters in this field," "this field has to match a key in this other table," etc.

#

You should implement application logic in the application itself

rain karma May 29, 2018, 8:09 PM

#

So instead of storing the the rule configurations in a database of rows and collumns, you would have them in a type of file that you can parse through?

fair laurel May 29, 2018, 8:10 PM

#

Doesn't really matter where you store the configurations. If you have a database already, you could use that too.

#

Actually, it's probably better there, if the rules are directly related to the data

vestal apex May 29, 2018, 8:11 PM

#

^ I would do it with files because it's easy and I could easily tinker with and adapt them for other purposes, but the medium isn't significant to implementing the general concept

rain karma May 29, 2018, 8:13 PM

#

The file type is something that I haven't thought of. I was looking at putting this into an .csv to have it shown like a database, but honestly it's getting really hairy with trying to keep track of all the rules for each set of classes. Ex:

📎 unknown.png

#

Some of them have more than one "Choose X courses"

#

So, I couldn't just do a bitfield, I'd have to have a grouping column to differentiate the groups

fair laurel May 29, 2018, 8:16 PM

#

That's going to be a little bit complicated however you set it up, but there should be a reasonable way to implement it.

I'd probably have a Class table (with details for each class) and a Group table (with details about how many courses are required from the group). Then I'd set up a many-to-many relationship between the two.

#

Do a little reading on what a many-to-many relationship looks like in SQL, if you aren't familiar with it already. It's not an immediately obvious concept.

#

But it's very powerful once you understand it.

rain karma May 29, 2018, 8:17 PM

#

Well, I know that for M-N you split it up into two identifying 1-M relationships

novel wharf May 30, 2018, 11:55 AM

#

{
    'board': [[0,0,0], [0,0,0], [0,0,0]],
    'turn': 0,
}

#

@light summit If this is my dict, how would you store it in redis?

light summit May 30, 2018, 12:00 PM

#

Did you read on how namespaces work?

novel wharf May 30, 2018, 12:03 PM

#

Not finding anything about namespaces in the docs.

#

I'm finding hmset("RedisKey", dictionaryToSet), but you mentioned something about not supporting multiple layers?

light summit May 30, 2018, 12:08 PM

#

I'm with someone now. Give me a moment

#

Separate words in your key name using a ;. That creates browsable namespaces. You can use matching searches on them or just use that to organize your keys. On the surface it may look like sublevels, but it's really just a naming convention.

#

If you play around with Redis Desktop Manager, I promise you'll understand very quickly

#

@novel wharf

novel wharf May 30, 2018, 12:16 PM

#

Ooh, interesting.

#

I only today started adding "namespaces" to the keys as I needed to separate the data. Separating it into table.id and game.id for instance.

light summit May 30, 2018, 12:18 PM

#

So, say you have a namespace for games. Then, under games, you can have namespaces that are the IDs for each game. Under that, you can have keys for tables, rows, turn, etc. however you want to store the data

#

games;1234567;table # list of string dumped rows or separate keys for row lists. Ya know, whatever totes your goat
games;1234567;turn # string
games;1234567;players # set

#

It may seem cumbersome at first, but with f string or what have you, it's easy to abstract out the name ugliness and keep everything well organized programmatically. Bonus point for making it a breeze to glance over with a browser

novel wharf May 30, 2018, 12:24 PM

#

Not managing to install the redis browser on my linux.

light summit May 30, 2018, 12:25 PM

#

I did it on Windows fairly straight forward, so I can't provide much help there ¯_(ツ)_/¯

#

There may be another better browser for Linux. I haven't done much looking

novel wharf May 30, 2018, 12:26 PM

#

Anyway, not sure qute what you mean with the long strings of data. Is that the value I want to store?

#

Or is that how it's actually stored in redis?

light summit May 30, 2018, 12:26 PM

#

What time zone are you in?

novel wharf May 30, 2018, 12:27 PM

#

Uuh, stockholm, +1 GMT?

light summit May 30, 2018, 12:28 PM

#

Shit. I get off work at 15:00 EDT. May be just enough time I can go over it in voice when I get home and explain it much better with screen share to help you visualize it

novel wharf May 30, 2018, 12:29 PM

#

That'd be awesome~

#

That's just 6 hours behind me, so 21 ish.

light summit May 30, 2018, 12:30 PM

#

Consider the 45 minute drive home

novel wharf May 30, 2018, 12:30 PM

#

Anyhow, as for now I assume that's how I should store my keys in redis? Separating with ; like in your examples.

light summit May 30, 2018, 12:30 PM

#

Imagine those ; as separating directory names

#

So that all key names are a hypothetical full path from root

#

If you give them all plain Jane names, you storing all "files" in root dir

novel wharf May 30, 2018, 12:31 PM

#

How is this necessary though? In case I somehow want all of the data connected to games for instance?

light summit May 30, 2018, 12:32 PM

#

You putting anything else in redis besides your game instances?

novel wharf May 30, 2018, 12:33 PM

#

I'm storing packed gameinfo dict, players dict, status string.

#

Actually, in the game lobby I'd like to collect the players dict from all tables, so a namespace fetch would be amazing.

light summit May 30, 2018, 12:40 PM

#

You may want to look at scaniter.

#

It creates an iterable of keys that match an expression.

novel wharf May 30, 2018, 2:44 PM

#

@light summit I can't seem to be able to set a dict as value in redis.

#

set and hset requires bytearray, bytes, float, int, or str type. hmset makes even less sense.

#

Feels like I should just use hset('table:id', key, json.dumps(dict))

#

There's also no such thing as scan_iter. Perhaps I'm using a different version. Although it's just calling self.scan and yields.

#

But alright, I guess if I use hset, using tableid and key, I can do a hscan of tableid and get all of its data.

torn sphinx May 30, 2018, 7:04 PM

#

Hello guys I have a problem. I am trying to set up a leveling up system. If I do .level @Blload#6680 its tells me what rank I am now and how many exp I have. Once I rank up and then I do again .level @Blload#6680 it doesnt respond. I do that for like paste that like 3 - 4 times then it works. It shows my new rank and exp but why it works only after 3 tries?

#

it has something to do with the exp set I thinki I dont know. I also have made the bot to Congratulate the user in the rank up and also made a check so if the user is already that rank it wont congratulate him again. and this is where the problem is I think. Because I put return after the check so it doesn continues to the next if statement

#

📎 unknown.png

dull scarab May 30, 2018, 7:08 PM

#

You sure you want to check for exact exp, and not a range between 5 < x < 20

torn sphinx May 30, 2018, 7:10 PM

#

yeah I want that

#

I dont know how lol

novel wharf May 30, 2018, 7:14 PM

#

A nice way to debug your code is to add a lot of prints. Find out where your code runs and what steps it's skipping.

#

If you're doing very specific if-statements, it feels like you might have a case that gets ignored or returned too early, and you wouldn't get a reply.

light summit May 30, 2018, 7:30 PM

#

I used to add a shit ton of prints (and still some times do), but PyCharm's debugger is actually really nice. Throw in some breakpoints and run. It'll stop at each, show you all the objects in mem, and allow you to step through your code.

torn sphinx May 30, 2018, 7:34 PM

#

Okey I will try that thanks

rancid bronze May 31, 2018, 12:51 AM

#

can anyone help with aws database deployment?

#

please?

#

so i set up a RDS and i know something is wrong with the security group. when i telnet the url at 3306, i get a Could not open connection to the host, on port 3306: Connect failed Right now the security group rules should be allowing for connections, the only inbound rules are these: https://i.imgur.com/90ClqpG.png i'm super frustrated idk

Imgur

#

also i know i can use 3306 on my local since i can connect to other dbs

rancid bronze May 31, 2018, 2:27 AM

#

lol nvm

#

i just replaced that one

stoic pendant May 31, 2018, 10:16 AM

#

hey folks - i was just talking with @terse stump and they suggested i come here to ask for advice about what database i should run for my project - i can tell you a bit about it here. so far i think i need a time series db like Grafana or InfluxDB.

i am gathering SEO performance metrics like domain authority and keyword rankings for a number of websites and their competitors.

i want to store them in such a way that I can then graph them and present them to our clients, and they can view these metrics over time so: last week, last 3 months, last year etc.

i'm confident that i can scrape the metrics that i'm after using python but i'm a bit clueless when it comes to importing them to a database and could do with some with advice on how to get started with this. someone suggest SQL to me in the past, but that wouldn't apply to a time series DB would it?

viral crag May 31, 2018, 10:17 AM

#

Time series databases are a very specific thing

#

rdb for example

#

What they do is aggregate data based on granularity

#

The further back you go, the less granularity it keeps

#

So you can have the last week, then averages for the last three weeks, the last month, the last six months, the last year, etc

#

Very efficient

#

I believe it's known as time bucketing

stoic pendant May 31, 2018, 10:20 AM

#

@viral crag
I'm looking for something that will display the true figures from each month, so let's say April 2018, the Domain Authority for my client's website was 50.
March was 49, February was 49, January was 47, etc. I'd like that to go as far back as possible and also graph the competitors Domain Authorities on the same graph but a different coloured line.

Is that easy to do? Do I want a time series DB for this, or is it overkill?

#

or maybe i'm jumping the gun by talking about displaying them at this stage?

viral crag May 31, 2018, 10:21 AM

#

It depends on the granularity you need

#

Obviously if you need accurate figures for every month ever then dropping granularity is not what you want

stoic pendant May 31, 2018, 10:22 AM

#

by granularity do you mean tiny fluctuations over a small space of time, like server load or web traffic?

viral crag May 31, 2018, 10:22 AM

#

Huh?

#

We're talking about data here

stoic pendant May 31, 2018, 10:23 AM

#

i'm confused 😄 sorry

viral crag May 31, 2018, 10:23 AM

#

Well we've established that a time series database isn't going to work for you

stoic pendant May 31, 2018, 10:23 AM

#

ok 😃

viral crag May 31, 2018, 10:23 AM

#

Since you're going to be doing a lot of increments

#

You'll want a database that has that built into the query language

#

That way you can do the changes entirely on the server

#

Instead of having to get the data, change it, and put it back

#

Other than that, anything should be fine really

stoic pendant May 31, 2018, 10:27 AM

#

ok, i think i understand. so even something like MSSQL might work?
but avoid time series DB altogether you're saying?

terse stump May 31, 2018, 10:28 AM

#

you can still use Grafana with an SQL data source

stoic pendant May 31, 2018, 10:31 AM

#

so grafana is a good way of displaying the data as i want it, but not a database as such? sorry for being thick. i'm really new to the idea of databases and trying to grasp how they work compared with google sheets and excel.

terse stump May 31, 2018, 10:31 AM

#

grafana display data