#databases | Python | Page 65

willow sentinel Apr 28, 2019, 8:45 PM

#

I erased the database and then ran if the user didnt have a account his part

📎 image0.png

torn sphinx Apr 28, 2019, 8:46 PM

#

What sql query you used to create the db?

willow sentinel Apr 28, 2019, 8:46 PM

#

Sqlite3

torn sphinx Apr 28, 2019, 8:47 PM

#

Not the db type

willow sentinel Apr 28, 2019, 8:47 PM

#

Oh all three numbers are integers

torn sphinx Apr 28, 2019, 8:47 PM

#

I am gonna try to recreate your db

#

Like you know CREATE TABLE ...

willow sentinel Apr 28, 2019, 8:48 PM

#

Yes

#

Create table if not exists usersmoney (Id INTEGER DEFAULT 1000, money DEFAULT INTEGER, streak INTEGER DEFAULT 0);

torn sphinx Apr 28, 2019, 8:51 PM

#

BTW i see you are using a non-async(sqlite3) lib with an async lib(discordpy) i wouldn't do that if i were you

willow sentinel Apr 28, 2019, 8:51 PM

#

Already been told that

#

Kinda too late?

torn sphinx Apr 28, 2019, 8:51 PM

#

Never too late

willow sentinel Apr 28, 2019, 8:51 PM

#

Ok then whaat do i use?

torn sphinx Apr 28, 2019, 8:52 PM

#

I mean sqlite doesn't have async alternatives iirc but you can switch to MySQL or PostgreSQL

willow sentinel Apr 28, 2019, 8:52 PM

#

Would i have to rewrite my code?

#

Hat much

#

Eh>

torn sphinx Apr 28, 2019, 8:53 PM

#

Just a little bit worth it imo

#

BTW your table name doesn't match

#

In your screenshot you refer to it as usermoney and on the query its usersmoney

willow sentinel Apr 28, 2019, 8:55 PM

#

I just typed it wrong on here probably

#

How would i make a MySQL in python

#

I’m looking at the website and it looks like you have too login?

#

To*

torn sphinx Apr 28, 2019, 8:56 PM

#

You need to run the db seperately

#

And use aiomysql to communicate with it

willow sentinel Apr 28, 2019, 8:56 PM

#

You see i cant do that

#

Is there an async sql that i can run inside of python

torn sphinx Apr 28, 2019, 8:58 PM

#

https://github.com/jreese/aiosqlite

#

@willow sentinel found the problem

#

id = currentid should be id = c.fetchone()

willow sentinel Apr 28, 2019, 9:02 PM

#

That was what it used to be but then it would always create a new row no matter what

torn sphinx Apr 28, 2019, 9:03 PM

#

try replacing id == None to id is None

#

(both)

willow sentinel Apr 28, 2019, 9:04 PM

#

Currently i cant get. Aiosqlite to work so hold it

#

📎 image0.png

#

📎 image0.png

torn sphinx Apr 28, 2019, 9:08 PM

#

You can only use await in async functions

#

Or you can use async.run

#

instead of await

willow sentinel Apr 28, 2019, 9:09 PM

#

Now its telling me that dB isn’t defined

#

Nvm got it

#

📎 image0.png

#

I cant await this?

torn sphinx Apr 28, 2019, 9:14 PM

#

@willow sentinel You can't use await while not in an async function, But you can use asyncio.run -> async.run(cursor.execute((...))

willow sentinel Apr 28, 2019, 9:15 PM

#

Ok now I’m getting context manager object is not iterable

torn sphinx Apr 28, 2019, 9:16 PM

#

@willow sentinel make an async def main(...

#

And call async.run on that

willow sentinel Apr 28, 2019, 9:16 PM

#

Gosh databases are so frustrating

torn sphinx Apr 28, 2019, 9:18 PM

#

ye

willow sentinel Apr 28, 2019, 9:19 PM

#

Ok so now i have another syntax error.

#

📎 image0.png

torn sphinx Apr 28, 2019, 9:22 PM

#

@willow sentinel make a main func

novel gust Apr 28, 2019, 9:23 PM

#

stop making d.py commands in on_message

#

dont think you can hide that from me @willow sentinel

torn sphinx Apr 28, 2019, 9:24 PM

#

He seems like hes still using the old version

novel gust Apr 28, 2019, 9:24 PM

#

where?

#

also aiosqlite is bad

#

use asyncpg and pg

torn sphinx Apr 28, 2019, 9:25 PM

#

he said he can't use postgre and mysql

novel gust Apr 28, 2019, 9:25 PM

#

lmao why

#

cause he's using replit?

torn sphinx Apr 28, 2019, 9:26 PM

#

idk

#

@novel gust any idea to store a dict(str:str) and a list of strings?

novel gust Apr 28, 2019, 9:27 PM

#

a dict

#

of strings to strings

#

and a list of strings

#

ok focus on the first one

#

explain the purpose of this dict

torn sphinx Apr 28, 2019, 9:28 PM

#

Holding addresses for non-numerical ids

willow sentinel Apr 28, 2019, 9:30 PM

#

Eh?

📎 image0.png

#

Do i need to await this?

torn sphinx Apr 28, 2019, 9:31 PM

#

@willow sentinel just use a main func

willow sentinel Apr 28, 2019, 9:32 PM

#

That’s what. I thought this was.

torn sphinx Apr 28, 2019, 9:33 PM

#

@willow sentinel So this is in async def main()?

willow sentinel Apr 28, 2019, 9:35 PM

#

I didnt know what a main func was so :/

#

Like this?

📎 image0.png

torn sphinx Apr 28, 2019, 9:37 PM

#

forget what i said

#

What are you trying to achieve?

willow sentinel Apr 28, 2019, 9:38 PM

#

A database that uses I’d’s to determine the amount of ‘money’ someone has and then use it for gambling etc.

torn sphinx Apr 28, 2019, 9:39 PM

#

And what are you trying to achieve in the async with... part?

willow sentinel Apr 28, 2019, 9:40 PM

#

Not really anything i just want it to work

torn sphinx Apr 28, 2019, 9:42 PM

#

Wdym by work?

willow sentinel Apr 28, 2019, 9:43 PM

#

Function in a way that doesn’t cause any errors and keeps players data stored safely

torn sphinx Apr 28, 2019, 9:43 PM

#

Drop the main and async with part

#

You just need to replace the sqlite parts

willow sentinel Apr 28, 2019, 9:44 PM

#

How so?

torn sphinx Apr 28, 2019, 9:45 PM

#

i mean replace conn.execute etc

willow sentinel Apr 28, 2019, 9:46 PM

#

But i set con = aiosqlite.connect?

#

Conn

torn sphinx Apr 28, 2019, 9:48 PM

#

async with aiosqlite.connect(...) as conn

willow sentinel Apr 28, 2019, 9:53 PM

#

Like this?

📎 image0.png

novel gust Apr 28, 2019, 10:08 PM

#

stop being fucking ignorant about aiosqlite

#

and python in general

#

you need to replace async with with await

spiral burrow Apr 28, 2019, 10:09 PM

#

Really

willow sentinel Apr 28, 2019, 10:09 PM

#

Ive already done that and it just gives me syntax errors

novel gust Apr 28, 2019, 10:09 PM

#

seen his other code?

#

kthxbye

spiral burrow Apr 28, 2019, 10:09 PM

#

!tempmute 328678556899213322 1d take some time to cool off

delicate fieldBOT Apr 28, 2019, 10:10 PM

#

:incoming_envelope: :ok_hand: muted @novel gust until Mon, 29 Apr 2019 22:10:01 GMT (take some time to cool off).

spiral burrow Apr 28, 2019, 10:10 PM

#

Stop being shitty to other people

willow sentinel Apr 28, 2019, 10:11 PM

#

He’s probably gonna yell at me in anther server instead lol. But thanks.

torn sphinx Apr 28, 2019, 10:21 PM

#

@willow sentinel async with aiosqlite.connect(...) as conn:

fading river Apr 28, 2019, 10:26 PM

#

Anybody know how the precision argument to a Float works in SQLAlchemy? The docs just say it's "the numeric precision for use in DDL CREATE TABLE", whatever that means

#

I just want to store values with a four digit significand with one digit after the radix, like 123.4

#

So if I can hint the underlying sqlite store to use fewer bytes I would like to do that.

torn sphinx Apr 28, 2019, 10:28 PM

#

DECIMAL(p,s): Exact numerical, precision p, scale s. Example: decimal(5,2) is a number that has 3 digits before the decimal and 2 digits after the decimal - stackoverflow

fading river Apr 28, 2019, 10:29 PM

#

Wouldn't that be with the SQLAlchemy Numeric type, not Float?

torn sphinx Apr 28, 2019, 10:30 PM

#

True but i think precision would be the same

fading river Apr 28, 2019, 10:30 PM

#

I have actually read the doc, it says Numeric is "A type for fixed precision numbers, such as NUMERIC or DECIMAL."

#

But I didn't want the overhead of translating into Python Decimal objects

glossy scroll Apr 28, 2019, 11:06 PM

#

I've been invited to help out with a project using MariaDB and I'm pretty sure it should be asynchronous because ofdiscord.py as well as pretty consistent errors for example: 2019-04-28 22:38:50 21 [Warning] Aborted connection 21 to db: '<db>' user: '<user>' host: '<ip>' (Got an error reading communication packets)(I removed the db name, the user and the ip). I believe this comes from discord.py... but I'm honestly not sure
However there doesn't seem like there's many options that'll work. Can anyone point to a good MariaDB asynchronous library, or should I just convert over to something like Postgres with asyncpg or am I just incorrect about my assumption?

keen cedar Apr 28, 2019, 11:30 PM

#

After helping LukeAbby, if you can, can someone lemme know how I can refactor this query? :,)

WITH pls_refactor_this AS 
    (WITH filtered_scores AS 
        (SELECT user_id, exp FROM scores WHERE guild_id = $1) 
     SELECT user_id, exp, ROW_NUMBER() OVER (ORDER BY exp DESC, user_id DESC) FROM filtered_scores) 
SELECT row_number FROM pls_refactor_this WHERE user_id = $2;```

#

Got it to this, can I refactor it any further?

WITH ordered_scores
AS (
    SELECT user_id ,ROW_NUMBER() OVER (
            ORDER BY exp DESC, user_id DESC
            )
    FROM scores
    WHERE guild_id = $1
    )
SELECT row_number
FROM ordered_scores
WHERE user_id = $2;```

Also, there won't be any side effects from this, right?

cunning ginkgo Apr 29, 2019, 6:52 AM

#

How could I make an expiring database entry in postgresql? It's linked to a flask backend but that doesn't matter.

#

Like, it's deleted after x hours sorta thing

pure scroll Apr 29, 2019, 7:50 AM

#

there is no such functionality, you need to cleanup your tables yourself. E.g have a cron job that would run delete query every X minutes

red osprey Apr 29, 2019, 1:43 PM

#

Guys, I used SQLAlchemy to develop a simple web app connected to a local sqlite db while testing. It uses sessions (db.session.add, db.session.commit etc). My prod db is in google cSQL. I have docs showing how to connect using pymysql, but that seems to be create a connection, and then squirt SQL commands through the connection. Is there a way to use db.sessions with cSQL?

wind pelican Apr 29, 2019, 6:57 PM

#

looks like sessions are part of the sqlalchemy library, so no not likely.
what part of the sessions do you like? the sql bindings you are using might have something similar

#

for example sqlite has a cursor that you can use to build transactions

#

oh looking in to it anything that uses pythons db api spec is likely to autocommit and so transactions dont reall work :/

inner lintel Apr 30, 2019, 6:33 AM

#

Hello,
my question aboutredis-py
can i store list on it ?

my_list=[{"name": "James", "age": "12"}, {"name": "Jorden", "age": "15"}]
db.set("list", my_list)```

pure scroll Apr 30, 2019, 7:27 AM

#

you need to serialise it, redis as any other storage is not able to store python object, you can only store serialised objects

red osprey Apr 30, 2019, 8:08 AM

#

@wind pelican In my tests, using sqlite, it enabled me to create a generic upsert function that each of my models could use (where the unique_id is NOT the pk - dont ask, I didn't design the db...). Worked a charm, so was hoping to do a simple lift n shift to my cSQL db

#

def upsert(model, item, **kwargs):
    dt = datetime.now()
    #session = db.session
    instance = db.session.query(model).filter_by(**kwargs).first()
    if instance:
        # update
        print('Exists')
        itemmembers = [attr for attr in dir(item) if not (attr.startswith("_") or  attr.startswith("query") or  attr.startswith("metadat"))]
        instancemembers = [attr for attr in dir(instance) if not attr.startswith("__")]
        for elem in itemmembers:
            print(elem, getattr(item, elem), ' | ', getattr(instance, elem))
            # check each member in item
            if getattr(item, elem) is not None:
                # if the received instance contains a value for
                # that member, then update the value in the db
                setattr(instance, elem, getattr(item, elem))
        setattr(instance, 'updated_at', dt)
        db.session.commit()
        return "Exists: " + instance.unique_id
    else:
        # insert
        print('Update')
        instance = item
        setattr(instance, 'created_at', dt)
        setattr(instance, 'updated_at', dt)
        db.session.add(instance)
        db.session.commit()
        return "Insert: " + instance.unique_id```

#

Background: It's a system info logger. Clients send a JSON object of a list of infos. This web app receives the JSON, maps the keys to db fields, and then does an upsert

#

Otherwise, crafting an INSERT ON DUPLICATE UPDATE for each JSON type and class would be a ball-ache

#

Connection turned out to be really simple using cSQL proxy: ```python
from flask_sqlalchemy import SQLAlchemy

app.config['SQLALCHEMY_DATABASE_URI'] = (
'mysql+pymysql://{nam}:{pas}@127.0.0.1:3306/{dbn}').format (
nam=db_user,
pas=db_password,
dbn=db_name
)
db = SQLAlchemy(app)``` - I'll use a unix_socket once I move the app to GAE

knotty parcel Apr 30, 2019, 7:21 PM

#

Can I get help?

#

With sqlite3

#

https://hastebin.com/oqocosaxer.py

#

Ignoring exception in member_join
Traceback (most recent call last):
File "/home/runner/.local/lib/python3.6/site-packages/discord/client.py", line 255, in _run_event
await coro(*args, **kwargs)
File "/home/runner/cogs/welcome.py", line 32, in on_member_join
embed = discord.Embed(colour=discord.Colour(0xac6f8f), description=(result1[0]).format(members=members,mention=mention,user=user, guild=guild))
AttributeError: 'NoneType' object has no attribute 'format'

#

📎 main.sqlite

wary merlin Apr 30, 2019, 7:26 PM

#

not related to sqlite

knotty parcel Apr 30, 2019, 7:27 PM

#

Is there a solution?

wary merlin Apr 30, 2019, 7:27 PM

#

u_u

knotty parcel Apr 30, 2019, 7:27 PM

#

I tried debugging it

#

I get None

wary merlin Apr 30, 2019, 7:27 PM

#

use aiosqlite plz

knotty parcel Apr 30, 2019, 7:27 PM

#

aiosqlite?

wary merlin Apr 30, 2019, 7:28 PM

#

sqlite is blocking

knotty parcel Apr 30, 2019, 7:28 PM

#

How do I use it

wary merlin Apr 30, 2019, 7:28 PM

#

https://github.com/jreese/aiosqlite#usage

GitHub

jreese/aiosqlite

asyncio bridge to the standard sqlite3 module. Contribute to jreese/aiosqlite development by creating an account on GitHub.

#

also

#

return
print("None")

#

return stop the function

#

the print is never called

#

>>> def f():
    print("1")
    return
    print("2")

    
>>> f()
1

knotty parcel Apr 30, 2019, 7:31 PM

#

I see thank you

wind pelican Apr 30, 2019, 8:12 PM

#

@knotty parcel also you are using format strings to generate your sql query, do not do that. Its only advantage is that it increases the chances of someone breaking your queries/hacking your bot.
use the built in system for passing in arguments:

t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)```

#

there are more advanced options but the tldr is that you can pass your data in as actual data and sqlite will take care of sanitizing most of it

knotty parcel Apr 30, 2019, 8:16 PM

#

okay
@torn sphinx ...

torn sphinx Apr 30, 2019, 10:02 PM

#

the way I showed you worked I see what you did wrong from what I showed

#

description=(result1[0]) the () should not have been there

#

@knotty parcel

fluid nova May 1, 2019, 3:19 AM

#

really not sure if this is the right place to ask this but i need help with something that lnolves a large amount of text data... json... or somethign else if recommended .. and flask/jinja

#

anyone available ?

drifting moth May 1, 2019, 7:54 AM

#

hey guys

#

I need some assistance with running some SQL commands, what method is best to communicate these?

red osprey May 1, 2019, 9:04 AM

#

@wind pelican I checked, and looking here - https://cloud.google.com/appengine/docs/flexible/python/using-cloud-sql - cSQL does support SQLAlchemy sessions. Turns out 1 of the columns in cSQL was slightly different to the corresponding column in my sqlite db (and my model class). Once I matched it across, it works. Just for ref, in case anyone asks in future. Means I can use that upsert function and have a really lean app

Google Cloud

Using Cloud SQL for MySQL | App Engine flexible environment fo...

torn sphinx May 1, 2019, 11:48 AM

#

if i want to make a json file with information about all the members in server. i need to make a json list for any member?

#

i wrote here becuse it about json

river barn May 1, 2019, 12:55 PM

#

I already answered you in #discord-bots

#

Don't post questions in two different channels please

fading river May 2, 2019, 3:01 AM

#

I have a bunch of SQLAlchemy ORM-mapped classes in different files, but when it comes time to create the tables for them, is there a simple way to do that? Or do I have to hunt them down, import every single one, and do a metadata.create_all(engine) on them?

#

Actually I'm going to try having a single declarative base

#

Yep. That's how I should have done it.

#

oh god circular import I'm so bad at this

ionic pecan May 2, 2019, 5:56 AM

#

@fading river i‘d recommend using alembic for this

quiet ermine May 2, 2019, 10:53 AM

#

If its flask its probably best to use flask-migrate

#

Just to support multiple databases for debug and production

fading river May 2, 2019, 10:58 AM

#

not flask, I'm polling IOT devices and feeding the results to dash

hardy grove May 2, 2019, 1:56 PM

#

Hey There

#

Does Heroku Free Hosting Supports SqlLite3 Python

#

?

#

Will it Work

#

Just Ping me :D

river barn May 2, 2019, 5:12 PM

#

sqlite3 cannot really be "hosted", it's just a file based db lol

#

so yeah it should work

#

~~but don't use heroku~~

fading river May 2, 2019, 5:39 PM

#

@ionic pecan that seems to be a migration tool, would it help me out in this scenario?
So what I've got is a db.py that hosts the declarative base, which is then imported to the various files that have ORM mapped classes. But those files must be run (imported somewhere) in order to register the classes, meaning I can't have both the declarative base and the call to create the tables in the same file!

#

Because that's a textbook circular import

ionic pecan May 2, 2019, 5:46 PM

#

Yeah

#

The idea is to drop the c all to create the tables from your regular code altogether and have alembic manage that

#

Because if you do that you will also get conflicts once you start changing your schema

patent glen May 2, 2019, 6:12 PM

#

@hardy grove sqlite will not work on Heroku, it stores data in a file and Heroku deletes all files other than the ones that were originally deployed periodically

river barn May 2, 2019, 6:38 PM

#

This is why you don't use heroku

hardy grove May 2, 2019, 6:54 PM

#

then tell me a free hosting server ;-;

torn sphinx May 2, 2019, 7:21 PM

#

How do you define a string on creating a table in psycopgs2, I suppose its STRING?

#

Or is it TEXT?

tropic zealot May 2, 2019, 7:26 PM

#

TEXT

torn sphinx May 2, 2019, 7:29 PM

#

And how do I insert a null value into it? do I just do ""?

#

@tropic zealot

tropic zealot May 2, 2019, 7:32 PM

#

Yeah, that looks right.

torn sphinx May 2, 2019, 7:32 PM

#

Sorry for tagging you btw

tropic zealot May 2, 2019, 7:32 PM

#

All good.

torn sphinx May 2, 2019, 7:33 PM

#

def create_server_if_not_exists(server_id: str):
    c.execute("SELECT COUNT(*) FROM Users WHERE UserID=%s", (str(server_id),))
    user_count = c.fetchone()[0]
    if user_count < 1:
        print("[Servers Table] Creating server with id " + str(server_id))
        c.execute("INSERT INTO Users VALUES (%s, %s, %s, %s, %s, %s)", (str(server_id), "", "", False, False))
        db.commit()```

#

Look good?

#

(I should use f strings but yeah)

#

@tropic zealot

#

Given that the 2&3 values are TEXT

tropic zealot May 2, 2019, 7:34 PM

#

Try it out and see. I've only just begun learning SQL myself.

torn sphinx May 2, 2019, 7:34 PM

#

oh ok

#

cheers

tropic zealot May 2, 2019, 7:35 PM

#

That looks like it's inserting a server ID into a users table. Are you sure that's what you wanna do?

torn sphinx May 2, 2019, 7:38 PM

#

Oh Cheers

#

Didn't notice that

#

😊

maiden halo May 2, 2019, 10:34 PM

#

📎 unknown.png

#

📎 unknown.png

#

Not really sure what to do with this now... It works on other apps, just not this...

lilac spruce May 3, 2019, 12:29 AM

#

so with dealing with sub/supertypes, are both the subtypes supposed to have the "same" primary key ?

fading river May 3, 2019, 12:36 AM

#

@maiden halo Your project is probably in a different venv than the one you have the mysql package installed in

#

Can you go to one of the projects where you have mysql importing successfully, and run this script python import mysql print(mysql)

lilac spruce May 3, 2019, 12:38 AM

#

this is the table set up i am looking at at the moment, but idk if its right

📎 teststuff.png

hardy grove May 3, 2019, 4:06 AM

#

Wow

light zinc May 3, 2019, 8:48 AM

#

Hey,
Questions about sqlalchemy
Can i add comment and doc for column at the same time? So I dont have to make variable or duplicate the comment?

price = db.Column(db.Integer, nullable=False, comment="Price is in cents.", doc="Price is in cents.")

#

And second question is how can I add comment on table?

maiden halo May 3, 2019, 8:59 AM

#

@fading river

📎 unknown.png

fading river May 3, 2019, 11:13 AM

#

So if you start the other script with python champs.py does it work?

maiden halo May 3, 2019, 11:27 AM

#

I made a new conda env and it worked out, but is there a way to install these modules for all envs?

#

Also I'm running into a new problem now. Not a syntax error, but wondering how can I get just clean info from DB without the dictionary tags and \n's

#

cursor.execute("SELECT champion FROM all_champions")
all_champs=cursor.fetchall()
cursor.execute("SELECT matchup from all_matchups")
all_matchups=cursor.fetchall()
for champion1 in all_champs:
    for champion2 in all_champs:
        matchup=f"{champion1} vs {champion2}"
        print(matchup)```

#

('Aatrox\n',) vs ('Aatrox\n',)

#

How do I remove the ('from infront and \n,) from behind?

#

Do I need to regex? I feel like there's an easier and better way to do this.

light zinc May 3, 2019, 11:53 AM

#

You can try something like:

re.sub(r"[\(\)\n',]", "",  champion1)

or maybe

re.sub(r"\W", "",  champion1)

Though if I can, I would fix how they are stored in db

glossy dock May 3, 2019, 12:52 PM

#

how does one create a sqlachemy class that inherits from two classes that share a base? I'm getting a

sqlalchemy.exc.InvalidRequestError: Class <class '__main__.Consumable'> has multiple mapped bases: [<class 'Action.Action'>, <class 'Item.Item'>]

I essentially want to have the tables 'action' and 'item', but a class Consumable that would let me have the properties of both actions and items and then save to both tables

pure scroll May 3, 2019, 12:53 PM

#

https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/mixins.html

visual rune May 3, 2019, 2:28 PM

#

Hey all, can anyone tell me whats wrong with this query ?

INSERT INTO prefixes (guild_id, prefix) VALUES (123456789, 'test') ON CONFLICT (guild_id) DO UPDATE SET prefix='test' WHERE guild_id = 123456789;

it returns this error column reference "guild_id" is ambiguous

#

oh and im using postgres

pure scroll May 3, 2019, 2:28 PM

#

WHERE prefixes.guild_id = 123456789;

#

should work

visual rune May 3, 2019, 2:30 PM

#

Yeah it works. Thanks

torn sphinx May 3, 2019, 3:11 PM

#

databases are hard :c

hot sphinx May 3, 2019, 4:39 PM

#

Hi all, I come from a JS background and the popular database is MongoDB for that. What is the equivalent of that for Python users? (What is the popular database I should study on)

ionic pecan May 3, 2019, 4:45 PM

#

PostgreSQL

hot sphinx May 3, 2019, 4:48 PM

#

Thanks! @ionic pecan Also is there official certifications for postgreSQL or mySQL? MongoDB has one: https://university.mongodb.com/certification/developer/about

MongoDB Developer Certification - MongoDB University

Become a MongoDB certified developer by taking MongoDB University developer track courses. Earn your developer certification and advance your career, learn more.

ionic pecan May 3, 2019, 4:52 PM

#

none that I‘m aware of, nope

#

the postgresql development group is a nonprofit afaik

hot sphinx May 3, 2019, 4:53 PM

#

@ionic pecan Ahh I see. Thank you! 😃

ionic pecan May 3, 2019, 4:53 PM

#

no prob

#

Feel free to ask here if you have any questions

hot sphinx May 3, 2019, 4:55 PM

#

Thanks! @ionic pecan I do have another: Is MySQL and Oracle the same thing? Im a little confused if theres a oracle db and a separate mySQL db when I hear people talk about it

ionic pecan May 3, 2019, 4:56 PM

#

I‘m pretty sure they are different

#

Oracle is some commercial database with AI in it and whatnot, mysql is open source

#

There‘s also MariaDB which is a fork of mysql

patent glen May 3, 2019, 5:34 PM

#

the confusion is because Oracle, the company, bought MySQL a while back.

#

so it owns both of them now

tawny sail May 3, 2019, 6:00 PM

#

hey guys

#

how do i make conditional insert in sqlalchemy-aio

#

like sql code would be (UPDATE guild_settings SET prefix=%s WHERE guild_id = %s', data)

tawny sail May 3, 2019, 6:27 PM

#

nvm,i got it

torn sphinx May 3, 2019, 10:18 PM

#

sql = "SELECT * FROM has_channel_scalit WHERE userID = %s"
val = (str(ctx.author.id))

That gives an error "near %s"

merry prism May 3, 2019, 10:49 PM

#

How is it supposed to tell what %s is? @torn sphinx

#

Look at how you wrote it

tawny sail May 4, 2019, 5:24 AM

#

@torn sphinx you have to provide a tuple at the end

#

for the actual value

#

it will look something like this

conn.execute(sql, (val,))```

merry prism May 4, 2019, 2:18 PM

#

Yup.. Declaring a SQL statement, then declaring the data that goes into it doesn't make any sense, its Python, top to bottom

keen cedar May 4, 2019, 3:03 PM

#

Okay so, I have a database table users that has columns user_id and exp.

Now, I have a dictionary, the keys are user ID's and the values are EXP.

I want to update the database table. If it contains the User ID already - it shall update the EXP if the new EXP from the dictionary is higher than the one in the database. If it doesn't contain the user ID - it should insert a new row, with the user ID and the experience from the dictionary.

What would the query look like? Don't want to loop through the list and querying each of the item of the dictionary separately

sleek mango May 4, 2019, 5:37 PM

#

Hey, how can I update all the documents in a collection in mongodb ? I just wanna add an array to the end of a document

#

I was thinking maybe I could loop through all the documents using pymongo and update them

#

Is there any GUI which can do this job for me ?

neon turtle May 5, 2019, 6:39 AM

#

i need info about dealing blob objects like documents and media files
like

#

inserting into a db
retrieving ...

#

the general way of how to do

neon turtle May 5, 2019, 7:18 AM

#

hey anyone there
i just need a tutorial or a blog kinda thing
ping me if u have one to give me

faint prairie May 5, 2019, 4:19 PM

#

you just treat them like any binary object, so open the file in binary mode, read it into memory, stuff it in the database

torn sphinx May 5, 2019, 4:44 PM

#

Error:

Error adding user: current transaction is aborted, commands ignored until end of transaction block

Code:

try:
            with self.db.cursor() as cursor:
                sql = """ INSERT INTO players (user_id, joined, xp_points) VALUES (%s, %s, %s)"""
                cursor.execute(sql, (member.id, member.joined_at, 0))
            self.db.commit()
            print("Added user %s to database." % member.id)
        except Exception as e:
            print("Error adding user: %s" % e)

I am unable to INSERT INTO my players table. I have added quotes, backticks, and deleted them entirely. I have attempted to emulate this variable declaration from https://pynative.com/python-postgresql-insert-update-delete-table-data-to-perform-crud-operations/:

  postgres_insert_query = """ INSERT INTO mobile (ID, MODEL, PRICE) VALUES (%s,%s,%s)"""

Still no such luck. Ive been trying to figure this out for a couple hours, so any direction would be greatly appreciated!

#

When I did have the quotes, Id get this error:

Error adding user: syntax error at or near "'user_id'"
LINE 1: INSERT INTO players ('user_id', 'joined', 'xp_points') VALUE..

river barn May 5, 2019, 4:47 PM

#

pretty sure you either don't need the commit() method with the context manager or it needs to be in the block

#

In the example you linked it doesn't use the with block so it's probably that

torn sphinx May 5, 2019, 4:51 PM

#

I didnt even notice that! ._.
Ill try it and get back to ya!

torn sphinx May 5, 2019, 5:27 PM

#

    def get_player(self, user_id):
        try:
            #with 
            cursor = self.db.cursor()
            sql = "SELECT 'user_id', 'joined', 'xp_points' FROM players WHERE 'user_id'=%s"
            cursor.execute(sql, (user_id,))
            result = cursor.fetchone()
            if not result:
                print("User does not exist: %s" % user_id)
            else:
                return result
        except Exception as e:
            print("Error looking up userid %s.\n%s" % (user_id, e))

    def add_all_users_to_db(self):
        for member in self.bot.get_all_members():
            self.add_user_to_db(member)

    def add_user_to_db(self, member):
        if self.get_player(member.id):
            return

        try:
            cursor = self.db.cursor() 
            sql = """ INSERT INTO player_users (user_id, joined, xp_points) VALUES (%s, %s, %s)"""
            cursor.execute(sql, (member.id, member.joined_at, 0))
            #self.db.commit()
            print("Added user %s to database." % member.id)
        except Exception as e:
            print("Error adding user: %s" % e)

#

Error looking up userid .
current transaction is aborted, commands ignored until end of transaction block
Error adding user: current transaction is aborted, commands ignored until end of transaction block

#

Stillk getting the same error >.>

river barn May 5, 2019, 5:35 PM

#

seems weird

#

btw for a discord bot you should use asyncpg, not psycopg2

torn sphinx May 5, 2019, 5:35 PM

#

I had no idea that existed 😮

river barn May 5, 2019, 5:36 PM

#

https://github.com/MagicStack/asyncpg

torn sphinx May 5, 2019, 5:38 PM

#

I appreciate it!

#

Im checking out the docs now

ornate isle May 5, 2019, 8:13 PM

#

If you want to use asyncpg with SQLA core, I’ve been using Tom Christie’s new databases library: https://github.com/encode/databases

GitHub

encode/databases

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

torn sphinx May 5, 2019, 11:03 PM

#

After I refactored it to include asyncpg/asyncio, I keep getting an error saying RuntimeWarning: coroutine 'connect_database' was never awaited, but when I debug the function it connects with no issue. Its not until I run everything within my object that I get that error. @river barn

rustic yarrow May 5, 2019, 11:13 PM

#

Anyone know what kind of issues you might run into on MongoDB compared to something like SQL?

formal oar May 5, 2019, 11:41 PM

#

cursor.execute(sql, (member.id, member.joined_at, 0)) you're missing a , @river barn

river barn May 5, 2019, 11:42 PM

#

?

formal oar May 5, 2019, 11:42 PM

#

(member.id, member.joined_at, 0,)

river barn May 5, 2019, 11:43 PM

#

A) Shouldn't be needed because the , at the end serves only for defining tuples with just one item
B) I didn't post the code

formal oar May 5, 2019, 11:44 PM

#

Oof pinged the wrong guy

torn sphinx May 5, 2019, 11:44 PM

#

Hey thanks!

#

I didnt notice that!

formal oar May 5, 2019, 11:44 PM

#

What do you mean serves only for defining tuples with just one item?

#

It won't work

river barn May 5, 2019, 11:50 PM

#

You don't need it

#

In [29]: (1)                                                                                                                                       
Out[29]: 1

In [30]: (1,)                                                                                                                                      
Out[30]: (1,)

In [31]: (1,2)                                                                                                                                     
Out[31]: (1, 2)

In [32]: (1,2,)                                                                                                                                    
Out[32]: (1, 2)```
see this

#

But I think you are getting the query formatting wrong, does psycopg2 use %s formatting for parameters?

torn sphinx May 5, 2019, 11:53 PM

#

Yes it does

#

not sure about asyncpg though

#

still reading those docs

river barn May 5, 2019, 11:53 PM

#

asyncpg uses $index

#

aka something like execute("SELECT name FROM test WHERE id=$1 AND exp=$2", user.id, user.exp)

torn sphinx May 5, 2019, 11:56 PM

#

That makes sense

#

Im still trying to figure out why I keep getting TypeError: A Future, a coroutine or an awaitable is required even though Im pretty sure Ive set all my coroutines and awaitables properly

ornate isle May 6, 2019, 12:05 AM

#

@torn sphinx show your code. are you awaiting it properly?

#

if youre calling it in a method, the methods need to be async as well and be awaited

#

@rustic yarrow if your data is very structured and would be using relations (table of users, each user owns books from the book table, each book has an author from the author table, etc) then mongo is not good

#

in general mongo is easier to start with but then a mistake in the long run, except in pretty specific use cases where the data is not handled via schema very well and the read/write patterns are such that mongo is better with

rustic yarrow May 6, 2019, 12:09 AM

#

So mongo is a mistake in long run because it becomes too difficult to manage relations?

ornate isle May 6, 2019, 12:10 AM

#

mongo is bad with relations in general in comparison

#

SQL is geared for it

#

mongo is happier when your data is all nested and doesnt have to relate to other things. it can do it but it is not great

#

beyond that, there are just larger issues that result out of having no schema

#

maintainability etc. mongo is much more difficult to manage as you grow

rustic yarrow May 6, 2019, 12:12 AM

#

I see that makes sense

torn sphinx May 6, 2019, 12:13 AM

#

Couldnt post the whole code becasue character restrctions and felt it was wrong to take it out of context

📎 rpg_bot.py

ornate isle May 6, 2019, 12:13 AM

#

you can use a pastebin or something next time but im looking at this. which methods are failing

torn sphinx May 6, 2019, 12:14 AM

#

Exception has occurred: TypeError
An asyncio.Future, a coroutine or an awaitable is required
  File "C:\Program Files\Python36\Lib\asyncio\tasks.py", line 526, in ensure_future
    raise TypeError('An asyncio.Future, a coroutine or an awaitable is '
  File "C:\Program Files\Python36\Lib\asyncio\base_events.py", line 452, in run_until_complete
    future = tasks.ensure_future(future, loop=self)
  File "C:\Users\Ryan\Desktop\WebDev\Python Projects\Misc\Bots\DiscordRpgBot\rpg_bot.py", line 128, in <module>
    loop.run_until_complete(RpgBot(BOT_TOKEN))
  File "C:\Program Files\Python36\Lib\runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "C:\Program Files\Python36\Lib\runpy.py", line 96, in _run_module_code
    mod_name, mod_spec, pkg_name, script_name)
  File "C:\Program Files\Python36\Lib\runpy.py", line 263, in run_path
    pkg_name=pkg_name, script_name=fname)

#

I forgot about pastebin lol

fringe tiger May 6, 2019, 12:15 AM

#

Opening that file in IDE immediately spawns "Coroutine was not awaited" errors

ornate isle May 6, 2019, 12:15 AM

#

haha

#

yeah @wild plank you are just giving asyncio a class

torn sphinx May 6, 2019, 12:15 AM

#

lmao dem feels exactly

ornate isle May 6, 2019, 12:15 AM

#

RpgBot() does not do anything on its own by instantiating it

#

oh yeah it does

#

but you still dont want to do it that way, also you dont want it to call a new event loop

#

you cant put async functions in init

#

__init__

#

put it in a method of the bot, and then await that method

fringe tiger May 6, 2019, 12:17 AM

#

What IDE are you using? It should notify you of these errors

slate spire May 6, 2019, 12:17 AM

#

you're trying to run the bot itself rather than calling run from it

#

you gave the RpgBot has a run method that runs the bot for you

ornate isle May 6, 2019, 12:18 AM

#

@mighty dome his __init__ method has the db start doing stuff async

#

which isnt going to work

slate spire May 6, 2019, 12:18 AM

#

it calls run_until_complete when initing it

#

and you wouldn't wrap the whole thing in run_until_complete either way

ornate isle May 6, 2019, 12:18 AM

#

yeah

#

or call a new event loop

fringe tiger May 6, 2019, 12:18 AM

#

Ye this file is just one big error 😄

ornate isle May 6, 2019, 12:19 AM

#

haha yeah well i think this is a first attempt at async stuff and async scares people away

torn sphinx May 6, 2019, 12:19 AM

#

Yeah I had

if __name__='__main__:
    bot = RpgBot(BOT_TOKEN)
     bot.run()

before I tried moving over to asyncpg, which I read needed asyncio

#

vscode @fringe tiger

#

lol it is my first time with async stuff

ornate isle May 6, 2019, 12:19 AM

#

yeah once you go asyncio you have to make sure eveyrthing support it

#

dont sweat it, it's difficult in python

#

remove the call to new event loop, have the db stuff occur in a method, which you await()

fringe tiger May 6, 2019, 12:20 AM

#

Aside from weird loop logic and few eyecandies, you're forgetting to call multiple await calls

ornate isle May 6, 2019, 12:22 AM

#

@self.bot.event
async def on_member_join(member):
    await self.add_user_to_db(member)

#

notice the await

#

gotta do that on everything basically once you go async

torn sphinx May 6, 2019, 12:23 AM

#

gotcha

fringe tiger May 6, 2019, 12:23 AM

#

Yes, 5 of those. Basically for each call of async function you need to write await before the call.

ornate isle May 6, 2019, 12:24 AM

#

async with self.db.cursor() as cursor:
    sql = """ INSERT INTO player_users (user_id, joined, xp_points) VALUES (%s, %s, %s)"""
    await cursor.execute(sql, (member.id, member.joined_at, 0))

#

etc

torn sphinx May 6, 2019, 12:40 AM

#

The bot works! I literally awaited everything, including my print statements lol
https://pastebin.com/j20TdkUz

Pastebin

[Python] RpgBot - Pastebin.com

#

Upon shutting down the bot, I still get this error RuntimeWarning: coroutine 'connect_database' was never awaited

fringe tiger May 6, 2019, 12:42 AM

#

No need for that, just await what is needed... but good work I guess

#

Idk why your IDE didn't warn you but for python dev I would recommend PyCharm

torn sphinx May 6, 2019, 12:43 AM

#

Yeah Im using vscode, not an IDE

#

That might explain it

#

Im running it through pycharm now

ornate isle May 6, 2019, 12:49 AM

#

meh

#

i write 90% async and never touch pycharm so its not a must

fringe tiger May 6, 2019, 12:53 AM

#

Welp if he had used it he would have saved himself a lot of time :P

ornate isle May 6, 2019, 12:53 AM

#

i dont think so. this is his first async project and he didnt now he had to 'await' stuff at all. he knows the line says something hasnt been awaited, he posted it

#

just didnt know what to do about it

fringe tiger May 6, 2019, 12:54 AM

#

Ah oki then. Idk I know the problem only from the last few msgs, I didn't go that far up.

ornate isle May 6, 2019, 12:55 AM

#

@torn sphinx same issue -- the db function isnt being awaited in the end. not really probematic, but theres a bigger potential issue -- you are connecting and disconnecting from the db every time you make a query to it. im not sure how frequently this bot will be doing stuff but that may or may not be the behavior you want

torn sphinx May 6, 2019, 12:56 AM

#

That db will be integral to the bot, since the bots function is to gamify discord a bit

ornate isle May 6, 2019, 12:56 AM

#

if you are fine with it doing it this way but just want to have it shut up with the asyncio errors, then put the connect_database function inside the run loop

#

yeah i just mean its slower to have it connect and disconnect on every single request

#

but either way youll want to remove self.db = connect_database() and put it somewhere inside the run loop

#

i'm not familiar with how the discord bot works but i assume it has some sort of startup and shutdown handlers, yeah?

#

assuming on_ready is on setup, then i'd put the db connect await in there

torn sphinx May 6, 2019, 1:00 AM

#

Yes it does

ornate isle May 6, 2019, 1:00 AM

#

and then the await disconnect on the shutdown one

#

self.db = await asyncpg.connect() in the startup, await self.db.close() in shutdown handler, as long as theyre all inside the run looop

torn sphinx May 6, 2019, 1:15 AM

#

I ended up doing this!

    def prepare_client(self):
        @self.bot.event
        async def on_ready():
            self.db = connect_database()
            await self.bot.change_presence(activity=Game(name=" DiscordRPG"))
            await self.add_all_users_to_db()
            await print("[*] Connected to Discord as: " + self.bot.user.name)
            await self.db.close()

#

Since connect_database already has the asyncpg.connect function in it

ornate isle May 6, 2019, 1:16 AM

#

yes that works

#

well

#

did you remove the await con.close() from connect_database

torn sphinx May 6, 2019, 1:17 AM

#

Yes

#

async def connect_database():
    connection = await asyncpg.connect(host=DB_HOST,
                                       port=DB_PORT,
                                       user=DB_USER,
                                       password=DB_PASS,
                                       database=DB_NAME
                                       )
    print('[*] Connected to database: %s' % DB_NAME)
    return connection

ornate isle May 6, 2019, 1:17 AM

#

boom. okay thats what i was wondering

torn sphinx May 6, 2019, 1:17 AM

#

😁

ornate isle May 6, 2019, 1:17 AM

#

perfect

torn sphinx May 6, 2019, 1:18 AM

#

I really appreciate the help!
Now to refactor the sql and I should be able to write to the db!

#

But im definitely not getting those errors anymore!

#

I have a feeling Ill be back plenty x)

fringe tiger May 6, 2019, 1:18 AM

#

But you still closed it at the end of on_ready?

torn sphinx May 6, 2019, 1:18 AM

#

For now, yeah

#

But this way Ill remember it when I put together the shutdown handler

fringe tiger May 6, 2019, 1:21 AM

#

I'm not sure what type of db you are using but what I have done is use a botvar (bot variable) for db connection.

Basically in your init just do
self.bot.connection = await connect_database()

#

Then you can acces it simply with self.bot.connection

torn sphinx May 6, 2019, 1:22 AM

#

PostgreSQL 10

#

When I tried adding is at a bot variable, it goes back to giving me RuntimeWarning: coroutine 'connect_database' was never awaited

fringe tiger May 6, 2019, 1:32 AM

#

Yes

#

Dude

#

If it's an async function

#

You need to await

torn sphinx May 6, 2019, 1:36 AM

#

right, this is what I had. I may have misunderstood what you were suggesting

def __init__(self, token):
        self.bot = Bot(client)
        self.bot.db = await connect_database()
        self.token = token
def prepare_client(self):
        @self.bot.event
        async def on_ready():
            await self.bot.db
            await self.bot.change_presence(activity=Game(name=" DiscordRPG"))
            await self.add_all_users_to_db()
            await print("[*] Connected to Discord as: " + self.bot.user.name)
            await self.db.close()

Problem is I get an invalid syntax since init is not an async function

#

and according to dkh, making init an async function doesnt work

fringe tiger May 6, 2019, 1:40 AM

#

Ah yes I'm on mobile, though you can use init but forgot. Still the suggestion stands, just declare it a botvar somewhere else. That way, in future, if you use cogs it'll be easy to pass commection around.

torn sphinx May 6, 2019, 1:41 AM

#

Now that makes sense! 😁
I didnt think about that! Thank you!

dawn pulsar May 6, 2019, 8:10 AM

#

Is there anyway I can do some "SELECT * FROM table WHERE column1 IN my_list"

pure scroll May 6, 2019, 8:17 AM

#

@dawn pulsar what do you mean by that and which database is that?

dawn pulsar May 6, 2019, 8:24 AM

#

MariaDB

#

So I have a list of people

#

And I only want to select data where the column (say name) is people in that list

pure scroll May 6, 2019, 9:09 AM

#

https://mariadb.com/kb/en/library/in/

MariaDB KnowledgeBase

IN

True if expression equals any of the values in the list

chilly hamlet May 6, 2019, 10:57 AM

#

I'm looking for recommendations for an ORM-ish library, which doesn't do the whole "keep track of loaded/added objects and then flush in one go" thing, but just has get/save/delete operations which translate directly to select/insert/update/delete queries. Searching the web didn't turn out much, but I don't really know how such a library would be called. Anyone know if such a thing exists?

terse stump May 6, 2019, 11:03 AM

#

not sure what you are asking about tbh. are you look for something simpler then SQLAlchemy?

#

Pony maybe?

chilly hamlet May 6, 2019, 11:11 AM

#

I didn't know about Pony, it's an interesting concept. but I don't think it's what I'm looking for

#

I think I just want something really simple, where the objects that represent DB rows and the stuff that loads and saves them keep no connections between each other, sort of keeping the entire thing as stateless as possible

#

even if that means that I loose some higher level features like lazy loading

#

Something that sits in the middle between SqlAlchemy's core and ORM parts. Like core, that works with record classes in its selects ands updates

pure scroll May 6, 2019, 11:55 AM

#

I think with sqlalchmeny you can just skip using session and use connection abstraction directly, that might do what you need

#

but anyways why don't you need those caching optimizations?

quiet ermine May 6, 2019, 12:04 PM

#

I am having issues trying to migrate a db with flask-migrate

#

Error: https://pastebin.com/aRWaeX1z

Pastebin

Traceback (most recent call last): File "/home/owen/.local/shar...

#

Even if I put the sqlalchemy.url to the right connection, it still shows the same

#

(owen is my username; I set it to access postgres so it's essentially doing postgresql+psycopg2:/owen:1234@127.0.0.1:5432/owen instead of postgresql+psycopg2://DB_USER:1234@127.0.0.1:5432/DB)

pure scroll May 6, 2019, 12:10 PM

#

your credentials are valid, it just says that there is no database called owen

#

you can have more than one database on your database server

#

the last part of url is the name of database

quiet ermine May 6, 2019, 12:15 PM

#

Yep

#

@pure scroll All the configs are right but it it is just doing owen all the time?

chilly hamlet May 6, 2019, 12:40 PM

#

reading further, on the subject, I think the thing I want to try living without is the "unit of work pattern"

#

As to why, it's mostly desire to experiment doing things in a different way.

pure scroll May 6, 2019, 12:53 PM

#

okay, well I'd say unit of work is quite a nice pattern

#

you can just set your session to autocommit I guess and that would to the trick for you

prime prism May 6, 2019, 12:55 PM

#

How should I go about DateTime in sqlite?

chilly hamlet May 6, 2019, 1:25 PM

#

you can just set your session to autocommit I guess and that would to the trick for you

I just re-read the documentation on autocommit and, like its name suggests, that's about transaction management. I don't see any relation to the unit-of-work stuff. Am I missing something?

pure scroll May 6, 2019, 1:44 PM

#

unit of work is abstraction of a transaction in your code. It accumulates all the changes that you need to do and then runs it in one short transaction on the actual database layer. In the case of sqlalchemy this is what they call session object.
if you set it to autocommit=True it won't accumulate any changes but would rather directly execute it on the database.

chilly hamlet May 6, 2019, 2:00 PM

#

So, you're saying that with autocommit=True assigning a value to an object's field will fire off an immediate UPDATE to the DB? I'll give it a try

pure scroll May 6, 2019, 2:01 PM

#

well, not sure here, in the wost case you can do blackmagic and override __setattr__ method of your db models and make it call commit/flush on your connection/session

#

but I really don't see much of reasons to go for active record over unit of work

chilly hamlet May 6, 2019, 2:03 PM

#

seems like with autocommit=True you just need to call flush() manually. so it really has no connection with unit of work

#

active record means that the record classes map themselves to the DB, and I don't want that either

chilly hamlet May 6, 2019, 4:03 PM

#

I think I managed to achieve what I wanted with SQLAlchemy, by using the declarative stuff, but without a session, just SQLAlchemy Core stuff, and converting between record instances and dict(-like) objects. Time to see how much of a mess that is

umbral needle May 6, 2019, 8:09 PM

#

Hello!

#

I am writing an app that interfaces with a url to pull json data from it. I then parse through that data to find the matching dictionary value for a zip code. I know that I am interfacing directly with a data layer and that my data layers need to be completely separate, but I'm wondering if I should keep that module separate and call from it as necessary or if that wouldn't matter.

#

url = "https://public.opendatasoft.com/api/records/1.0/search/?dataset=us-zip-code-latitude-and-longitude&q=co&lang=en&facet=state&facet=timezone&facet=dst&refine.state=CO"

url = "https://public.opendatasoft.com/api/records/1.0/search/?dataset=us-zip-code-latitude-and-longitude&q=co&lang=en&rows=5000&facet=state&facet=timezone&facet=dst&refine.state=CO"

json_content = requests.get(url).json()
content = json.dumps(json_content)
content = json.loads(content)
content = content["records"]

def find_zip_code(passed_zip, data):
return next(filter(lambda r: r['fields']['zip'] == passed_zip, content), None)

#

I'm using the requests library as well as the json library

merry prism May 6, 2019, 11:47 PM

#

@umbral needle ```py on top of your code

#

And ``` on the bottom

#

For code blocks in Discord chat, makes it so much easier to read

#

url = "https://public.opendatasoft.com/api/records/1.0/search/?dataset=us-zip-code-latitude-and-longitude&q=co&lang=en&facet=state&facet=timezone&facet=dst&refine.state=CO"
## url = "https://public.opendatasoft.com/api/records/1.0/search/?dataset=us-zip-code-latitude-and-longitude&q=co&lang=en&rows=5000&facet=state&facet=timezone&facet=dst&refine.state=CO"
json_content = requests.get(url).json()
content = json.dumps(json_content)
content = json.loads(content)
content = content["records"]

def find_zip_code(passed_zip, data):
        return next(filter(lambda r: r['fields']['zip'] == passed_zip, content), None)

quiet ermine May 7, 2019, 12:15 AM

#

Any help in #help-croissant :/

#

Database migrations with flask-migrate are being tempermental

umbral needle May 7, 2019, 1:03 AM

#

thank you!

ripe stratus May 7, 2019, 5:13 PM

#

Anybody here familar with pymssql and _mssql?

ionic pecan May 7, 2019, 5:37 PM

#

Don't ask to ask

ripe stratus May 7, 2019, 5:42 PM

#

I'm not asking to ask, I'm asking if somebody here and not AFK has experience with pymssql. but I'll ask anyway.

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

Forgive the bad white space and indentation, hastebin screwed it up, but it's right in the file.

I can do all the things with _mysql (part of pymssql), insert, delete, and return COUNTS. However, when I try to do a simple query for certain row (or even all of them) I'm getting a None back.

wide osprey May 7, 2019, 5:50 PM

#

Anyone formiliar with PostgreSQL and Python?

unborn sentinel May 7, 2019, 6:30 PM

#

What issues are you having

wide osprey May 7, 2019, 6:34 PM

#

Nothing, just would like to get some tips to prevent beginner mistakes. ^^

peak bison May 7, 2019, 7:47 PM

#

Does anyone know if this guy

#

https://github.com/tortoise/tortoise-orm

GitHub

tortoise/tortoise-orm

Familiar asyncio ORM for python, built with relations in mind - tortoise/tortoise-orm

#

comes with something like inspectdb from django-orm or sqlalchemy automap

#

Because I've been reading yet I couldn't find anything on that

torn sphinx May 7, 2019, 7:49 PM

#

Hey

hearty thorn May 8, 2019, 4:20 PM

#

I have a question about sqlalchemy query objects... if I set up a query like ```
query=session.query(mappedClass).filter(mappedClass.field=='critera1')

is there a way to update the critera1 with criteria2, or should I just create a new query object for the new criteria2?

**Update: ** I think maybe I should just create the query by itself like `query=session.query(mappedClass)` and then when I need  to filter it and return results I'll  just call `query.filter(mappedClass.field=='criteria').one()` as needed but still curious if I could just update the filter criteria.

quiet ermine May 8, 2019, 7:45 PM

#

Getting issues with migrations (flask-migrate) with postgresql

#

Getting this error when using flask db migrate: https://hastebin.com/akoxapuvac.sql

#

Basically: sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL: database "owen" does not exist

#

All the URLs for sqlalchemy/flask-migrate are pointed to the right postgres are not set to owen

quiet ermine May 8, 2019, 8:06 PM

#

Oh shoot I just used alembic and it fixed right away

inner lintel May 8, 2019, 10:11 PM

#

i want to store ['apple', 'orange', 'bannana'] on redis

#

then get them like a list

#

not one by one

#

how ?

quiet ermine May 8, 2019, 11:49 PM

#

.items()

#

@inner lintel

torn sphinx May 9, 2019, 1:55 AM

#

Traceback (most recent call last):
  File "testdb.py", line 19, in <module>
    loop.run_until_complete(connect_database())
  File "/usr/lib/python3.5/asyncio/base_events.py", line 466, in run_until_complete
    return future.result()
  File "/usr/lib/python3.5/asyncio/futures.py", line 293, in result
    raise self._exception
  File "/usr/lib/python3.5/asyncio/tasks.py", line 239, in _step
    result = coro.send(None)
  File "testdb.py", line 13, in connect_database
    database=DB_NAME
  File "/home/turtle/.local/lib/python3.5/site-packages/asyncpg/pool.py", line 400, in _async__init__
    await self._initialize()
  File "/home/turtle/.local/lib/python3.5/site-packages/asyncpg/pool.py", line 417, in _initialize
    await first_ch.connect()
  File "/home/turtle/.local/lib/python3.5/site-packages/asyncpg/pool.py", line 125, in connect
    self._con = await self._pool._get_new_connection()
  File "/home/turtle/.local/lib/python3.5/site-packages/asyncpg/pool.py", line 463, in _get_new_connection
    **self._connect_kwargs)

#

  File "/home/turtle/.local/lib/python3.5/site-packages/asyncpg/connection.py", line 1688, in connect
    max_cacheable_statement_size=max_cacheable_statement_size)
  File "/home/turtle/.local/lib/python3.5/site-packages/asyncpg/connect_utils.py", line 551,
in _connect
    raise last_error
  File "/home/turtle/.local/lib/python3.5/site-packages/asyncpg/connect_utils.py", line 543,
in _connect
    connection_class=connection_class)
  File "/home/turtle/.local/lib/python3.5/site-packages/asyncpg/connect_utils.py", line 513,
in _connect_addr
    connector, timeout=timeout, loop=loop)
  File "/usr/lib/python3.5/asyncio/tasks.py", line 400, in wait_for
    return fut.result()
  File "/usr/lib/python3.5/asyncio/futures.py", line 293, in result
    raise self._exception
  File "/usr/lib/python3.5/asyncio/tasks.py", line 239, in _step
    result = coro.send(None)
  File "/usr/lib/python3.5/asyncio/base_events.py", line 731, in create_connection
    infos = f1.result()
  File "/usr/lib/python3.5/asyncio/futures.py", line 293, in result
    raise self._exception
  File "/usr/lib/python3.5/concurrent/futures/thread.py", line 55, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/usr/lib/python3.5/socket.py", line 733, in getaddrinfo
    for res in _socket.getaddrinfo(host, port, family, type, proto, flags):
socket.gaierror: [Errno -2] Name or service not known

#

import asyncio
import asyncpg
from settings import *


loop = asyncio.get_event_loop()

async def connect_database():
    conn = await asyncpg.create_pool(host=DB_HOST,
                                    port=DB_PORT,
                                    user=DB_USER,
                                    password=DB_PASS,
                                    database=DB_NAME
                                    )
    print('[*] Connected to database: $1', DB_NAME)
    

loop.run_until_complete(connect_database())

#

Not sure if the error is from my connect_database() function or from using PostgreSQL in a docker container

#

I have verified that the creds passed into create_pool() are correct

gleaming basin May 9, 2019, 4:00 AM

#

whats the best and easiest databases?

#

that avoids downloads if possible

serene thicket May 9, 2019, 4:25 AM

#

depends on your use case @gleaming basin

gleaming basin May 9, 2019, 4:25 AM

#

currency

serene thicket May 9, 2019, 4:26 AM

#

you can get MSSQL Server Express for free, and will handle most basic needs, but i know many dont like MSSQL

#

you can also get a postgress DB for free

gleaming basin May 9, 2019, 4:26 AM

#

MongoDB seemed to be fine, but I cannot do stuff with it

serene thicket May 9, 2019, 4:26 AM

#

i personally, have always been a MSSQL person, so id recommend it, especially if what you are doing isnt anything big

gleaming basin May 9, 2019, 4:26 AM

#

it says that my bot is connected

serene thicket May 9, 2019, 4:26 AM

#

i dont have much experience with Mongo unfortunately

gleaming basin May 9, 2019, 4:26 AM

#

but their documentations do not help at all

serene thicket May 9, 2019, 4:27 AM

#

i would recommend posting your code here (with any pw/auth tokens removed) with a question outlining your exact issue

#

someone will be able to better help you that way 😃

gleaming basin May 9, 2019, 4:28 AM

#

ok, thanks!

serene thicket May 9, 2019, 4:31 AM

#

Hoping someone might be able to help me out here.
https://mystb.in/atagosucok.py

I have a script that generates a set of data from an API call and then inserts it directly into a MSSQL Server database.

The issue i am having is that my insert query, which is meant to handle deduping, doesn't seem to work. The WHEN MATCHED statement seems to always resolve to false, resulting in all my data being reinserted instead of either updated or only inserting new records.

What i think is the issue is that my ``ON (target.company_id = '[{company_id}]') isn't working.

My guess is based off what I'm seeing in the print, that the target table isn't getting defined.

Any one have any ideas what's going on? or what I'm missing here.

Mystbin

A fast intuitive paste service.

#

for reference the insert query is on lines 100 - 118

serene thicket May 9, 2019, 6:33 AM

#

nvm, disregard, i have realised the utter stupidity that is what i was doing..... didnt need the [] in the variables for sql statement

inner lintel May 9, 2019, 10:30 AM

#

how to sort redis dat from hiegher to lower

#

i used db.get

#

multibe time for diffrent queries

#

and results is

#

5

#

2

#

1

#

7

#

i want em to be
7
5
2
1

#

how ?

inner lintel May 9, 2019, 11:27 AM

#

^^

quiet ermine May 9, 2019, 11:32 AM

#

You can reverse it

#

something = db.get().items()[::-1]

#

Or something like db.get().order().desc().items()

#

¯_(ツ)_/¯

#

@inner lintel

inner lintel May 9, 2019, 11:51 AM

#

not one db.get

#

i used like 5 times

#

and everyone returns a value

dawn pulsar May 9, 2019, 6:57 PM

#

Any known vulnerabilities for Maria DBs with select access and sanitized inputs?
Please ping

#

Or how would you do a mysql.connector injection attack, surely that wouldn't give me more access? If I have select only access, then even commenting won't help right?>

ionic pecan May 9, 2019, 8:36 PM

#

nothing we can help you with

harsh osprey May 10, 2019, 7:24 AM

#

Hey. Whenever I try to use json.dump(...) in the file it will start a new line.
Example:

{"Line": 1}

*After dumping *

{"Line": 1}
{"Line": 1, "page": 2}

Any reason why and/or how to stop it?

#

My current workaround is just deleting the file, making a new blank one, then using .dump(...) After

ornate isle May 10, 2019, 7:38 AM

#

@harsh osprey you haven't specified what you're expecting it to be instead--a merge of those 2 dicts as one? the 2 dicts but on one line? if you're expecting them to be merged, then this is by design. you are opening the file i am gussing in append mode, rather than write (w) mode. if you want to always have the file be the combined data (in one json object, not on separate lines) then you need to read the json data from the file, update it, and write all of it back out again, in write mode, not append.

hazy mango May 10, 2019, 8:37 AM

#

@harsh osprey open the file in w mode not a

harsh osprey May 10, 2019, 10:05 AM

#

Ah that's why

subtle flax May 10, 2019, 2:43 PM

#

I have the following postgres insert code in my application: ```python
for name in to_create:
new_role = await create_role(name=name)

await conn.fetch(
"INSERT INTO color_roles (guild_id, role_id) VALUES ($1, $2)",
guild.id, new_role.id
)``` Could I do 1 INSERT instead of N somehow?

pure scroll May 10, 2019, 2:49 PM

#

what kind of postgres python driver is that?

subtle flax May 10, 2019, 2:50 PM

#

asyncpg

pure scroll May 10, 2019, 2:50 PM

#

https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg.connection.Connection.executemany

subtle flax May 10, 2019, 2:51 PM

#

Thanks

hoary wren May 11, 2019, 9:22 AM

#

@bot.command()
async def quest(ctx):
    daily_quest = ['Roll a dice.', 'Send 4 messages', 'Use a bot command']
    quest = daily_quest[random.randrange(len(daily_quest))]
    with mydb.cursor() as cursor:
        sql = "INSERT INTO discord(userid,quest,points) " \
        "VALUES(%s,%s,%d)"
        cursor.execute(sql, (ctx.author.id, quest, 1))
    mydb.commit()
    print("Added user %s to database." % ctx.author.id)

#

I want to store user data in my databse but it gives me this error AttributeError: enter

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

ornate isle May 11, 2019, 6:24 PM

#

@hoary wren something is wrong with your context manager (the with statement). doesn't look like you're supposed to be using it with db.cursor()

torn sphinx May 11, 2019, 7:27 PM

#

someone good with sqlalchemy?
i want to select from db only rows with certan value..
reading documentation right now cant find it..

#


data = db.Table('MATCH_OP', metadata, autoload=True, autoload_with=engine)
query = db.select([data] )
ResultProxy = conn.execute(query)
ResultSet = ResultProxy.fetchall()

#

i got this far..

ionic pecan May 11, 2019, 7:40 PM

#

add .where to the end of select

#

https://docs.sqlalchemy.org/en/13/core/tutorial.html#selecting

#

with the condition you want

torn sphinx May 11, 2019, 8:06 PM

#

thank you mate

torn sphinx May 12, 2019, 5:26 AM

#

what DB is easiest to use for discord bots?

ionic pecan May 12, 2019, 7:36 AM

#

Postgresql

ornate isle May 12, 2019, 7:41 AM

#

haha, no, postgres is not the easiest database. it is arguably among the best though

#

@torn sphinx if you dont know much about databases, i guess ask yourself how important the data is. if you want to use something really easy, you can use sqlite and something like dataset: https://dataset.readthedocs.io

#

with dataset, you essentially write to the database like it's just a python dict. if the sql database doesn't have the table or fields you are trying to write to, it invisibly just creates them.

#

if you are okay with it being filesystem-based, there are things like TinyDB, that write to json files but let you still query them like a 'real' database: https://github.com/msiemens/tinydb

GitHub

msiemens/tinydb

TinyDB is a lightweight document oriented database optimized for your happiness :) - msiemens/tinydb

#

would i use any of these in some mission-critical application with invaluable data? no. i'd use postgres (and replicas, and backups, and other things.) but if you want easy, there you go

torn sphinx May 12, 2019, 8:14 AM

#

How can I store a datetime object in Postgresql?

ornate isle May 12, 2019, 8:21 AM

#

@torn sphinx in a timestamptz field. and if using sqlalchemy this would be as TIMESTAMP(True). (you almost certainly want to store the time zone with it, so make sure you're also using datetimes properly)

torn sphinx May 12, 2019, 8:48 AM

#

I'm sorry, I don't follow

ornate isle May 12, 2019, 9:35 AM

#

@torn sphinx are you writing raw sql or using sqlalchemy or something? are you asking what field type to use in the postgres schema? basically most people default to just using postgres' timestamp field type, but this does not store time zone information, i.e. it can be a total mess if your app, say, let users get push notifications at certain times and you weren't storing the time zone data in the database. when do you send it?

#

so i'm saying you should most likely use postgres' timestamp with time zone field type in your db schema (also known as timestamp tz), which translates to the TIMESTAMP(True) field if you are using sqlalchemy. but that this also means you should store the timezone when you save the datetime object. datetime in python also doesn't hold time zone info by default. so how to handle that kinda depends on how you're writing to the database

#

a lot of people (myself included) either use pytz to add proper time zone info to datetime objects, or other time libraries entirely, sucha as pendulum. (i default to pendulum over datetime pretty much always, not just for db stuff)

#

sorry that it is probably more complicated than you thought haha. lots of folks just dont see this one coming until they realize all their time data has been wrong for a long time, or when python throws errors if you try to compare timestamps and it doesn't know the time zones

torn sphinx May 12, 2019, 9:54 AM

#

When I create a table do I create a TIMESTAMP like this: c.execute("""CREATE TABLE IF NOT EXISTS Users( UserID BIGSERIAL, Xp INTEGER, Sparks INTEGER, Level INTEGER, Premuim BOOLEAN, LastLogin TIMESTAMP)""")

#

@ornate isle?

ornate isle May 12, 2019, 10:03 AM

#

@torn sphinx i gave you the answer... i'd use TIMESTAMP WITH TIME ZONE instead of just TIMESTAMP, and you should try to understand why this is so you dont run into trouble later

torn sphinx May 12, 2019, 10:03 AM

#

I read what you said

ornate isle May 12, 2019, 10:03 AM

#

but it's going to yell at you if you then try to just insert a datetime object without a timezone

torn sphinx May 12, 2019, 10:04 AM

#

How can I pass a datetime object with a timezone?

#

Idk pytz

ornate isle May 12, 2019, 10:09 AM

#

if whatever you are building isnt going to be dealing with people scheduling things in different time zones and such, what you are doing is ok. but insert datetime.utcnow() into the db not datetime.now()

#

if you do need it to not break if people are using the thing you are building across time zones and where that accuracy matters, then you will need to become familiar with these things

torn sphinx May 12, 2019, 10:41 AM

#

It's for a discord bot

#

Strftime

#

I use that for UTC times

ionic pecan May 12, 2019, 12:01 PM

#

@ornate isle timestamptz does not store the timezone

#

and dataset is a library, not a database - if you use an abstraction like dataset or sqla it really does not matter which database you‘re using apart from setup

pulsar quarry May 12, 2019, 12:15 PM

#

📎 question_keys.png

#

How do I find the CK and FK for the Guest table? So far I have CK = guestNo and guestName PK = guestNo FK = guestNo.

#

Just not sure if its correct.

copper echo May 12, 2019, 12:28 PM

#

sql execute ALTER TABLE public.stars 
ALTER COLUMN author_id TYPE BIGINT,
ALTER COLUMN guild_id TYPE BIGINT,
ALTER COLUMN message_id TYPE BIGINT,
ALTER COLUMN bot_message_id TYPE BIGINT,
ALTER COLUMN channel_id TYPE BIGINT,
ALTER COLUMN starboard TYPE BIGINT
``` Why when i run my command to change type of column it gives me error

#

<class 'asyncpg.exceptions.DatatypeMismatchError'> - column "author_id" cannot be cast automatically to type bigint
HINT:  You might need to specify "USING author_id::bigint".

ionic pecan May 12, 2019, 12:40 PM

#

which type does it currently have

#

@copper echo are you using int or string currently?

copper echo May 12, 2019, 12:43 PM

#

nvm i added USING (author_id::bigint) and it worked

pulsar quarry May 12, 2019, 1:17 PM

#

@ionic pecan Hi, can you help with the question above? appreciate it

ionic pecan May 12, 2019, 1:18 PM

#

i don't think you can use name or address as a candidate / primary / foreign key

#

multiple people can live at one address, multiple people can have the same name

pulsar quarry May 12, 2019, 2:25 PM

#

@ionic pecan So for guest table, there would be no CK, PK?

ionic pecan May 12, 2019, 2:27 PM

#

why not the guest number?

ornate isle May 12, 2019, 7:18 PM

#

@ionic pecan timestamp with time zone stores the timestamp in UTC, accounts for the offset if the timestamp you give it specifies it is from a specific time zone, and then gives it back to you in the proper offset when requested, so while you are correct in that it does not store "America/Los_Angeles" in the field, it does do this conversion for the user and for as long as i can remember has been the recommended approach to storing timestamps for most situations

ionic pecan May 12, 2019, 7:19 PM

#

the offset is the database time zone though

#

This issue has some good discussion on it: https://github.com/elixir-ecto/ecto/issues/1868#issuecomment-268169955

GitHub

utc_datetime having no affect on PostgreSQL DDL · Issue #1868 · ...

Precheck Related to issue #535 which introduced the concept of explicitly storing timestamps with a time zone (in particular, for explicitly storing the UTC TZ). Environment Elixir version (elixir ...

ornate isle May 12, 2019, 7:29 PM

#

and like most others, seems to trail off without everyone in full agreement on anything except you should probably use timestamp with time zone and still use UTC for everything if you can

#

in this case it makes sense for a lib like ecto to default to using timestamptz because this can result in assumptions that it shouldn't make

#

but ive always felt for sanity it is best as a developer to be explicit about it, especially with python's history of tz-naive datetimes and more importantly perhaps all the people and app that use(d) them not fully properly understanding this

gleaming basin May 14, 2019, 3:06 AM

#

Hello

distant epoch May 14, 2019, 9:57 AM

#

Hi
Is it possible to insert stuff with sqlite in a variable table?
this inserts variables:
c.execute('''INSERT INTO table (id, name, value) VALUES(?,?,?)''', (id_var, name_var, value_var))
but I need to insert in a table with a variable name, sadly this doesn't work:
c.execute('''INSERT INTO ? (id, name, value) VALUES(?,?,?)''', (table_name, id_var, name_var, value_var))
any ideas?

polar osprey May 14, 2019, 10:51 AM

#

Boring Background Information
Hey guys. I am responsible for something that could be called a small warehouse for 1 month every year. Each year we would go and collect more or less the same material. (about 4 Trucks full) and then work with that stuff for 2 weeks and then give it back.
During these 4 weeks I'm responsible for all the stuff. Up until now I've been working with an Excel table but it's annoying and I want to make something better with Python and a MySQL database. (Python for the interface and to create PDF reports). I'm currently thinking about how to best set everything up and thought maybe one of you guys could help me out.

Actual Question
The main problem I face is that I have material (let's take a certain kind of brush for example and has an ID:5886) that belongs into 3 different kind of boxes. Box A has it two times. Box B and box C have it once. On top of that, Box C is also in a bigger Box Z.

How would I best go about organizing my Data to accommodate this?

I know of two ways:
a.) Have a table Material and a second table Material in Boxes. The second table would then specify what is in which box and underbox and what not. The problem I see with this if I want to generate a list with everything that is in Box Z I would have to run multiple querries (afaik).

b.) I read an article about the Adjacency List Model. The problem I see with this method is that one item can only have one parent. Which would work in my case.

Any advice?

pure scroll May 14, 2019, 11:09 AM

#

as far as I know classic solution for this is to have table
product which would just say hey i'm a brush of that model, of that vendor
then you would have another table for so called stock keeping units where items of that table are actual physical items.
Means every brush of the same type would have a different record in this table
and then you would reference where this item is actually placed within the warehouse.
so if this is what your materia in boxes table about then you are doing it right

#

as for the second part of multiple queries this is actually not an issue as long as those queries are running fast. In fact there are cases when running multiple queries would work faster than doing that within 1 query. But nevertheless you could always use join statements to return such things in 1 database request.

distant epoch May 14, 2019, 4:13 PM

#

Hello again!
I'm bumping my question as it got scrolled away: https://canary.discordapp.com/channels/267624335836053506/342318764227821568/577796519114309662
It would greatly help me to know if it is possible or if there is another way to get around this problem, thank you in advance

pure scroll May 14, 2019, 4:20 PM

#

so, afaik connection.execute is creating so called prepared statements, the advantage of prepared statements on DB is that query can be validated before parameters are inserted to it. so if you do multiple inserts with the prepared statement it will save execution time on your database.
now having that said, you can guess that in order to validate prepared statement you need to know which table it belongs to, means you can't pass table name as a parameter here.

#

what you can do is you can pre-format your query with the table name first, by using python string formatting. Please also keep in mind that if table name is a user input thing, and not just a variable that is hardcoded somewhere (in other words table name string cannot be considered as trusted) you will also need to escape it first, in order to avoid possible sql injection attacks.

distant epoch May 14, 2019, 4:22 PM

#

ah I see that makes sense thanks a lot

#

and yes it is an user input, I will try to make something to filter it

carmine heart May 14, 2019, 4:28 PM

#

Unless the user can also create tables dynamically, you have a predetermined ~~list~~ set of possible values.

distant epoch May 14, 2019, 5:22 PM

#

Exactly

knotty parcel May 14, 2019, 5:58 PM

#

gnoring exception in message
Traceback (most recent call last):
  File "/home/runner/.local/lib/python3.6/site-packages/discord/client.py", line 251, in _run_event
    await coro(*args, **kwargs)
  File "/home/runner/cogs/levels.py", line 19, in on_message
    if resultc[0] == 0:
TypeError: 'NoneType' object is not subscriptable

#

@carmine heart

indigo mason May 14, 2019, 6:08 PM

#

resultc is None apparently

knotty parcel May 14, 2019, 6:10 PM

#

@indigo mason

#

It is

#

As that server never changed anything

#

@ionic pecan

ionic pecan May 14, 2019, 6:11 PM

#

sup

indigo mason May 14, 2019, 6:12 PM

#

@knotty parcel So what's your issue?

#

You can't do

#

None[123]

knotty parcel May 14, 2019, 6:12 PM

#

I already did a querie

#

@ionic pecan

dbc= sqlite3.connect('main.sqlite')
      cursor= dbc.cursor()
      cursor.execute(f"SELECT leveling FROM main WHERE guild_id = {message.author.guild.id}")
      resultc = cursor.fetchone()
      if resultc[0] == 0:
        cursor.close()
        dbc.close()
        return
      elif resultc[0] is None:
        cursor.close()
        dbc.close()

ionic pecan May 14, 2019, 6:14 PM

#

i have no idea why you're pinging me

knotty parcel May 14, 2019, 6:14 PM

#

It doesn't work

#

And because I saw you helping people up chat I thought you would now how to fix it

indigo mason May 14, 2019, 6:15 PM

#

Please don't ping random people

ionic pecan May 14, 2019, 6:15 PM

#

!ask

delicate fieldBOT May 14, 2019, 6:15 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.

indigo mason May 14, 2019, 6:15 PM

#

You are not owed help

knotty parcel May 14, 2019, 6:15 PM

#

Ignoring exception in message
Traceback (most recent call last):
  File "/home/runner/.local/lib/python3.6/site-packages/discord/client.py", line 251, in _run_event
    await coro(*args, **kwargs)
  File "/home/runner/cogs/levels.py", line 19, in on_message
    if resultc[0] == 0:
TypeError: 'NoneType' object is not subscriptable

indigo mason May 14, 2019, 6:15 PM

#

resultc[0]

#

resultc is None

#

I'm not sure what you want us to say

ancient bough May 14, 2019, 8:44 PM

#

Anyone know how to fix this, it's for my Discord bot

So I'm trying to create a database, but I'm getting errors...

Code -

@bot.event
async def on_ready():
    db = sqlite3.connect('main.sqlite')
    cursor = db.cursor()
    cursor.execute('''
        CREATE TABLE IF TABLE NOT EXIST main(
        guild_id TEXT,
        msg TEXT,
        channel_id TEXT
        )
        ''')

Error -

    ''')
sqlite3.OperationalError: near "TABLE": syntax error```

slate spire May 14, 2019, 8:45 PM

#

IF NOT EXISTS, not IF TABLE NOT EXIST

ancient bough May 14, 2019, 8:46 PM

#

Tysm

slate spire May 14, 2019, 8:46 PM

#

no probs

torn sphinx May 15, 2019, 2:40 AM

#

anyone can tell me why employee_id is returning sqlite3.IntegrityError: NOT NULL constraint failed: Client.employee_id

tawny sail May 15, 2019, 3:55 AM

#

Read the error

#

It says the condition for not null failed

#

I guess u forgot to pass a value for it or maybe dint set it for auto incriment

#

@torn sphinx

ancient bough May 15, 2019, 6:04 AM

#

class WelcomeCog(commands.Cog, name='Welcome'):

    def __init__(self, bot):
        self.bot = bot

    @commands.Cog.listener()
    async def on_member_join(member):
        print(f'{member} Has just joined the server.')
        embed = discord.Embed(colour=0xdf0d22, description=f"Welcome to the server! You are the {len(list(member.guild.members))} member!")
        embed.set_thumbnail(url=f"{member.avatar_url}")
        embed.set_author(name=f"{member.name}", icon_url=f'{member.avatar_url}')
        embed.set_footer(text=f"{member.guild}", icon_url=f"{member.guild.icon_url}")
        embed.timestamp = datetime.datetime.utcnow()

        channel = member.guild.get_channel(id=576735952827973654)

        await channel.send(embed=embed)

    @commands.group(invoke_without_command=True)
    async def welcome(self, ctx):
        await ctx.send('Available Setup Commands: \nwelcome channel <#channel> \nwelcome text <message>')

    @welcome.command()
    async def channel(self, ctx, channel:discord.TextChannel):
        if ctx.message.author.guild_permissions.manage_messages:
            db = sqlite3.connect('main.sqlite')
            cursor = db.cursor()
            cursor.execute(f'SELECT channel_id FROM main WHERE guild_id = {ctx.guild.id}')
            result = cursor.fetchone()
            if result is None:
                sql = ("INSERT INFO main(guild_id, channel_id) VALUES(?,?)")
                val = (ctx.guild.id, channel.id)
                await ctx.send(f'Channel has been set to {channel.mention}')
            elif result is not None:
                sql = ("UPDATE main SET channel_id = ? WHERE guild_id = ?")
                val = (channel.id, ctx.guild.id)
                await ctx.send(f'Channel has been updated to {channel.mention}')
                cursor.execute(sql, val)
                db.commit()
                cursor.close()
                db.close

#

I'm trying to make a data base so then i'm able to set a welcome channel, now, I'm trying to make it set the Channel ID and the Guild ID into the database

#

But it's not setting anything

quasi storm May 15, 2019, 11:22 AM

#

What module should I use for asynchronous postgres, im looking at aiopg but im open to other suggestions

torn sphinx May 15, 2019, 11:53 AM

#

@tawny sail : Yea i forget to pass the id, haha.

tawny sail May 15, 2019, 12:14 PM

#

@quasi storm aio sqlalchemy

bleak pecan May 15, 2019, 2:15 PM

#

    async def _update(self, usr, gem):
        connection = await self.bot.db.acquire()
        async with connection.transaction():
            query = "UPDATE contenders SET gems = (gems + $1), pool = (pool - ABS($1)) WHERE id = $2"
            await self.bot.db.execute(query, gem, usr.id)
        await self.bot.db.release(connection)

    async def _pool(self, usr, gem):
        connection = await self.bot.db.acquire()
        async with connection.transaction():
            query = "UPDATE contenders SET gems = (gems - $1), pool = (pool + $1) WHERE id = $2"
            await self.bot.db.execute(query, gem, usr.id)
        await self.bot.db.release(connection)

await self._pool(usr,  gems)
await self._update(winner, gems)
await self._update(loser, -gems)```

any way those 2 methods be reduced to just one... without another arg

pure scroll May 15, 2019, 2:17 PM

#

you mean two update methods?

torn sphinx May 15, 2019, 2:22 PM

#

📎 Screenshot_2019-05-15_16.15.40.png

#

Hello everyone. I have a problem with a .sql file. python can't recognize input

#

any idea ?

bleak pecan May 15, 2019, 2:25 PM

#

I mean merge _pool & _update with something in the query,
but theres probs no way without another arg, unless there's some math tweak...

pure scroll May 15, 2019, 2:30 PM

#

you can do abs on python side

#

that is your math trick 😄

pure scroll May 15, 2019, 2:47 PM

#

ah okay, I see it has + vs -

#

so I guess if you do + - abs(value) it's the same as - abs(value)and calculation of-abs(value)` you can have on python side

#

but to be honest it feels wrong

#

those are two different actions, and there is no reason to re-use the code

#

the code could and should be re used only if two things are repenting same logical action, if not then you should not do it, having copy-paste in this case is not a crime

bleak pecan May 15, 2019, 4:42 PM

#

just felt wrong as they're so similar, I think i'll just add an optional arg, yea the SQL abs works there... that saved me a check, cant think of away without another flag/arg/marker so i'll have to go with that

lapis zenith May 15, 2019, 9:10 PM

#

Im trying to make a script to search through multiple large text files for a string

#

but I keep getting this error

#

📎 pythonw_2019-05-15_17-08-28.png

#

What is going wrong here?

#

I tested it with 2 small files (<10k lines each) and it was working fine

slim echo May 15, 2019, 9:14 PM

#

Have you googled the error?

#

UnicodeDecodeError happens when there is an invalid character in the file

lapis zenith May 15, 2019, 9:19 PM

#

hmm ok, I just downloaded some random database to test my program, i guess some of the emails have weird chars in them?

slim echo May 15, 2019, 9:20 PM

#

I guess that's not out of the question

#

So my guess will be to read the file as bytes

#

So open("file", "rb")

lapis zenith May 15, 2019, 9:23 PM

#

oh, ok. Im getting another error now tho cause idk how to work with bytes

#

pretty new to this, only finished 1 year of compsci :/

bleak pecan May 15, 2019, 10:17 PM

#

state an encoding in open may do. utf-8 probs best bet

#

if not latin-1

lapis zenith May 15, 2019, 10:31 PM

#

^ that worked, thanks!

torn sphinx May 16, 2019, 11:48 AM

#

Hey guys

#

i need a help with

#

Article.objects.filter(tags='Destaques')

#

this return me a error like:
ValueError: invalid literal for int() with base 10: 'Destaques'

#

when i make a:

>>> Article.objects.all()[0].tags
<django.db.models.fields.related_descriptors.create_forward_many_to_many_manager.<locals>.ManyRelatedManager object at 0x7f2ed4f329b0>

carmine heart May 16, 2019, 12:07 PM

#

I think Django expect the id of the Tags, here. In which field within your Tags should that 'Destaques' be?

torn sphinx May 16, 2019, 12:12 PM

#

>>> t = Article.objects.all()[0].tags.values()
>>> t
<QuerySet [{'id': 3, 'name': 'Destaques', 'created': datetime.datetime(2018, 10, 22, 11, 23, 38, 193246)}, {'id': 2, 'name': 'Notícias', 'created': datetime.datetime(2018, 10, 22, 11, 16, 2, 842396)}]>

#

i'm making like this

#

>>> t.filter(name='Destaques')
<QuerySet [{'id': 3, 'name': 'Destaques', 'created': datetime.datetime(2018, 10, 22, 11, 23, 38, 193246)}]>

#

but i can't use delete method

#

to delet this tag

carmine heart May 16, 2019, 12:13 PM

#

Okay, when you use filter like that on your Article objects, tags refers to the entire object, so filter will work by default on the id field

#

If you want to filter tags based on the name field, you need to specify that

#

Article.objects.filter(tags__name='Destaques')

#

should work in your case, I think

torn sphinx May 16, 2019, 12:14 PM

#

oh

#

let me try

#

@carmine heart : and if i want to delete this tag?

#

actually

#

i don't want delete the object

#

just the tag

#

class Tag(models.Model):
    created = models.DateTimeField(
        auto_now_add=True)
    name = models.TextField(unique=True)



class Article(models.Model):
    created = models.DateTimeField(
        auto_now_add=True)
    publish_date = models.DateTimeField(
        default=timezone.now)
    updated = models.DateTimeField(
        default=timezone.now)
    image = models.BooleanField(
        default=False)
    image_updated = models.DateTimeField(
        default=timezone.now)
    show_image = models.BooleanField(
        default=False
    )
    owner = models.ForeignKey(
        'auth.User', 
        related_name='articles', 
        on_delete=models.CASCADE)
    title = models.TextField()
    subtitle = models.TextField(
        blank=True)
    tags = models.ManyToManyField(Tag)

#

this is they relationship

#

tag is a many to many field

carmine heart May 16, 2019, 12:20 PM

#

I'm not too sure, I haven't used Django that much myself

torn sphinx May 16, 2019, 12:21 PM

#

yea

#

bt

#

the whey you said it's a good whey

#

i found one thing

#

for article in Article.objects.filter(tags__name='Destaques'):
    article.tags.remove()

#

but help(article.tags.remove) dont have documentation

#

😢

#

yeaaa

#

i got it

#

@carmine heart : you're a god.

#

thank you

#

>>> for article in Article.objects.filter(tags__name='Destaques'):
...     article.tags.filter(name='Destaques').delete()

#

just saving here.

pure scroll May 16, 2019, 12:37 PM

#

that is not really efficient way. since you would make one delete statement per article

#

Tags.filter(name="Destaques").delete() would have better performance

torn sphinx May 16, 2019, 12:41 PM

#

@pure scroll : This look the same thing

#

But tags is a relationship between tag and article

#

are two different things

pure scroll May 16, 2019, 12:43 PM

#

you can query Tag table directly

#

ah okay, it's m2m

#

I see now

torn sphinx May 16, 2019, 12:44 PM

#

Not actually. Because tag is indepently from article.
Tag exist even when a article dont.

pure scroll May 16, 2019, 12:44 PM

#

sorry, forget what I just said

torn sphinx May 16, 2019, 12:44 PM

#

but you remind me about performance. That's actually a serious thing i have to think about

#

take about 2 minutes to delete all the destaques tags

pure scroll May 16, 2019, 12:45 PM

#

well, that is the bad part of orms, they hide such things

#

I'm not sure how would you access this relation table using django orm

#

but you could do that with 1 delete statement in pure sql

#

like

delete from <tags_to_articles>
using tags
where tags.id = tags_to_article.tag_id
and tags.name = "destaques"

torn sphinx May 16, 2019, 12:57 PM

#

this work in django? @pure scroll

pure scroll May 16, 2019, 12:57 PM

#

you need to lookup for how to execute raw statements in django

#

also you need to know the table name that stores this m2m relation

#

probably there is a way to do that using ORM, but I never had to do that in django before. maybe someone else could come up with a different way, not a raw sql

pliant oxide May 16, 2019, 7:34 PM

#

Hi, I got a question about collecting data (via Python and Scrapy and SQLAlchemy, but it could doesn't matter) and inster it to database.
I got Items defined like this

class Country(scrapy.Item):
    code = scrapy.Field() # PL, IT
    name = scrapy.Field() # Polska, Włochy

class City(scrapy.Item):
    country_id = scrapy.Field() # code of Country
    code = scrapy.Field() # POZ, ROM
    name = scrapy.Field() # Poznań, Rzym

class Airport(scrapy.Item):
    city_code = scrapy.Field() # code of City
    code = scrapy.Field() # POZ, CIA
    name = scrapy.Field() # Ławica, Ciampino

class Airline(scrapy.Item):
    code = scrapy.Field() # FR
    name = scrapy.Field() # Ryanair

class Flight(scrapy.Item):
    from_dest = scrapy.Field() # code of Airport
    to_dest = scrapy.Field() # code of Airport
    airline_code = scrapy.Field() # code of Airline
    flight_time = scrapy.Field() # 1:25
    price = scrapy.Field() # 979.32
    date = scrapy.Field() # /Date(1557124200000)/

class Offer(scrapy.Item):
    outbound_flight = scrapy.Field()
    inbound_flight = scrapy.Field()

And I got response something like this:

"AirportsNames": [
    {
        "Key": "POZ",
        "Type": "Airport",
        "Value": "Ławica"
    },
    {
        "Key": "BGY",
        "Type": "Airport",
        "Value": "Bergamo Orio al Serio"
    }
],
"AirportsCityCodes": [
    {
        "Key": "POZ",
        "Value": "POZ"
    },
    {
        "Key": "BGY",
        "Value": "MIL"
    }
],
"AirportsCountryCodes": [
    {
        "Key": "POZ",
        "Value": "PL"
    },
    {
        "Key": "BGY",
        "Value": "IT"
    }
],
"CityNames": [
    {
        "Key": "POZ",
        "Type": null,
        "Value": "Poznań"
    },
    {
        "Key": "MIL",
        "Type": null,
        "Value": "Mediolan"
    }
],
"CountryNames": [
    {
        "Key": "PL",
        "Value": "Polska"
    },
    {
        "Key": "IT",
        "Value": "Włochy"
    }
],

#

My script which is getting that reponses async (there are much more responses like that) creates Items from this response. I want to insert this to database, I know how to do it in a simple way, but I don't know how to add relation, for example City record from Cities table to Country record from Countries table.
I know I can do something like "Get ID of country in countries table by CityToCountry code and then assign it to {country_id, cityname, citycode}", but what if this country record not exists in table yet (because it's async)?
I think I can use MySQL database triggers, so I will send {airportName, airportCode, cityName, cityCode, countryName, countryCode} and then this triggers will insert in proper tables data.

#

`
In a simple question: Is it a good way to do it with a trigger when I don't know how to do it in a code (maybe it's impossible)?

unkempt sable May 17, 2019, 7:26 AM

#

!warn 442005563451244544 Do not post copypasta in on-topic channels

delicate fieldBOT May 17, 2019, 7:26 AM

#

:incoming_envelope: :ok_hand: warned @pure basin (Do not post copypasta in on-topic channels).

indigo mason May 17, 2019, 7:51 AM

#

He did it in D.py too :-/

brave lotus May 17, 2019, 8:33 AM

#

sorry if this is a really stupid question, i'm very new at this! so lets say this is one of my tables its row values:

food(name, emoji, rarity)

and here's some entries:

(pizza, 🍕, common)
(apple, 🍎, common)
(taco, 🌮, uncommon)
...and like 47 more

now i want to make a table that keeps track per user of how much they got of each food. so my table and row values will be this:

food_user(user_id, name, count)

now if i wanna store that for each person i'd have to do something like 50 times this for each person:

(1, pizza, 6)
(1, apple, 73)
...48 more

is there any way i'm overlooking that would not require 50 seperate entries just for one user?

limpid hound May 17, 2019, 8:36 AM

#

@brave lotus it all depends on what you're trying to adapt the code for.
To an extent, you can choose to add columns per type of item.
I have seen horrible examples of this where they had thousands (!) of columns

#

but if you expect to see a large amount of different types of items, I'd go with a design closer to this:
[ user ] - [ item ] - [amount ]

#

having 50 columns just for the different types of items sounds like a design I'd stay away from (it's not flexible in the long run)

#

unless you're expecting to do really heavy analysis of your users item counts, I'd have a hard time motivating a wide table

brave lotus May 17, 2019, 8:47 AM

#

what exactly do you mean with the design you wrote? in what place would it be?

limpid hound May 17, 2019, 8:54 AM

#

ah, my bad, I misread your question to begin with!
Either you write a insert for every row (with the current table-design that you described) which would be a row for every item and person (I'd skip any row that's currently set to 0 items) or you're going to have to write some code that will generate the necessary rows.

Not knowing the amount of user_id's you're planning on entering data for, the amount of time saved by writing code to do it for you is uncertain 😃

#

if you absolutely don't want one row per item per user_ID, you would have to build a "wider" table with many columns (one column per item-type)

pure scroll May 17, 2019, 8:58 AM

#

is there any way i'm overlooking that would not require 50 seperate entries just for one user?
what is the issue with having that?

#

that is classic m2m relation you have

limpid hound May 17, 2019, 8:59 AM

#

you could also have a column storing JSON or XML if you fancy that, but I'd recommend going for one row per item-type+user_id

brave lotus May 17, 2019, 8:59 AM

#

ah yes exactly, i asked because in both a narrow and wide way it seems like it would not scale well when the amount of items goes up even more

limpid hound May 17, 2019, 9:00 AM

#

@brave lotus one way of keeping the amount of rows "low" would be to not store any 0-values

#

but it depends on the application you're writing, what's expected out of it and how you want to work with it

#

if you're worried about the table-performance, I would make some kind of calculation on what the worst-case scenario would be in 1~5 years, filling the table with data that corresponds to what you're expecting in your worst case scenario and see how it behaves

brave lotus May 17, 2019, 9:04 AM

#

basically to me it seemed like a problem that you're creating so much entries for just one person, but if it isn't then i'll just take that and go on with it. i'm just new to working with databases

#

ah yeah that's a good point, i'll keep that in mind

limpid hound May 17, 2019, 9:06 AM

#

a good practice (that I don't see often enough) is to expect some kind of worst case scenario and see how the application works under those

pure scroll May 17, 2019, 9:15 AM

#

which DB is that btw?

brave lotus May 17, 2019, 9:15 AM

#

thanks so much for the help! means i can continue with my project haha

torn sphinx May 17, 2019, 10:53 AM

#

hmm.. how would I go about escaping quotes in this SQL statement? sql print("INSERT INTO records (ipaddr, port22, tcpbanner) VALUES('{}', TRUE, \"{}\");".format(y["ip"], """{}""".format(str(shlex.quote(banner)))))

#

I thought shlex.quote would work, but an sql error is returned

#

(im using aiomysql)

#

it doesnt come with prepared statement support apparently so...

tawny sail May 17, 2019, 11:10 AM

#

Use %s

torn sphinx May 17, 2019, 11:13 AM

#

I'll try lol

pure scroll May 17, 2019, 11:46 AM

#

@torn sphinx why do you need to escape it in the first place?

torn sphinx May 17, 2019, 11:47 AM

#

because sometimes tcpbanner has characters like ' and it breaks the query

pure scroll May 17, 2019, 11:48 AM

#

you have execute method on database adapters

#

that is able to do escaping correctly

valid ruin May 17, 2019, 12:17 PM

#

i need guidance to hook up sql with discord.py?

#

i use sqlite3 library but am not very good with it

#

i am trying to store user data, i thought about using csv but thats too simple

#

i need something more... capable

carmine heart May 17, 2019, 12:19 PM

#

capable of what exactly?

#

I mean, there's a lot you can do with text files

harsh pulsar May 17, 2019, 12:22 PM

#

@valid ruin you can try an ORM like Sqlalchemy, peewee, or pony

#

But i strongly recommend learning how to use a "low level" sql library like the sqlite3 one in python

#

ORMs tend to be "big" libraries

#

Basic sql libraries like sqlite3 arent that hard to use and are all more or less the same as each other

valid ruin May 17, 2019, 12:25 PM

#

i just found out about the pickle library

harsh pulsar May 17, 2019, 12:25 PM

#

Definitely don't do that

valid ruin May 17, 2019, 12:25 PM

#

i just want to save variables permanently

harsh pulsar May 17, 2019, 12:25 PM

#

Hm

valid ruin May 17, 2019, 12:25 PM

#

hm

harsh pulsar May 17, 2019, 12:26 PM

#

I cant say I recommend it

#

What is your hangup with sqlite

valid ruin May 17, 2019, 12:26 PM

#

i just dont know it that well, ik basics in sql but not how to use it in python

#

python 3 to be specific

#

im going to give pickle a try, but if not I'll be back >:(

#

lol

harsh pulsar May 17, 2019, 12:28 PM

#

if it breaks, you get to keep both halves lol

#

python actually defines a specification for sql libraries, its relatively standardized

#

import sqlite3

connection = sqlite3.connect('appdata.db')

def read_app_parameter(parameter_name):
    cursor = connection.cursor()
    cursor.execute('select value from app_parameters where name = ?', (parameter_name))
    return cursor.fetchone()[0]

something like that

void otter May 17, 2019, 12:31 PM

#

Sqlalchemy works great with sqlite and postgresql

torn sphinx May 17, 2019, 12:35 PM

#

@pure scroll I was using await cur.execute and it wasn't working properly

pure scroll May 17, 2019, 12:39 PM

#

@torn sphinx can you show that par of the code?

torn sphinx May 17, 2019, 12:40 PM

#

well

#

I can show you the broken version

#

I edited it earlier and its not inserting anymore but yeah sec

#

rip

📎 Screenshot_from_2019-05-17_13-44-18.png

#

https://ghostbin.co/paste/rznp7

#

some traceback from one of the lines https://ghostbin.co/paste/ktkjs

pure scroll May 17, 2019, 12:49 PM

#

okay, yeah that is what I thought

#

cur.execute("INSERT INTO records (ipaddr, port2323, tcpbanner) VALUES('{}', TRUE, '{}');".format(y["ip"], """{}""".format(str(banner))))
generally speaking most of the times formatting database queries as a strings is not a good idea

#

what you should be doing is this

cur.execute("INSERT INTO records (ipaddr, port2323, tcpbanner) VALUES(?, TRUE, ?)", (y["ip"], str(banner)))

#

this way you let database adapter to handle escaping of the values, plus this way you will avoid possible sql injections

torn sphinx May 17, 2019, 1:12 PM

#

idk if that even works, aiomysql doesn't support prepared statements

#

oh wait

#

I didnt scroll down

#

they must've forgot to document it

#

https://github.com/aio-libs/aiomysql/issues/293

GitHub

Any plans to support SQL prepared statements? · Issue #293 · aio...

https://dev.mysql.com/doc/refman/5.6/en/sql-syntax-prepared-statements.html Do you have any plans to support this? Could be useful to utilise MySQL server more efficient, e.g. do not parse SQL on s...

#

I'll try lol

#

lol ugh, its not inserting any records

#

but its not throwing any errors

pure scroll May 17, 2019, 1:21 PM

#

are you calling commit ?

#

because according to your code you call commit every 100 records

#

but if you are processing less than 100 records you won't call commit at all

torn sphinx May 17, 2019, 1:23 PM

#

I am

#

I have print("Inserting records") await conn.commit() print("Done")

#

at the end

#

📎 Screenshot_from_2019-05-17_14-23-27.png

#

📎 Screenshot_from_2019-05-17_14-23-33.png

pure scroll May 17, 2019, 1:25 PM

#

okay, I don't think you are supposed to do inserts on cursor

#

this should be done on connections itself

torn sphinx May 17, 2019, 1:26 PM

#

oh

#

AttributeError: 'Connection' object has no attribute 'execute'

#

uh

pure scroll May 17, 2019, 1:27 PM

#

yeah, okay just went throught docs, you really have to use cursor for all the db commands, sorry it's a bit different with postgres

#

my bad 😃

harsh pulsar May 17, 2019, 1:28 PM

#

@torn sphinx usually you have to create a cursor. some database libraries let you call execute on the connection directly as a shortcut

pure scroll May 17, 2019, 1:28 PM

#

can you share the current state of the code

torn sphinx May 17, 2019, 1:29 PM

#

sec

#

https://ghostbin.co/paste/wxpqv

pure scroll May 17, 2019, 1:31 PM

#

ah, you don't have await before any of the execute calls

#

just add await to all the execute calls

torn sphinx May 17, 2019, 1:31 PM

#

oh oops

#

INSERT INTO records (ipaddr, port2323, tcpbanner) VALUES('181.112.136.50', TRUE, "'SSH-2.0-OpenSSH_7.2\r\n'");
Traceback (most recent call last):
  File "dbinsert.py", line 47, in <module>
    loop.run_until_complete(test_example_execute(loop))
  File "/usr/lib/python3.5/asyncio/base_events.py", line 387, in run_until_complete
    return future.result()
  File "/usr/lib/python3.5/asyncio/futures.py", line 274, in result
    raise self._exception
  File "/usr/lib/python3.5/asyncio/tasks.py", line 239, in _step
    result = coro.send(None)
  File "dbinsert.py", line 37, in test_example_execute
    await cur.execute("INSERT INTO records (ipaddr, port2323, tcpbanner) VALUES(?, TRUE, ?)", (y["ip"], str(banner)))
  File "/home/root/.local/lib/python3.5/site-packages/aiomysql/cursors.py", line 237, in execute
    query = query % self._escape_args(args, conn)
TypeError: not all arguments converted during string formatting
Exception ignored in: <bound method Connection.__del__ of <aiomysql.connection.Connection object at 0x7ff7a50e6e10>>
Traceback (most recent call last):
  File "/home/root/.local/lib/python3.5/site-packages/aiomysql/connection.py", line 1072, in __del__
  File "/home/root/.local/lib/python3.5/site-packages/aiomysql/connection.py", line 298, in close
  File "/usr/lib/python3.5/asyncio/selector_events.py", line 566, in close
  File "/usr/lib/python3.5/asyncio/base_events.py", line 497, in call_soon
  File "/usr/lib/python3.5/asyncio/base_events.py", line 506, in _call_soon
  File "/usr/lib/python3.5/asyncio/base_events.py", line 334, in _check_closed
RuntimeError: Event loop is closed

#

if you want it shorter, TypeError: not all arguments converted during string formatting

pure scroll May 17, 2019, 1:36 PM

#

might be that i's using %s instead of ?

#

await cur.execute("INSERT INTO records (ipaddr, port2323, tcpbanner) VALUES(%s, TRUE, %s)", (y["ip"], str(banner)))

torn sphinx May 17, 2019, 1:38 PM

#

lemme c

#

(its inserting for now 😂)

#

I think it is anyway lol, hasn't committed yet

hazy mango May 17, 2019, 2:32 PM

#

@torn sphinx what version of python are you using?

torn sphinx May 17, 2019, 5:19 PM

#

it worked, tx @pure scroll

#

@hazy mango 3.5.2

hazy mango May 17, 2019, 5:20 PM

#

Any particular reason you haven't updated?

#

@torn sphinx

torn sphinx May 17, 2019, 5:20 PM

#

nope

#

well on my laptop I can't, on the VPS I have no reason to

#

all new features are probably useless to me

hazy mango May 17, 2019, 5:21 PM

#

I was thinking f-strings

torn sphinx May 17, 2019, 5:21 PM

#

oh I despise those lol

hazy mango May 17, 2019, 5:21 PM

#

They make database work a lot easier in my opinion

torn sphinx May 17, 2019, 5:21 PM

#

they look like a typo every time I see one

hazy mango May 17, 2019, 5:21 PM

#

Not really?

torn sphinx May 17, 2019, 5:22 PM

#

they really do

#

its as if someone accidentally put an f in there

patent glen May 17, 2019, 5:22 PM

#

...most things you do with databases you should not be using f-strings for

#

or any kind of string formatting

#

it can be necessary for dynamic table or column selection, but that's usually a sign of an awkward database design

torn sphinx May 17, 2019, 5:22 PM

#

either way it worked lul

📎 Screenshot_from_2019-05-17_18-21-59.png

#

nooow

#

is there something like arrays in mysql

#

that can easily have data added to it

#

I know json is there but its effort to add values to it

hazy mango May 17, 2019, 5:23 PM

#

I do lol, because I do like f"select x,y from z where w='{w}'" @patent glen

patent glen May 17, 2019, 5:24 PM

#

@pure scroll %s is correct for mysql, different databases have different ways of doing it (and different libraries - psycopg uses %s but asyncpg uses the native postgresql $1)

#

@hazy mango that's really a bad idea what if w has ' in it

hazy mango May 17, 2019, 5:24 PM

#

It never does in my case

#

*cases

patent glen May 17, 2019, 5:24 PM

#

it's not a great habit to get into

hazy mango May 17, 2019, 5:25 PM

#

My friends always said it was better than using ?

indigo mason May 17, 2019, 5:26 PM

#

Your friend is incorrect I'm afraid

torn sphinx May 17, 2019, 5:26 PM

#

I doubt it is

indigo mason May 17, 2019, 5:26 PM

#

F-strings are great for string formatting sure

#

But you're not formatting a string

#

You're formatting a request that's about to execute on your database

hazy mango May 17, 2019, 5:27 PM

#

So what’s the preferred way of doing it?

indigo mason May 17, 2019, 5:27 PM

#

Even if your string is properly cleaned or not user generated, there's no point risking it or not using the proper format

#

https://canary.discordapp.com/channels/267624335836053506/342318764227821568/578996187890450452

#

What if someone put something malicious as w? Someone could drop your entire table through a command

torn sphinx May 17, 2019, 5:28 PM

#

so is there something like a list in mysql? basically every record in the db has sub-records, atm I have boolean columns but thats going to be a lot of columns when I go on to add more.

hazy mango May 17, 2019, 5:28 PM

#

Yeah I know but as I’ve said that will never happen

#

It’s only ever me using the database

indigo mason May 17, 2019, 5:29 PM

#

As stated, still a bad habit to get into

#

And for what?

torn sphinx May 17, 2019, 5:29 PM

#

Its also a pain whenever inserting the records lol, I have a lot of duplicates

hazy mango May 17, 2019, 5:29 PM

#

Well how would I do selections based on variables?

indigo mason May 17, 2019, 5:31 PM

#

Same as usual?

#

📎 unknown.png

hazy mango May 17, 2019, 5:32 PM

#

data = list(cur.execute("SELECT name FROM logins"))
users = [x[0] for x in data]
username = input(“Enter Username:\n”)
if username in users:
    dats = list(cur.execute(f"SELECT salt, password FROM logins WHERE user='{username}'")```what would the last statement become?

indigo mason May 17, 2019, 5:32 PM

#

https://canary.discordapp.com/channels/267624335836053506/342318764227821568/578996187890450452

hazy mango May 17, 2019, 5:32 PM

#

I can’t view links

#

Can you send screenshot pls

indigo mason May 17, 2019, 5:33 PM

#

📎 unknown.png

hazy mango May 17, 2019, 5:33 PM

#

use %s for SQLite3 as well?

#

Or would it be ? for SQLite3

#

@indigo mason

patent glen May 17, 2019, 5:59 PM

#

@hazy mango cur.execute('select salt, password from logins where user=?', [username])

#

it's ? for sqlite3, or... :name, I believe, if you use a keyword dictionary

hazy mango May 17, 2019, 6:00 PM

#

Is it [] or ()?

#

@patent glen

patent glen May 17, 2019, 6:00 PM

#

it can be either, but if you use () with a single argument you need (username,)

hazy mango May 17, 2019, 6:01 PM

#

Oh yeah fair enough

patent glen May 17, 2019, 6:01 PM

#

personally i find [] more readable in addition to being less likely to trip people up on the comma thing

hazy mango May 17, 2019, 6:01 PM

#

Yeah I'll use [] then

#

Thanks :~)

minor maple May 17, 2019, 7:33 PM

#

Does anyone know about flask?

ionic pecan May 17, 2019, 7:34 PM

#

!ask

delicate fieldBOT May 17, 2019, 7:34 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.

#

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.

void otter May 17, 2019, 7:53 PM

#

@sterile yokeNav#4064 what do you want to know

kindred cargo May 18, 2019, 9:49 AM

#

is sqlite an online database?

void otter May 18, 2019, 9:51 AM

#

it's local database

kindred cargo May 18, 2019, 9:55 AM

#

what are some good online databases?

void otter May 18, 2019, 9:56 AM

#

postgres

#

mysql

#

ms sql

#

i use postgres for my site

indigo mason May 18, 2019, 10:48 AM

#

MySQL is ok but there's little reason to use it if you can use Postgres

dusty scroll May 18, 2019, 12:54 PM

#

i'm getting Access denied for user 'xxx'@'localhost' (using password: YES) even though I am using the correct password

#

and i am connecting from localhost

#

i connect find through the command lien https://asphalt.is-a-vir.us/dn3cuqbl.png

silent tapir May 18, 2019, 4:36 PM

#

how do you try to connect to it?

kindred cargo May 19, 2019, 10:31 AM

#

i'm facing a query api rate limit issue in google sheets, what other database has a higher limit?

void otter May 19, 2019, 10:31 AM

#

postgtres

kindred cargo May 19, 2019, 10:33 AM

#

i'm using a discord bot to store user data. postgtres is a good database for it? @void otter

void otter May 19, 2019, 10:33 AM

#

yes

#

maybe mysql

#

i use postgres for my web app and it works fine

kindred cargo May 19, 2019, 10:35 AM

#

is it an online database? @void otter

#

idw it to be a local one

void otter May 19, 2019, 10:36 AM

#

yes it is online db

#

sqlite is local

kindred cargo May 19, 2019, 12:24 PM

#

import discord
from discord.ext import commands

client = pymongo.MongoClient('mongodb+srv://xx:xx@xx.mongodb.net/test?retryWrites=true')
db = client.get_database('Users')
records = db.get_collection('wager_lounge')
test = {'test':'1'}
records.insert_one(test)
print('t')```
why does it not add the document?

void otter May 19, 2019, 12:27 PM

#

do you have your tables set up

kindred cargo May 19, 2019, 12:27 PM

#

yeah i do!

worldly flint May 19, 2019, 1:33 PM

#

anyone can teach me python ? home alone here T_T

carmine heart May 19, 2019, 1:51 PM

#

@worldly flint This isn't the right channel for such a question (this is a channel for questions about databases). Also, while people can help you and explain concepts to you when you have questions about them, this isn't the right place to look for someone who teaches Python TO you. It's probably best to go with one of the many available resources or, if you want a tutor, to look in your local area for someone who's willing to teach you Python.

worldly flint May 19, 2019, 1:52 PM

#

I see

raw onyx May 19, 2019, 8:59 PM

#

quick question, why do people say aiosqlite is bad?

ornate isle May 19, 2019, 9:06 PM

#

@raw onyx i dont know who "people" are

#

the lib works reasonably well from my limited usage. i think people just get caught up in the whole "sqlite db is a file" thing. you cant have multiple people using it at once, it is locked while in use, the normal sqlite issues, but you can sometimes run into this more frequently with aiosqlite if you don't know how to use it properly

#

but as far as i know there is nothing inherently wrong with it.

#

most of the time i'm not using it directly, but using it with sqlalchemy via the async databases library

tropic zealot May 19, 2019, 9:29 PM

#

@raw onyx Because, at the end of the day, SQLite is still a file-based system that can write only finish executing as fast as the hard disk the file is on.

#

It's not terrible, but there are better options out there that you should look into considering.

ornate isle May 19, 2019, 11:26 PM

#

@tropic zealot better options than aiosqlite? or sqlite in general? i think @raw onyx was asking about the aio lib rather than the db itself. also sqlite is fantastic in many situations and also disks can be pretty damn fast these days

raw onyx May 19, 2019, 11:26 PM

#

my question was about the "is aiosqlite really that bad to the point where i should avoid it"

#

i should've said it like that

ornate isle May 19, 2019, 11:26 PM

#

the library or the database

#

aiosqlite is a async driver for sqlite the database

#

to which are you referring

raw onyx May 19, 2019, 11:27 PM

#

im just asking if what people say is true or not

#

im no expert on databases

ornate isle May 19, 2019, 11:28 PM

#

okay so you are asking about the database itself

#

aiosqlite is not a database. sqlite is the database, aiosqlite is the package python uses to talk to sqlite asynchronously

raw onyx May 19, 2019, 11:28 PM

#

well, then the question is "is sqlite bad?"

ornate isle May 19, 2019, 11:28 PM

#

to answer your question, sqlite is amazing and is the most widely-deployed piece of software on earth (truth) but there are certain things you definitely do not want to use it for

#

if your database is being accessed by multiple apps, or youll have multiple server instances, or anything like that, you cant really use it

#

if your app will just live in one place on one server accessing one db, you can use it.

#

but only one thing can access it at a time, so if you have multiple threads or workers that try to use it, theyll each have to take their turn because its just one big file

#

in practice this basically means you probably shouldnt use it for something that you expect to be getting hundreds of requests/second to or something.

#

but yes i do know it's a pain to get started with the other databases. that is fine. what a lot of people do is use sqlite during development and switch to postgres over time

#

if you are using an ORM or query library (as opposeed to just writing raw SQL) this is even easier because to switch from one db to the other you just change the line of code that specifices which db you are using and most/all of the code will just work

raw onyx May 19, 2019, 11:33 PM

#

the bottom line im getting is, that i shouldn't use sqlite for any large application?

ornate isle May 19, 2019, 11:33 PM

#

i would use postgres

#

and most will say this

#

but it is fully acceptable to use sqlite in the meantime while you build the app and learn postgres, it shouldnt have to slow you down or anything

#

is the app a web app that people will be using?

raw onyx May 19, 2019, 11:34 PM

#

no, it's a discord bot, it currently uses aiosqlite for it's database interaction

ornate isle May 19, 2019, 11:34 PM

#

ahhh okay

#

so see that is an example of something i would think sqlite is fine for

#

the bot talks to the db and nothing else

#

i have a few bots running off even more hilarious options than sqlite

#

just make sure to back up the database frequently since it just lives as a file on the same server as the bot

raw onyx May 19, 2019, 11:37 PM

#

okay

ornate isle May 19, 2019, 11:37 PM

#

my recommendation would be use sqlite for this

#

make your bot

#

also over time learn postgres just to know it

#

eventually if you choose you can swap them out

raw onyx May 19, 2019, 11:38 PM

#

swapping them out shouldn't be hard, right?

#

or require rewritting the code for the database

ornate isle May 19, 2019, 11:39 PM

#

youre using the discord bot api? im not too familiar with it. does it handle the database stuff for you, you just give it the db url?

#

or is this your own db code

raw onyx May 19, 2019, 11:39 PM

#

it's my own db code

#

the discord library doesn't do that stuff for you

ornate isle May 19, 2019, 11:39 PM

#

right. were you planning to use an ORM like sqlalchemy or just write raw sql

raw onyx May 19, 2019, 11:39 PM

#

i guess you could say raw sql?

ornate isle May 19, 2019, 11:41 PM

#

so in that case the syntax is similar but not the same. for basic stuff it will look very familiar but a few thigns would have to be changed

#

like field types are a bit different

#

bear in mind i'm just coming at this from a 'launching now is preferred to taking time to learn new stuff if the current thing will work' approach

#

if you dont mind digging into postgres a bit, that might be better since youre writing raw sql and dont want to have to change your code later

raw onyx May 19, 2019, 11:43 PM

#

alright

#

thanks for the advice

ornate isle May 19, 2019, 11:43 PM

#

and then youll knw postgres which youd use for anything really big

#

and youll want to use the asyncpg library

raw onyx May 19, 2019, 11:46 PM

#

yes, i know that

torn sphinx May 20, 2019, 9:05 AM

#

is there a word for total number of queries

#

something.. like.. population?

#

I'm looking for the technical term

harsh pulsar May 20, 2019, 12:11 PM

#

What would the context be

pale crest May 20, 2019, 12:48 PM

#

@torn sphinx Maybe a "batch"?