#databases | Python | Page 60

torn sphinx Feb 25, 2019, 1:32 AM

#

it ok

torn sphinx Feb 25, 2019, 4:38 AM

#

What would the best database be if I wanted to make a discord economy bot?

tiny hazel Feb 25, 2019, 5:11 AM

#

That depends entirely on what your bot will do. Will it need to handle a heavy stream of constant of data? What size data is it going to store? Do you need concurrent read/write access? Will it be doing heavy aggregations regularly? Do you want something open source or proprietary?

Depending on your answers, you could be looking at MySQL, SQLite, Mongo DB, SQL Server, BigQuery, or any number of options. They all have their merits, it just depends on what you're looking for.

glad bobcat Feb 25, 2019, 6:27 AM

#

I have an SQL alchemy object with relationships cleanly defined, and I would like to eager load its children, but only certain fields of them
I managed to do it with one children with:

games_query = session.query(sp.EsportsGame).options(load_only("gameId")) \
    .options(joinedload('participants')
             .load_only('championId', 'teamId', 'win'))```
But then when I tried to do a second joinedload to do the same for participants children, I get:
```AttributeError: '_UnboundLoad' object has no attribute 'options'```
```python
games_gold_data_query = session.query(RankedGame).options(load_only('gameId')) \
    .options(joinedload('participants')
             .load_only('teamId')
             .options(joinedload('frames')))```

#

I managed to get it to work with:

query = session.query(RankedGame, RankedGameParticipant, RankedGameParticipantFrame) \
    .join(RankedGameParticipant).join(RankedGameParticipantFrame)
    .options(
        Load(RankedGame).load_only('gameId'),
        Load(RankedGameParticipant).load_only('teamId'),
        )

#

but isn't there a cleaner way? :/

glad bobcat Feb 25, 2019, 6:44 AM

#

I found like a hundred ways to query the data, but I'd want the most convenient: keep my python structure only loads the field I need in children, while still being able to iterate on them

glad bobcat Feb 25, 2019, 7:06 AM

#

Ok I found the right syntax for the fastest loading of the fields I'm interested in, and now I have another issue I find absolutely no help with. Does anybody know how to only load childs with a specific value? Here is my current query:

games_gold_data_query = session.query(RankedGame) \
    .options(selectinload(RankedGame.participants).selectinload(RankedGameParticipant.frames),
             Load(RankedGame).load_only('gameId'),
             Load(RankedGameParticipant).load_only('teamId', 'win'),
             Load(RankedGameParticipantFrame).load_only('totalGold', 'xp', 'timeStamp')) \
    .limit(100)

#

I'd like to only load frames with a certain timestamp

torn sphinx Feb 25, 2019, 3:20 PM

#

Any reason as to why conn = await asyncpg.connect(user='', password='',database='keywords', host='') pnums = await conn.fetch("SELECT pnum FROM keywords WHERE user = $1" , user) print(pnums) returns a Empty List, when the user value in the database is still there?

#

P.S: The word (which isn't shown here) is the Primary Key in this case

dull scarab Feb 25, 2019, 3:48 PM

#

what does the table look like?

#

if you do print(await conn.fetch("SELECT * FROM keywords;")

torn sphinx Feb 25, 2019, 3:51 PM

#

I found out the error

#

I did "user" instead of "userid" which is what it should have been

odd cloud Feb 26, 2019, 2:02 AM

#

hello all! what is the best way to secure an api that accepts post information from a user for their acct? for example, the url contains the api key, but couldn't anyone who has access to that passing data intercept the key and just use it themselves? better to send key in post? Thanks guys/gals

#

additionally, should i have some other credential piece to authorize with it

patent glen Feb 26, 2019, 2:03 AM

#

if you're concerned about interception, use SSL

odd cloud Feb 26, 2019, 2:03 AM

#

but does it hurt to send the api key through url if it's a post

patent glen Feb 26, 2019, 2:04 AM

#

the post isn't any harder to intercept than the url

odd cloud Feb 26, 2019, 2:04 AM

#

ah ok

#

like for instance, if i'm at a router level, can i read the urls passing through if ussing ssl

patent glen Feb 26, 2019, 2:04 AM

#

the url might be slightly more likely to leak through other means like browser history of get-based test requests, but not interception.

odd cloud Feb 26, 2019, 2:05 AM

#

good point

patent glen Feb 26, 2019, 2:05 AM

#

no - the url is protected in SSL (except for the domain name in some cases)

odd cloud Feb 26, 2019, 2:05 AM

#

that's definitely something i haven't considered, i think i'll change it to post

#

should i use another auth piece to ensure security?

patent glen Feb 26, 2019, 2:06 AM

#

you could use digest authentication, with the api key as the password

odd cloud Feb 26, 2019, 2:06 AM

#

this would be for a little reporting daemon btw

#

so i wouldn't be entering the extra auth info each time

#

not by hand anyways

patent glen Feb 26, 2019, 2:08 AM

#

if you're concerned about interception but can't use ssl, digest authentication is probably better than nothing (but keep in mind a malicious router could still modify a non-ssl request in flight)

odd cloud Feb 26, 2019, 2:26 AM

#

i can use ssl, that's no problem

#

thanks for the insight

undone apex Feb 26, 2019, 4:00 AM

#

Hey! I am hosting my webserver using IONOS, and am currently trying to connect to it using docsql. IONOS provided a custom host/server name to me, but every time I try to connect I get the error "no such host". I have tried changing tcp to all other protocols and it just says "unknown network transfer type". What am I doing wrong?

#

(have also tried to change the host to localhost, did not work)

undone apex Feb 26, 2019, 4:52 AM

#

found the answer. ionos doesn't allow direct connections from myself to the db.

torn sphinx Feb 26, 2019, 4:53 AM

#

Can unicode emojis be put into a sqlite table?

pine pivot Feb 26, 2019, 4:59 AM

#

📎 unknown.png

#

http://baldi.me/blog/emoji-in-sql

Emoji in SQL - SELECT 🗣 FROM 👤 - Cristian Baldi - Web Develo...

I had a doubt, can you use Emoji in SQL? Turns out you can, and it works very well.

#

so, yes

undone apex Feb 26, 2019, 5:58 AM

#

Hello. I have coded myself into a bit of a problem here. I have a sql server that is hosted by a third party, that does not allow direct connections, meaning I can't just query the data from python code on my machine. I would have to write python code that goes to my webserver, have php get my sql data, and then return it to my python code. Is this possible? If so, what is this called? I don't even know where to begin googling this issue. Thanks! (posted this in #help-coconut as well)

worthy prism Feb 26, 2019, 10:09 AM

#

Make a php api

torn sphinx Feb 26, 2019, 2:35 PM

#

For postgresql i set the word as the primary ket. Does that mean that duplicate words can't exist in the database?

ionic pecan Feb 26, 2019, 6:56 PM

#

this is missing a lot of context

#

but yes, primary keys tend to be unique by definition

torn sphinx Feb 26, 2019, 7:35 PM

#

oof ok

#

Thanks

rain field Feb 26, 2019, 8:56 PM

#

Will this work if my db name is pointage, and i have 2 columns( discordID and points)


@bot.event
@commands.has_role('Admin')
async def loadmembres(ctx):

        import mysql.connector

        mydb= mysql.connector.connect(
            host = "localhost"
            user= "cashcash_kygo"
            passwd = "---"
            database = "cashcash"
            )
        
        cursor = mydb.cursor()

        role = ctx.guild.get_role(543940187525349387)
        for member in ctx.guild.members:
        if role in member.roles:
            cursor.execute(""" INSERT INTO `pointage` (`DiscordID`, `Points`) VALUES (%s,%s)""" %(member.Id, 0))

#

(I haven't closed the connection yet ik)

#

Anyone?

ionic pecan Feb 27, 2019, 7:27 PM

#

most "will this work" questions can answer themselves if you test it out yourself

#

did you try that?

#

also you should never interpolate sql statements with %

lusty carbon Feb 27, 2019, 8:31 PM

#

On the above, may I ask why not?

ionic pecan Feb 27, 2019, 8:33 PM

#

because passing in arbitrary user input can mess up your database pretty badly

patent glen Feb 27, 2019, 8:34 PM

#

anyway, for mysql.connector, what you should be doing instead is almost the same as that: py cursor.execute(""" INSERT INTO `pointage` (`DiscordID`, `Points`) VALUES (%s,%s)""", (member.id, 0))

#

other database engines sometimes make you use $1, ?, or :name instead

#

anyway, since member id isn't under user control, this particular case would be fine, but it's a bad habit to get into

lusty carbon Feb 27, 2019, 8:45 PM

#

Thank you for the explanation 😃

rain field Feb 27, 2019, 8:50 PM

#

Thanks

torn sphinx Feb 27, 2019, 10:41 PM

#

I'd like to have a mongoDB collection for server config settings for a discord bot, where each server has its own document. I don't really want to query every time i want to check a server's config, so my guess is I should have a cache (either a dict or list?) and query the db only when the config changes or a document isn't in the cache.

#

Does anyone know the best way to do this?

torn sphinx Feb 28, 2019, 2:50 AM

#

yo, i need help plz cx

odd cloud Feb 28, 2019, 2:51 AM

#

hey all, i'm not able to get tzlocal to work. ModuleNotFoundError: No module named 'tzlocal' after pip install. any thoughts?

torn sphinx Feb 28, 2019, 6:46 AM

#

i decided on using LRUCache from cachetools PepoThumbsUp

delicate fieldBOT Feb 28, 2019, 7:25 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.

serene slate Feb 28, 2019, 8:54 AM

#

Hi, I am using MySQLdb lib to connect to and submt data to a MySQL database (incase it matters I believe it is InnoDB)
I have lots of connections open to this DB but I want to know which causes the least strain on the DB.

Open a connection to the DB,
Loop over data, on each loop:

Get data, process data, submit data, commit change.
Close DB connection.

Loop over data:

Get Data, Process Data, Open Connection, Submit Data, commit change, close connection

Open connection
Loop over data:

Get data, process data, submit data
After looping is done, commit and close.

How would you do it?

opaque palm Feb 28, 2019, 9:07 AM

#

I'd use 3. but you could always time each. whichever is quickest is likely the least strain?

willow quiver Feb 28, 2019, 9:07 AM

#

Hii, I am stuck at an hackthon alone! i need help with anything with raspi

#

I have a picam and am trying to do object detection but it stopped working

#

so I need to change it rn

#

I just got 14 hrs more

opaque palm Feb 28, 2019, 9:09 AM

#

This doesnt seem like it has anything to do with databases ? i think you'll get more help in the #microcontrollers channel

willow quiver Feb 28, 2019, 9:09 AM

#

thanks 😃 I ll go there

opaque palm Feb 28, 2019, 11:17 AM

#

Hi all,
What library do you recommend when working with adodb sql server ?

torn sphinx Feb 28, 2019, 12:02 PM

#

Hey guys, anyone here know postgres?

#

How can i restore a file.postgres with postgres?

final mason Feb 28, 2019, 4:22 PM

#

I'm writing some python code that will take user's order and put them in an SQL database. I need an ID for each order number, but what's the best way to make that id?

odd cloud Feb 28, 2019, 6:08 PM

#

by not using a pk

#

by auto incrementing it

#

is that what you mean?

#

or do you mean, that you want to auto create the id.

#

in which case auto_inc true

#

anyone here work with timezone a lot?

final mason Feb 28, 2019, 7:20 PM

#

Auto-increment would be totally fine, I've just never done it before

copper sphinx Feb 28, 2019, 10:48 PM

#

@torn sphinx pipe it to the cli utility.

#

IF it's a SQL file

#

Otherwise pgrestore

raw onyx Mar 1, 2019, 1:45 AM

#

i get this error with my sqlite3 command
sqlite3.OperationalError: near "WHERE": syntax error

c.execute('SELECT * FROM currency WHERE id=? AND WHERE other_id=?', (id, other_id))

im wondering if it's because of the first WHERE or at the AND WHERE

#

well, i figured out that you can do

c.execute('SELECT * FROM currency WHERE id=? ', (id, ))
c.execute('SELECT * FROM currency WHERE other_id=?', (other_id, ))

if anybody can tell me how i could shorten this (where i used the AND operator), please ping me

slate spire Mar 1, 2019, 3:04 AM

#

you don't need the 2nd WHERE

#

it's just WHERE id=? AND other_id=?

raw onyx Mar 1, 2019, 3:15 AM

#

i did that a while ago after some more research, and it worked like a charm

#

thanks!

slate spire Mar 1, 2019, 3:23 AM

#

no probs, apologies for the late response

prime prism Mar 1, 2019, 3:06 PM

#

Should I switch from running local MySQL to LiteSQL for my data fetching scripts? Is it slower than running local service?

rain field Mar 1, 2019, 8:30 PM

#

When I insert a row with the following id

#

453660706492121088

#

it inserts this inside my table

#

2147483647

#

why is that?

#

is the id too big?

ionic pecan Mar 1, 2019, 8:39 PM

#

needs more context

#

which database are you using, how are you inserting the data, ...

rain field Mar 1, 2019, 8:45 PM

#

well mysql

#

@bot.command()
async def donner_points(ctx, mention:discord.User, nmbre: int):

    cnx = mysql.connector.connect(host='localhost',
                                  user='cashcash_kygo',
                                  password='...',
                                  database='cashcash')
    cursor = cnx.cursor()

    cursor.execute("SELECT `DiscordID` FROM `pointage` WHERE DiscordID = %s" % (mention.id))
    result = cursor.fetchone()

    if result:

        cursor.execute("SELECT `Points` FROM `pointage` WHERE DiscordID = %s" % (mention.id))
        pointsavant = (cursor.fetchone()[0])
        nouvo_points = (int(pointsavant) + nmbre)
        cursor.execute("UPDATE `pointage` SET `Points` = %s WHERE DiscordID = %s" % (nouvo_points, mention.id))
        await ctx.send(" %s possède désormais %s points " % (mention.name, nouvo_points))
        cnx.commit()
        cursor.close()
        cnx.close()

    else:

        await ctx.send(" %s n'existe pas dans ma base de données, je l'ajoute à l'instant..." % (mention.name))
        cursor.execute("INSERT INTO `pointage`(`DiscordID`, `Points`) VALUES (%s,%s)" % (mention.id, nmbre))
        await ctx.send(" %s possède désormais %s points " % (mention.name, nmbre))
        cnx.commit()
        cursor.close()
        cnx.close()

#

@ionic pecan

#

it always sets the mention.id as

📎 unknown.png

#

no matter who I mention

languid wind Mar 1, 2019, 8:47 PM

#

Hey

rain field Mar 1, 2019, 8:48 PM

#

Hello

#

I personally have a feeling it has to do with (cursor.fetchone()[0])

languid wind Mar 1, 2019, 8:50 PM

#

What is this thing ?

rain field Mar 1, 2019, 8:50 PM

#

wdym

languid wind Mar 1, 2019, 8:50 PM

#

you said it has do with this, but I don't know what is it xd

rain field Mar 1, 2019, 8:51 PM

#

pointsavant = (cursor.fetchone()[0])

ionic pecan Mar 1, 2019, 8:57 PM

#

whats your schema definition

languid wind Mar 1, 2019, 9:00 PM

#

Back

#

It got the full explain

rain field Mar 1, 2019, 9:00 PM

#

you mean the structure?

languid wind Mar 1, 2019, 9:01 PM

#

no read this :

#

I'm looking for a free server/computer for 20 minutes, the time to render two big pictures with 'geometrize' for a powerful PC,...

Geometrize is a software for create very cool Sceacable Vector Graphics pictures ( known as SVG files ) with your own pictures using the power of forms ( diferents settings ).

In fact, I want to create a banner for my YouTube Channel, and I would use the filter 'Quadratic Bezier' for recreate the picture with 10 000 - 15 000 curbe lines, and export it as SVG

My problem is that I got a really bad laptop and I can't do this with it, the app crash after 5000 forms when I try to it ( I got only 4 Go RAM, 2.2Ghz processor and a NVIDIA 950M)

So yeah that all, can someone help me ?

#

Someone tell me to go here

rain field Mar 1, 2019, 9:01 PM

#

Idk i'm not a graphics expert

languid wind Mar 1, 2019, 9:02 PM

#

T.T

rain field Mar 1, 2019, 9:08 PM

#

you mean the structure @ionic pecan

#

?

ionic pecan Mar 1, 2019, 9:12 PM

#

the sql used to create your table

rain field Mar 1, 2019, 9:40 PM

#

MariaDB

#

?

patent glen Mar 1, 2019, 9:57 PM

#

the "CREATE TABLE" statement

#

e.g. sql create table tablename (column1 type1, column2 type2) if you used a graphical tool to create the tool it should provide a way to export it

#

@rain field

rain field Mar 1, 2019, 9:59 PM

#

my table is in phpmyadmin

#

so no

patent glen Mar 1, 2019, 10:01 PM

#

https://www.itsupportguides.com/knowledge-base/tech-tips-tricks/how-to-generate-sql-create-table-script-using-phpmyadmin/

#

apparently mariadb has a show create table statement that's the important bit, the rest is just how to get phpmyadmin to show the full output

rain field Mar 1, 2019, 10:10 PM

#

alright i'll do that

patent glen Mar 1, 2019, 10:21 PM

#

i need to go, so someone else will need to help you from now, sorry

rain field Mar 1, 2019, 10:28 PM

#

okay

#

well I did that

#

I set full text

#

but still having the same issue

raw onyx Mar 1, 2019, 10:40 PM

#

i have went from sqlite3 to aiosqlite3 for my discord bot. now the issue is that the data it returns is a generator instead of a tuple

data = c.fetchone()
# returns a generator object

how do i actually get the data from the generator? i've tried iterating through it but that doesn't seem to work

pure cypress Mar 1, 2019, 10:52 PM

#

aren't you supposed to await it?

raw onyx Mar 1, 2019, 10:58 PM

#

oh my god

#

i forgot to add an await

#

thank you Mark

pure cypress Mar 2, 2019, 5:03 AM

#

I'm working with a database via Qt (pyside2). The driver is sqlite.

I need to get the value of an auto incremented field. The problem is that I need it before the changes are committed. I'm guessing there's no work around, as the value of an auto field wont be determine until it's committed since it's the driver's job to evaluate what that value is, right?

pure cypress Mar 2, 2019, 5:45 AM

#

I found a way to rearrange my code so that I can get the values after committing

gilded narwhal Mar 2, 2019, 8:30 AM

#

hey @pure cypress , iirc there's a cursor.lastrowid that you can access after inserting each row, not sure if it requires a commit or not though

#

and of course that's assuming the auto-incremented field is the id anyway

pure cypress Mar 2, 2019, 8:50 AM

#

Yeah I am using it and it does require a commit

#

Thanks though

lucid aspen Mar 2, 2019, 9:58 AM

#

Anyone here with some knowledge about peewee?

#

I'm trying to separate the class definitions in a different file, but for some reason it's messing up the imports

#

here's a minimal working example

#

https://gist.github.com/Kurolox/a0c6fc072934d17d14b263c09b1611be

Gist

Minimal example of issues trying to have peewee working on two dif...

Minimal example of issues trying to have peewee working on two different files - db_classes.py

#

or well, non-working example, I guess

lucid aspen Mar 2, 2019, 10:15 AM

#

Nevermind, it was an issue with circular dependencies that I missed

#

apologies

normal narwhal Mar 2, 2019, 11:28 AM

#

lol io arcana

scenic olive Mar 2, 2019, 3:58 PM

#

database: id, player1, player2, player3, player4, player5, etc.
is there any good way in sqlite to look if a specific player matches either one of playerX in the db? without fetching each row and matching each colomn? like "select * from matches where={{{player in players[1-99]}}}"

novel wharf Mar 2, 2019, 4:12 PM

#

select * from matches where player in (player1,player2,player3,...) perhaps?

#

Or a for loop of sorts if there are too many columns.

rain field Mar 2, 2019, 5:44 PM

#

using mysql

#

how can i fetch respectively the first id value

#

second and third

#

📎 unknown.png

#

cursor.fetchone() [0]

#

1

#

and 2?

#

nvm got it

plain radish Mar 2, 2019, 5:53 PM

#

if you want to limit it in your SQL query so you avoid having the db do all the work of fetching it all in the first place

#

you can just use LIMIT

#

in this case, if it's only first 3 you want, you would do LIMIT 3

lucid aspen Mar 2, 2019, 6:33 PM

#

so I'm trying to store some information in a database, but I have multiple versions of each string depending on the language they're on

#

the fastest way to do it would be to have a column for each language, but that doesn't sound maintainable in the long term

#

what's the best way to deal with multiple language strings on this kind of scenario?

#

for example, since I'm using peewee, let's say I have this model

#

class Profile(BaseModel):
    profile_id = IntegerField(primary_key=True)
    some_string = CharField()

#

some_string has multiple versions depending on the language, so I guess that I could do this

#

class Profile(BaseModel):
    profile_id = IntegerField(primary_key=True)
    some_string_english = CharField()
    some_string_spanish = CharField()
    some_string_french = CharField()```

#

which is kinda horrible as the number of languages grow

#

so, what could be other solutions to this?

raw onyx Mar 2, 2019, 6:59 PM

#

im having this error when creating a table using aiosqlite3

await c.execute('CREATE TABLE IF NOT EXISTS currency (id, other_id, currency')

i get this error when doing the table creation
sqlite3.OperationalError: near "currency": syntax error
i can't figure out why this error happens. can anybody help me with this?

wind pelican Mar 2, 2019, 7:01 PM

#

where is your closing ) inside the string?

raw onyx Mar 2, 2019, 7:01 PM

#

did i miss that?

wind pelican Mar 2, 2019, 7:01 PM

#

ye

raw onyx Mar 2, 2019, 7:01 PM

#

oh, i see what you mean

#

yep, that fixed the issue. thanks!

torn sphinx Mar 2, 2019, 8:50 PM

#

im looking into using sqlite

#

is it like this

#

cause im trying to save data for users and servers

#

267624335836053506:

#

thats the python server id

#

267624335836053506
*eh

#

like that

carmine heart Mar 2, 2019, 8:52 PM

#

I think I'm missing a bit of context, what are you asking and what are you trying to do?

torn sphinx Mar 2, 2019, 8:53 PM

#

wondering whether its possible to do json sort of style

#

hmm

#

dictionaries for each server

#

basically

carmine heart Mar 2, 2019, 8:55 PM

#

json sort of style within sqlite?

torn sphinx Mar 2, 2019, 8:55 PM

#

uh kinda

carmine heart Mar 2, 2019, 8:55 PM

#

It's probably better to work with the database format of sqlite

#

What kind of bot do you have?

#

And does it mind blocking calls?

torn sphinx Mar 2, 2019, 8:55 PM

#

just a small one, i was planning of doing levels

#

i mean

#

it wouldnt be to bad to use blocking calls would it

#

it only blocks that command until finished right

carmine heart Mar 2, 2019, 8:56 PM

#

If you have something blocking it will block the whole bot for moment

torn sphinx Mar 2, 2019, 8:56 PM

#

oh

#

hmm

carmine heart Mar 2, 2019, 8:56 PM

#

How large a server are we talking about?

torn sphinx Mar 2, 2019, 8:57 PM

#

i just applied for discordbots.org

#

so its a public bot

#

so uh

#

maybe small servers

#

50-150

#

members

carmine heart Mar 2, 2019, 9:01 PM

#

I think sqlite is fine for smaller servers, but you should really just look into sqlite at that point and not try to force a JSON data structure because that's what you know

torn sphinx Mar 2, 2019, 9:01 PM

#

hmm okay

carmine heart Mar 2, 2019, 9:01 PM

#

You can also look into a db an db module that is async

torn sphinx Mar 2, 2019, 9:02 PM

#

recommendations?

carmine heart Mar 2, 2019, 9:07 PM

#

@plain radish can probably give you a better answer and he loves it when I ping him

torn sphinx Mar 2, 2019, 9:07 PM

#

lol

plain radish Mar 2, 2019, 9:08 PM

#

if you're looking into a full SQL server, postgresql is the server id recommend, using the python library asyncpg.

#

if you're wanting to use sqlite, i'd recommend aiosqlite

#

if you want json data structures, use json, not sqlite

torn sphinx Mar 2, 2019, 9:08 PM

#

i guess im gonna go with json

plain radish Mar 2, 2019, 9:08 PM

#

sql gives a pretty big benefit though in scalability

#

and keep in mind json is still file access, same as sqlite

torn sphinx Mar 2, 2019, 9:09 PM

#

ok

plain radish Mar 2, 2019, 9:09 PM

#

you will need to make sure you don't await while accessing the file

torn sphinx Mar 2, 2019, 9:09 PM

#

kk

plain radish Mar 2, 2019, 9:09 PM

#

and avoid unnecessary frequent writes

#

and that should be good

torn sphinx Mar 2, 2019, 9:09 PM

#

kk

plain radish Mar 2, 2019, 9:10 PM

#

for example, some people have tried storing guild specific prefixes in a json data file before

#

and in their get_prefix function, they opened the json file, retrieved the value and then closed it again

#

that's unnecessary

#

just load the entire dataset from the json on bot start and when an admin changes the prefix, change the cached setting, and save the changed data then

torn sphinx Mar 2, 2019, 9:26 PM

#

import json
data = {
    "president": {
        "name": "Zaphod Beeblebrox",
        "species": "Betelgeusian"
    }
}
with open("data_file.json", "w") as write_file:
    json.dump(data, write_file)
json_string = json.dumps(data)
print(json_string)```

#

tried this

#

AttributeError: module 'json' has no attribute 'dump'

#

apparently

plain radish Mar 2, 2019, 9:28 PM

#

weird

torn sphinx Mar 2, 2019, 9:28 PM

#

idk why it wont work

plain radish Mar 2, 2019, 9:29 PM

#

how are you running it

#

wait

torn sphinx Mar 2, 2019, 9:29 PM

#

python json.py

#

oh shoot

plain radish Mar 2, 2019, 9:29 PM

#

you don't have a local module named json in the same directory

#

lol

#

there it is

torn sphinx Mar 2, 2019, 9:29 PM

#

oh

#

so i have to pip json?

#

gonna try simplejson

plain radish Mar 2, 2019, 9:31 PM

#

what, no

torn sphinx Mar 2, 2019, 9:31 PM

#

wtf

#

it worked

#

lma

#

lmao

#

import simplejson as json
data = {
    "president": {
        "name": "Zaphod Beeblebrox",
        "species": "Betelgeusian"
    }
}
with open("data_file.json", "w") as write_file:
    json.dump(data, write_file)
json_string = json.dumps(data)
print(json_string)```

plain radish Mar 2, 2019, 9:31 PM

#

it didn't work originally because it wasn't import the stdlib json module

torn sphinx Mar 2, 2019, 9:31 PM

#

{"president": {"name": "Zaphod Beeblebrox", "species": "Betelgeusian"}}
PS C:\Users\Jack Matthews\Desktop\Python Testing>```

plain radish Mar 2, 2019, 9:31 PM

#

it was importing json.py in your current directory

torn sphinx Mar 2, 2019, 9:31 PM

#

oh

#

oh

#

im stupid

plain radish Mar 2, 2019, 9:32 PM

#

imports have order of checking

torn sphinx Mar 2, 2019, 9:32 PM

#

let me rename that

plain radish Mar 2, 2019, 9:32 PM

#

yes

#

yes you should

torn sphinx Mar 2, 2019, 9:32 PM

#

ty it works

#

im actual stupid

plain radish Mar 2, 2019, 9:33 PM

#

all good

torn sphinx Mar 2, 2019, 9:40 PM

#

how would i add on to the json without deleting the existing json

#

uh

#

and how every 100 xp would i add to level

#

        data = {
            mesage.author.id: {
                "xp": 1+loadthis[message.author.id][xp],
                "level": 1
            }
        }```

#

uh can i ping scragly

carmine heart Mar 2, 2019, 10:03 PM

#

You can't edit a part of the json file

torn sphinx Mar 2, 2019, 10:03 PM

#

oh

carmine heart Mar 2, 2019, 10:04 PM

#

The only option you have is read it in as a whole, modifying it and writing it back out again

torn sphinx Mar 2, 2019, 10:04 PM

#

oh that suckss but okay

#

i dont think json is working well for me

#

so i guess im gonna do aiosqlite

torn sphinx Mar 2, 2019, 10:26 PM

#

okay

#

how do i create a table for each user

#

or what would a better system be

#

uh

#

idk if i can ping people

#

...

#

okay issue im facing is

#

i cant make a table for each user id cause its '''''' not ""

#

how would i get around this....

#

uh

#

@plain radish can you help me please?

plain radish Mar 2, 2019, 10:46 PM

#

you wouldn't make a table per user, no

torn sphinx Mar 2, 2019, 10:47 PM

#

okay

plain radish Mar 2, 2019, 10:47 PM

#

you'd need to specify a table design that contains all the data columns for each user, and use user id as the ID column likely

#

using the ID column as the unique identifier

torn sphinx Mar 2, 2019, 10:47 PM

#

how would i do that..?

plain radish Mar 2, 2019, 10:47 PM

#

do you know sql?

torn sphinx Mar 2, 2019, 10:47 PM

#

ive heard of it

#

isnt this sqlite

plain radish Mar 2, 2019, 10:48 PM

#

yes

#

sqlite

#

which uses sql, the query language

#

you'll need to know the basics of sql, the query language, to run queries that add, remove or edit data

torn sphinx Mar 2, 2019, 10:49 PM

#

hmm okay...

plain radish Mar 2, 2019, 10:49 PM

#

for example adding a table would be like ```sql
CREATE TABLE table_name (
column_one text,
column_two text);

#

etc

torn sphinx Mar 2, 2019, 10:49 PM

#

okay

plain radish Mar 2, 2019, 10:49 PM

#

its a mini language

#

but it's a lot more simple

#

you'll want to establish the basics in that along with the basics of schema design

#

which is how to design your db and tables

torn sphinx Mar 2, 2019, 10:50 PM

#

uh okay

plain radish Mar 2, 2019, 10:50 PM

#

you try looking at the sql tutorial by w3schools maybe
https://www.w3schools.com/sql/

torn sphinx Mar 2, 2019, 11:08 PM

#

how would i do numbers insteand of text @plain radish for xp and level

plain radish Mar 2, 2019, 11:08 PM

#

you would set the data type to the correct number type

torn sphinx Mar 2, 2019, 11:08 PM

#

okay, i should read docs

#

im stupid sorry

#

doesnt seem to be one what

plain radish Mar 2, 2019, 11:13 PM

#

https://www.sqlite.org/datatype3.html

#

yeah there's docs on sqlite datatypes

torn sphinx Mar 2, 2019, 11:15 PM

#

so int?

plain radish Mar 2, 2019, 11:16 PM

#

in SQL INTEGER and INT are two different datatype values

#

implementations might make them equivalent or different depending on the flavour of server you're using

#

in this case, you're using sqlite, so INTEGER is what you want

pure cypress Mar 2, 2019, 11:18 PM

#

It accepts either

#

https://www.sqlite.org/datatype3.html#affinity_name_examples

torn sphinx Mar 2, 2019, 11:23 PM

#


_run_event
    await coro(*args, **kwargs)
  File "IceCream.py", line 55, in on_message
    for row in cur.execute('SELECT * FROM levels ORDER BY level'):
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/cursor.py", line 130, in execute
    res = yield from self._execute(self._cursor.execute, sql, parameters)
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/cursor.py", line 56, in _execute
    res = yield from self._conn.async_execute(func, *args, **kwargs)
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/connection.py", line 137, in async_execute
    return (yield from self._execute(func, *args, **kwargs))
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/connection.py", line 128, in _execute
    func
RuntimeError: await wasn't used with future
Future exception was never retrieved
future: <Future finished exception=OperationalError('no such table: levels')>
Traceback (most recent call last):
  File "/usr/lib/python3.7/concurrent/futures/thread.py", line 57, in run
    result = self.fn(*self.args, **self.kwargs)
sqlite3.OperationalError: no such table: levels

#

    conn = await aiosqlite3.connect('bot.db')
    cur = await conn.cursor()
    cur.execute('''CREATE TABLE levels
             (userid text, xp integer, level integer)''')
    cur.execute(f"INSERT INTO levels VALUES ({str(message.author.id)},1,1)")  
    conn.commit()
    for row in cur.execute('SELECT * FROM levels ORDER BY level'):
        print(row)  ```

#

i get that error

#

uh

#

scragly you there?

#

aiosqlite3 doesnt have docs so using the sqlite3 docs

plain radish Mar 2, 2019, 11:35 PM

#

yes

#

sorry, i'm stuck with a different question atm, i'll be back in a few mins

torn sphinx Mar 2, 2019, 11:36 PM

#

Okay sorry

ionic pecan Mar 2, 2019, 11:39 PM

#

Try to commit after you create the table

torn sphinx Mar 2, 2019, 11:39 PM

#

mk

ionic pecan Mar 2, 2019, 11:39 PM

#

I‘m not sure how sqlite handles transactions but in Postgres data definition is transactional

torn sphinx Mar 2, 2019, 11:40 PM

#

_run_event
    await coro(*args, **kwargs)
  File "IceCream.py", line 55, in on_message
    for row in cur.execute('SELECT * FROM levels ORDER BY level'):
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/cursor.py", line 130, in execute
    res = yield from self._execute(self._cursor.execute, sql, parameters)
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/cursor.py", line 56, in _execute
    res = yield from self._conn.async_execute(func, *args, **kwargs)
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/connection.py", line 137, in async_execute
    return (yield from self._execute(func, *args, **kwargs))
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/connection.py", line 128, in _execute
    func
RuntimeError: await wasn't used with future
Future exception was never retrieved
future: <Future finished exception=MemoryError()>
Traceback (most recent call last):
  File "/usr/lib/python3.7/concurrent/futures/thread.py", line 57, in run
    result = self.fn(*self.args, **self.kwargs)
MemoryError

pure cypress Mar 2, 2019, 11:42 PM

#

you need to await the cur.execute()

torn sphinx Mar 2, 2019, 11:42 PM

#

k

#

<Future pending cb=[_chain_future.<locals>._call_check_cancel() at /usr/lib/python3.7/asyncio/futures.py:348]>
Ignoring exception in on_message
Traceback (most recent call last):
  File "/usr/local/lib/python3.7/dist-packages/discord/client.py", line 227, in _run_event
    await coro(*args, **kwargs)
  File "IceCream.py", line 55, in on_message
    for row in cur.execute('SELECT * FROM levels ORDER BY level'):
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/cursor.py", line 130, in execute
    res = yield from self._execute(self._cursor.execute, sql, parameters)
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/cursor.py", line 56, in _execute
    res = yield from self._conn.async_execute(func, *args, **kwargs)
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/connection.py", line 137, in async_execute
    return (yield from self._execute(func, *args, **kwargs))
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/connection.py", line 128, in _execute
    func
RuntimeError: await wasn't used with future

slate spire Mar 2, 2019, 11:45 PM

#

the traceback doesn't look like you awaited it

torn sphinx Mar 2, 2019, 11:46 PM

#

oh i have to do all of the cur.execute

#

sorry

#

okay wooho

#

got it working

#

how do i delete rows?

#

and how do i check if a userid already exists and if so update it

#

uh

#

@pure cypress able to help?

#

@plain radish can you help when youre availiable please

plain radish Mar 3, 2019, 12:03 AM

#

http://www.sqlitetutorial.net/sqlite-delete/

#

DELETE statement

torn sphinx Mar 3, 2019, 12:03 AM

#

okay tyy

#

also

plain radish Mar 3, 2019, 12:03 AM

#

updating http://www.sqlitetutorial.net/sqlite-update/

torn sphinx Mar 3, 2019, 12:03 AM

#

okay

#

how would i identify with userid

plain radish Mar 3, 2019, 12:04 AM

#

check the update page

torn sphinx Mar 3, 2019, 12:04 AM

#

('316373998378418176', 1, 1)
('316373998378418176', 1, 1)
('316373998378418176', 1, 1)
('316373998378418176', 1, 1)
('316373998378418176', 1, 1)
('316373998378418176', 1, 1)
('316373998378418176', 1, 1)
('316373998378418176', 1, 1)
('316373998378418176', 1, 1)
('398601531525562369', 1, 1)

#

the first is the userid

plain radish Mar 3, 2019, 12:04 AM

#

it shows how to narrow the update scope

torn sphinx Mar 3, 2019, 12:04 AM

#

kk

plain radish Mar 3, 2019, 12:04 AM

#

specifically the WHERE clause

torn sphinx Mar 3, 2019, 12:05 AM

#

ok

#

how would i check if user is already in system?

rain field Mar 3, 2019, 12:09 AM

#

cursor.execute('SELECT  `user` IN `table` WHERE `user` = userid')
result = cursor.fetchone()
if result:

torn sphinx Mar 3, 2019, 12:46 AM

#

    conn = await aiosqlite3.connect('bot.db')
    cur = await conn.cursor()
    await cur.execute(f'SELECT * FROM levels WHERE userid = {str(message.author.id)}')
    result = await cur.fetchone()
    if result:
        await message.channel.send("Success")
    else:
        await cur.execute(f"INSERT INTO levels VALUES ({str(message.author.id)},1,1)")  
    for row in await cur.execute('SELECT * FROM levels ORDER BY level'):
        print(row) ```

#

this doesnt always goes to else

#

@plain radish

#

uh...

#

can you help me please

#

@pure cypress...?

#

...

#

@plain radish uhh

#

wait

#


Ignoring exception in on_message
Traceback (most recent call last):
  File "/usr/local/lib/python3.7/dist-packages/discord/client.py", line 227, in _run_event
    await coro(*args, **kwargs)
  File "IceCream.py", line 51, in on_message
    await cur.execute(f'SELECT * IN levels WHERE {str(message.author.id)} = userid')
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/cursor.py", line 130, in execute
    res = yield from self._execute(self._cursor.execute, sql, parameters)
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/cursor.py", line 56, in _execute
    res = yield from self._conn.async_execute(func, *args, **kwargs)
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/connection.py", line 137, in async_execute
    return (yield from self._execute(func, *args, **kwargs))
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/connection.py", line 128, in _execute
    func
  File "/usr/lib/python3.7/concurrent/futures/thread.py", line 57, in run
    result = self.fn(*self.args, **self.kwargs)
sqlite3.OperationalError: near "IN": syntax error

#

this error

#

from

#

    await cur.execute(f'SELECT * IN levels WHERE {str(message.author.id)} = userid')
    result = await cur.fetchone()
    if result:
        await cur.execute(f'SELECT * FROM levels WHERE userid = {str(message.author.id)}')
        await message.channel.send("Success")
    else:
        await cur.execute(f"INSERT INTO levels VALUES ({str(message.author.id)},1,1)")  
    for row in await cur.execute('SELECT * FROM levels ORDER BY level'):
        print(row) 
    await bot.process_commands(message)```

#

@rain field that doesnt seem to work properly, IN syntax error so i switched to FROM and still doesnt work

#

await cur.execute(f'SELECT * FROM levels WHERE {str(message.author.id)} = userid')```

patent glen Mar 3, 2019, 1:31 AM

#

don't use f-strings for queries

torn sphinx Mar 3, 2019, 1:32 AM

#

oh...

patent glen Mar 3, 2019, 1:32 AM

#

also, you want an update statement there, I think

#

though I think there's a way to do "insert if doesn't exist, update if it does" in sqlite

torn sphinx Mar 3, 2019, 1:32 AM

#

thats just looking for it

#

that checks if it exists

patent glen Mar 3, 2019, 1:33 AM

#

right

torn sphinx Mar 3, 2019, 1:33 AM

#

    await cur.execute(f'SELECT * FROM levels WHERE {str(message.author.id)} = userid')
    result = await cur.fetchone()
    if result:```

#

and else it adds it

patent glen Mar 3, 2019, 1:33 AM

#

it should be FROM

#

but your next one, if it exists, should be update, not select

torn sphinx Mar 3, 2019, 1:33 AM

#

what bit?

patent glen Mar 3, 2019, 1:33 AM

#

here i'll just make the changes i'm talking about

torn sphinx Mar 3, 2019, 1:33 AM

#

mk

patent glen Mar 3, 2019, 1:34 AM

#

you're trying to add one level, right?

torn sphinx Mar 3, 2019, 1:34 AM

#

one xp

patent glen Mar 3, 2019, 1:35 AM

#

is the field called xp?

torn sphinx Mar 3, 2019, 1:35 AM

#

yeah

#

and every 75 xp they level up

patent glen Mar 3, 2019, 1:35 AM

#

    await cur.execute('SELECT * FROM levels WHERE userid = ?', [message.author.id])
    result = await cur.fetchone()
    if result:
        await cur.execute('UPDATE levels SET xp = xp + 1 WHERE userid = ?', [message.author.id])
        await message.channel.send("Success")
    else:
        await cur.execute('INSERT INTO levels VALUES (?,1,1)', [message.author.id])  
    for row in await cur.execute('SELECT * FROM levels ORDER BY level'):
        print(row) 
    await bot.process_commands(message)

#

there's probably a better way to structure it, but this is how you'd do what you're trying to do

torn sphinx Mar 3, 2019, 1:36 AM

#

Ignoring exception in on_message
Traceback (most recent call last):
  File "/usr/local/lib/python3.7/dist-packages/discord/client.py", line 227, in _run_event
    await coro(*args, **kwargs)
  File "IceCream.py", line 51, in on_message
    await cur.execute('SELECT * IN levels WHERE userid = ?', [message.author.id])
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/cursor.py", line 130, in execute
    res = yield from self._execute(self._cursor.execute, sql, parameters)
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/cursor.py", line 56, in _execute
    res = yield from self._conn.async_execute(func, *args, **kwargs)
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/connection.py", line 137, in async_execute
    return (yield from self._execute(func, *args, **kwargs))
  File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/connection.py", line 128, in _execute
    func
  File "/usr/lib/python3.7/concurrent/futures/thread.py", line 57, in run
    result = self.fn(*self.args, **self.kwargs)
sqlite3.OperationalError: near "IN": syntax error

patent glen Mar 3, 2019, 1:36 AM

#

oh oops

#

should be FROM

torn sphinx Mar 3, 2019, 1:36 AM

#

mk

#

hmm

#

it doesnt seem to be working...

#

doesnt add XP

#

keeps printing the same thing

#

('316373998378418176', 1, 1)

#

userid text, level integer, xp integer is my format

#

doesnt even send successs

#

uh @patent glen know why?

patent glen Mar 3, 2019, 1:50 AM

#

i don't know

#

can you post the whole code for your bot?

#

http://paste.pythondiscord.com

torn sphinx Mar 3, 2019, 1:50 AM

#

okay.

patent glen Mar 3, 2019, 1:51 AM

#

also are you on discord.py async or rewrite?

torn sphinx Mar 3, 2019, 1:51 AM

#

rewrite

#

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

patent glen Mar 3, 2019, 1:51 AM

#

author.id is an integer in rewrite, i wonder if that might be part of the issue

torn sphinx Mar 3, 2019, 1:51 AM

#

thats why i stringed it/

patent glen Mar 3, 2019, 1:52 AM

#

ah - well what you were doing with the f-string wasn't going to make it an actual string for the sql, so i didn't include that

torn sphinx Mar 3, 2019, 1:52 AM

#

oh.

patent glen Mar 3, 2019, 1:52 AM

#

i'm not sure why you want it as a string anyway

torn sphinx Mar 3, 2019, 1:52 AM

#

good point

#

idk, just forgot it was a int and thought it was string and now im gonna change that

patent glen Mar 3, 2019, 1:53 AM

#

maybe you need to commit and close the connection at the end of the function

torn sphinx Mar 3, 2019, 1:54 AM

#

mk

patent glen Mar 3, 2019, 1:54 AM

#

commit immediately after the insert or update tbh

#

and then close at the end

torn sphinx Mar 3, 2019, 1:55 AM

#

gotta recreate the databse oof

patent glen Mar 3, 2019, 1:55 AM

#

column types don't actually matter in sqlite

torn sphinx Mar 3, 2019, 1:55 AM

#

userid integer, level integer, xp integer

#

really?

patent glen Mar 3, 2019, 1:55 AM

#

oh wait, apparently they do now

#

they used to not

#

that might be why it was failing

#

no, it shouldn't

#

hmm

#

probably the commit thing

torn sphinx Mar 3, 2019, 1:56 AM

#

does cursor or conn close and commit

patent glen Mar 3, 2019, 1:58 AM

#

on normal sqlite3, both have close methods that do different things (you want connection), and commit is on connection

#

not 100% sure on aio but probably the same

final mason Mar 3, 2019, 1:58 AM

#

Is there a way to copy everything from one table, append it to another table, delete the contents of the first table, but keep the auto-incremented id?

patent glen Mar 3, 2019, 1:58 AM

#

@final mason on what db, sqlite3?

final mason Mar 3, 2019, 1:58 AM

#

mySQL

torn sphinx Mar 3, 2019, 1:59 AM

#

now to add a check fpr wjetjer tje user is a bot

patent glen Mar 3, 2019, 2:00 AM

#

@final mason find out what the auto increment value is by doing this https://stackoverflow.com/questions/15821532/get-current-auto-increment-value-for-any-table

#

and you can set the auto increment value on the new table with sql ALTER TABLE tablename AUTO_INCREMENT=(value);

#

is the new table a new table, or is it an existing table with data already in it and the same schema? https://docs.oracle.com/cd/E17952_01/mysql-5.0-en/create-table-select.html may be helpful if it's for a new table

#

mysql also supports INSERT INTO... SELECT for that case, but I'd be worried about id collisions

#

@torn sphinx did it spam with adding itself XP?

#

didn't think of that

#

you should probably remove the success message anyway, and then you won't have that cycle regardless of the bot check (i would have printed it to the console instead of sending it, tbh)

final mason Mar 3, 2019, 2:04 AM

#

@patent glen Yeah, what made me start looking for other solutions was id collisions

#

The IDs are gonna be order numbers, so they all need to be unique

patent glen Mar 3, 2019, 2:04 AM

#

well

#

i mean

#

what exactly is going on

#

are there two rows already in the tables that have the same ID? there's no good way around that

final mason Mar 3, 2019, 2:05 AM

#

Table 'jobList' has rows with IDs 1, 2, 3, 4, etc

#

This table is for jobs that haven't been processed yet

patent glen Mar 3, 2019, 2:05 AM

#

oh wait

#

you're worried about the delete resetting the auto increment ID to zero?

#

it shouldn't tbh

#

not on a normal DELETE FROM anyway; don't know about truncate table

final mason Mar 3, 2019, 2:06 AM

#

Oh

#

Well let's give it a try haha

patent glen Mar 3, 2019, 2:06 AM

#

auto increment value is a property of the table, it's not calculated on the fly from current contents lol

#

truncate does reset auto increment, but delete shouldn't

#

truncate is technically DDL so you shouldn't be using it for normal operations anyway

glad bobcat Mar 3, 2019, 2:09 AM

#

HeyGuys

#

I have a MySQL database that I use with SQL alchemy mainly and I'd like to offer a way to my co workers to access data about it easily (in particular averages, counts of certain columns, and all that)

torn sphinx Mar 3, 2019, 2:10 AM

#

Yeah lol

glad bobcat Mar 3, 2019, 2:11 AM

#

How would you recommend going about it? Using a BI tool to automate it or do it through an extra database on my server where I store this info?

#

I know a friend who does this with a Redis instance for his team-facing data

patent glen Mar 3, 2019, 2:12 AM

#

well the first question is, how much do you trust your coworkers and how technically inclined are they

glad bobcat Mar 3, 2019, 2:12 AM

#

0

#

I just wanna supply them with good looking visualisation and easy access to data I curated (like averages/counts of a table on certain constraints and all that)

patent glen Mar 3, 2019, 2:12 AM

#

ah

#

i don't know what tools exist for that then

torn sphinx Mar 3, 2019, 2:13 AM

#

This happened lol.

📎 Screenshot_2019-03-02-21-12-50.png

glad bobcat Mar 3, 2019, 2:13 AM

#

I know Tableau, PowerBI, Metabase, Grafana, but I'm not sure if that's the right way to go about it

patent glen Mar 3, 2019, 2:13 AM

#

@torn sphinx tbh just remove the success message, you don't want it replying to everything anyone ever says with that anyway

torn sphinx Mar 3, 2019, 2:13 AM

#

Yeah prob will

patent glen Mar 3, 2019, 2:13 AM

#

add a bot check or don't, but with no success message you won't have that problem anyway

torn sphinx Mar 3, 2019, 2:14 AM

#

How would I do level+1 x75 and check if that matches xp

#

Like turn the values into variables

patent glen Mar 3, 2019, 2:15 AM

#

i'd probably change your initial existence check to get the values, like

#

    await cur.execute('SELECT * FROM levels WHERE userid = ?', [message.author.id])
    result = await cur.fetchone()
    if result:
        level, xp = result[1:]
        xp += 1
        if xp >= 75:
            level += xp // 75
            xp %= 75
        await cur.execute('UPDATE levels SET level = ?, xp = ? WHERE userid = ?', [level, xp, message.author.id])
        #await message.channel.send("Success")
    else:
        await cur.execute('INSERT INTO levels VALUES (?,1,1)', [message.author.id])  
    for row in await cur.execute('SELECT * FROM levels ORDER BY level'):
        print(row) 
    await bot.process_commands(message)

#

you could probably do the math (and the upsert) in sql, but it's easier to understand what's going on this way imo

#

wait uh

#

is xp meant to be total xp or xp in current level?

#

code above was assuming it means xp in current level, i.e. gets reset to 0 on level up

torn sphinx Mar 3, 2019, 2:20 AM

#

Total XP

#

75 xp to level up

#

And this is global

#

i need to make config tmrw

patent glen Mar 3, 2019, 2:23 AM

#

ok then just do level = xp // 75 instead of that if thing

final mason Mar 3, 2019, 2:23 AM

#

@patent glen You were right 😃 Using the DELETE function keeps the ID

patent glen Mar 3, 2019, 2:23 AM

#

yeah

torn sphinx Mar 3, 2019, 2:24 AM

#

mk

patent glen Mar 3, 2019, 2:24 AM

#

basically truncate table is only intended for if you're doing a "factory reset" of all your data

final mason Mar 3, 2019, 2:24 AM

#

Ended up being a lot easier than I expected it to be haha

patent glen Mar 3, 2019, 2:24 AM

#

it doesn't respect triggers or constraints either aiui

final mason Mar 3, 2019, 2:24 AM

#

What's the difference between truncate and drop then?

patent glen Mar 3, 2019, 2:24 AM

#

truncate keeps the schema intact

#

i.e. the table still exists with all the columns constraints etc

#

drop means no more table

final mason Mar 3, 2019, 2:25 AM

#

Ahh alright

#

Neat, I'll keep that in mind

#

I'm "teaching myself" SQL and Python for my Senior Design class

#

And by "teaching myself", I mean I'm asking a shitload of questions on here and doing more Googling than I've ever done before hahaha

torn sphinx Mar 3, 2019, 2:27 AM

#

LMAO same here but sqlite, python and discord.py

#

Learnt python a bit tho

#

@patent glen getting error at level, xp = result[:]

patent glen Mar 3, 2019, 2:41 AM

#

@torn sphinx It was result[1:]

torn sphinx Mar 3, 2019, 2:42 AM

#

Ik forgot to type that

#

Still error tho

rain field Mar 3, 2019, 2:42 AM

#

using mysql?

torn sphinx Mar 3, 2019, 2:42 AM

#

Valueerror

#

No sqlite

#

Not enough values to unpack, expected 2 got one @patent glen

patent glen Mar 3, 2019, 2:44 AM

#

are there not three columns in your table?

#

print result

torn sphinx Mar 3, 2019, 2:44 AM

#

Mk

#

What the frick lol

📎 Screenshot_2019-03-02-21-46-24.png

#

There is 3

patent glen Mar 3, 2019, 2:48 AM

#

1:

#

not :1

torn sphinx Mar 3, 2019, 2:48 AM

#

don't ask why I'm on a tablet I needed a break from pc

#

Oh okay

patent glen Mar 3, 2019, 2:49 AM

#

1: should take all but the first, :1 would be only the first

#

look up slicing for more details on how it works

torn sphinx Mar 3, 2019, 2:50 AM

#

Mk

#

Cool, it works

#

I am now wondering where I went wrong

#

It now levels up at every message

📎 Screenshot_2019-03-02-22-02-25.png

#

@bot.event
async def on_message(message):
    conn = await aiosqlite3.connect('bot.db')
    cur = await conn.cursor()
    await cur.execute('SELECT * FROM levels WHERE userid = ?', [message.author.id])
    result = await cur.fetchone()
    if result:
    	print(result)
    	level, xp = result[1:]
    	if xp == level+1*75:
    		await cur.execute('UPDATE levels SET level = level + 1 WHERE userid = ?', [message.author.id])
    		await conn.commit()
    		lvlup = await message.channel.send(f"Hey {message.author.mention}, you leveled up to level {str(level)}")
    		await asyncio.sleep(3)
    		await lvlup.delete()
    	else:
        	await cur.execute('UPDATE levels SET xp = xp + 1 WHERE userid = ?', [message.author.id])
        	await conn.commit()
    else:
        await cur.execute('INSERT INTO levels VALUES (?,1,1)', [message.author.id]) 
        await conn.commit() 
    for row in await cur.execute('SELECT * FROM levels ORDER BY level'):
        print(row)
    await conn.close()
    await bot.process_commands(message)```

#

@patent glen

#

The times is * right

patent glen Mar 3, 2019, 3:06 AM

#

...honestly, i'd probably stick to the way i structured it

torn sphinx Mar 3, 2019, 3:06 AM

#

hmm mk...

patent glen Mar 3, 2019, 3:06 AM

#

but to do it your way you need parentheses, (level+1)*75

torn sphinx Mar 3, 2019, 3:07 AM

#

okay

#

How would I delete all rows now

patent glen Mar 3, 2019, 3:08 AM

#

    await cur.execute('SELECT * FROM levels WHERE userid = ?', [message.author.id])
    result = await cur.fetchone()
    if result:
        level, xp = result[1:]
        xp += 1
        level2 = xp // 75
        if level2 > level:
            await cur.execute('UPDATE levels SET level = ?, xp = ? WHERE userid = ?', [level2, xp, message.author.id])
            await ... # send leveled up message
        else:
            await cur.execute('UPDATE levels SET xp = ? WHERE userid = ?', [xp, message.author.id])
    else:
        await cur.execute('INSERT INTO levels VALUES (?,1,1)', [message.author.id])  
    for row in await cur.execute('SELECT * FROM levels ORDER BY level'):
        print(row) 
    await bot.process_commands(message)```

#

delete from levels

lucid aspen Mar 3, 2019, 9:04 AM

#

Sorry for asking this question again, but since nobody answered me yesterday...

#

How would you handle strings with multiple languages in a database?

eager star Mar 3, 2019, 9:26 AM

#

@lucid aspen in terms of storage i dont think it makes a difference, but make sure that you set the proper encoding on your table/database

#

for example, if you are using mysql you should just use utf8mb4 and you won't have to worry about what string values are put in

lucid aspen Mar 3, 2019, 9:31 AM

#

Thanks for the advice

#

For now I've thought that having a table in the database dedicated to the strings would be the most sensible solution regarding storage, though I'm not very happy about having non-int primary keys for it

lucid aspen Mar 3, 2019, 10:09 AM

#

Something like this

#

📎 db.png

ionic pecan Mar 3, 2019, 10:22 AM

#

Not sure if a database is what you wanna use for this

#

Gettext seems to be what you want

#

!d g gettext

delicate fieldBOT Mar 3, 2019, 10:23 AM

#

`gettext`

gettext — Multilingual internationalization services Source code: Lib/gettext.py The gettext module provides internationalization (I18N) and localization (L10N) services for your Python modules and applications. It supports both the GNU gettext message catalog API and a higher level, class-based API that may be more appropriate for Python files. The interface described below allows you to write your module and application messages in one natural language, and provide a catalog of [...]```None

lucid aspen Mar 3, 2019, 10:23 AM

#

I don't want the database for this

#

I want it for something else

#

it just so happens that I also have to deal with this issue

eager star Mar 3, 2019, 1:01 PM

#

azure has a pretty good and free translation endpoint with 2m character credits a month

#

just need to generate creds

rustic yarrow Mar 3, 2019, 7:06 PM

#

2m characters would be on order of 1k pages?

torn sphinx Mar 3, 2019, 7:08 PM

#

if premiumAccountTime< timeNow:
        print("0")
        async with aiosqlite.connect('database.db') as db:
            cursor = await db.execute("UPDATE users SET premium = 'No' WHERE id = :id",
                {'id': message.author.id})
            print("1")
        async with aiosqlite.connect('database.db') as db:
            cursor = await db.execute("UPDATE users SET rank = :rank WHERE id = :id",
                {'rank': nextImage, 'id': message.author.id})
            await db.commit()
            await cursor.close()
            print("2")```
This used to work right, now it doesnt. It doesnt remove premium from a user when time expires.

#

It prins 0,1,2

#

But doesnt update database

rustic yarrow Mar 3, 2019, 7:11 PM

#

i dont know much about this but why are you doing this asynchronously?

torn sphinx Mar 3, 2019, 7:11 PM

#

What do yuo mean

#

I dont udnerstand sorry

rustic yarrow Mar 3, 2019, 7:12 PM

#

i mean what are you even trying to do

#

it looks like it is supposed to run 0,1,2...

torn sphinx Mar 3, 2019, 7:12 PM

#

yes it does

#

But it doesnt update the database

#

"premium" is still set to "Yes"

#

while i made it to update it to "No"

rustic yarrow Mar 3, 2019, 7:17 PM

#

hmm strange i dont see any issues

#

your premium column is 'Yes' and 'No'?

torn sphinx Mar 3, 2019, 7:19 PM

#

📎 unknown.png

#

Yes

#

I dont see anything wrong in the code too, as I said it was working fine a few days ago. Idk whats going ont

#

fixed it. just copied it and pasted in an other line.

#

Weird, but fixed it

robust summit Mar 3, 2019, 10:32 PM

#

I'm trying to figure out the optimal way to implement this idea, but I haven't been happy with my ideas for it yet.

I am making a django project where there are players. Players can play missions. Missions are composed of a set of 1 or more objectives. When a player starts a mission, all of the objectives belonging to that mission have yet to be completed by that player. Upon that player's completion of all objectives belonging to a particular mission, that mission is marked as completed by that player.

My idea is that a player will have many-to-many relations for completed missions, in-progress missions, and in-progress objectives. When a player starts a mission, it get's added to their in-progress missions relation. All objectives belonging to that mission are added to the player's in-progress objectives relation. When an objective is completed, it is removed from the in-progress objectives relation, and then a check will happen to see if any objectives belonging to the mission that the previously completed objective belonged to remain in the user's list of in-progress objectives. If not, the mission is removed from the in-progress mission relation and added to the completed missions relation.

Is there a better way to handle this? I feel like there should be a way which requires less logic in the application.

plain radish Mar 4, 2019, 12:01 AM

#

you're handling state by changing where the data is stored

#

multiple tables shouldn't really carry the same type of data like that, having each table act as the current state of something

#

if you adjust the schema to something like

#

📎 unknown.png

#

you have player info where you need it, and the state of all the players missions can be inferred by the data in the player_objective table

#

the player_objective table has an entry for every objective a player starts and completes, and the completed col tracks completion state

#

with this setup, you can do a single query (each) to:

start new objectives/missions for each player
see what missions have been started by a player
see each mission's progress for a player

#

there's no need to move rows around to different tables to track state

quiet ermine Mar 4, 2019, 12:07 AM

#

@plain radish What's the program/website you are using for this: https://cdn.discordapp.com/attachments/342318764227821568/551917728886423552/unknown.png

plain radish Mar 4, 2019, 12:07 AM

#

sqldbm.com

quiet ermine Mar 4, 2019, 12:07 AM

#

Cool, thanks

robust summit Mar 4, 2019, 12:07 AM

#

Would I need the same type of junction table between player and mission in order to tell which missions have been started / completed by a player, or is that inferred through the player_objective table?

plain radish Mar 4, 2019, 12:08 AM

#

it's inferred

#

since the relationship reaches the whole way to missions

plain radish Mar 4, 2019, 12:23 AM

#

for example, as a basic demonstration of how it can be inferred:

SELECT player_objective.player_id AS player_id, COUNT(objective.mission_id) AS objective_count
FROM player_objective, objective
WHERE player_objective.objective_id = objective.objective_id
AND player_objective.mission_id = 1

#

or something

#

i don't have test data or anything, but the idea is that would show the number of objectives that have at the least been started by the player for the mission of ID 1

#

ofc, you can join things however you want in the SQL with whichever datatable being the focus, you just have to write it how you want.

robust summit Mar 4, 2019, 12:27 AM

#

Thank you, I will look into this more

tame quartz Mar 4, 2019, 1:27 AM

#

Anyone have any good examples of python scripts used to manage mariadb (or MySQL) looking to build some programs to do some self service stuff (backups/restores etc..)

rain field Mar 4, 2019, 7:03 AM

#

https://pynative.com/python-mysql-tutorial/

pynative

Python MySQL Tutorial Using MySQL Connector Python [Complete Guide]

Python MySQL tutorialUsing MySQL Connector Python mainly focuses on accessing the MySQL database, perform data insertion, data retrieval, data update and data deletion, transaction management, connection pooling and error handling techniques to develop robust python programs ...

#

@tame quartz

cloud dawn Mar 4, 2019, 3:38 PM

#

which database to use for a full fledged e commerce website is mongodb Django good or is mysql Django good or do i use both at specific places can someone guide me

ionic pecan Mar 4, 2019, 6:04 PM

#

postgres is generally the way to go

raven rain Mar 4, 2019, 9:36 PM

#

Hi, do i have to buy a hosted database for hosting my mysql database, or can i host it on my VPS ?

plain radish Mar 4, 2019, 9:44 PM

#

@raven rain you should be fine to install and host it on your vps

raven rain Mar 4, 2019, 9:44 PM

#

okay thanks

#

So on my vps, i connect it on localhost @plain radish ?

plain radish Mar 4, 2019, 9:45 PM

#

Sure

raven rain Mar 4, 2019, 9:45 PM

#

Okay :D

torn sphinx Mar 5, 2019, 1:32 AM

#

I'm using sqlite and did:

#

sqlite_file = 'proxys.sqlite'  
            conn = sqlite3.connect(sqlite_file)
            c = conn.cursor()
            print(finished)
            d = c.execute("UPDATE proxy SET ip_address = ? WHERE orderid = ?",[finished,"#1132"])
            avais = d.fetchall()
            conn.commit()
            conn.close()
    ```

#

finished is an ip address and does not exist anywhere in the database (is unique)

#

However i still got a Traceback (most recent call last): File "cha.py", line 28, in <module> d = c.execute("UPDATE proxy SET ip_address = ? WHERE orderid = ?",[finished,"#1132"]) sqlite3.IntegrityError: UNIQUE constraint failed: proxy.ip_address

#

error

#

When trying to update the ip address

#

I just manually did it and the database accepted it.

torn sphinx Mar 5, 2019, 2:39 AM

#

that's pretty cool

#

Uhhhh...?

torn sphinx Mar 5, 2019, 2:59 AM

#

I meant sqldbm.com sorry

plain radish Mar 5, 2019, 3:05 AM

#

@cloud dawn if you could give our nickname policy a quick look over, it would be greatly appreciated :)
https://pythondiscord.com/about/rules#nickname-policy

cloud dawn Mar 5, 2019, 11:33 AM

#

I thought its not annoying will change it now thanks

#

I hope this works

plain radish Mar 5, 2019, 11:34 AM

#

it's still not standard lettering, it'll need be basic characters that anyone can type on a normal keyboard

#

sorry for the hassle, but it prevents issues with mentioning

cloud dawn Mar 5, 2019, 11:34 AM

#

No problem thanks

plain radish Mar 5, 2019, 11:34 AM

#

thanks for being understanding 😃

raven rain Mar 5, 2019, 12:02 PM

#

How can I have list in SQL ?

eager star Mar 5, 2019, 12:06 PM

#

multiple tables perhaps, or if you are lazy and don't need to look up the things in the list often or efficiently you can just store them as some delimited string

raven rain Mar 5, 2019, 12:14 PM

#

So I have to represents users in SQL, and each user have a spell list that we can add or remove spells. So I create a new table spells that contains my user id. So I have to dosql CREATE TABLE spells( id INTEGER PRIMARY KEY AUTO_INCREMENT UNIQUE, user_id INTEGER UNIQUE NOT NULL, spell0 TEXT, spell1 TEXT, spell2 TEXT, ..., spell24 TEXT )???

nova hawk Mar 5, 2019, 12:15 PM

#

spell_user
user_id int
spell
pk = userid and spell combined.

raven rain Mar 5, 2019, 12:16 PM

#

aaah

#

okay thank's

nova hawk Mar 5, 2019, 12:17 PM

#

For many to many relationships you have a table like that to combine them

raven rain Mar 5, 2019, 12:17 PM

#

but there is no spells table 🤔 There is just uses and spell_user

nova hawk Mar 5, 2019, 12:18 PM

#

You would have a table of users, a table of spells, and a table that connects the two.

raven rain Mar 5, 2019, 12:18 PM

#

So in my spells table, i put all spells that exists ?

nova hawk Mar 5, 2019, 12:19 PM

#

Yes

raven rain Mar 5, 2019, 12:19 PM

#

Okay thank's i understand now

nova hawk Mar 5, 2019, 12:19 PM

#

And in the user_spells table, spell will be a foreign key, user will be a foreign key

raven rain Mar 5, 2019, 12:23 PM

#

This is not better to put spell_id in user_spell table ?

nova hawk Mar 5, 2019, 12:24 PM

#

user_spell contains the id of both user and spell.
That way you signify that a user can have multiple spells, and a spell can be known by multiple users.

raven rain Mar 5, 2019, 12:25 PM

#

Why we have to define foreign key ? I don't understand the utility of foreign keys

nova hawk Mar 5, 2019, 12:25 PM

#

Having a foreign key ensure that you don't assign a non-existing spell to a user, or that you have a non-existing user learn a spell.

#

Since they will have to exist in the spell table and in the user table.

raven rain Mar 5, 2019, 12:26 PM

#

Okay

#

And if i want to define all spell in my spells table, how can i do it ? I have to do INSERT INTO spells VALUES (<spell_name>) for each spell ?

nova hawk Mar 5, 2019, 12:30 PM

#

If you have a list of all the names you could quickly do it in a for loop.

raven rain Mar 5, 2019, 12:30 PM

#

Okay thx

plain radish Mar 5, 2019, 12:34 PM

#

in certain flavours, you can add multiple row inserts into the query

#

INSERT INTO spells
VALUES
  (<spell_name>),
  (<spell_name>),
  (<spell_name>),
  (<spell_name>),
  (<spell_name>),
  (<spell_name>);

#

like that

raven rain Mar 5, 2019, 1:34 PM

#

Now, if i want to represent a player bag that contains 10 slots how can i do ? I did sql CREATE TABLE IF NOT EXISTS bags( bag_id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, user_id TEXT UNIQUE NOT NULL, slot0 TEXT NOT NULL DEFAULT "void", slot1 TEXT NOT NULL DEFAULT "void", slot2 TEXT NOT NULL DEFAULT "void", slot3 TEXT NOT NULL DEFAULT "void", slot4 TEXT NOT NULL DEFAULT "void", slot5 TEXT NOT NULL DEFAULT "void", slot6 TEXT NOT NULL DEFAULT "void", slot7 TEXT NOT NULL DEFAULT "void", slot8 TEXT NOT NULL DEFAULT "void", slot9 TEXT NOT NULL DEFAULT "void", FOREIGN KEY(user_id) REFERENCES players(user_id) )But i don't know if it's the most efficient way

#

Because now, If i want to add a player and his bag, how can i do ? I have to add them at the same time because if i do INSERT INTO players VALUES (user.id, <bag_id>) i don't know bag_id, so first i add the bag with INSERT INTO bags VALUES (user.id) but it will returns me an error because there is no player created with user.id as user_id.

plain radish Mar 5, 2019, 1:41 PM

#

you need to create the player first

#

insert the player row in players table, with a RETURNS statement for the id

#

use that returned value for inserting in related info

raven rain Mar 5, 2019, 1:45 PM

#

I don't understand

#

My players table is represented like this sql CREATE TABLE IF NOT EXISTS players( user_id TEXT UNIQUE NOT NULL PRIMARY KEY, bag_id INTEGER, FOREIGN KEY(bag_id) REFERENCES bags(bag_id) )

prime prism Mar 5, 2019, 2:22 PM

#

You probably want to reference player in bags table, not bag in players table

#

If all players are restricted to one ten slot bag you could merge those tables

#

And if you want to have variable slots bags, you should split bag to two tables, bags and slots where bag references the player and slots references the bag

#

Bag size would then be determined by number of slots referencing given bag

#

select count(*) from slots where bag_id=<your bag id>

steel slate Mar 5, 2019, 2:34 PM

#

an alternative to a slots table would be to let bag_id, slot_id be a compound key on a storage_table so that each slot would be a row

#

you could mitigate this with a total_slots column on the player table

full canyon Mar 5, 2019, 4:12 PM

#

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1364, "Field 'inviteurl' doesn't have a default value")
[SQL: INSERT INTO invites (name, invites, redeemed) VALUES (%(name)s, %(invites)s, %(redeemed)s)]
[parameters: {'name': 'Fury#2591', 'invites': 0, 'redeemed': 0}]```

#

sqlalchemy

#

error

#

any ideas?

#

im stuck

#

https://hastebin.com/sucoyojigu.py

steel slate Mar 5, 2019, 4:24 PM

#

@full canyon default values are used when you attempt to insert data without a value for that column

#

I can't view your code from my network

full canyon Mar 5, 2019, 4:25 PM

#

what bin should i use?

steel slate Mar 5, 2019, 4:26 PM

#

I can't access code bins from work

full canyon Mar 5, 2019, 4:26 PM

#

https://paste.helpch.at/ejolumenac.py

#

what about this

steel slate Mar 5, 2019, 4:27 PM

#

wow, congrats, you found one that my network doens't block

#

but, the code doesn't actually show where the issue is coming form

full canyon Mar 5, 2019, 4:29 PM

#

oh

steel slate Mar 5, 2019, 4:30 PM

#

the one user that you create seems to have an inviteurl provided

full canyon Mar 5, 2019, 4:30 PM

#

yea

#

(Background on this error at: http://sqlalche.me/e/2j85)

#

this is part of the error

raven rain Mar 5, 2019, 6:08 PM

#

Why my AUTOINCREMENT doesn't work sql CREATE TABLE IF NOT EXISTS bags( bag_id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT UNIQUE NOT NULL, FOREIGN KEY(user_id) REFERENCES players(user_id) )`````` c.execute(f'INSERT INTO bags VALUES (?)', (user.id,)) sqlite3.OperationalError: table bags has 2 columns but 1 values were supplied

plain radish Mar 5, 2019, 6:09 PM

#

when you're not inserting every single value and relying on a default value that the schema provides (including autoincrement) you will need to provide the column names in the INSERT statement

raven rain Mar 5, 2019, 6:09 PM

#

aah

#

okay

#

But i get an OperationalError 🤔 FOREIGN KEY(user_id) REFERENCES players(user_id)""") sqlite3.OperationalError: near ")": syntax error

plain radish Mar 5, 2019, 6:12 PM

#

you should show your full code when you have issues

raven rain Mar 5, 2019, 6:12 PM

#

I already send the code where the error is

plain radish Mar 5, 2019, 6:12 PM

#

that's a single line from traceback but it appears your issue is with the sql statement in full

#

what you sent was just the sql as it was meant to be put in

#

but your code doesn't appear to have the same thing

#

specifically of note, a closing bracket

raven rain Mar 5, 2019, 6:14 PM

#

Yes i found the error

odd cloud Mar 5, 2019, 7:52 PM

#

Hi all, i'm doing something like this... Tasks.objects.all().... task_interval = dayofweek or today

#

is there a shortcut i can use to stick after that =?

#

since OR doesn't actually work

#

do i need to use Q?

steel slate Mar 5, 2019, 8:04 PM

#

@odd cloud what are you using for this?

#

because it isn't SQL

odd cloud Mar 5, 2019, 8:04 PM

#

nvm it was a dumb question

#

but thank you! i figured it out

full canyon Mar 5, 2019, 8:09 PM

#

Hey

#

does any1 know how to read the data in a mysql table using sqlalchemy?

#

tag me if you respond

ionic pecan Mar 5, 2019, 8:23 PM

#

what have you tried already?

#

the sqlalchemy docs are pretty extensive

steel slate Mar 5, 2019, 8:24 PM

#

also, what happens when you try that

#

@full canyon

#

@full canyon

#

@full canyon

full canyon Mar 5, 2019, 8:32 PM

#

try what

steel slate Mar 5, 2019, 8:37 PM

#

What have you tried already

#

@full canyon

carmine heart Mar 5, 2019, 8:41 PM

#

Why are you pinging them four times in short succession?

steel slate Mar 5, 2019, 8:43 PM

#

they asked to be tagged if somebody responded

carmine heart Mar 5, 2019, 8:44 PM

#

This looks a bit excessive and spammy to me, though

white tusk Mar 5, 2019, 8:46 PM

#

Hey Im trying to make two separate tables in sqlite3 that are linked with ids, one table will have usernames, passwords and the other will contain the amounts of a selected items

#

Anyone know how I could manage that?

ionic pecan Mar 5, 2019, 8:47 PM

#

How much do you know about SQL?

white tusk Mar 5, 2019, 8:49 PM

#

A basic amount, I can create tables and do stuff with the data

#

Im just trying to make it so that when an id in the first table is made it will also add an id in the second one

ionic pecan Mar 5, 2019, 8:50 PM

#

Your schema is already set up?

white tusk Mar 5, 2019, 8:50 PM

#

Yeah

ionic pecan Mar 5, 2019, 8:51 PM

#

Ah. I think this might be what you're looking for: https://www.sqlite.org/lang_createtrigger.html

#

you'd create a trigger to insert a new row in the selected items table when a new entry in the first table is created

white tusk Mar 5, 2019, 8:51 PM

#

Ok I see

#

Thanks for the help Ill take a look

ionic pecan Mar 5, 2019, 8:51 PM

#

Feel free to ask if you need help 👍

white tusk Mar 5, 2019, 8:59 PM

#

Where should I place my trigger in my python code?

ionic pecan Mar 5, 2019, 9:05 PM

#

You should create it as part of your schema migrations

white tusk Mar 5, 2019, 9:27 PM

#

CREATE TRIGGER same_id
  AFTER INSERT
  ON accounts
BEGIN
  INSERT INTO portfolio(AAPL, AMZN, GOOGL, HMC, INTC, MSFT, NVDA, WMT) VALUES(0, 0, 0, 0, 0, 0, 0, 0)

Not too sure what Im doing wrong here

full canyon Mar 5, 2019, 9:27 PM

#

volcyyy

#

u good with sqlalchemy

#

?

#

@ionic pecan

ionic pecan Mar 5, 2019, 9:29 PM

#

!t ask

delicate fieldBOT Mar 5, 2019, 9:29 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.

ionic pecan Mar 5, 2019, 9:29 PM

#

@white tusk I think you're missing an END

#

and a semicolon terminating the INSERT

full canyon Mar 5, 2019, 9:29 PM

#

i need to read every row in a table

#

sql

#

using sqlalchemy

#

i have tried so much

#

the documentation aint doin it for me

ionic pecan Mar 5, 2019, 9:30 PM

#

can you show me which code you're stuck with?

full canyon Mar 5, 2019, 9:30 PM

#

yea 1 sec

#

pycharm is saying no to loading

#

📎 unknown.png

#

this dont work

#

and i got it from docs

ionic pecan Mar 5, 2019, 9:32 PM

#

which error do you get?

full canyon Mar 5, 2019, 9:34 PM

#

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM invites' at line 2")
[SQL: SELECT
FROM invites]
(Background on this error at: http://sqlalche.me/e/f405)

full canyon Mar 5, 2019, 10:49 PM

#

thanks

raven rain Mar 6, 2019, 12:22 AM

#

Hi, I have to do a database that will be accessible from 2 differents application. So should i stay with sqlite or should i change for mysql ?

worthy prism Mar 6, 2019, 3:50 AM

#

@full canyon add a *

#

And if it's for a discord bot, it must be async

dull scarab Mar 6, 2019, 7:06 AM

#

Sqlite is a text file on steroids

#

I wouldnt access it from multiple sources

#

@raven rain

gilded narwhal Mar 6, 2019, 7:31 AM

#

if you're willing to use sqlite with WAL journal mode it should address most concurrency concerns

#

https://www.sqlite.org/wal.html

#

There are advantages and disadvantages to using WAL instead of a rollback journal. Advantages include:

WAL is significantly faster in most scenarios.
WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.
Disk I/O operations tends to be more sequential using WAL.
WAL uses many fewer fsync() operations and is thus less vulnerable to problems on systems where the fsync() system call is broken.

#

you use the following pragma to activate it
PRAGMA journal_mode=WAL;

#

(you only need to do this once, the change is persistent)

teal rampart Mar 6, 2019, 9:10 AM

#

Hi guys, I want create a system for my school that find a substitute for a user defined class,
but I don't know how store all the timetables and sort them in a sqlite database. Any help?

steel slate Mar 6, 2019, 2:59 PM

#

@teal rampart I would make one table for the predefined class periods (if there are any)

#

and then i would use timestamp or break the time out into the bits that you care about

torn escarp Mar 6, 2019, 5:06 PM

#

I have a table with column 0 as datetime, column 1 as numeric(0 or 1).
What i want to do is to sum column 1 every 5 minutes(from column 0's datetime).
I have found an sql statement but I have hard time querying it in python.

📎 unknown.png

#

can you help me do it using ```
conn = sqlite3.connect('path')
curs = conn.cursor()
curs.execute("select strftime('%H-%M', 0) AS minutes, sum(1) AS totalcars")
result = curs.fetchall()

heady charm Mar 6, 2019, 5:19 PM

#

Im trying to come up with a mongodb model for my marketplace platform idea

#

but im not sure how to do it

#

should I make one document for every user and embed everything in that such as sales history etc

#

or should I put those things in another collection and reference to there

eager star Mar 6, 2019, 5:29 PM

#

whats the error @torn escarp

torn escarp Mar 6, 2019, 5:46 PM

#

@eager star i thought the

GROUP BY
ORDER BY```
are another different line but it was a single statement. I finally got it by adding it all inside the curs.execute. papabless ![finger_gun](https://cdn.discordapp.com/emojis/364895920019603456.webp?size=128 "finger_gun")

eager star Mar 6, 2019, 5:50 PM

#

🙏

torn sphinx Mar 6, 2019, 8:24 PM

#

hello everyone so I am trying to create a small database from scratch can anyone give me some pointers as to how to try to do this?

#

I am sort of a noobie

unborn sentinel Mar 6, 2019, 8:45 PM

#

http://www.sqlitetutorial.net/sqlite-python/ This would probably be a good place to start. You'll probably want to search around and learn some SQL syntax, though.

SQLite Tutorial

admin

SQLite Python

This section guides you how to work with the SQLite database using Python sqlite3 module.

#

SQL is fairly straight forward, but you still have to know what you're doing

torn sphinx Mar 6, 2019, 8:47 PM

#

I was talking to a developer a couple of days ago about the project that I am working on and I mentioned adding an SQL server to my application and he said that an SQL server was like having Optimus Prime put a small nail in the wall and that I would probably be able to create my own database. I hope that makes sense

unborn sentinel Mar 6, 2019, 8:48 PM

#

It does, and in most cases I would agree

#

However SQLite is made for small applications, doesn't require a server, etc.

#

It's a handy option, and a great way to learn about database structure without having to deal with the other headachey aspects of it

steel slate Mar 6, 2019, 8:50 PM

#

you still handle it as if you had an sql server, but you run it on the application server

unborn sentinel Mar 6, 2019, 8:50 PM

#

Right.

#

It lives where ever your program lives

torn sphinx Mar 6, 2019, 8:51 PM

#

That sounds good however I do have a question about SQLite since you mentioned that it doesn't require a server does that mean that the data is stored in the application itself?

unborn sentinel Mar 6, 2019, 8:51 PM

#

No, it's stored as a singular file

#

No different than if you were housing your data in a .json or the like

torn sphinx Mar 6, 2019, 8:52 PM

#

So the file can be on one computer while another computer is running my application and still has access to the data in the file yes?

bright spruce Mar 6, 2019, 8:52 PM

#

what is data base and waht can it be used for

unborn sentinel Mar 6, 2019, 8:53 PM

#

I've you've got permissions to access that computer and can easily access that stuff, I suppose you can

#

@bright spruce

📎 unknown.png

steel slate Mar 6, 2019, 8:53 PM

#

you usually put it on the same computer as the application

bright spruce Mar 6, 2019, 8:53 PM

#

oh

#

so

#

hacking...?

unborn sentinel Mar 6, 2019, 8:54 PM

#

I'll explain once I'm done helping Cobra

bright spruce Mar 6, 2019, 8:54 PM

#

ok

steel slate Mar 6, 2019, 8:54 PM

#

Database is a representation of data in a way that is conducive for sifting through remotely

bright spruce Mar 6, 2019, 8:55 PM

#

mmm ok

torn sphinx Mar 6, 2019, 8:55 PM

#

Last question SQLite means I need to also learn SQL language right?

unborn sentinel Mar 6, 2019, 8:55 PM

#

It'll make things easier, yes

#

But you can usually do just fine with the basics

steel slate Mar 6, 2019, 8:55 PM

#

or an interface framework

unborn sentinel Mar 6, 2019, 8:55 PM

#

Like Pony

#

https://ponyorm.org/

steel slate Mar 6, 2019, 8:56 PM

#

if you use django you can have frontend and db stuff builtin all together

unborn sentinel Mar 6, 2019, 8:56 PM

#

That's also an option if you're building a site

steel slate Mar 6, 2019, 8:56 PM

#

but any ORM will let you do similar

unborn sentinel Mar 6, 2019, 8:56 PM

#

Plus, how can you say no to this little guy?

📎 unknown.png

torn sphinx Mar 6, 2019, 8:58 PM

#

Very nice well thank you Mr Hemlock and Python4fun I'll take a look at the options you've both mentioned your advice is very helpful

unborn sentinel Mar 6, 2019, 8:58 PM

#

Any time

torn sphinx Mar 6, 2019, 8:58 PM

#

😃

copper sphinx Mar 6, 2019, 11:40 PM

#

Is there a python3 supported non-blocking library for accessing Azure MSSQL instance?

dull scarab Mar 7, 2019, 6:58 AM

#

@torn sphinx sqlite is basically a textfile on steroids 🤷‍♂️

patent glen Mar 7, 2019, 5:00 PM

#

@copper sphinx i'm not sure, have you tried aioodbc?

copper sphinx Mar 7, 2019, 5:06 PM

#

No, I have not. Thank you. :)

#

oof That same library supports MySql, MSSQL, and Postgres? That's interesting.

patent glen Mar 7, 2019, 5:26 PM

#

it's windows only i think, it uses windows odbc drivers

#

you can probably also access an excel sheet

steel slate Mar 7, 2019, 5:28 PM

#

M$ has Access

#

also Microsft SQL server

ornate isle Mar 7, 2019, 7:45 PM

#

anything that supports sqlalchemy can be worked to run async via sqlalchmy core (non-ORM)

#

new great lib for this is 'databases': https://github.com/encode/databases but does not appear to support MSSQL at present. but since its using sqlalchemy core underneath i dont know why it couldnt

GitHub

encode/databases

Async database support for Python. 🗄. Contribute to encode/databases development by creating an account on GitHub.

#

i have never touched mssql and rarely come across it so i'm not much help there

#

but i know sqlalchemy supports it

copper sphinx Mar 7, 2019, 9:07 PM

#

I'm actually using SQLA, but was wondering discord bot with direct MSSQL.

misty nebula Mar 7, 2019, 10:34 PM

#

is there a way to make sqlalchemy learn already existing database?

marsh hill Mar 7, 2019, 11:12 PM

#

Hi all, i'm noobish in DB, would like your opinion.

#

I have a table which is tournament

#

another table which is roster

#

How could i associate a roster to a tournament?

#

Should I do like a table tournament_roster

#

Each row i have the id of roster + id of the tournament ?

#

Everytime that i want a kind of list, should i do a table to associate?

final shale Mar 7, 2019, 11:25 PM

#

when creating your table roster, you would create an primary and foreign key, so when you want to associate other tables, such as tournament, you would by there ID, so when you query you would JOIN them together, hope that helps.

#

FYI: you would create a foreign key in tournament also @marsh hill

prisma mesa Mar 7, 2019, 11:27 PM

#

Hey, can someone help me formulate a query in MySQL please. I’m assuming it will need to be a nested query and I'm not sure how I'd write it

I have values in my table that follow a format similar to this characters-uniqueID

I need to find a value in my table thats equal to a string I compare against. I then need to update all the characters that appear before the hyphen to a new value

final shale Mar 7, 2019, 11:32 PM

#

https://mode.com/sql-tutorial/sql-subqueries/ <--- how nested querys work

Mode Community

Writing Subqueries in SQL

This lesson of the SQL tutorial for data analysis covers using subqueries in SQL with aggregate functions, conditional logic, and joins.

prisma mesa Mar 7, 2019, 11:38 PM

#

hmm

#

SELECT *
FROM (
    UPDATE table
    SET (name) = value
    )
WHERE name = another_value

#

something like this?

torn sphinx Mar 7, 2019, 11:39 PM

#

my friend keeps saying that having a table within a table means its a 3 dimensional table. Is that right?

final shale Mar 7, 2019, 11:42 PM

#

yes @prisma mesa , remember that your inner query is goin to be executed first, then your outer query last

marsh hill Mar 7, 2019, 11:43 PM

#

@final shale ok

And i would do the table tournament_roster with both foreign key right ? Thanks for your help

final shale Mar 7, 2019, 11:43 PM

#

yup, no problem good sir

prisma mesa Mar 7, 2019, 11:46 PM

#

yeah thanks, just trying to get my head around it

#

reading back over my initial question, would it require a nested query? @final shale

raw onyx Mar 8, 2019, 12:02 AM

#

is it possible to make a table have new elements in it if it already exists? something like updating a table to allow a new value to be inserted in to it. i couldn't find anything about this on google

gilded narwhal Mar 8, 2019, 12:04 AM

#

@raw onyx you want to add a column to a table if the table exists? am i understanding that correctly?

raw onyx Mar 8, 2019, 12:05 AM

#

essentially, yes

#

so i'd have column 1 in a table, but then i would like to add a new column called column 2

gilded narwhal Mar 8, 2019, 12:05 AM

#

sqlite?

raw onyx Mar 8, 2019, 12:05 AM

#

yes, sqlite3

#

actually it's aiosqlite3 but it doesn't make a difference from what i can tell

gilded narwhal Mar 8, 2019, 12:07 AM

#

you can use the alter table [table_name] add column [column_name] [column_type] default [default_value] query for adding columns

#

you can also leave the default out i believe

#

as for checking whether the table exists first, im not sure

raw onyx Mar 8, 2019, 12:08 AM

#

i might be able to do alter table if not exists

#

anyways, thanks for the help!

gilded narwhal Mar 8, 2019, 12:08 AM

#

wouldnt that be doing the opposite?

#

trying to alter the table if it doesnt exist?

#

which would then fail

raw onyx Mar 8, 2019, 12:08 AM

#

my bad

#

it would probably be if exists then

#

i got confused with create table if not exists

gilded narwhal Mar 8, 2019, 12:09 AM

#

https://stackoverflow.com/a/40908855/10444096 check this out, looks like you cant do 'if exists' in alter queries

Stack Overflow

ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite

We've recently had the need to add columns to a few of our existing SQLite database tables. This can be done with ALTER TABLE ADD COLUMN. Of course, if the table has already been altered, we want...

#

but you can use that pragma

raw onyx Mar 8, 2019, 12:10 AM

#

so, there's no sqlite command for if exists, but i can work my way around that

gilded narwhal Mar 8, 2019, 12:10 AM

#

not in alter queries, yes

prisma mesa Mar 8, 2019, 12:11 AM

#

Hey, can someone help me formulate a query in MySQL please. I'm struggling to figure it out

I have values in my table that follow a format similar to this characters-uniqueID

I need to find a value in my table thats equal to a string I compare against. I then need to update all the characters that appear before the hyphen to a new value

nova hawk Mar 8, 2019, 12:15 AM

#

You want to do this with SQL queries?

prisma mesa Mar 8, 2019, 12:19 AM

#

well, its being done outside, but i also need to update the value inside the table

nova hawk Mar 8, 2019, 12:20 AM

#

You select the value, handle it in python, then update the values

prisma mesa Mar 8, 2019, 12:25 AM

#

i understand, i was just a little stumped by how i'd formulate the query. i would have replaced the values with python values. however, i think ive found an alternative solution which could avoid this. so i'll try that, and if it doesnt work i'll come back

ionic zenith Mar 8, 2019, 12:51 AM

#

Hello! I'm not sure if this is the best place to ask since it's mostly of a design question that involves a Discord Bot (using discord.py rewrite) and a PSQL database, so if it isn't the correct place I apologize and I'll switch to a proper channel.
So I'm building a feature on a bot which awards users points every X amount of minutes. The points are currently stored in a database. Have to take in consideration that the bot can see over 150k users, so it's not small and I want to take performance into account.

At first I thought of storing the time the last message was sent by the user in the database and when that user talks again retrieve the last time the user chatted, calculate and update the value on the database, but this is two queries.

Second thought I got is just one database call when the user sends a message and do it in a single query with something along the lines of:

UPDATE users a 
SET points = a.points + round((extract(epoch from now() at time zone 'utc' at time zone 'utc') - extract(epoch from lastmsg)) / 60) * b.rewardpts,
lastmsg = now() at time zone 'utc'
FROM guilds b
WHERE a.guild = b.guild;

My question is, is there any better (and if possible efficient) method to do this? I like the second approach but i'm not too sure if it would affect in performance due to many users talking.

EDIT: Now that I noticed, if the user never talks, last message would be empty the first time they chat...

feral flame Mar 8, 2019, 12:55 AM

#

Hey! Any good online database schema designer? I tried dbdiagram.io but the code generated is not working with SQLite

#

Another solution would be "translating" the generated code

#

Any idea why ALTER TABLE `orders` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

#

doesn't work ?

#

sqlite3.OperationalError: near "FOREIGN": syntax error

#

Only stackflow I read about it says that the foreign key should be within parenthesis (and it is)

nova hawk Mar 8, 2019, 1:06 AM

#

Sqlite doesn't support adding constraints to existing tables

feral flame Mar 8, 2019, 1:10 AM

#

Only at CREATE then?

nova hawk Mar 8, 2019, 1:12 AM

#

Yes

feral flame Mar 8, 2019, 1:14 AM

#

ty

#

For beginners you recommend SQLite or PostgreSQL ?

#

it's a small project, not a big data thing

torn sphinx Mar 8, 2019, 1:19 AM

#

postgresql

feral flame Mar 8, 2019, 1:20 AM

#

Can it be stored into a file?

torn sphinx Mar 8, 2019, 1:21 AM

#

que?

feral flame Mar 8, 2019, 1:23 AM

#

Like with SQLite in can create a file database.db and use it as my database

#

PostegreSQL seems to be server side (if that's the correct expression)

tame quartz Mar 8, 2019, 1:33 AM

#

Anyone familiar with MySQL/MariaDB know how to just get the create table statement from "show create table"?

#

Cursor result seems to pull it as a list which is making it tough to parse

#

Example: if you run "Show create table test" you get 2 columns in the return, one has the table name and one the create statement. When you get it thought a cursor in MySQL.connector it returns a list

#

Of the 2 values

feral flame Mar 8, 2019, 3:11 AM

#

Is it bad to insert a Null value into a varchar? Idk if it is even possible

tame quartz Mar 8, 2019, 3:34 AM

#

What DB? @feral flame

feral flame Mar 8, 2019, 3:37 AM

#

SQLite

tame quartz Mar 8, 2019, 3:39 AM

#

Usually nulls aren't awful in relational dbs

#

And are preferred to blanks

#

Blanks usually allocate memory while nulls don't

feral flame Mar 8, 2019, 3:46 AM

#

Ty

#

Damn this is a pain in the ass that SQLite doesn't support adding foreign keys after

tame quartz Mar 8, 2019, 3:49 AM

#

https://www.sqlite.org/nulls.html

feral flame Mar 8, 2019, 3:53 AM

#

I was asking about the Null because I want to do a table with like (Null, green, red, blue) with corresponding ids and if the color is grey (or any color not RGB) return Null

#

idk if it is better to let a Null into the other table, or returning id 1 (Null) from this table

#

idk if that makes sense

📎 unknown.png

#

~~btw is a foreign key optional? because in the 2nd example there is a Null value~~

feral flame Mar 8, 2019, 3:23 PM

#

Can someone give me an opinion about which of the two examples above is better? Or advantages/disadvantage of each one?

meager agate Mar 8, 2019, 4:04 PM

#

Just put the color and then handle the logic for what you return in your code. I guess I could see why you might want to do it this way

#

It just seems limiting if you ever wanted to change things in the future

regal portal Mar 8, 2019, 4:10 PM

#

Hello i have (0, 1, 0) as result
(0, 1, 0)

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record inserted.")```

lilac sundial Mar 8, 2019, 4:39 PM

#

Hello guys. I am using MySQL and since yestarday my queries started timing out. Real simple and small queries (adding a column or a table or creating a view). It seems to be the case with me innoDB table only. Previously such queries had no problem and were instant. Any idea what can be the problem ?

feral flame Mar 8, 2019, 4:43 PM

#

@meager agate sorry I didn't understand what you recommended. Option 1 (putting Null values into FK while there is no matching color in the color table) or Option 2 (Letting a Null option in the color table)

regal portal Mar 8, 2019, 8:53 PM

#

import mysql.connector

mydb = mysql.connector.connect(user='xxxxxx', password='xxxxxx', database='xxxxxx')
mycursor = mydb.cursor()

sql = "INSERT INTO Blacklist (reason) VALUES ('test')"
mycursor.execute(sql)

mydb.commit()```

#

📎 IMG_20190308_215211.png

#

I have this result

eager star Mar 8, 2019, 9:41 PM

#

whats the column type

#

might have an autoincrement on it

regal portal Mar 8, 2019, 9:45 PM

#

It say i can only do that to the first column

eager star Mar 8, 2019, 9:46 PM

#

can you paste result of show create table Blacklist

regal portal Mar 8, 2019, 9:48 PM

#

📎 IMG_20190308_224754.png

#

When i want to change INT to Varchar, it return me automaticily INT

📎 IMG_20190308_225007.png

eager star Mar 8, 2019, 9:52 PM

#

cant see forsenT

regal portal Mar 8, 2019, 9:57 PM

#

📎 IMG_20190308_225716.png

dusky cape Mar 9, 2019, 2:20 PM

#

does SQLite have a function/parameter to keep a trailing zero? it appears to take values like 525.20 and add 525.5 into the database. Thoughts? [Also, the column is set to be a Real Value]

dusky cape Mar 9, 2019, 2:39 PM

#

Never mind my question. I realized it was easier to add a clause when going to display the values to essentially re-add the trailing zero.

As an added note. the values are all currency based, so they are formatted for 0.00 when originally added through the uses of the decimal module and the native round().

Since SQLite3 isn't saving the trailing zero I just re-added it by calling Decimal and round() function when the program goes to display.

regal portal Mar 9, 2019, 3:06 PM

#

📎 IMG_20190309_160444.png

#

Hello why is that not working? :f

haughty glen Mar 9, 2019, 5:30 PM

#

Need help with some SQL. I know how I could do this, but i'm wondering if there is a common operation to solve this without doing multiple querys

So I have a many to many table with a column "Count". It is tracking how many times one user has won a game against another user. I'm wanting to get "Hiscore" results where it is sorted by the user with the most wins. ie. the sum of the count column where they are in the "Winner" column.

I could do this by getting all the users and then filtering my hiscores table by the user and getting the sum, then storing the data, then sorting hi to low, but, I feel like there is a SQL query that will do all this for me. Any direction?

heavy horizon Mar 9, 2019, 5:30 PM

#

any good tutorial to setup postgresql

#

@haughty glen https://www.w3schools.com/sql/sql_orderby.asp

#

no really sure if that's what you want

haughty glen Mar 9, 2019, 5:39 PM

#

i found this stack voerflow... ill give it a shot https://stackoverflow.com/questions/13060782/sum-data-in-a-column-based-on-another-column-data

Stack Overflow

SUM() data in a column based on another column data

I have a sql table

Project ID Employee ID Total Days

1 100 1
1 100 1
1 100 ...

#

Are you just looking to install postgres or connect it to a certain project?

keen rock Mar 9, 2019, 8:12 PM

#

So I have a db setup but I don't know how to actually grab that data from the database and use it in my python; any tips?

#

Using MongoDB

keen rock Mar 9, 2019, 8:28 PM

#

@novel wharf Can you help?

novel wharf Mar 9, 2019, 8:31 PM

#

Haven't used MongoDB.

heavy horizon Mar 9, 2019, 11:51 PM

#

should i create a new role in postgres for my discord bots?

quasi holly Mar 10, 2019, 1:08 AM

#

@heavy horizon I used the postgres personally with my bots but you could do that if you want.

shadow willow Mar 10, 2019, 1:35 AM

#

I have an issue I've been trying to fix all day, but I couldn't figure it out. It's probably something very simple that I overlooked, but:

#

I'm new to database creation and this is my setup so far.

#

http://prntscr.com/mvpx6g

Lightshot

Screenshot

Captured with Lightshot

#

But the problem is, I keep receiving this same error over and over again.

#

(10061 No connection could be made because the target machine actively refused it)

#

Could someone please tell me what I'm doing wrong?

#

@ me. ^

regal portal Mar 10, 2019, 7:37 AM

#

@shadow willow you have to set the username and the password

earnest swan Mar 10, 2019, 6:31 PM

#

@shadow willow Hello, I know your problem comes from having no localhost MySQL server set up and I think your solution is to look into XAMPP, as that's the extent of my knowledge about running MySQL servers. I used XAMPP for PHP initially, but using the mysql.connector module like you are doing, you can access it exactly the way you're doing it right now with Python.

#

Other than that, this is what the connector should look like:

#

cnx = mysql.connector.connect(user="root", password="", host="localhost")

#

https://www.apachefriends.org/download.html

Download XAMPP

Download XAMPP for Windows, Linux, and OS X. Older versions of Solaris are also available.

#

Setting and starting up the (Apache web server + MySQL server) will allow you to acces PHPmyadmin through: http: // localhost:(PORT NUMBER)/phpmyadmin/

shadow willow Mar 10, 2019, 7:08 PM

#

@earnest swan Ty.

clear adder Mar 10, 2019, 7:44 PM

#

Hello there 👋

#

A little question about postgresql

#

INSERT . . . ON CONFLICT DO NOTHING

#

So that means if there’s a conflict the value won’t be inserted?

novel wharf Mar 10, 2019, 8:03 PM

#

ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative action.
ON CONFLICT DO UPDATE updates the existing row that conflicts with the row proposed for insertion as its alternative action.

eager star Mar 10, 2019, 8:29 PM

#

for mysql you can except the integrity error and just pass

ionic pecan Mar 10, 2019, 9:04 PM

#

mysql cares about any kind of data integrity?

eager star Mar 10, 2019, 9:16 PM

#

mysql FeelsOkayMan 👉 ❤

torn sphinx Mar 10, 2019, 11:16 PM

#

how do i delete all rows of a sqlite db

#

table

toxic rune Mar 10, 2019, 11:20 PM

#

DELETE FROM table;

This should delete all rows. (You can select specific ones by doing WHERE ...

torn sphinx Mar 10, 2019, 11:20 PM

#

i tried that...

toxic rune Mar 10, 2019, 11:21 PM

#

Any errors, something? I don't see why it wouldn't work.

torn sphinx Mar 10, 2019, 11:21 PM

#

no

#

no errors

copper sphinx Mar 10, 2019, 11:31 PM

#

What am I doing wrong?

select 
   a.person_name, a.channel_id, count(*), b.channel_name
from 
   discord_messages a
GROUP BY 
   a.person_name, 
   a.channel_id 
FULL OUTER JOIN discord_channels b ON a.channel_id = b.channel_id