#databases | Python | Page 48

dull scarab Jul 22, 2018, 4:51 PM

#

and simply throw in an id <-> itemid in your inventory table when that user obtains that item

#

to store them all in a variable like inventory for iteration, a bit of querying would be needed

torn sphinx Jul 22, 2018, 4:55 PM

#

Alright ty

torn sphinx Jul 22, 2018, 5:24 PM

#

Has anyone ever used YAML to automate Google Sheets?

hazy crystal Jul 22, 2018, 5:46 PM

#

guys what would be a efficent way to query a big db 100k + records

#

i will have a searchbar query that db and fipter the output

#

filter

#

it contains video so im gonna look for terms on the table. 2 cols will bd used in the search

ionic pecan Jul 22, 2018, 6:10 PM

#

which database are you using?

hazy crystal Jul 22, 2018, 7:08 PM

#

to tell the truth i kind of have no idea

#

didn't change any config

#

its a mysql db , not sure about the engine its using

#

ive been thinking of limiting a query to 10k , grab the records do fuzzy search on it and if x records are not found , repeat with next 10K

#

that would speed things a bit i think

ornate abyss Jul 22, 2018, 7:20 PM

#

You have SQL for a reason

#

As long as your query is not awful, and you have setup proper indexes, leave the DB to do all that stuff for you

hazy crystal Jul 22, 2018, 7:27 PM

#

well the server IO performance is bad since its on my laptop

#

well the server IO performance is bad since its on my laptop

orchid charm Jul 23, 2018, 9:44 AM

#

@torn sphinx use commas or a separate table with inserts of items. I use SQLite and in my discord program i have a item table for a collectable I call banners

This is how it grabs the information
cur.execute(“SELECT * FROM * WHERE ID = (SomeId)”)
ItemList = cur.fetchall

If you want to use a comma way do this
cur.execute(f”UPDATE * SET * = *{newitem} WHERE * = *”)

Then just do a fetchone and re the output to get the items which are in between the ,

#

These both should work

#

and btw cur is your connection cursor

#

I would use the comma way due to it being way more efficient

#

A item table is easier but would use more data

ionic pecan Jul 23, 2018, 10:11 AM

#

DON'T use f-strings for parameter substitution

#

bad, bad, bad, bad, bad

#

if newitem happens to be something like ```sql
'hi';
SELECT
'DROP TABLE ' || name || ';'
FROM
sqlite_master
WHERE
type = 'table'; --

torn sphinx Jul 23, 2018, 10:14 AM

#

I use keys

#

Is that good?

#

c.execute("INSERT INTO users VALUES (:name, :id, :xp)", {'name': ctx.message.author.display_name, 'id': ctx.message.author.id, 'xp': 0})```

#

@orchid charm ty

vestal apex Jul 23, 2018, 10:16 AM

#

keys are fine, that's using execute's substitution mechanism

#

even if you're not taking user input, you should never ever use unclean string substitution

#

better to be in the habit of doing it the right way every time

hazy crystal Jul 23, 2018, 12:51 PM

#

guys is it possible to insert string date directly as date format on a mysql db?

patent glen Jul 23, 2018, 2:35 PM

#

@hazy crystal probably, it might have to be in a particular format

#

According to https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html, it should be in yyyy-mm-dd hh:mm:ss format.

hazy crystal Jul 23, 2018, 3:06 PM

#

@patent glen what if its yyyy-mm-dd (without h) and send from a python script as a string?

patent glen Jul 23, 2018, 3:16 PM

#

@hazy crystal yyyy-mm-dd should be fine for date, i was looking at datetime

#

anyway try it and see

#

works for me with mysql.connector

hazy crystal Jul 23, 2018, 3:37 PM

#

i guess i should try it , btw do you know js by chance? I need to read a jquery script to find out how its working 😄

patent glen Jul 23, 2018, 3:44 PM

#

i know basic js but don't have a clue about jquery

hazy crystal Jul 23, 2018, 3:45 PM

#

ok its fine gonna figure it out with trial and error :D. btw i tried inserting it in the right format and it works.

torn sphinx Jul 23, 2018, 3:50 PM

#

You might find js channels in #315249263103967242

glossy scroll Jul 23, 2018, 8:56 PM

#

This might be a really basic database question, but how would you suggest working with a connection across multiple modules.

#

or is this more suited to one of the help channels?

patent glen Jul 23, 2018, 9:04 PM

#

depending on your use case, you could either: pass in the connection (or cursor) to whatever function needs it, maintain the connection (and/or cursor) in a single module and have every other module import that module, or just keep opening and closing connections instead of having a single connection object (sqlite connections are cheap, and i think most other db modules implement a connection pool)

#

some of these solutions may not be suitable for multithreaded usage.

glossy scroll Jul 23, 2018, 9:13 PM

#

I currently have a helper class, so that I don't litter the code up with a bunch of the same queries

#

that could be changed to open and close a connection each time one of it's function is called

#

or I could make it a singleton(the pythonicish way)

#

Which seems better or does it ultimately not matter?

patent glen Jul 23, 2018, 9:21 PM

#

well you could make the connection an attribute on the class instance

#

make sure to add a close method, and maybe also a context manager, if you go that route

orchid charm Jul 23, 2018, 9:22 PM

#

I only use f strings if it can’t be something dangerous

vestal apex Jul 24, 2018, 3:01 AM

#

@orchid charm well, don't

charred marsh Jul 24, 2018, 11:29 AM

#

im trying to add columns in a loop in sqlite3 without using string concatenation due to the risk of SQL injection. i found this answer on stack exchange https://stackoverflow.com/questions/30824392/add-columns-to-an-sqlite-table-in-a-python-loop however i get a syntax error as follows

cursor.execute("ALTER TABLE data ADD COLUMN ? INTEGER", ("test",))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: near "?": syntax error

any ideas as to what im doing wrong? (using python3 if that means anything) edit: please ping me in your response

Stack Overflow

Add columns to an SQLite table in a Python loop

I make a database (using sqlite3) for a scientific program (Python).
This program build makes loops which gradually build the database.
So the problem is to add column with a loop. I made an examp...

ornate abyss Jul 24, 2018, 11:59 AM

#

@charred marsh You cant use parameters for table/column names

#

In this instance, you have to use string formatting

charred marsh Jul 24, 2018, 12:19 PM

#

hmm ok, i wonder why it worked on stackexchange then 😕

ornate abyss Jul 24, 2018, 3:50 PM

#

If you mean his answer, I doubt he tested it

torn sphinx Jul 24, 2018, 5:46 PM

#

Hello any fast way to transfer a json database into SQLite? I would do it buy copy pasting but.... Theres 1.200 people in my database and each of them have like 8 colums so rip lol

ionic pecan Jul 24, 2018, 6:07 PM

#

no idea what a "json database" is, but if it's a json file, then just do it through python, you may want to look into the json module aaaaand

#

bot.docs['sqlite3.Cursor.executemany']

delicate fieldBOT Jul 24, 2018, 6:08 PM

#

`sqlite3.Cursor.executemany`

executemany(sql, seq_of_parameters)```Executes an SQL command against all parameter sequences or mappings found in the sequence *seq\_of\_parameters*. The [`sqlite3`](#module-sqlite3 "sqlite3: A DB-API 2.0 implementation using SQLite 3.x.") module also allows using an [iterator](../glossary.html#term-iterator) yielding parameters instead of a sequence.

```py
import sqlite3

class IterChars:
    def __init__(self):
        self.count = ord('a')

def __iter__(self):
        return self

def __next__(self):
        if self.count > ord('z'):
            raise StopIteration
        self.count += 1
        return (chr(self.count - 1),) # this is a 1-tuple

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table characters(c)")

theIter = IterChars()
cur.executemany("insert into characters(c) values (?)", theIter)

cur.execute("select c from characters")
print(cur.fetchall())
```  Here’s a shorter example using a [generator](../glossary.html#term-generator):

```py
import sqlite3
import string... [read more](https://docs.python.org/3.7/library/sqlite3.html#sqlite3.Cursor.executemany)

ionic pecan Jul 24, 2018, 6:08 PM

#

you need to go from json dictionary to values, and then make that into a list of tuples

torn sphinx Jul 24, 2018, 7:24 PM

#

ok ty

torn sphinx Jul 24, 2018, 9:59 PM

#

Hello. If I want to get access to for example "level"
I have to do py data[3]
Why I can't Just use py data["level"] :/

#

http://prntscr.com/kakou2

Lightshot

Screenshot

Captured with Lightshot

ionic pecan Jul 24, 2018, 10:58 PM

#

is that... excel?

#

anyways, if you're using the built-in sqlite3 module, i think you're stuck with that since it returns a list of tuples

ornate abyss Jul 24, 2018, 11:11 PM

#

There is something called row_factory

#

https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.row_factory

#

@torn sphinx @ionic pecan

torn sphinx Jul 24, 2018, 11:13 PM

#

Its ok its the same thing

#

Thanks anyway

ornate abyss Jul 24, 2018, 11:13 PM

#

What?

torn sphinx Jul 24, 2018, 11:13 PM

#

@ionic pecan Thats a DB Browser for Sqlite

#

@ornate abyss ?

#

Whats the diffrerence if I do
data["level"]
or
data[3]

#

Its the same lol

ionic pecan Jul 24, 2018, 11:14 PM

#

no it isn't

#

one is key access, the other is index access

mild orbit Jul 25, 2018, 9:18 AM

#

Im looking ELK channels, to dicuss elasticsearch nosql etc

ornate abyss Jul 25, 2018, 11:50 AM

#

NoSQL would still fit in here @mild orbit

quaint swallow Jul 25, 2018, 4:54 PM

#

apologies in advance for asking the simplest questions, but which way of storing data is a good start? i'm trying to program a small discord bot that stores user input data from my friends for a small game.. should I create one list/array for each user and store the list in a .csv or .txt file?

#

or is there better options? do you have any good learning material from the internet?

torn sphinx Jul 25, 2018, 4:55 PM

#

most of the people use json files for simple data in discord bots

quaint swallow Jul 25, 2018, 4:57 PM

#

json sounds rather advanced or am i wrong? wouldn't a .txt or .csv file be enough?

torn sphinx Jul 25, 2018, 4:58 PM

#

i'd say the contrary

#

json files are easy

quaint swallow Jul 25, 2018, 4:58 PM

#

i'm a beginner beginner by the way

torn sphinx Jul 25, 2018, 4:58 PM

#

and you have a builtin json module

quaint swallow Jul 25, 2018, 4:58 PM

#

okay

#

i'll see what I can do with it, thanks!

hallow jetty Jul 25, 2018, 5:02 PM

#

sqlite3 is a fairly easy Python module. Requires a little knowledge of SQL though.

quaint swallow Jul 25, 2018, 5:08 PM

#

👌🏻

sly hazel Jul 25, 2018, 5:24 PM

#

@quaint swallow Json is not too hard
You basically just treat it as a python dict, then use the json module to save and load the json file

torn sphinx Jul 25, 2018, 5:26 PM

#

yeah if you can use lists and dicts, youre pretty much set with json. its not that difficult

gleaming frost Jul 25, 2018, 6:01 PM

#

@quaint swallow json's have worked a charm for me

#

https://sharpweb.me.uk/i/wgdqm.png

#

One thing i would say is keep all the funcs that read and write to it in one file

#

Something like storage.py

#

That way, if you move to a database, its easier to move over than having the code all over the place ;D

clever crypt Jul 25, 2018, 6:06 PM

#

Is it a bad idea to keep a connection to a database open for the entire runtime of an application? Is this something that prevents others from accessing the database?

#

I have been using sqlite so far, I have been learning mariadb because I would like to migrate to that eventually.

hallow jetty Jul 25, 2018, 6:43 PM

#

Depends on the database, but it's a good idea to just open a connection when you need it. Less of a chance for corruption

patent glen Jul 25, 2018, 6:44 PM

#

@clever crypt an open uncommitted transaction is more likely to cause problems than an open connection

#

though some databases have a limit to the number of connections or cursors that can be open

quaint swallow Jul 25, 2018, 6:46 PM

#

@gleaming frost oooh that looks nice

gleaming frost Jul 25, 2018, 6:46 PM

#

@quaint swallow It works rly well. if you get stuck i can show you my code ;D

quaint swallow Jul 25, 2018, 6:47 PM

#

that would be awesome, i'm gonna play around with it first tho 👍🏻 👍🏻

gleaming frost Jul 25, 2018, 6:49 PM

#

Aight

clever crypt Jul 25, 2018, 7:48 PM

#

@patent glen, thank-you. Sorry for the late reply. I'm at work and lunch break ended.

#

I considered my options, and I could have my application reconnect to the DB for each transaction. I haven't measured the overhead of that though, but it looks like that's how most web apps work.

torn sphinx Jul 25, 2018, 7:51 PM

#

Hey can I have help

#

my leaderboard command is not working

#

@client.command(pass_context=True)
async def leaderboard(ctx):
    print("1")
    res1 = conn.execute("SELECT Coins FROM Users ORDER BY Coins DESC;")
    print("2")
    res1 = res1.fetchall()
    print("3")
    embed = discord.Embed(title="Most coins", description="The top users with the most Coins", color=0xfffafa)
    print("4")
    res2 = conn.execute("SELECT UserID FROM Users ORDER BY Coins DESC;")
    print("5")
    res2 = res2.fetchall()
    print("6")
    for counter in range(0, 2):
        print("7")
        for member in ctx.message.server.members:
            print("8")
            counter_id = str(res2[counter])[2:-3]
            print("9")
            print(counter_id)
            print("10")
            print(member.id)
            print("11")
            if counter_id == member.id:
                print("12")
                embed.add_field(name=member.display_name, value=str(res1[counter])[1:-4], inline=False)
                print("13")
                break
    await client.say(embed=embed)
    print('15')

#

I tried printing the numbers for it to detect the error

#

but there is no error

#

and doesn't respond

clever crypt Jul 25, 2018, 7:59 PM

#

Hype, here's what I've noticed so far. If you just one want result you can use fetchone() instead of fetchall()

#

you can combine your query

res.fetchone() # ("John Smith",10)```

lavish ferry Jul 25, 2018, 8:02 PM

#

someone help me about Heroku Postgres ?

clever crypt Jul 25, 2018, 8:03 PM

#

I'm assuming of course that UserID is TEXT

#

Hype, if you have an interactive shell, you could also run

print(res.fetchone())```

#

just to make sure the table isn't empty

ornate abyss Jul 25, 2018, 11:26 PM

#

@hallow jetty

Depends on the database, but it's a good idea to just open a connection when you need it. Less of a chance for corruption
??????

hallow jetty Jul 26, 2018, 12:27 AM

#

Connect->Make changes->Save Changes-> Disconnect

ornate abyss Jul 26, 2018, 1:35 AM

#

I meant more the second part

#

Also, it depends on your application, if you have lots of connections to the database you want a pool, if it's now and again, closing and opening is fine

hallow jetty Jul 26, 2018, 3:17 AM

#

I'm by no means an expert, but I was just thinking about traffic not being saved if it crashes

full geyser Jul 26, 2018, 3:19 AM

#

@quaint swallow if u want something REALLY easy, use json files with the livejson module

#

Autocreates files and auto saves everything

slate bone Jul 26, 2018, 5:17 AM

#

Guys what is the best module to deal with MySQL. I care more about the features and components than difficulty like it’s ok if it’s hard

torn sphinx Jul 26, 2018, 7:48 AM

#

@clever crypt

#

res = conn.execute("SELECT UserID, Coins FROM Users ORDER BY Coins DESC;")
res.fetchone() # ("John Smith",10)

#

does it have to be exactly like this?

#

can I change the John Smith bit

glass hearth Jul 26, 2018, 7:49 AM

#

the comment?

torn sphinx Jul 26, 2018, 7:50 AM

#

Hey can I have help
my leaderboard command is not working

@client.command(pass_context=True)
async def leaderboard(ctx):
    print("1")
    res1 = conn.execute("SELECT Coins FROM Users ORDER BY Coins DESC;")
    print("2")
    res1 = res1.fetchall()
    print("3")
    embed = discord.Embed(title="Most coins", description="The top users with the most Coins", color=0xfffafa)
    print("4")
    res2 = conn.execute("SELECT UserID FROM Users ORDER BY Coins DESC;")
    print("5")
    res2 = res2.fetchall()
    print("6")
    for counter in range(0, 2):
        print("7")
        for member in ctx.message.server.members:
            print("8")
            counter_id = str(res2[counter])[2:-3]
            print("9")
            print(counter_id)
            print("10")
            print(member.id)
            print("11")
            if counter_id == member.id:
                print("12")
                embed.add_field(name=member.display_name, value=str(res1[counter])[1:-4], inline=False)
                print("13")
                break
    await client.say(embed=embed)
    print('15')

I tried printing the numbers for it to detect the error
but there is no error
and doesn't respond

#

@glass hearth

glass hearth Jul 26, 2018, 7:50 AM

#

this isn't normal discord.py?

torn sphinx Jul 26, 2018, 7:50 AM

#

SQL

#

#databases

#

and yes normal discord.py aswell

glass hearth Jul 26, 2018, 7:51 AM

#

i never heard of client.say

#

can't be of any help sorry

torn sphinx Jul 26, 2018, 7:51 AM

#

Okay no problem thnx for trying

vestal apex Jul 26, 2018, 8:24 AM

#

oh geeze you guys were in here too

torn sphinx Jul 26, 2018, 9:50 AM

#

https://hastebin.com/kixicisuro.py
For some reason it says that the database is locked when I do this command.

ionic pecan Jul 26, 2018, 9:52 AM

#

whats the full error?

torn sphinx Jul 26, 2018, 9:53 AM

#

Exception in command 'coinflip', database is locked

#

Just this

#

The command works fine on my main bot

#

I just copied to make some changes

ionic pecan Jul 26, 2018, 9:54 AM

#

https://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked did you see this?

torn sphinx Jul 26, 2018, 9:54 AM

#

Yeah I did

#

But It works on my other bot with the same structured. But it sont a cog

ionic pecan Jul 26, 2018, 9:55 AM

#

are both bots running at the same time?

#

not sure if thats the issue, but if you're on unix, you can run sudo lsof path/to/your/sqlite.db to check which processes have the database file opened

torn sphinx Jul 26, 2018, 9:56 AM

#

Yea but i use diffrent token for each. also the main bot is running in an other pc

#

Let me test that

#

Btw whats sudo? It doesnt recognize it

ionic pecan Jul 26, 2018, 10:00 AM

#

sounds like you're on windows, for that I don't know, sorry

#

if you're curious regardless, see https://en.wikipedia.org/wiki/Sudo

torn sphinx Jul 26, 2018, 10:13 AM

#

📎 unknown.png

#

Should I close them and try again?

#

Closed them and tried again and it says its locked again

#

@ionic pecan I think i know why

#

I think its becasue I have this

conn = sqlite3.connect('test.db')
def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d
conn.row_factory = dict_factory
c = conn.cursor()``` 
in both files but if I dont have it I get error that 'c; is not defined

#

Or I just need c = conn.cursor() ?

#

But then I get conn is not defined

#

Fixed. I just import conn

#

Thanks

ornate abyss Jul 26, 2018, 11:18 AM

#

@slate bone I've found to have the best experience with pymysql

#

@hallow jetty having a pool of connections will not corrupt your data.

slate bone Jul 26, 2018, 11:49 AM

#

Thx

quaint swallow Jul 26, 2018, 1:01 PM

#

@full geyser nice, thanks for the tip!

warm hare Jul 26, 2018, 2:31 PM

#

I'm using Mongo rn

#

I was wondering, should create a user per collection

#

Or just 1 user with read/write access to every colllection?

patent glen Jul 26, 2018, 2:32 PM

#

@torn sphinx locked database means you have an open (not yet committed) transaction modifying that table on another connection.

#

you should always commit after doing modifications

torn sphinx Jul 26, 2018, 2:41 PM

#

@patent glen

#

how is it locked?

patent glen Jul 26, 2018, 2:46 PM

#

by the other transaction

#

it's so that you don't read the table in an inconsistent state

quaint swallow Jul 26, 2018, 2:53 PM

#

with open("dat.json", 'r') as f:
data = json.load(f)

#

why do i keep getting this error?

📎 unknown.png

#

my json file is simple and in the same directory

#

{
'id': 134,
'name': 'myname'
}

patent glen Jul 26, 2018, 2:54 PM

#

json can't have single quotes

quaint swallow Jul 26, 2018, 2:54 PM

#

oooooh

patent glen Jul 26, 2018, 2:55 PM

#

it's a very strict subset of javascript

#

so even though javascript allows single quotes or unquoted attribute names, double quotes are required

quaint swallow Jul 26, 2018, 2:55 PM

#

okay, thanks!

#

works now

hallow jetty Jul 26, 2018, 4:13 PM

#

@ornate abyss Corrupt was the wrong word, but you're right. Pools are good. I was just thinking about a smaller db, like sqlite3, where you can only have 1 connection.

ornate abyss Jul 26, 2018, 4:15 PM

#

@hallow jetty You can have more than one connection for sqlite tho

hallow jetty Jul 26, 2018, 4:17 PM

#

Unless it's changed since a year ago (when I used) I remember having to deal with that restriction.

#

Ah I see, it just locks the Database.

ornate abyss Jul 26, 2018, 4:18 PM

#

You can only have one write at a given time, maybe thats what you meant

hallow jetty Jul 26, 2018, 4:18 PM

#

Yep

ornate abyss Jul 26, 2018, 4:18 PM

#

But as for connections, you can have multiple

hallow jetty Jul 26, 2018, 4:19 PM

#

Looks like it. Frameworks have spoiled me tbh

ornate abyss Jul 26, 2018, 4:19 PM

#

lol

shut crater Jul 26, 2018, 5:32 PM

#

Anyone familiar with a similar discord for SQL & Database help/discussion? Or are we able to ask non-python SQL related questions here.

viral crag Jul 26, 2018, 5:32 PM

#

Some of our #315249263103967242 might be more suitable if you're not working with python or the database directly

shut crater Jul 26, 2018, 5:38 PM

#

Thanks for the tip!

quaint swallow Jul 26, 2018, 5:39 PM

#

So I have a string in my json file with many objects:

#

{"users": [{"id": 1, "name": "Sandordude#7438", "str": 0, "weapon": "Dolch"}, {"id": 2, "name": "User2#4325", "str": 0, "weapon": "Weapon2"}, ...]}

#

how do I access for example "name"?

#

If I want to check if the name "Sandordude" is somewhere in the string

#

Sorry if I didnt express myself well in terms of terminology

#

I'm using the livejson module which allows me to modify this data as if it was a python list/dictionary

#

if "Sandordude#7438" in f["users"]:
print("You're already signed in")
return

#

anyway, this isn't the solution

dull scarab Jul 26, 2018, 5:50 PM

#

Does users have to be a list in this case?

#

You could achieve much the same with it just being a dict

quaint swallow Jul 26, 2018, 5:50 PM

#

it can be the id

#

but user would be better

#

since it's easier for me as i'm not experienced at all

dull scarab Jul 26, 2018, 5:51 PM

#

Anyways, with that structure you probably have to search the list

#

Traversing through the list and looking for user["name"] == "something"

quaint swallow Jul 26, 2018, 5:57 PM

#

hmm I guess, I just don't know how to program it since there's many "user" and users["name"] would basically look for an object that looks like this: {"name": something} inside of the [ ... ] array

dull scarab Jul 26, 2018, 6:00 PM

#

Iterate over the list checking each users name, and break out when you find it to avoid checking unnecessary users

quaint swallow Jul 26, 2018, 6:01 PM

#

i'll find a way to do that 👍🏻 appreciate it

dull scarab Jul 26, 2018, 6:02 PM

#

I can provide an example if you don't get it after trying a bit 😄

quaint swallow Jul 26, 2018, 6:02 PM

#

alright 😄 lets see if I can do it by myself

#

maybe tomorrow if you dont mind since I gotta go atm

#

also the structure of my json file must be horrible but whatever, it's my first time 😛

dull scarab Jul 26, 2018, 6:03 PM

#

Np, feel free to tag me if im online (have work tomorrow so might not be)

quaint swallow Jul 26, 2018, 6:04 PM

#

sure! i'm not in a hurry anyway, nice from you

#

have a great day 👋🏻

dull scarab Jul 26, 2018, 6:04 PM

#

You too c:

quaint swallow Jul 26, 2018, 6:04 PM

#

tyty

hazy rock Jul 27, 2018, 3:16 AM

#

can someone tell me the module name for python to interact with MySQL database?

plucky grotto Jul 27, 2018, 3:21 AM

#

@hazy rock There are probably many. I like to work with SQLAlchemy.

wispy fable Jul 27, 2018, 3:46 AM

#

We recommend sqlite3 for anyone who's new to SQL

#

It's already bundled with standard python installations and it's simple to use

#

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

patent glen Jul 27, 2018, 3:47 AM

#

for MySql specifically there are several different modules available

#

look at https://stackoverflow.com/questions/43102442/whats-the-difference-between-mysqldb-mysqlclient-and-mysql-connector-python for differences between them

wispy fable Jul 27, 2018, 3:49 AM

#

If you search "python mysql" on google/yahoo/bing/ecosia (save the trees!), then you will get a lot of stuff regarding what you need

slate bone Jul 27, 2018, 5:48 AM

#

Guys can I use SQLite3 to store thousands of data ?

broken linden Jul 27, 2018, 5:51 AM

#

The word data isn't really a unit

slate bone Jul 27, 2018, 5:51 AM

#

I mean like players data ?

#

Like 30 thousand for example ?

broken linden Jul 27, 2018, 5:51 AM

#

You can

slate bone Jul 27, 2018, 5:52 AM

#

What about 100k ?

broken linden Jul 27, 2018, 5:52 AM

#

But at some point you'll want to use a real db

#

You can in theory

slate bone Jul 27, 2018, 5:52 AM

#

How can I know if that some point has came lol ?

#

And it’s time to use a real db ?

broken linden Jul 27, 2018, 5:53 AM

#

When the query time seriously affects the experience of the user I guess

slate bone Jul 27, 2018, 5:53 AM

#

Thanks mate

wispy fable Jul 27, 2018, 6:38 AM

#

@slate bone What is your data

#

Because it really does depend on how much space each player would take up

#

SQL databases as considered the most effective way to store and access large amounts of data, although at a certain point where you do run into issues with query time, you should probably consider using better hardware as well as a more professional sql lib

slate bone Jul 27, 2018, 7:01 AM

#

Ermm I just want to store a list of names without any other data but the names may reach 100k

#

Like I feel it doesn’t really deserve MySQL

wispy fable Jul 27, 2018, 7:03 AM

#

MySQL would be overly complicated if you're only storing names

#

I think sqlite3 should work, but you should probably do some research on what people benchmark

slate bone Jul 27, 2018, 7:43 AM

#

Ok thanks

ornate abyss Jul 27, 2018, 1:02 PM

#

Sqlite is more than capable

unborn sentinel Jul 27, 2018, 4:24 PM

#

Are there any good tutorials out there on sqlite3?

#

The Python site's documentation mentions the one on w3schools but I'm always kind of wary about them

torn sphinx Jul 27, 2018, 4:27 PM

#

Yes

unborn sentinel Jul 27, 2018, 4:28 PM

#

Let me rephrase that then, can someone provide a link to a good tutorial on sqlite3

#

Or rather, would someone please provide me a link to one

torn sphinx Jul 27, 2018, 4:30 PM

#

@unborn sentinel

#

https://www.youtube.com/watch?v=pd-0G0MigUA

YouTube

Corey Schafer

Python SQLite Tutorial: Complete Overview - Creating a Database, T...

In this Python SQLite tutorial, we will be going over a complete introduction to the sqlite3 built-in module within Python. SQLite allows us to quickly get u...

▶ Play video

unborn sentinel Jul 27, 2018, 4:31 PM

#

Any in written form? I'm not overly partial to videos since I do most of my coding while I'm at work

#

Apologies, I should have specified

torn sphinx Jul 27, 2018, 4:33 PM

#

It is basically in writing

ornate abyss Jul 27, 2018, 5:03 PM

#

@unborn sentinel when you say tutorial for sqlite, do you know SQL already and want to know how to work with the python library?

unborn sentinel Jul 27, 2018, 5:04 PM

#

You know, after thinking about it, I'm not entirely sure what I meant. I guess I only have one specific use case in mind. Forget I asked, I think I figured out what I needed

#

Thanks though

ornate abyss Jul 27, 2018, 5:06 PM

#

All good

quaint swallow Jul 27, 2018, 8:36 PM

#

hey guys, i'm doing an if statement that goes like this: if message.content.startswith(-> key from python dictionary<-).. but I don't know what to put into the parentesis

#

can anyone help me out?

#

so it is not triggered if the key is not in the dictionary

viral crag Jul 27, 2018, 8:38 PM

#

if key in dictionary

patent glen Jul 27, 2018, 8:38 PM

#

if key in mydict and message.content.startswith(mydict[key])

#

or, if you want to be super clever and nobody can read your code

#

message.content.startswith(mydict.get(key,()))

quaint swallow Jul 27, 2018, 8:41 PM

#

hmm I know it's super simple but I got and still get some errors, thanks though

#

sorry for the easy questions

#

btw @dull scarab I was able to fix the problem so no more help is needed, thanks for your help!

#

I asked you to help me out the next day

#

🙏🏻

dull scarab Jul 27, 2018, 8:44 PM

#

Uhm, what was it about 😅

quaint swallow Jul 27, 2018, 8:45 PM

#

it was about searching through lists, but my list was badly structured so I created a new one

#

and now it works

dull scarab Jul 27, 2018, 8:45 PM

#

Oh right

#

Gj okhandbutflipped

quaint swallow Jul 27, 2018, 8:46 PM

#

👌🏻 👌🏻

#

thanks

#

okay so it says "key" is not defined.. i'm not searching for a particular key but rather one that is part of the dictionary

#

I guess theres no easy way and i'll have to do a more complex if statement

patent glen Jul 27, 2018, 8:54 PM

#

for key, value in mydict.items():
    if message.startswith(key):
        ...do something with value...
        break```

#

i thought you already had a key, which may or may not be in the dict, and you were searching for something that started with the value corresponding to that key

#

@quaint swallow

quaint swallow Jul 27, 2018, 8:57 PM

#

hmm no, the if statement should be activated when a certain user input is part of the dictionary

#

hmm lets see :/

quaint swallow Jul 27, 2018, 9:22 PM

#

basically the outcome of the above mentioned code @patent glen is more than one key so if message.startswith(key) won't work

#

print(str(key))
outcome: e.g. key1, key2, key3

#

for example, the if (user_input) statement should be activated if the user_input equals to a key of my list

#

unfortunately I only have a "if message.content.startswith" statement

gleaming frost Jul 27, 2018, 10:02 PM

#

@quaint swallow move to the cogs version?

quaint swallow Jul 27, 2018, 10:05 PM

#

i've written quite a lot so far for my standards and i'd like to keep the syntax as it is @gleaming frost

gleaming frost Jul 27, 2018, 10:05 PM

#

@quaint swallow ok.....

#

So is it !func (input)?

quaint swallow Jul 27, 2018, 10:06 PM

#

i'm too much of a beginner for this :/

gleaming frost Jul 27, 2018, 10:06 PM

#

Is that how the command is triggered?

quaint swallow Jul 27, 2018, 10:06 PM

#

I can define the trigger how I want but I have to use "message.content.startswith" to recall the trigger

gleaming frost Jul 27, 2018, 10:08 PM

#

Ok so you can pass message.content down

#

So....

quaint swallow Jul 27, 2018, 10:08 PM

#

maybe it helps you to know what it's actually about

#

it's a discord bot and i'd like to create a command where one user "attacks" the other

#

so in order to check if the user that is attacked is part of the dictionary I have to do an if statement

gleaming frost Jul 27, 2018, 10:09 PM

#

I'm doing the exact same thing right now!

quaint swallow Jul 27, 2018, 10:09 PM

#

haha really? 😄

gleaming frost Jul 27, 2018, 10:09 PM

#

And I have learnt alot... From doing it twice

#

So if you want... I'll give you some advice?

quaint swallow Jul 27, 2018, 10:09 PM

#

if you don't mind

#

but I already have quite some code, the syntax basically is fixed now

#

if that makes sense

gleaming frost Jul 27, 2018, 10:11 PM

#

class Fights:

    Instances = {}

    Def __init__(self, player1, player2):
        Self.p1 = player1
        Self.p2 = player 2
        Fights.instances[player1.id, player2.id] = self

#

Don't take that code as working, I'm on my phone

#

Then for the players....

#

class Players:
    def __init__(self, author):
        self.health = 300
        self.damage = 20
        self.author = author

#

@quaint swallow then you can just pass players names as message.author

#

And all the game stats

#

Will be stored in Fights.instances

quaint swallow Jul 27, 2018, 10:15 PM

#

sounds interesting, i did it waaay more complicated though

#

and more inefficient

gleaming frost Jul 27, 2018, 10:15 PM

#

Sounds like my first attempt... Lmao

quaint swallow Jul 27, 2018, 10:15 PM

#

it sounds great though 😛

#

I never worked with classes before

#

its my first time coding for real

gleaming frost Jul 27, 2018, 10:16 PM

#

Classes are great

#

I'm using that exact system for my own code.

#

It's very useful, and also makes your code surprisingly readable

quaint swallow Jul 27, 2018, 10:17 PM

#

yeah

gleaming frost Jul 27, 2018, 10:17 PM

#

Because then you can do p1.health ect

#

You could in theory make larger battles with that 2

#

@quaint swallow so what exactly were you stuck on?

quaint swallow Jul 27, 2018, 10:18 PM

#

I'll definitely hang to classes next time but I'm gonna try to end it "my way", even if it sucks

gleaming frost Jul 27, 2018, 10:19 PM

#

xD

quaint swallow Jul 27, 2018, 10:19 PM

#

my problem is that I don't know how to check if the user input is part of the class

#

haha yeah

gleaming frost Jul 27, 2018, 10:19 PM

#

Ok so

quaint swallow Jul 27, 2018, 10:19 PM

#

i'm using discord.py

gleaming frost Jul 27, 2018, 10:19 PM

#

Are you comparing it to a dict?

quaint swallow Jul 27, 2018, 10:19 PM

#

yeah

#

the command is going to be:

#

!fight User

gleaming frost Jul 27, 2018, 10:19 PM

#

What does the key store?

quaint swallow Jul 27, 2018, 10:20 PM

#

this is what it looks like:

#

{"Marius#3523": [20, 1, "Mace"], "Sandordude#7438": [13837, 1, "Sword"]}

gleaming frost Jul 27, 2018, 10:20 PM

#

Ok so message.author

quaint swallow Jul 27, 2018, 10:21 PM

#

yea

gleaming frost Jul 27, 2018, 10:22 PM

#

for key in dict.keys():
    if str(message.author) == key:
        print("success")
    else:
        print(f"Debug {key} {message.author}")

#

Run that (might be mistakes on mobile)

#

Print debugging..... Xd

quaint swallow Jul 27, 2018, 10:22 PM

#

😛 no problem

#

but key is referring to the values 20, 1 and the str "Mace" right?

gleaming frost Jul 27, 2018, 10:23 PM

#

No

#

Key refers to the marius#6969 part

quaint swallow Jul 27, 2018, 10:23 PM

#

ok

#

lemme try

gleaming frost Jul 27, 2018, 10:23 PM

#

The value is the list

quaint swallow Jul 27, 2018, 10:23 PM

#

I did something similar just some time ago but I'll try again

gleaming frost Jul 27, 2018, 10:24 PM

#

Alright.

#

Send back the print debug if you can

#

Always useful xd

quaint swallow Jul 27, 2018, 10:25 PM

#

ok

#

it always prints success for some reason

#

Debug Marius#3523 Sandordude#7438
success
Debug Marius#3523 Sandordude#7438
success

#

no matter what I type in this is printed

#

ooh wait

#

it should be message.content

#

if that exists

#

not author

gleaming frost Jul 27, 2018, 10:29 PM

#

Ohhhh

#

Yeah

#

Yep.

#

Sry xd

quaint swallow Jul 27, 2018, 10:29 PM

#

haha np 😛

#

[2:29 AM] Sandordude: Marius#3523
[12:29 AM] Sandordude: sdgsd

#

success
Debug Sandordude#7438 Sandordude#7438
Debug Marius#3523 Sandordude#7438
Debug Sandordude#7438 Sandordude#7438

#

it works now

gleaming frost Jul 27, 2018, 10:30 PM

#

Yay

#

Wait r u sure it's working?

#

2 of those times in the debug it matches

quaint swallow Jul 27, 2018, 10:31 PM

#

mmmmmm

#

not sure o.O

#

does it?

#

what does debugging do exactly?

gleaming frost Jul 27, 2018, 10:31 PM

#

Send me your code.

quaint swallow Jul 27, 2018, 10:31 PM

#

ok

#

#Fight
for key in users.keys():
if str(message.content) == key:
print("success")
else:
print(f"Debug {key} {message.author}")

gleaming frost Jul 27, 2018, 10:31 PM

#

Ok so

quaint swallow Jul 27, 2018, 10:31 PM

#

still dont know how to insert the code in a more fashionable way in discord

gleaming frost Jul 27, 2018, 10:31 PM

#

I just call it debugging

#

Because it's easier.

quaint swallow Jul 27, 2018, 10:32 PM

#

yeah

gleaming frost Jul 27, 2018, 10:32 PM

#

Basically it should only print debugging if it fails

quaint swallow Jul 27, 2018, 10:32 PM

#

actually it should have worked because the second time i just wrote nonsense

#

"jadsjaskjf"

#

yes exactly

gleaming frost Jul 27, 2018, 10:32 PM

#

It clearly shouldn't be failing

#

When they match

quaint swallow Jul 27, 2018, 10:32 PM

#

it didnt

#

"success"

#

first outcome

gleaming frost Jul 27, 2018, 10:32 PM

#

Wait show one output

quaint swallow Jul 27, 2018, 10:32 PM

#

ok

gleaming frost Jul 27, 2018, 10:33 PM

#

And your dict

#

```python

#

That's how you show it

#

Top line ```python

#

Bottom line ```

quaint swallow Jul 27, 2018, 10:34 PM

#

Sandordude#7438

#

nice

#

ok so

#

Debug Marius#3523 Sandordude#7438
success

#

after I wrote

#

Sandordude#7438

#

📎 unknown.png

gleaming frost Jul 27, 2018, 10:35 PM

#

Ok

#

Cool

quaint swallow Jul 27, 2018, 10:35 PM

#

📎 unknown.png

#

but it still shows the debug one

#

for some reaosn

gleaming frost Jul 27, 2018, 10:35 PM

#

📎 Screenshot_20180727_233531.jpg

#

That mess confused me

#

xD

quaint swallow Jul 27, 2018, 10:35 PM

#

damn, sorry 😄

gleaming frost Jul 27, 2018, 10:35 PM

#

It should show the debug one

#

Because it's a for loop

#

So even tho the first one worked

#

The rest will still run

#

To fix that, add break after the print success

quaint swallow Jul 27, 2018, 10:36 PM

#

ohh I get it

#

yeah

gleaming frost Jul 27, 2018, 10:36 PM

#

And you can safely get rid of the else and debug, because we know it's working now.

quaint swallow Jul 27, 2018, 10:37 PM

#

yea, nice!

#

okay so i'm going to find out how this copes with !fight User

#

should work tho

#

thanks so much!

gleaming frost Jul 27, 2018, 10:38 PM

#

No problem!

#

I'm doing the same thing rn, so don't be afraid to ask!

quaint swallow Jul 27, 2018, 10:38 PM

#

I know how to do it already

#

okay, nice!

gleaming frost Jul 27, 2018, 10:38 PM

#

We should see eachothers bots?

quaint swallow Jul 27, 2018, 10:38 PM

#

yeah I was just about to propose that

#

lemme send you mine

#

its a beginner one though so its gonna be confusing

patent glen Jul 27, 2018, 11:21 PM

#

@gleaming frost how did you get the ``` itself inside a code block?

📎 unknown.png

gleaming frost Jul 27, 2018, 11:21 PM

#

I typed some strange code.

#

To try and get it to display properly

ornate abyss Jul 28, 2018, 1:53 AM

#

what in the gas

torn sphinx Jul 28, 2018, 2:06 PM

#

what are databases

ornate abyss Jul 28, 2018, 2:30 PM

#

A database is another bit of software running on your computer which has been designed to hold, well data.

#

@torn sphinx What exactly is it you wanted to know?

torn sphinx Jul 28, 2018, 2:31 PM

#

what data it holds?

#

what type?

ornate abyss Jul 28, 2018, 2:31 PM

#

What do you mean by what type

fallow plinth Jul 28, 2018, 2:34 PM

#

It can hold any type of data

torn sphinx Jul 28, 2018, 2:34 PM

#

like

#

oh

#

so usernames, passwords etc?

fallow plinth Jul 28, 2018, 2:35 PM

#

yeah

#

like records of employees or students

torn sphinx Jul 28, 2018, 2:35 PM

#

so if i made a login screen it needs a database to send all the usernames and pw's to the database

#

so i get all their info?

fallow plinth Jul 28, 2018, 2:35 PM

#

Yeah if you want to store

torn sphinx Jul 28, 2018, 2:36 PM

#

how do i make a database?

fallow plinth Jul 28, 2018, 2:36 PM

#

In small project you can store passwords directly

#

In python?

torn sphinx Jul 28, 2018, 2:36 PM

#

idk

fallow plinth Jul 28, 2018, 2:36 PM

#

For database you can try sqllite

torn sphinx Jul 28, 2018, 2:36 PM

#

o shit no sql

fallow plinth Jul 28, 2018, 2:36 PM

#

Database softwares are different

torn sphinx Jul 28, 2018, 2:36 PM

#

it blows my brain up

#

ok

fallow plinth Jul 28, 2018, 2:36 PM

#

Well there are no sql databases too

#

But i don't know about them

torn sphinx Jul 28, 2018, 2:37 PM

#

easy coded ones?

fallow plinth Jul 28, 2018, 2:37 PM

#

MongoDB is quite famous

#

I don't know about it

torn sphinx Jul 28, 2018, 2:37 PM

#

k thanks for help!

ornate abyss Jul 28, 2018, 2:39 PM

#

SQLite will be perfect for this

#

You dont have to do any complicated joins or anything

torn sphinx Jul 28, 2018, 2:45 PM

#

good

orchid charm Jul 28, 2018, 4:48 PM

#

So i am working on adding "Memory" to my discord bot

#

And i am working on a function that loads new information into the database to be pulled on boot as "Memory"

#

It executes every few minutes

#

But how would i cross check information like this with the database
{'344289705627484163': {'IGN': 'Squint', 'FC': 'SW-0000-0000-0000'},}

#

I am using SQLite3 BTW

#

But basically my "Memory" for Profiles is a very long version of that

#

But i would need to check what parts of it aren't in the database, and update/insert accordingly

#

I actually got a idea on how to do this, but its gonna be annoying

#

Ok, so i got a issue
When i do a fetchall all items come as this
(101020, 'Unset', 'SW-0000-0000-0000', '1', 'C-', 'C-', 'C-', 'C-', None, 'https://cdn.discordapp.com/attachments/444297968049455125/459864703821414402/banner-octo.png', 'No,')

#

📎 Screen_Shot_2018-07-28_at_10.02.22_AM.png

#

I might have figured it out though

ornate abyss Jul 28, 2018, 5:05 PM

#

So do you still need help?

orchid charm Jul 28, 2018, 5:05 PM

#

Yeah i still need to figure out how to check this information correctly

#

📎 Screen_Shot_2018-07-28_at_10.05.25_AM.png

#

Cause i can now Format the Information in any way i like

ornate abyss Jul 28, 2018, 5:09 PM

#

Check this information correctly?

orchid charm Jul 28, 2018, 5:10 PM

#

So basically what i want is a loop that checks for anything new and updates if it finds anything new

#

Its for "Memory"

ornate abyss Jul 28, 2018, 5:11 PM

#

Does the bot have separate processes that would change this memory

#

Or is it all through the bot

orchid charm Jul 28, 2018, 5:11 PM

#

It has commands that would modify the "Memory"

ornate abyss Jul 28, 2018, 5:11 PM

#

Alright, so why not change the parts that need to be changed on the commands

orchid charm Jul 28, 2018, 5:11 PM

#

 self.bot.SplatNetQueue.append(ctx.message.channel.id)

ornate abyss Jul 28, 2018, 5:11 PM

#

Instead of inserting and querying

orchid charm Jul 28, 2018, 5:12 PM

#

Cause i use SQLite which is blocking

#

Or something

#

So i want to use the least amount of SQLite3 as possible

#

            cur.execute('INSERT INTO Queue(Channel) VALUES(?)', (ctx.message.channel.id,))
            conn.commit()
            self.bot.SplatNetQueue.append(ctx.message.channel.id)

People have said doing stuff like this in a command is bad

ornate abyss Jul 28, 2018, 5:13 PM

#

But what I'm guessing you're doing, is on the command updating the database and then selecting everything again?

orchid charm Jul 28, 2018, 5:14 PM

#

What that right there is doing is Updating the Database then adding to memory

ornate abyss Jul 28, 2018, 5:14 PM

#

Right

orchid charm Jul 28, 2018, 5:15 PM

#

What people said is i should make a loop that executes every like 5-10 Minutes that checks the difference between the "Memory" and the Database, then updates/inserts new info

ornate abyss Jul 28, 2018, 5:16 PM

#

Are you simply just inserting a new row each time something happens?

#

I see that "unset" in there a few times

orchid charm Jul 28, 2018, 5:16 PM

#

Updating too

#

'[[[101018]]] - [[[Mang]]] - [[[SW-0000-0000-0000]]] - [[[41]]] - [[[A]]] - [[[S+0]]] - [[[A+]]] - [[[A]]] - [[[None]]] - [[[https://cdn.discordapp.com/attachments/444297968049455125/459864703821414402/banner-octo.png]]] - [[[No,]]] - ', '[[[101019]]] - [[[Cube]]] - [[[SW-3234-2940-3525]]] - [[[18]]] - [[[C-]]] - [[[C-]]] - [[[C-]]] - [[[C-]]] - [[[None]]] - [[[https://cdn.discordapp.com/attachments/444297968049455125/459864703821414402/banner-octo.png]]] - [[[No,]]] - ', '[[[101020]]] - [[[Unset]]] - [[[SW-0000-0000-0000]]] - [[[1]]] - [[[C-]]] - [[[C-]]] - [[[C-]]] - [[[C-]]] - [[[None]]] - [[[https://cdn.discordapp.com/attachments/444297968049455125/459864703821414402/banner-octo.png]]] Thats what all of this stuff is for

#

"Profile" items

ornate abyss Jul 28, 2018, 5:16 PM

#

I mean, that looks horrific on phone

orchid charm Jul 28, 2018, 5:17 PM

#

Thats just a new reformat

ornate abyss Jul 28, 2018, 5:17 PM

#

Right

orchid charm Jul 28, 2018, 5:17 PM

#

cur.execute('SELECT User, IGN, FC, Level, RM, SZ, TC, CB, Gender, Banner, Title FROM Profile')
PrintCur = cur.fetchall()
ReformatList = []
for item in PrintCur:
    Var = ''
    for item in item:
        Var += f'[[[{item}]]] - '
    ReformatList.append(Var)
print(ReformatList)

#

Thats how it loads the "Memory"

#

Do you think i should be doing this every few minutes load into the database thing?

#

Or should i just stick to using Update Database Update memory on command

fallow plinth Jul 28, 2018, 5:19 PM

#

Update on command

orchid charm Jul 28, 2018, 5:19 PM

#

    @utility.command(name='splatnet-add')
    @commands.has_permissions(administrator=True)
    @commands.cooldown(1, 5, type=BucketType.user)
    async def splatnet_debug(self, ctx):
        conn = sqlite3.connect(f'{self.bot.V}/tartar.db')
        cur = conn.cursor()
        if ctx.message.channel.id in self.bot.SplatNetQueue:
            cur.execute('DELETE FROM Queue WHERE Channel=?', (ctx.message.channel.id,))
            conn.commit()
            self.bot.SplatNetQueue.remove(ctx.message.channel.id)
            embed=discord.Embed(color=discord.Colour.dark_grey(), )
            embed.set_author(name=self.bot.config.name, icon_url=self.bot.config.url)
            embed.add_field(name='SplatNet Channel Deleted!', value=f"{ctx.message.author.mention} the Channel {ctx.message.channel.mention} is no longer a SplatNet2 Channel!", inline=False)
            await ctx.send(embed=embed)
        else:
            cur.execute('INSERT INTO Queue(Channel) VALUES(?)', (ctx.message.channel.id,))
            conn.commit()
            self.bot.SplatNetQueue.append(ctx.message.channel.id)
            embed=discord.Embed(color=discord.Colour.dark_grey(), )
            embed.set_author(name=self.bot.config.name, icon_url=self.bot.config.url)
            embed.add_field(name='SplatNet Channel Created!', value=f"{ctx.message.author.mention} the Channel {ctx.message.channel.mention} is now a SplatNet2 Channel!", inline=False)
            await ctx.send(embed=embed)
        cur.close()

So this would be the best way

#

If i wanted to update something

#

This is the simplest one i got

#

401872749720502272: {'User': 101020, 'IGN': 'Unset', 'FC': 'SW-0000-0000-0000', 'Level': '1', 'RM': 'C-', 'SZ': 'C-', 'TC': 'C-', 'CB': 'C-', 'Gender': None, 'Banner': 'https://cdn.discordapp.com/attachments/444297968049455125/459864703821414402/banner-octo.png', 'Title': 'No,'}}

#

This is the most Complicated one i have

gleaming frost Jul 28, 2018, 7:23 PM

#

@orchid charm is this a public bot?

#

My only reason is anyone on any server with admin perms can use it

orchid charm Jul 28, 2018, 8:20 PM

#

@gleaming frost This is not the Public Version

gleaming frost Jul 28, 2018, 8:20 PM

#

alright

orchid charm Jul 28, 2018, 8:20 PM

#

There is a Open Source Splatoon Bot that has some remakes of code from the private one

#

Working on Implementing code from the Private one to the Open Source One

#

https://github.com/Squint-Eye/SplatoonBot

GitHub

Squint-Eye/SplatoonBot

SplatoonBot - A Open Source Splatoon Bot that does Profiles, Automatic SplatNet, GrizzCo, and Rotation Features

#

Thats the current status of the Open Source Version

#

I am considering just using a JSON Database for this one, but SQLite is still simpler

#

@gleaming frost

#

This program is p easy to set up

#

And the code that currently is in it is decent

#

I am working on more efficiency stuff in it

gleaming frost Jul 28, 2018, 8:24 PM

#

Ok

#

Cool!

orchid charm Jul 28, 2018, 8:30 PM

#

    @commands.group(aliases=['profil', 'account'], invoke_without_command=True, ignore_extra=False, case_insensitive=True)
    @profile_check()
    @bot_user_check()
    async def profile(self, ctx, user: discord.Member=None):
        try:
            conn = sqlite3.connect(f'{self.bot.directory}/splatoon-bot.db')
            cur = conn.cursor()
            if user == None:
                user = ctx.author.id
            Check = self.bot.get_user(user.id)
            cur.execute('SELECT IGN, FC, Level, RM, SZ, TC, CB, Banner FROM Profile WHERE ID={0.id}'.format(user))
            IGN, FC, Level, RM, SZ, TC, CB, Banner = cur.fetchone()
            cur.close()
            embed=discord.Embed(color=discord.Colour.teal(), title=f"{ctx.message.author.name}'s Profile")
            embed.set_author(name=self.bot.config.name, icon_url=self.bot.config.url)
            embed.add_field(name=f'Username:', value=f'{IGN}', inline=True)
            embed.add_field(name=f'Friend Code', value=f'{FC}', inline=True)
            embed.add_field(name=f'Ranked Statistics', value=f'Rainmaker: {RM}\nTower Control: {TC}\nSplat Zones: {SZ}\nClam Blitz: {CB}', inline=True)
            embed.add_field(name=f'Level', value=f'Level: {Level}', inline=True)
            embed.set_image(url=Banner)
            await ctx.send(embed=embed)
        except TypeError:
            embed=discord.Embed(color=discord.Colour.teal(), description=f'Specified user does not have a Profile!')
            embed.set_author(name=self.bot.config.name, icon_url=self.bot.config.url)
            await ctx.send(embed=embed)

#

Like working on making the Profile Command Group less big and clunky

torn sphinx Jul 29, 2018, 3:39 AM

#

CREATE TABLE IF NOT EXISTS servers (
    server_id BIGINT CONSTRAINT server_pk PRIMARY KEY,
    submission_channel_id BIGINT,
    prefixes VARCHAR[],
    max_rating INTEGER
)

I would like to be able to know if the table was created. Am using asyncpg, does CREATE TABLE return something?

#

ping me

torn sphinx Jul 29, 2018, 4:49 AM

#

Well if you execute without an error surely it’ll work right?

#

@torn sphinx

#

it doesn't matter

#

IF NOT EXISTS removes that error

#

anyways just gonna ignore it

#

Ok

ornate abyss Jul 29, 2018, 12:29 PM

#

@torn sphinx why not remove the NOT EXISTS and catch the error

#

Then you'll know

rough river Jul 30, 2018, 12:05 AM

#

How should I check if a column in my database equals something and if so add to another column.

ionic pecan Jul 30, 2018, 12:10 AM

#

what have you tried already?

#

i mean, it's kinda hard to figure out what exactly you're trying to do, because your question isn't completely clear

#

@rough river can you give us some more context?

rough river Jul 30, 2018, 12:24 AM

#

I have an adventure command that generates enemies killed and deaths and from that I determine the xp earned but I don't want to have people with high amounts xp so I want the level to go up every time the xp hits a certain number. The number I'm comparing it to is the level x 500 + 500 .

ornate abyss Jul 30, 2018, 12:32 AM

#

You would first query their xp and level, add to their xp (and level if needs be), and update their values again

ornate abyss Jul 30, 2018, 12:49 AM

#

@rough river

rough river Jul 30, 2018, 12:57 AM

#

But how could I check if xp is equal to the amount of xp needed to level up

#

Would I have to do if: or i there an easier way

ornate abyss Jul 30, 2018, 2:55 AM

#

An if yes

hallow drum Jul 30, 2018, 10:50 AM

#

I just learnt python's syntax

#

and all these codes you guys are sending me

#

is scaring the shit out of me..

ornate abyss Jul 30, 2018, 12:00 PM

#

???

torn sphinx Jul 30, 2018, 3:42 PM

#

best way to handle alot of pictures without compressing?

ornate abyss Jul 30, 2018, 3:44 PM

#

I'm unsure what you are really asking

#

And why this channel

torn sphinx Jul 30, 2018, 3:44 PM

#

its about databases?

gusty spindle Jul 30, 2018, 3:44 PM

#

how is it about databases?

ornate abyss Jul 30, 2018, 3:48 PM

#

^

gleaming frost Jul 30, 2018, 5:05 PM

#

@torn sphinx Load them as BYTEIO's?

torn sphinx Jul 30, 2018, 7:15 PM

#

https://docs.python.org/3/library/sqlite3.html#default-adapters-and-converters

#

can someone explain this to me?

#

where does that 'current_timestamp' column name even comes from?

patent glen Jul 30, 2018, 7:20 PM

#

current_timestamp is built in to sqlite

torn sphinx Jul 30, 2018, 7:21 PM

#

it doesnt make sense otherwise

#

so it will just yield the current timestamp?

#

I'm completely new to db, what I want to achieve is to store and read a timestamp

patent glen Jul 30, 2018, 7:24 PM

#

sqlite doesn't really have a timestamp type

#

current_timestamp returns a string

#

the section you are reading indicates that you can store datetime objects in the database

torn sphinx Jul 30, 2018, 7:24 PM

#

it clearly has a timestamp type and even a current timestamp if it does this

patent glen Jul 30, 2018, 7:25 PM

#

wait, nevermind

#

ok it's not a real type

#

it's just a tag that tells python how to read it out

#

the as "...[timestamp]" is the important part of that example

torn sphinx Jul 30, 2018, 7:28 PM

#

what does it do?

patent glen Jul 30, 2018, 7:29 PM

#

it sets the type tag, which python then uses to look up the converter that turns it into a python datetime object

#

the value is still stored in the database as a string

torn sphinx Jul 30, 2018, 7:32 PM

#

my c.execute('select p from test as "p [timestamp]"')

#

doesnt make it into an object

patent glen Jul 30, 2018, 7:35 PM

#

you have to have used sqlite3.PARSE_COLNAMES in the connection

torn sphinx Jul 30, 2018, 7:40 PM

#

thanks

patent glen Jul 30, 2018, 7:41 PM

#

PARSE_COLNAMES is for the "[timestamp]" thing, PARSE_DECLTYPES is for the create table types

acoustic flint Jul 30, 2018, 8:10 PM

#

Hey, does anyone use SQLAlchemy

#

I'm wondering if I should have one instance of engine and base in some "database.py" that my classes import from, or if each class should have their own instance

#

if anyone has any input pease @ me

torn sphinx Jul 30, 2018, 9:30 PM

#

i want to create a table with n number of columns, handy way to do that?

torn sphinx Jul 30, 2018, 9:58 PM

#

c.execute('CREATE TABLE options (poll_id, {})'.format(gen_options()))

#

can I do it withotu .format?

ornate abyss Jul 30, 2018, 10:03 PM

#

@torn sphinx @patent glen
"it clearly has a timestamp type and even a current timestamp if it does this"
Sqlite does not have a timestamp datatype, what it has are functions capable of manipulating the other data types

torn sphinx Jul 30, 2018, 10:04 PM

#

of course I meant that it has adapters for datetime

ornate abyss Jul 30, 2018, 10:16 PM

#

But there is not a data type for it

#

Which is what you said

torn sphinx Jul 30, 2018, 10:17 PM

#

yes there is not

#

I started reading about databases today

ornate abyss Jul 30, 2018, 10:19 PM

#

Glad we got that sorted

patent glen Jul 30, 2018, 11:16 PM

#

@torn sphinx To be clear, my point was that the adapter is built into the python sqlite3 library, not sqlite itself. You won't necessarily find it in other languages, and similar features in other languages may not work the same way.

tropic zealot Jul 30, 2018, 11:17 PM

#

Am I able to talk about database schema here?
I wanna talk ideas with people who are here, just in case I'm actually losing my mind on my ideas.

#

Just in case I get a !tag ask, I'm only asking because I don't know if I'm allowed to here.

patent glen Jul 31, 2018, 12:18 AM

#

i don't see why not

#

(we've definitely had discussions of database schemas here and in help channels before as a result of X-Y problems before and no-one's considered it off-topic iirc)

tropic zealot Jul 31, 2018, 12:20 AM

#

Alright, coool.

#

So, I plan on having a database for my Discord bot that will store Dungeons & Dragons character information. I'm fairly certain that I want to store everything per-server, and not let characters find their way into other servers, but I haven't quite decided on it. I do know, however, that I would like certain per-server configurations stored within this database as well. I'm not sure if I should have more than one table to manage each of these. This is my idea for table schema so far:

📎 unknown.png

patent glen Jul 31, 2018, 12:31 AM

#

what kind of database is this?

#

like, sql doesn't have a tree structure like that, but you could consider multiple columns (server id, user id, character id) all as part of a single key

tropic zealot Jul 31, 2018, 12:32 AM

#

This is a rethinkdb database.

patent glen Jul 31, 2018, 12:32 AM

#

ok that's not really something i'm familiar with

#

i know sql

tropic zealot Jul 31, 2018, 12:44 AM

#

RethinkDB is a JSON-based NoSQL database.

ionic pecan Jul 31, 2018, 1:24 AM

#

I guess you could model the tree structure in SQL with foreign keys. characters would have a foreign key to users, for example

#

and users could have a composite pk (guild_id, user_id)

gleaming frost Jul 31, 2018, 11:46 AM

#

Is rethinkdb asynchronous?

#

For use with discord.py?

ornate abyss Jul 31, 2018, 12:38 PM

#

https://rethinkdb.com/docs/async-connections/

ionic pecan Jul 31, 2018, 12:39 PM

#

seems like it only supports tornado and twisted

gleaming frost Jul 31, 2018, 12:40 PM

#

What are they?

viral crag Jul 31, 2018, 12:42 PM

#

wrong.

ornate abyss Jul 31, 2018, 12:42 PM

#

https://www.rethinkdb.com/api/python/set_loop_type/

viral crag Jul 31, 2018, 12:42 PM

#

https://rethinkdb.com/api/python/#set_loop_type

ornate abyss Jul 31, 2018, 12:43 PM

#

Seems you can use twisted, tornado, gevent and asyncio

viral crag Jul 31, 2018, 12:43 PM

#

tornado, twisted, gevent and asyncio, yep

gleaming frost Jul 31, 2018, 12:43 PM

#

Wait i can use asyncio?

#

Ok cool

ornate abyss Jul 31, 2018, 12:43 PM

#

Yep.

gleaming frost Jul 31, 2018, 12:43 PM

#

Wait a sec

#

So using async def

#

?

ornate abyss Jul 31, 2018, 12:43 PM

#

well that just defines a function as async

#

so im not really sure what you mean

gleaming frost Jul 31, 2018, 12:44 PM

#

How do i use asyncio?

#

I haven't really used it

#

So don't understand what it does

torn sphinx Jul 31, 2018, 12:45 PM

#

!help

delicate fieldBOT Jul 31, 2018, 12:45 PM

#

Doc:
  docs        Lookup documentation for Python symbols.
Information:
  roles       Returns a list of all roles and their
  server      Returns an embed full of
Snakes:
  snakes      Commands from our first code jam.
Utils:
  pep         Fetches information about a PEP and sends it to the channel.
No Category:
  help        Shows this message.

Type !help command for more info on a command.
You can also type !help category for more info on a category.

ornate abyss Jul 31, 2018, 12:46 PM

#

Well probably the wrong channel, but asyncio is a library which allows you to setup an event loop, and use coroutines instead of the traditional synchronous programming

gleaming frost Jul 31, 2018, 12:46 PM

#

async with r.connect(db='marvel') as conn:
    await r.table('superheroes').run(conn)```

#

I think that would work?

ornate abyss Jul 31, 2018, 12:47 PM

#

Should do

#

mind ive never used the rethinkdb library, so take that "should do" with a pinch of salt

gleaming frost Jul 31, 2018, 12:48 PM

#

Yeah

#

Will a program always break if something that should be awaited isn't?

ornate abyss Jul 31, 2018, 12:51 PM

#

What do you mean by break?

#

If you mean error, most likely

gleaming frost Jul 31, 2018, 12:53 PM

#

Ok

#

Yeah sorry

ornate abyss Jul 31, 2018, 12:54 PM

#

you dont have to say sorry haha, i just wanted to make sure

#

But you could get some cases like for example, not awaiting asyncio.sleep, it wont technically break anything, it just will return a coroutine and not actually sleep

gleaming frost Jul 31, 2018, 1:00 PM

#

Yeah

#

I mean in code blocking scenarios

#

I don't want to do that and never know

ornate abyss Jul 31, 2018, 1:04 PM

#

You wouldnt block from forgetting to await an async function, you would block if you tried to run a synchronous function in an async setting

#

So using the same example above, if you used time.sleep over asyncio.sleep you would block the whole thing

gleaming frost Jul 31, 2018, 1:04 PM

#

Yeah

ionic pecan Jul 31, 2018, 1:17 PM

#

@viral crag the linked page only described tornado or twisted, not sure how I should figure that other stuff out.

#

@torn sphinx use bot commands in #bot-commands, not here

viral crag Jul 31, 2018, 1:17 PM

#

No it doesn't

#

https://www.rethinkdb.com/api/python/set_loop_type/

#

📎 unknown.png

ionic pecan Jul 31, 2018, 1:18 PM

#

https://rethinkdb.com/docs/async-connections/

#

this one

tropic zealot Jul 31, 2018, 1:19 PM

#

That document tripped me up as well

#

But, apparently, you can just set it and forget it with async, instead going with a web- or network driver.

#

Didn't get around to playing with it last night, though. Too much going on at home, went to sleep early.

torn sphinx Jul 31, 2018, 2:57 PM

#

I'm making a polling feature for my discord bot, it uses a sqlite database, how do I structure the database so I can check if a member has already voted?

ionic pecan Jul 31, 2018, 3:08 PM

#

we're gonna need some more context to help you with that

#

do you have any schema yet? or an idea how you want to lay it out?

torn sphinx Jul 31, 2018, 3:13 PM

#

for now I only have those two tables: polls (author, question, start_date, exp_date ..) and votes(option, vote_count)

ornate abyss Jul 31, 2018, 3:47 PM

#

Where do you store the votes of users?

torn sphinx Jul 31, 2018, 3:55 PM

#

in the database?

viral crag Jul 31, 2018, 3:56 PM

#

You clearly don't

#

You're just storing the count of the votes

#

You have to store each individual vote

torn sphinx Jul 31, 2018, 4:01 PM

#

like

#

how do I do that

ornate abyss Jul 31, 2018, 4:23 PM

#

first you want a foreign key within the votes table to link back to the poll (a user can have more than one vote if there are many polls or even many votes per poll if thats allowed(?)) and then secondly you will need to store the user with the vote else its purely just an anonymous tally of votes

torn sphinx Jul 31, 2018, 4:30 PM

#

is this alright?

📎 db.png

ornate abyss Jul 31, 2018, 4:34 PM

#

I'm unsure what the options table is for

#

And you probably want to store the users ID on the vote

torn sphinx Jul 31, 2018, 4:36 PM

#

aren't they stored?

#

every poll can have many option and every option can have many votes so..

ornate abyss Jul 31, 2018, 4:40 PM

#

Yes, so if you store the user id on the vote then you know who voted

#

And the votes would have a relation with the poll

torn sphinx Jul 31, 2018, 5:39 PM

#

votes have realtion with the poll through options, and I don't really need to know who voted for what, user_id would be discord_id

ornate abyss Jul 31, 2018, 5:42 PM

#

And you probably want to store the users ID on the vote yep ignore that completely missed the user_id

#

As for options, what is stored in there?

clever pebble Jul 31, 2018, 7:11 PM

#

he stores individual choices, options in a poll

#

so in a poll "which letter is the best?" A, B or C are the "options"

ornate abyss Jul 31, 2018, 7:16 PM

#

Ah I see

#

Yea seems alright

sacred heart Jul 31, 2018, 8:19 PM

#

Using sqlite3 when creating a table, what is the difference between PRIMARY KEY and UNIQUE?

patent glen Jul 31, 2018, 8:22 PM

#

@sacred heart several

#

primary keys also can't be null

#

a table can only have one primary key

#

a WITHOUT ROWID table uses the primary key as the main index (i.e. how the rows are organized in the table)

#

look at https://www.sqlite.org/lang_createtable.html for more detailed information

#

conceptually, a primary key is supposed to be the single set of values that characterizes a row in the table

sacred heart Jul 31, 2018, 8:25 PM

#

so it decides how a table is organised?

patent glen Jul 31, 2018, 8:28 PM

#

it can

sacred heart Jul 31, 2018, 9:21 PM

#

im not sure if i still understand sorry, is it for example a main set of values? and the others are secondart

#

*secondary?

young scarab Jul 31, 2018, 9:29 PM

#

hey all, just getting started in sqlite, created a db, added an item and am running a method to check if it has the 'owned' tag set to 1, not sure I'm doing it correctly, could someone breakdown the c.execute line for me?

import sqlite3
from items import items

conn = sqlite3.connect('items.db')

c = conn.cursor()

# c.execute("""CREATE TABLE items (
#         id integer,
#         name text,
#         attack integer,
#         elemental_damage_type text,
#         elemental_damage_amount integer,
#         parry_rate integer,
#         block_rate integer,
#         critical_attack_rate integer,
#         critical_attack_modifier integer,
#         bodypart text,
#         defense integer,
#         description text,
#         equipped integer,
#         owned integer,
#         price integer,
#         type text,
#         subtype text
#         )""")



def item_by_owned(owned):
    c.execute("SELECT * FROM items WHERE owned=:owned", {'owned': 1}) #comment here which each section means please?
    return c.fetchall()


inventory = item_by_owned(1)
print(inventory)

conn.close()

ornate abyss Jul 31, 2018, 9:59 PM

#

c.execute("SELECT * FROM items WHERE owned=:owned", {'owned': 1}) this part isnt right unfortunately

#

Where you want to substitute a value, you place a ?, and you give it a tuple of values you want to pass

#

c.execute("SELECT * FROM items WHERE owned = ?", (1,))

#

@young scarab

#

oh hang on

young scarab Jul 31, 2018, 10:02 PM

#

nice, thanks

ornate abyss Jul 31, 2018, 10:02 PM

#

apologies, i missed the :

#

you're using the named parameters instead

#

Let me rephrase, instead of the ?, you have :name this will map whatever name is to a value within the dictionary that you pass

#

so in your case, :owned, will find the value of owned within the passed dictionary, which for you is 1

#

@young scarab apologies

young scarab Jul 31, 2018, 10:05 PM

#

np, thanks

#

i thought that was it, but wanted to make sure, trying to do things the right way

#

i take it the above is more pythonic than:

c.execute("SELECT * FROM items WHERE owned = 1")

#

though both accomplish the same

ornate abyss Jul 31, 2018, 10:07 PM

#

Well its not the case of being pythonic, its to stop SQL injection

#

If you were you use string formatting, you could end up with a query like so
SELECT * FROM items WHERE owned = 1; DROP TABLE x;

#

with parameter substitution, only one SQL keyword (SELECT, DROP, INSERT) will be ran, so whatever the user passes will be treat as data, and data only

young scarab Jul 31, 2018, 10:09 PM

#

i see, i get it

#

thank you, i really appreciate you going over that with me

ornate abyss Jul 31, 2018, 10:11 PM

#

its all good

daring cairn Aug 1, 2018, 4:52 AM

#

So, using rethinkdb. Is there anyway to order_by an embedded field? So say I have this http://b1nzy-banned.me/B0LqZ.png and I want to create a list of the players total_xp in descending order

vestal apex Aug 1, 2018, 8:31 AM

#

think you could use merge to pull that off https://rethinkdb.com/api/python/merge/

#

@daring cairn

daring cairn Aug 1, 2018, 8:32 AM

#

Hmmmm

vestal apex Aug 1, 2018, 8:33 AM

#

here's a JS API example doing something similar https://stackoverflow.com/questions/33351037/sort-nested-timestamps-in-a-query

Stack Overflow

sort nested timestamps in a query

I need to make a query with a list of Incidents and his nested events ordered DESC by his startedAt and timestamp dates. By default ReQL give the dates with a ASC order. I've got the folowing struc...

daring cairn Aug 1, 2018, 8:34 AM

#

Alright I'll give it a shot in a few here, thanks

torn sphinx Aug 1, 2018, 11:10 AM

#

Anyone good with psql and flask-sqlalchemy?
I’ve got a model in sqlalchemy info=column(Binary() nullable=False) how do I create this table in psql? I was thinking create table test( info varchar(1000) NOT NULL

#

Pretty sure that’s wrong

ornate abyss Aug 1, 2018, 12:12 PM

#

SQLalchemy should make the tables for you

torn sphinx Aug 1, 2018, 12:25 PM

#

How

torn sphinx Aug 1, 2018, 12:55 PM

#

Never mind

#

I used oid to do it

torn sphinx Aug 1, 2018, 3:04 PM

#

ok I don't get it

#

what syntax do I need to use in order to get a datetime.datetime object from sqlite3

ornate abyss Aug 1, 2018, 3:16 PM

#

There is something within the sqlite3 module for python, so you do like so

#

c = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = c.cursor()
cur.execute("CREATE TABLE x (time timestamp)")

cur.execute("INSERT INTO x VALUES (?)", (datetime.datetime.now(),))

cur.execute("select time from x")
row = cur.fetchone()

#

And then row is a tuple containing the datetime

#

@torn sphinx

torn sphinx Aug 1, 2018, 3:20 PM

#

I think the only thing I have different is that I have additional 'sqlite3.PARSE_COLNAMES |'

ornate abyss Aug 1, 2018, 3:25 PM

#

If you run that ^, it returns a datetime object

noble anchor Aug 1, 2018, 6:13 PM

#

How would you write a MySql query to return the second purchase item per each customer? Excluding customers who purchased only 1 time

ornate abyss Aug 1, 2018, 6:19 PM

#

So do you want all items from people who bought more than one item?

#

Or only the second item from people who bought 2 or more items?

noble anchor Aug 1, 2018, 6:20 PM

#

second item from people who purchased at least 2 times.

ornate abyss Aug 1, 2018, 6:23 PM

#

Okay, so you would so something like this

#

SELECT rows FROM table WHERE customer = some_id ORDER BY id LIMIT 1, 1

#

Where, rows are the rows you want, table is your table name, customer is the column which relates to the customer, some_id is the customer you are searching for, id is the primary key of the items

#

Actually i didnt account for more than one item

#

oh wait nvm, the offset will just return nothing

noble anchor Aug 1, 2018, 6:27 PM

#

I was thinking similarly.. but it would only give me 1 row instead of all the customers

ornate abyss Aug 1, 2018, 6:34 PM

#

Hm

patent glen Aug 2, 2018, 4:29 AM

#

@noble anchor something like, sql select c.*, p4.* from customer inner join purchase p4 on p4.cust = c.id inner join ( select p1.customer_id, min(p2.seq) seq from purchase p1 inner join purchase p2 on p1.cust = p2.cust and p1.seq < p2.seq group by p1.customer_id) p3 on p3.cust = c.id and p3.seq = p4.seq

#

that's probably horrifying and low-performance, but i'm not sure there's a good way to get what you want

gleaming frost Aug 2, 2018, 7:18 AM

#

How do you view rethinkdbs files on server save?

tacit dagger Aug 2, 2018, 5:53 PM

#

Is there a way to write symbols like these in SQLite db? when i copy them and put them in db, they change

📎 Capture.PNG

patent glen Aug 2, 2018, 5:54 PM

#

@tacit dagger most of them are probably unicode, go to character map and scroll down with, like, Arial Unicode MS selected as the font

#

it looks like you're looking at, like, wingdings, now, which won't work

#

📎 unknown.png

tacit dagger Aug 2, 2018, 5:57 PM

#

ah thanks 😃

patent glen Aug 2, 2018, 5:58 PM

#

or go to, like, unicode websites

#

and search for the character you want

tacit dagger Aug 2, 2018, 6:04 PM

#

👍

wispy fable Aug 2, 2018, 9:02 PM

#

(wingdings is a font that just has symbols replacing all the regular characters)

unborn sentinel Aug 3, 2018, 1:05 PM

#

Dumb question, is it normal to do sqlite3 connections using with or is that considered bad form?

little wave Aug 3, 2018, 1:06 PM

#

its probably fine

ornate abyss Aug 3, 2018, 1:16 PM

#

@unborn sentinel You can use the connection with a context manager, it will automatically commit the changes if it was successful or it will rollback if an exception is raised

#

so to answer your question, its perfectly fine

unborn sentinel Aug 3, 2018, 1:16 PM

#

Oh that's actually really helpful then

#

I always forget to add commit

ornate abyss Aug 3, 2018, 1:17 PM

#

yea its nice, mind if an exception is raised, you still need to catch it, so just be aware of that

unborn sentinel Aug 3, 2018, 1:17 PM

#

-nods- Right

#

Thanks, that gives me a bit more confidence in working with this prototype

ornate abyss Aug 3, 2018, 2:24 PM

#

It's all good

torn sphinx Aug 3, 2018, 4:04 PM

#

how do I start using postgres?

broken linden Aug 3, 2018, 4:10 PM

#

you install it and do operation son it with SQL using either a dedicated console or some postgres connector for a programming lang

#

or what do you mean

torn sphinx Aug 3, 2018, 4:12 PM

#

I cant get it to work at all

#

I do sudo -u postgres -i

#

then initdb -D '/var/lib/postgres/data'

#

then it throws permission error for some reason

#

ffs

summer sable Aug 3, 2018, 5:51 PM

#

@noble anchor derive a table that selects only people with a count of > 1 row with their ID in it. Then from that select with a LIMIT of 2, and sort the derivsed results in reverse order, then select from that newly derived table a LIMIT 1

#

should end up with only 2nd position entries from people with 2 or more entries

#

so, SELECT rows FROM (SELECT rows FROM table WHERE peoplehavemultiplerows ORDER BY thenumber ASC LIMIT 2) ORDER BY thenumber DESC LIMIT 1

ornate abyss Aug 3, 2018, 6:00 PM

#

@summer sable I couldnt see that working

#

peoplehavemultiplerows how do you get this section without a GROUP BY, and the LIMIT 1 on the end of the query will limit the whole thing i would imagine

summer sable Aug 3, 2018, 6:03 PM

#

it would probably require a second nested derived table, yeah

#

but no, the limits definitely work locally, i've done that before

#

it will limit your end results to 1 overall, but that's what you want

#

oh wait i see what you mean, yes it won't work for a whole group of people hmm

patent glen Aug 3, 2018, 6:06 PM

#

SELECT cust.*, orders.* FROM cust JOIN orders ON cust.cust_id = orders.cust_id
WHERE orders.order_id in (SELECT order_id FROM orders o2 WHERE o2.cust_id = cust.cust_id ORDER BY o2.order_id limit 1, 1)```

ornate abyss Aug 3, 2018, 6:06 PM

#

he doesnt want 1 row overall tho

summer sable Aug 3, 2018, 6:07 PM

#

yeah i caught on just above

ornate abyss Aug 3, 2018, 6:07 PM

#

else what i said would have worked :)

patent glen Aug 3, 2018, 6:14 PM

#

@noble anchor ```sql
CREATE TABLE cust(cust_id INTEGER PRIMARY KEY);
CREATE TABLE "order"(order_id INTEGER PRIMARY KEY, cust_id INTEGER);
INSERT INTO cust VALUES(1);
INSERT INTO cust VALUES(2);
INSERT INTO cust VALUES(3);
INSERT INTO cust VALUES(4);
INSERT INTO "order" VALUES(101, 1);
INSERT INTO "order" VALUES(102, 1);
INSERT INTO "order" VALUES(103, 1);
INSERT INTO "order" VALUES(202, 2);
INSERT INTO "order" VALUES(401, 4);
INSERT INTO "order" VALUES(402, 4);
SELECT c., o. FROM "cust" c JOIN "order" o ON c.cust_id = o.cust_id
WHERE o.order_id in
(SELECT order_id FROM "order" o2 WHERE o2.cust_id = c.cust_id ORDER BY o2.order_id limit 1, 1);

1|102|1
4|402|4

#

full example to show this technique works

#

and here's the one I posted yesterday, adapted to this sample table schema - also works ```sql
select c., o4.
from cust c
inner join "order" o4 on o4.cust_id = c.cust_id
inner join (
select o1.cust_id, min(o2.order_id) order_id
from "order" o1
inner join "order" o2
on o1.cust_id = o2.cust_id and o1.order_id < o2.order_id
group by o1.cust_id) o3
on o3.cust_id = c.cust_id and o3.order_id = o4.order_id

ornate abyss Aug 3, 2018, 6:18 PM

#

that is one interesting query

#

Whats with the quotes on table name?

patent glen Aug 3, 2018, 6:19 PM

#

order is a keyword

ornate abyss Aug 3, 2018, 6:19 PM

#

ah of course

ionic pecan Aug 3, 2018, 6:19 PM

#

sql differentiates between single and double quotes doesn't it?

#

or is that different from database to database

patent glen Aug 3, 2018, 6:20 PM

#

officially

#

double quotes are for column names and table names

#

single quotes are for strings

#

I think sqlite blurs the lines a little bit

ionic pecan Aug 3, 2018, 6:20 PM

#

ah I see

#

I think I've also seen ` before

patent glen Aug 3, 2018, 6:20 PM

#

` is for table names in mysql

#

[] is for table names in MS sql

#

" is ANSI standard

ionic pecan Aug 3, 2018, 6:20 PM

#

"standards" 😛

ornate abyss Aug 3, 2018, 6:21 PM

#

[S]ingle quote for [S]trings, [D]ouble quote for things in the [D]atabase

#

i remember reading that on a stackoverflow post once and it stuck with me

ionic pecan Aug 3, 2018, 6:21 PM

#

oh thats a good thing to remember 👍

patent glen Aug 3, 2018, 6:21 PM

#

sqlite holds those interpretations, but lets you use the other one if it's unambiguous

#

select 'foo' from bar is always a string
select "foo" from bar is a string only if there is no foo column.
select foo from 'bar' is legal and refers to the table because you can't select from a string
select `foo` from bar always refers to a foo column
select [foo] from bar always refers to a foo column

ornate abyss Aug 3, 2018, 6:25 PM

#

select id from 'meal'; is not legal for mysql

#

neither is the []

patent glen Aug 3, 2018, 6:25 PM

#

right, sqlite is unusually permissive here

#

i'd guess that select "foo" won't give you a string either, if there's no foo column

#

wait huh it does

#

so mysql is more permissive than, say, oracle

ornate abyss Aug 3, 2018, 6:27 PM

#

I cant say ive used oracle so ill take your word

patent glen Aug 3, 2018, 6:28 PM

#

i use it at work and just tried it

ornate abyss Aug 3, 2018, 6:28 PM

#

👌

gleaming frost Aug 3, 2018, 9:20 PM

#

I'm mucking around with rethinkdb after using JSON files for my discord bot

#

I have multiple problems:

Is it better for rethink to handle all the searches for data in the file, or is it more effective to let python handle the searching

#

What's the best way to get it to generate empty data sets for me to pass dicts in later?

#

If you have more questions about what I'm doing, just @ me

viral crag Aug 3, 2018, 9:28 PM

#

you should offload as much processing to the database as you can

#

after all, that is what the database is best at

#

it's worth noting that even using rethinkdb's lambda filtering stuff will still run the lambda on the server

#

it parses the AST and translates it to something the query engine can use

ionic pecan Aug 3, 2018, 9:34 PM

#

that sounds pretty advanced

viral crag Aug 3, 2018, 9:43 PM

#

it's pretty neat actually

#

must've been a pain to write

glass bough Aug 4, 2018, 4:55 AM

#

so how exactly would I store ip addresses into a database?

#

or I guess, what kind of data type would I do?

#

assuming I'm using 1pv4

#

doing something like this: for i in c.execute("SELECT * FROM tableWHERE IP = {}".format(ip)):

#

gives this error: sqlite3.OperationalError: near ".0": syntax error

dull scarab Aug 4, 2018, 5:00 AM

#

You really shouldn't use string formatting for your queries

glass bough Aug 4, 2018, 5:00 AM

#

yea, but still, it was just an example

dull scarab Aug 4, 2018, 5:02 AM

#

You can store the ip as an int, or text.

glass bough Aug 4, 2018, 5:03 AM

#

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

#

the length of the string that happens to be the ip is 9

dull scarab Aug 4, 2018, 5:03 AM

#

What did you try?

glass bough Aug 4, 2018, 5:03 AM

#

for i in c.execute("SELECT * FROM sessions WHERE IP = ?", ip):

dull scarab Aug 4, 2018, 5:04 AM

#

and ip is a string?

glass bough Aug 4, 2018, 5:04 AM

#

yes

#

like 127.0.0.1

dull scarab Aug 4, 2018, 5:05 AM

#

What type is IP in your db

glass bough Aug 4, 2018, 5:05 AM

#

I've tried changing it between text and int

dull scarab Aug 4, 2018, 5:06 AM

#

oh right, wrap your argument in a tuple ... WHERE IP = ?", (ip, )):

glass bough Aug 4, 2018, 5:09 AM

#

thank you

#

forgot about that

dull scarab Aug 4, 2018, 5:09 AM

#

Fixed it?

glass bough Aug 4, 2018, 5:09 AM

#

yep

dull scarab Aug 4, 2018, 5:09 AM

#

👌🏾

waxen pawn Aug 4, 2018, 4:29 PM

#

is Sqlite3 good for managing database for a Sells Management Program ?

versed coyote Aug 4, 2018, 6:09 PM

#

No, you should use an actual DBMS instead, like Postgre or mysql

ionic pecan Aug 4, 2018, 6:25 PM

#

most "is sqlite3 good for ..." questions can probably be answered pretty well by their own page "Appropriate Uses For SQLite": https://www.sqlite.org/whentouse.html

#

however, for any commercial or larger-scale program, I'd just do what KnownError suggested

noble imp Aug 4, 2018, 8:13 PM

#

So over the past month, I've blown through introductory python books and videos and I feel pretty comfortable with it now. I'm interested in learning about Data structures and networking stuff, does anyone know of a series of videos that I could checkout? I don't mind books either.

#

I've tried looking stuff up about the topic but it seems like anytime I google what something is, I need to know what 10 other things are just to understand it. I there a linear way I can build up to these things?

versed coyote Aug 4, 2018, 9:13 PM

#

If you're interested in learning networking, this playlist is good: https://www.youtube.com/playlist?list=PLmdYg02XJt6QRQfYjyQcMPfS3mrSnFbRC

YouTube

CCNA/CCENT Training Series (200-120 CCNA, 100-101 ICND1, 200-101 I...

A full course of 84 videos for CCNA and CCENT Routing and Switching taught by Cisco Instructor Andrew Crouthamel. ► Purchase the videos (84 total!) in MP4 fo...

#

Just ignore/skip the Cisco/CCNA parts

#

Also this discussion is rather off topic for #databases

#

Honestly wish we had a networking channel

#

But we don't, so one of the off topic channels would suffice

noble imp Aug 4, 2018, 9:34 PM

#

Thank you! This is exactly something I was looking for. Sorry about that, didn't know where to ask.

versed coyote Aug 4, 2018, 9:36 PM

#

No worries

tropic zealot Aug 4, 2018, 10:05 PM

#

I seem to be encountering a problem with the rethinkdb database driver.

I'm attempting to set the loop type of the module to asyncio, as shown in the documentation,

r.set_loop_type("tornado")
conn = r.connect(host='localhost', port=28015)```
but when I do, I get this error:

```Traceback (most recent call last):
  File "C:/Users/Vincent/Desktop/Python/Projects/DnDBot/bot.py", line 8, in <module>
    r.set_loop_type('asyncio')
  File "C:\Users\Vincent\AppData\Local\Programs\Python\Python37-32\lib\site-packages\rethinkdb\net.py", line 677, in set_loop_type
    module = imp.load_module('rethinkdb.' + moduleName, moduleFile, pathName, desc)
  File "C:\Users\Vincent\AppData\Local\Programs\Python\Python37-32\lib\imp.py", line 235, in load_module
    return load_source(name, filename, file)
  File "C:\Users\Vincent\AppData\Local\Programs\Python\Python37-32\lib\imp.py", line 172, in load_source
    module = _load(spec)
  File "<frozen importlib._bootstrap>", line 696, in _load
  File "<frozen importlib._bootstrap>", line 677, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 724, in exec_module
  File "<frozen importlib._bootstrap_external>", line 860, in get_code
  File "<frozen importlib._bootstrap_external>", line 791, in source_to_code
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "C:\Users\Vincent\AppData\Local\Programs\Python\Python37-32\lib\site-packages\rethinkdb\asyncio_net\net_asyncio.py", line 114
    asyncio.async(self.conn._parent._continue(self))
                ^
SyntaxError: invalid syntax```

viral crag Aug 4, 2018, 10:07 PM

#

you're using python 3.7

#

@tropic zealot

#

that's your problem

tropic zealot Aug 4, 2018, 10:07 PM

#

...so I can't use rethink with 3.7?

viral crag Aug 4, 2018, 10:09 PM

#

you'll find plenty of things you can't use with 3.7

tropic zealot Aug 4, 2018, 10:10 PM

#

notlikethis

#

oh this is so sad

#

this is really sad

pure cypress Aug 4, 2018, 10:10 PM

#

seems to be the relevant issue

#

https://github.com/rethinkdb/rethinkdb/issues/6690

GitHub

Python3.7. asyncio.async() function is deprecated, use ensure_futu...

Python3.7 don't support asyncio.async() need replace with a asyncio.ensure_future(). rethinkdb/drivers/python/rethinkdb/asyncio_net/net_asyncio.py Line 136 in d602ca0 asyncio.async(self.conn._p...

tropic zealot Aug 4, 2018, 10:37 PM

#

That was the fix.

#

Fixed the version that I'm on; guess I'll have to include it in the bot's files.
Thanks.

chilly mesa Aug 5, 2018, 1:22 AM

#

Hello all, i open i db file with hex editor and i notice the PΠ in the beggining. What type of db is this?

versed coyote Aug 5, 2018, 4:54 AM

#

Try running the file command on it

#

It can identify a lot of file types using a list of magic numbers

pure cypress Aug 5, 2018, 4:58 AM

#

@versed coyote turns out he was trying to open thumbs.db 😄

versed coyote Aug 5, 2018, 4:58 AM

#

xD

#

Nah

#

Pretty sure that's not the OLE magic number

#

http://forensicswiki.org/wiki/OLE_Compound_File

pure cypress Aug 5, 2018, 5:17 AM

#

if i understood him correctly he said the file was called thumbs.db

#

he just didnt mention that at the beginning

#

started off saying that it has a .db extension and was a hidden file

versed coyote Aug 5, 2018, 5:20 AM

#

Ah

#

I probably decoded the hex wrong then

pure cypress Aug 5, 2018, 5:20 AM

#

i tried too

#

it doesnt match up

#

but who knows how his text editor interpreted the file

versed coyote Aug 5, 2018, 5:21 AM

#

Oh yeah didn't think about that

#

It probably tried to guess the encoding

pure cypress Aug 5, 2018, 5:21 AM

#

yup

#

i suggested opening it in a hex editor but that never ended up happening

#

anyway he seemed to drop it once he realised it was just thumbs.db

versed coyote Aug 5, 2018, 5:22 AM

#

Yeah...

torn sphinx Aug 5, 2018, 3:11 PM

#

I just deleted my database

#

and I made new one

#

and it isn't coming up

#

any help

#

Cause I am using the DB Browser for SQL

#

But I try open the database but it won't do it

#

cause there is no database

#

but all my database commands work

torn sphinx Aug 5, 2018, 4:04 PM

#

@dull scarab

#

conn = sqlite3.connect('Coins.db', isolation_level=None)
c = conn.cursor()

conn.execute("""CREATE TABLE IF NOT EXISTS Users(
                            UserID TEXT,
                            Coins INTERGER)""")

#

All my database commands work

#

It is just I wanted to create a new database

dull scarab Aug 5, 2018, 4:05 PM

#

And it's not creating a file?`

torn sphinx Aug 5, 2018, 4:05 PM

#

Nope

#

I even ran the bot

dull scarab Aug 5, 2018, 4:06 PM

#

If your commands are working, surely it's creating it somewhere

torn sphinx Aug 5, 2018, 4:07 PM

#

hm?

dull scarab Aug 5, 2018, 4:07 PM

#

Does the commands, that use the database, work?

median lava Aug 5, 2018, 6:46 PM

#

I'm using PostgreSQL and the following

SELECT banks.bankname, banks.logininformation, accounts.accountid FROM banks INNER JOIN accounts ON accounts.bankid=banks.id WHERE banks.scriptname = 'banking_scrape.banks.paypal';

is throwing an error. I assure you the column does exist!

[2018-08-05 19:45:06] [42703] ERROR: column accounts.bankid does not exist
[2018-08-05 19:45:06] Hint: Perhaps you meant to reference the column "accounts.bookid".

#

Nevermind. Was looking at the wrong schema.

torn sphinx Aug 5, 2018, 8:05 PM

#

Yes @dull scarab sorry for late answer

dull scarab Aug 5, 2018, 8:06 PM

#

Then you must have a db file somewhere, unless it's magically referring to a memory db

#

I'd love to help you figure it out, but I was just getting off :(

torn sphinx Aug 5, 2018, 8:07 PM

#

Okay thnx for help

#

maybe next time

dull scarab Aug 5, 2018, 8:07 PM

#

Try reposting the question, maybe someone else can help

torn sphinx Aug 5, 2018, 8:09 PM

#

Okay

hybrid raven Aug 5, 2018, 8:48 PM

#

I'm using SQLite3, and I have a bunch of guild id's, how would I get a value from that id?

#

Like, there's a column called id's and I'm trying to get the row with a specific value

#

(sorry, im new to this)

ornate abyss Aug 5, 2018, 8:49 PM

#

SELECT rowname FROM table WHERE rowname = specific id

#

Sorry for no markdown, on phone

hybrid raven Aug 5, 2018, 8:49 PM

#

ah, thank you

ornate abyss Aug 5, 2018, 8:49 PM

#

Np

hybrid raven Aug 5, 2018, 9:10 PM

#

how do i make sqlite get a value only if it exists?

patent glen Aug 5, 2018, 9:12 PM

#

what do you mean by exists

hybrid raven Aug 5, 2018, 9:13 PM

#

nvm

patent glen Aug 5, 2018, 9:16 PM

#

if a row doesn't exist, fetchall will return an empty list or fetchone will return None

hybrid raven Aug 5, 2018, 11:03 PM

#

I'm trying to make a change prefix command

    @has_permissions(manage_guild=True)
    @edit.command()
    @guild_only()
    async def prefix(self, ctx, prefix: clean_content):

        if len(prefix) > 5:
            return

        conn = sqlite3.connect('guilds.db')
        c = conn.cursor()

        c.execute(f"REPLACE INTO guilds (id, prefix) VALUES ({ctx.guild.id}, '{prefix}');")
        
        conn.commit()
        conn.close()

        await ctx.send(f":settings: Changed prefix to `{prefix}`")

I have this code, but whenever I run it, it sends the confirmation message at the end, but it doesn't actually change the prefix. Why is that?

ornate abyss Aug 5, 2018, 11:09 PM

#

Replace is for strings, what you want is UPDATE

#

@hybrid raven

hybrid raven Aug 5, 2018, 11:12 PM

#

ah, ok

#

would it be c.execute(f"UPDATE guilds (id, prefix) VALUES ({ctx.guild.id}, '{prefix}');")?

ornate abyss Aug 5, 2018, 11:13 PM

#

Not quite no

#

c.execute("UPDATE guilds SET prefix = ? WHERE id = ?", (prefix, ctx.guild.id))

hybrid raven Aug 5, 2018, 11:14 PM

#

ah, ok

#

that makes sense

ornate abyss Aug 5, 2018, 11:14 PM

#

You should never use string formatting with SQL queries by the way

hybrid raven Aug 5, 2018, 11:15 PM

#

why?

ornate abyss Aug 5, 2018, 11:15 PM

#

Leaves you wide open to SQL injection

hybrid raven Aug 5, 2018, 11:15 PM

#

oh ok

#

thanks!

ornate abyss Aug 5, 2018, 11:24 PM

#

all good