#databases | Python | Page 63

unborn sentinel Apr 3, 2019, 6:02 PM

#

Yeppers

twin plank Apr 3, 2019, 6:02 PM

#

Okay well thank you,

#

you'll likely hear from me again haha

unborn sentinel Apr 3, 2019, 6:03 PM

#

I look forward to it

twin plank Apr 3, 2019, 6:03 PM

#

😃

twin plank Apr 3, 2019, 6:26 PM

#

@unborn sentinel aha I'm stuck

unborn sentinel Apr 3, 2019, 6:26 PM

#

What's up

twin plank Apr 3, 2019, 6:26 PM

#

my laptop died so I can't try anything in but it's rly simple

#

just trying to move from my json to a DB

#

in the insert like or whatever

#

I used f string

#

and tried to make the variables send but like it wouldn't work

#

wait should I just convert my json?

unborn sentinel Apr 3, 2019, 6:29 PM

#

# Larger example that inserts many records at a time
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

So that's the basic way to do it. And yeah, ideally you'll want your JSON to be stripped of its keys and what not, organized by what goes where, and then you can dump them more or less all at once

#

May take a little fiddling about to make it work, but you'll get it

#

Give it a try when you're powered back up

twin plank Apr 3, 2019, 6:30 PM

#

I was like trying to write for each item in the list

unborn sentinel Apr 3, 2019, 6:31 PM

#

How do you mean?

twin plank Apr 3, 2019, 6:33 PM

#

for loop

#

item[I]

unborn sentinel Apr 3, 2019, 6:33 PM

#

Goooootcha

twin plank Apr 3, 2019, 6:33 PM

#

lol

unborn sentinel Apr 3, 2019, 6:33 PM

#

Oh well you can do that to prep it

twin plank Apr 3, 2019, 6:33 PM

#

what do I do with an SQL file?

unborn sentinel Apr 3, 2019, 6:33 PM

#

What do you mean?

#

Oh the file it generates?

#

Just put it where you want it so long as you can access it from your script

twin plank Apr 3, 2019, 6:34 PM

#

https://blog.sqlizer.io/posts/convert-json-to-sql/

SQLizer.io

How to convert JSON to SQL - the quick and easy way

unborn sentinel Apr 3, 2019, 6:34 PM

#

Wow, didn't know that was a thing

#

Nice

twin plank Apr 3, 2019, 6:35 PM

#

lol

unborn sentinel Apr 3, 2019, 6:36 PM

#

Well I guess in my brain I was like, "Someone has to have done this before, right?" but didn't look it up

twin plank Apr 3, 2019, 6:36 PM

#

yeah same

#

I was like convert one structured file to another seems easy enough

#

~~for somebody who knows what they are doing of course~~

unborn sentinel Apr 3, 2019, 6:37 PM

#

You'll want to double check it afterwards, because sometimes those automated systems aren't always perfect

twin plank Apr 3, 2019, 6:38 PM

#

yeah I think I'll use this

#

https://sqlitebrowser.org/

DB Browser for SQLite

unborn sentinel Apr 3, 2019, 6:38 PM

#

Sounds wise to me

#

Yeah the tedious part of using databases is getting them set up

#

Once they're up and loaded with your data they're a breeze

twin plank Apr 3, 2019, 6:41 PM

#

wait

#

📎 unknown.png

#

what type?

#

just mysql right?

unborn sentinel Apr 3, 2019, 6:42 PM

#

Noo, all of those are actually slightly different syntaxes

twin plank Apr 3, 2019, 6:42 PM

#

urg

#

CREATE TABLE IF NOT EXISTS items (
    `list_name` VARCHAR(6) CHARACTER SET utf8,
    `list_damage` INT,
    `list_health` INT,
    `list_price` INT
);
INSERT INTO items VALUES
    ('sword',10,25,50),
    ('shield',0,100,50),
    ('bat',5,15,25),
    ('gun',50,30,150);
f

unborn sentinel Apr 3, 2019, 6:44 PM

#

Looks logical to me.

twin plank Apr 3, 2019, 6:44 PM

#

Sounds wise... Looks logical lmao

unborn sentinel Apr 3, 2019, 6:45 PM

#

I'm wordy, I can't help it

twin plank Apr 3, 2019, 6:46 PM

#

This better?

CREATE TABLE IF NOT EXISTS data (
item_damage INT NULL,
item_health INT NULL,
item_name VARCHAR(6) NULL,
item_price INT NULL
);

INSERT INTO data VALUES
(10,25,"sword",50),
(0,100,"shield",50),
(5,15,"bat",25),
(50,30,"gun",150);

unborn sentinel Apr 3, 2019, 6:46 PM

#

Any particular reason to have the data ordered that way?

twin plank Apr 3, 2019, 6:47 PM

#

This was a diff converter

#

https://sqlify.io/convert/json/to/sqlite

Convert large JSON to SQLite in seconds

Convert and transform big files of JSON to SQLite in seconds. Add up to 100Gb of JSON or CSV data via file upload or URL or raw and output SQLite

unborn sentinel Apr 3, 2019, 6:47 PM

#

Gotcha gotcha

twin plank Apr 3, 2019, 6:51 PM

#

yay

#

📎 unknown.png

#

i might change the order tho haha

#

Omg today in my computing sci class they are learning... lists aha

tired elk Apr 3, 2019, 8:27 PM

#

anyone here familiar with writing to csv from a dataframe. it seems ot be adding information to my data. the data is in one row. and its numbering segments in that row

proper copper Apr 4, 2019, 12:34 AM

#

anyone saavy with sqlalchemy? Wondering what's some approaches for finding duplicates and renaming them incrementally. Could I do this in one step or would I have to fetch all duplicates, rename them in python (ORM) and then batch update

abstract herald Apr 4, 2019, 2:02 AM

#

so because im not sure if this fits in in #discord-bots or here..but we have a mongodb database weve been using for a year now, its a 3 node cluster and it costs me money to host.. its fine and all but we get roughly 14k requests per day (176538 in the past 2 days) ..ive been considering moving to a dedicated mysql server instead of the mongo cluster..we run about 3-4 bots on one vps and have plenty of leftover resources to use sqlite. ( DO Intel(R) Xeon(R) CPU E5-2650 v4 @ 2.20GHz) . the real question is..do you think our current hosting situation could also accomodate running instances of sqlite with the heavy amount of requests?

tl;dr - mongodb vs mysql vs sqlite for what id consider a medium workload

torn sphinx Apr 4, 2019, 2:50 AM

#

consider CloudSQL

#

it's hosted mysql

#

you'd be paying a lot less.. let me calculate in a few

proper copper Apr 4, 2019, 3:19 AM

#

+1 for the CloudSQL. The one feature i also like about cloudsql is its one less maintenance that someone has to do to maintain it. Definitely a big win on small teams where developers are scarce

twin plank Apr 4, 2019, 3:59 AM

#

Does this make sense? I have like no clue how to use this db even tho it's simple

📎 unknown.png

#

not blob whoops

#

can i store a list ? would it have to be a string

#

Oh do i have to create a whole other table and somehow call that one from this one?

#

@unborn sentinel I'm back aha

#

Still havent done anything cause im confused lol

proper copper Apr 4, 2019, 4:26 AM

#

Is the blob supposed to be a file?

twin plank Apr 4, 2019, 4:51 AM

#

I changed it to text @proper copper

#

It's a list or dict i keep forgetting

#

prob list

proper copper Apr 4, 2019, 4:52 AM

#

Does the database you're using support JSON?

#

Might be nicer to work with

#

when serialization/deserializing

twin plank Apr 4, 2019, 4:54 AM

#

It's SQLite

#

idk

#

prob

proper copper Apr 4, 2019, 5:18 AM

#

Looks like there is, but you may not need it unless you're trying to do some advance queries. But text works

twin plank Apr 4, 2019, 5:22 AM

#

@proper copper Any idea why this doesnt work? c.execute(f'SELECT identity FROM users WHERE identity={ctx.User.id}')

#

sqlite3.OperationalError: near "From": syntax error

proper copper Apr 4, 2019, 5:24 AM

#

Is this sqlalchemy?

#

oh wait, its sqlite

#

duh, im not sure tbh. It looks right

plain radish Apr 4, 2019, 5:33 AM

#

ctx.User.id User should be user here

#

capitalisation matters

#

also end your sql statements with a ;

proper copper Apr 4, 2019, 5:35 AM

#

Interesting. why wouldn't the interpolation work there?

plain radish Apr 4, 2019, 5:44 AM

#

what do you mean?

proper copper Apr 4, 2019, 5:47 AM

#

"ctx.User.id User should be user here"

#

Maybe I misread that

#

Not sure what you meant there by "user" here. why wouldn't the current code work assuming identity is the "id" property of ctx.User.id

twin plank Apr 4, 2019, 5:57 AM

#

@plain radish tried user and User

#

c.execute(f'SELECT identity FROM users WHERE identity={ctx.User.id};'):

plain radish Apr 4, 2019, 6:09 AM

#

@proper copper ok, let me ask you this, what is ctx here.

#

is it a discord.py commands extension Context instance?

#

if it is, ctx doesn't have any attribute named User or user in that case unless you're setting the attribute yourself.

#

if you're trying to get the author of the command, that would be ctx.author if using rewrite, or ctx.message.author if using v0.16.12

silent tapir Apr 4, 2019, 6:14 AM

#

can aiomysql use $1 in a query or must it be %s? haven't found anything on this.

plain radish Apr 4, 2019, 6:21 AM

#

aiomysql uses c-style string formatting with the % operator. It should support the same argument specifiers for that formatting method

#

It won't support other styles though, so $1 isn't going to work

silent tapir Apr 4, 2019, 6:24 AM

#

ok

silent tapir Apr 4, 2019, 8:06 AM

#

Ok, I had some time to test, but it does not seem to be supported, atleast

await cur.execute("INSERT INTO `test` (`key`, `value`) VALUES (%1, %2);", (key, value))```
does result in an error at the comma after `%1`.

young pier Apr 4, 2019, 8:33 AM

#

I have the following and am having an issue with it...

class InstancedDatabase(object):
    """ Instanced database handler class. """
    def __init__(self, databasefile):
        self._connection = sqlite3.connect(databasefile, check_same_thread=False)
        self._cursor = self._connection.cursor()

    def execute(self, sqlquery, queryargs=None):
        """ Execute a sql query on the instanced database. """
        if queryargs:
            self._cursor.execute(sqlquery, queryargs)
        else:
            self._cursor.execute(sqlquery)
        return self._cursor

    def commit(self):
        """ Commit any changes of the instanced database. """
        self._connection.commit()

    def close(self):
        """ Close the instanced database connection. """
        self._connection.close()
        return

    def __del__(self):
        """ Close the instanced database connection on destroy. """
        self._connection.close()

#---------------------------------------
# [Required] functions
#---------------------------------------
def Init():
    global MySet
    global AussieDB
    AussieDB = InstancedDatabase(DatabaseFile)
    if not os.path.isfile(DatabaseFile):
        AussieDB.execute("CREATE TABLE IF NOT EXISTS Users (user_id TEXT UNIQUE, stage_1 INTEGER, stage_2 INTEGER, stage_3 INTEGER)")
        AussieDB.commit()
    MySet = Settings(settingsFile)

For some reason on Init() it is creating the database but not the table...

dull scarab Apr 4, 2019, 9:02 AM

#

The file will be created when you connect to it

#

so if not os.path.isfile(DatabaseFile): will never be True

#

because this before AussieDB = InstancedDatabase(DatabaseFile)will create the file if it doesnt exist

#

I'd just execute that create table no matter

#

cause you're using IF NOT EXISTS so it wont do anything if it already exists

young pier Apr 4, 2019, 9:05 AM

#

Ah, ok...tyVm!

#

I hadn't even thought of that LUL

twin plank Apr 4, 2019, 9:07 AM

#

New problem...

c = conn.cursor()
    c.execute(f'INSERT OR IGNORE INTO users (ID, HP, CUR, INV) VALUES ({message.author.id}, {100}, {0}, {[]})')
    conn.commit()
    conn.close()

Traceback (most recent call last):
  File "C:\Users\Hunter\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\client.py", line 218, in _run_event
    await coro(*args, **kwargs)
  File "C:/Users/Hunter/PycharmProjects/OOP/main.py", line 14, in on_message
    c.execute(f'INSERT OR IGNORE INTO users (ID, HP, CUR, INV) VALUES ({message.author.id}, {100}, {0}, {[]})')
sqlite3.OperationalError: no such column:

#

📎 unknown.png

#

"no such column"???

dull scarab Apr 4, 2019, 9:08 AM

#

the string above says user_id, stage_1, stage_2, stage_3?

twin plank Apr 4, 2019, 9:08 AM

#

what?

dull scarab Apr 4, 2019, 9:09 AM

#

        AussieDB.execute("CREATE TABLE IF NOT EXISTS Users (user_id TEXT UNIQUE, stage_1 INTEGER, stage_2 INTEGER, stage_3 INTEGER)")```

twin plank Apr 4, 2019, 9:09 AM

#

AussieDB?

dull scarab Apr 4, 2019, 9:09 AM

#

Wait

#

Lol nvm

#

I thought it was the same guy

twin plank Apr 4, 2019, 9:10 AM

#

Oh no haha

dull scarab Apr 4, 2019, 9:10 AM

#

Specifically cause you said new problem just after i helped them

twin plank Apr 4, 2019, 9:10 AM

#

Oh sorry haha

#

I was here a while ago

dull scarab Apr 4, 2019, 9:10 AM

#

But, avoid using string formatting for your queries

twin plank Apr 4, 2019, 9:11 AM

#

yeah i'll fix that after

dull scarab Apr 4, 2019, 9:11 AM

#

I am not sure sqlite likes lists

twin plank Apr 4, 2019, 9:11 AM

#

It was working fine before but then i changed the db column names

dull scarab Apr 4, 2019, 9:12 AM

#

what does your table creation look like

twin plank Apr 4, 2019, 9:12 AM

#

wdym

dull scarab Apr 4, 2019, 9:12 AM

#

How do you create the table

twin plank Apr 4, 2019, 9:12 AM

#

oh i made it in the program

#

it worked before

dull scarab Apr 4, 2019, 9:12 AM

#

May have to refresh things then

#

Like make sure the software saved it,

#

and restart your script

twin plank Apr 4, 2019, 9:13 AM

#

i'll remake it see if that helps because i've refreshed too

dull scarab Apr 4, 2019, 9:13 AM

#

it may be reading an old version or something

#

also make sure you're connecting to the correct database

#

Else you can make your table programmatically instead

#

with CREATE TABLE IF NOT EXISTS ...

twin plank Apr 4, 2019, 9:15 AM

#

Does that go after the values segment?

dull scarab Apr 4, 2019, 9:16 AM

#

It's a separate execution

#

con.execute("CREATE TABLE...")
con.execute("INSERT ...")```

twin plank Apr 4, 2019, 9:17 AM

#

ahh

#

it won't stop saying it doesn't exist

dull scarab Apr 4, 2019, 9:17 AM

#

How do you connect to it

twin plank Apr 4, 2019, 9:18 AM

#

import sqlite3
conn = sqlite3.connect('saveData.db')

bot = commands.Bot(command_prefix='=')


@bot.event
async def on_message(message):
    if message.author == bot.user:
        return
    c = conn.cursor()
    c.execute(f'INSERT OR IGNORE INTO users (ID, HP, CUR, INV) VALUES ({message.author.id}, {100}, {0}, {[]})')
    conn.commit()
    conn.close()
    player = Player(*(c.execute(f'SELECT identity FROM users WHERE identity = {message.author.id}')))

dull scarab Apr 4, 2019, 9:19 AM

#

Are you sure it's not the 2nd execute?

#

I don't recall seeing identity being a column

twin plank Apr 4, 2019, 9:19 AM

#

ah

#

Nope

#

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Users\Hunter\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\client.py", line 218, in _run_event
    await coro(*args, **kwargs)
  File "C:/Users/Hunter/PycharmProjects/OOP/main.py", line 14, in on_message
    c.execute(f'INSERT OR IGNORE INTO users (ID, HP, CUR, INV) VALUES ({message.author.id}, {100}, {0}, {[]})')
sqlite3.OperationalError: no such column:

dull scarab Apr 4, 2019, 9:20 AM

#

create a command that prints "SELECT * FROM users"

twin plank Apr 4, 2019, 9:20 AM

#

identity was the old name

dull scarab Apr 4, 2019, 9:21 AM

#

actually, there probably a better way to do that

#

2 sec

#

SELECT sql from sqlite_master
WHERE tbl_name = "users" AND type = "table"```

#

What does that give if you execute that

twin plank Apr 4, 2019, 9:23 AM

#

Like this?

c.execute('SELECT sql from sqlite_master WHERE tbl_name = "users" AND type = "table"')

dull scarab Apr 4, 2019, 9:23 AM

#

yea

twin plank Apr 4, 2019, 9:23 AM

#

nothing

dull scarab Apr 4, 2019, 9:24 AM

#

did you get the result?

twin plank Apr 4, 2019, 9:24 AM

#

@bot.event
async def on_message(message):
    if message.author == bot.user:
        return
    c = conn.cursor()
    c.execute('SELECT sql from sqlite_master WHERE tbl_name = "users" AND type = "table"')
    # c.execute(f'INSERT OR IGNORE INTO users (ID, HP, CUR, INV) VALUES ({message.author.id}, {100}, {0}, {[]})')
    conn.commit()
    conn.close()
    player = Player(*(c.execute(f'SELECT ID FROM users WHERE ID = {message.author.id}')))
    print(player.identity)

#

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Users\Hunter\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\client.py", line 218, in _run_event
    await coro(*args, **kwargs)
  File "C:/Users/Hunter/PycharmProjects/OOP/main.py", line 18, in on_message
    player = Player(*(c.execute(f'SELECT ID FROM users WHERE ID = {message.author.id}')))
sqlite3.ProgrammingError: Cannot operate on a closed database.

dull scarab Apr 4, 2019, 9:25 AM

#

gotta fetch the rresult

twin plank Apr 4, 2019, 9:25 AM

#

oh lmao

dull scarab Apr 4, 2019, 9:25 AM

#

also you close the connection before the other execute is fired

#

thats the reason for the error

twin plank Apr 4, 2019, 9:26 AM

#

I thought i could still call for some reason

dull scarab Apr 4, 2019, 9:26 AM

#

also you can use context managers with sqlite

twin plank Apr 4, 2019, 9:27 AM

#

[('CREATE TABLE "users" (\n\t"ID"\tINTEGER NOT NULL UNIQUE,\n\t"HP"\tINTEGER NOT NULL,\n\t"CUR"\tINTEGER NOT NULL,\n\t"INV"\tTEXT NOT NULL,\n\tPRIMARY KEY("ID")\n)',)]

dull scarab Apr 4, 2019, 9:28 AM

#

con = sqlite3.connect(...)
with con:
    with con.execute("select ...") as cur:
        result = cur.fetchone()``` i believe should work for a context manager approach

#

Nontheless, that seems to be the correct table

twin plank Apr 4, 2019, 9:28 AM

#

Yep grumpy

dull scarab Apr 4, 2019, 9:28 AM

#

try removing the [] empty list

#

and put in a number or something

twin plank Apr 4, 2019, 9:29 AM

#

Um i think that was the issue

dull scarab Apr 4, 2019, 9:30 AM

#

Yeah, i thought it would be an issue at the start

twin plank Apr 4, 2019, 9:30 AM

#

this works:

c.execute(f'INSERT OR IGNORE INTO users (ID, HP, CUR, INV) VALUES ({message.author.id}, {100}, {0}, \'[]\')')

dull scarab Apr 4, 2019, 9:30 AM

#

Yeah, cause now it's a string

twin plank Apr 4, 2019, 9:31 AM

#

lol yeah

dull scarab Apr 4, 2019, 9:31 AM

#

also you can use " inside ' without having to escape

twin plank Apr 4, 2019, 9:31 AM

#

I though sql was ' only

dull scarab Apr 4, 2019, 9:31 AM

#

I don't believe so

twin plank Apr 4, 2019, 9:31 AM

#

Somebody told me that aha

dull scarab Apr 4, 2019, 9:31 AM

#

Can't recall tbh

#

Worst case juse swap it around

#

wrap your string in ", and your sql in '

twin plank Apr 4, 2019, 9:32 AM

#

Yeah it works fine with "

dull scarab Apr 4, 2019, 9:32 AM

#

👍🏽

twin plank Apr 4, 2019, 9:33 AM

#

Can you help me test something really quick

dull scarab Apr 4, 2019, 9:34 AM

#

maybe

twin plank Apr 4, 2019, 9:34 AM

#

I just need you to type something on my server

#

anything

#

just to see if it works for all users

dull scarab Apr 4, 2019, 9:34 AM

#

my dms are open, you dont need to friend me

twin plank Apr 4, 2019, 9:35 AM

#

It errored

#

database closed...?

#

it should reopen

dull scarab Apr 4, 2019, 9:36 AM

#

not if you call conn.close

twin plank Apr 4, 2019, 9:37 AM

#

So what do i do? Not close or do i have to write conn = sqlite3.connect('saveData.db') each time

dull scarab Apr 4, 2019, 9:37 AM

#

just commit

#

then proceed

#

only conn.close when you're closing your script

twin plank Apr 4, 2019, 9:37 AM

#

but what if it crashes?

dull scarab Apr 4, 2019, 9:37 AM

#

¯_(ツ)_/¯

twin plank Apr 4, 2019, 9:38 AM

#

Can you send something again

#

Umm weird

#

📎 unknown.png

#

idk what the second 2 ids are

#

first is me

#

then you

dull scarab Apr 4, 2019, 9:39 AM

#

any message it reads ¯_(ツ)_/¯

twin plank Apr 4, 2019, 9:40 AM

#

there was only two though

dull scarab Apr 4, 2019, 9:40 AM

#

other servers?

twin plank Apr 4, 2019, 9:40 AM

#

no

dull scarab Apr 4, 2019, 9:40 AM

#

old data then i guess

#

I know less that you in this case.

twin plank Apr 4, 2019, 9:40 AM

#

I just restarted it and it showed up when we sent the messages weird

dull scarab Apr 4, 2019, 9:41 AM

#

one of them dont even look like an id

twin plank Apr 4, 2019, 9:41 AM

#

yeah it's shorter i saw that

#

im going to restart it again

#

This showed up after like 10 seconds (399099742505926666,)

#

now more (399099742505926666,) (399099742505926666,) (80528701850124288,)

dull scarab Apr 4, 2019, 9:43 AM

#

print bot.guilds

#

!docs get discord.Client.guilds

delicate fieldBOT Apr 4, 2019, 9:44 AM

#

`discord.Client.guilds`

guilds```List[[`Guild`](#discord.Guild "discord.Guild")] – The guilds that the connected client is a member of.

dull scarab Apr 4, 2019, 9:44 AM

#

Yeah that's a thing

#

just making sure..

twin plank Apr 4, 2019, 10:23 AM

#

@dull scarab I forgot i had my bot on another server

#

the discordpy server

#

As it turns out... I have to switch out the db

#

to asyncpg

#

but it's serverbased and idk what to do

dull scarab Apr 4, 2019, 10:51 AM

#

You don't Have to swap it to asyncpg

#

but it's a good approach

#

just using aiosqlite would work in the shortterm if you dont plan on making the bot public and have it be in lots of guilds

#

If you want it to be server based that start using the guild id in your conditions, db etc

unkempt sable Apr 4, 2019, 12:55 PM

#

if I'm making a GUI app should I use asyncpg or just psycopg2?

ripe flare Apr 4, 2019, 1:39 PM

#

What's everyone's favourite database system for machine learning? Ie postgre etc

eager nest Apr 4, 2019, 2:30 PM

#

Hey I'm trying to build something and have kinda a general question on setting it up, so this is... a little non specific and big picture question. I think I have two questions: how can something be done and then follow up is there a better way in terms of the available options?

Overall, I want to select five values and add them together and then sort DESC

So what I'm trying to do is make a query that will have a few sub queries I think, because I'm trying to do a few things with it. It's going to have five parts:

three of the five parts are just selecting a value, pretty simple. the other two I will need to do some basic JOIN and math stuff.

I can paste in what I have that does part of the math for one of the five things I need to do.

in writing this... I'm trying to visualize what's going on. I can picture a simple JOIN between two columns but if I have a complicated query to write then I am not sure what it looks like beyond that simple venn diagram.

twin plank Apr 4, 2019, 3:01 PM

#

https://www.microfocus.com/documentation/idol/IDOL_12_0/MediaServer/Guides/html/English/Content/Getting_Started/Configure/_TRN_Set_up_PostgreSQL.htm
Can somebody better explain step 6-b please

twin plank Apr 4, 2019, 5:12 PM

#

nvm that tutorial wasn't even useful

#

i got it

#

mostly

#

how can I do this in postgres tho

#

f'INSERT OR IGNORE INTO users (ID, HP, CUR, INV) VALUES ({message.author.id}, {100}, {0}, "[]")'
```and I do plan on taking the vars out of the command aha

subtle flax Apr 4, 2019, 7:26 PM

#

I'm using postgres and have several different selects returning the same set of fields. Is it 100% wrong, big nonono, awful practice to use formatting string in any way?
Example: f"SELECT {USER_RETURN_FIELDS} FROM users WHERE ... = $1"
I use constant, users have no way to compromise it

twin plank Apr 4, 2019, 10:29 PM

#

It's insecure

patent glen Apr 4, 2019, 11:03 PM

#

i'd say that's fine tbh for a constant list of fields

#

it might be better to have something like a function that builds it from a list of strings, with quotes and stuff

#

@twin plank are you using asyncpg or psycopg2?

#

@subtle flax er see my reply above, didn't see how old your question was

twin plank Apr 4, 2019, 11:10 PM

#

asyncpg

#

Oh sorry I got that figured out already

twin plank Apr 5, 2019, 12:12 AM

#

^

#

@nova hawk lol

nova hawk Apr 5, 2019, 12:13 AM

#

postgres

twin plank Apr 5, 2019, 12:13 AM

#

yessir

#

But with asyncpg

nova hawk Apr 5, 2019, 12:14 AM

#

Haven't used that so not sure if it has that method.

twin plank Apr 5, 2019, 12:14 AM

#

Well I have no clue either haha

nova hawk Apr 5, 2019, 12:14 AM

#

I see it has an ON CONFLICT you can use for your inserts

#

For what to do when the id already exists

#

Alternativly you can do an update followed by an insert.

#

Update won't work when it doesn't exist. And insert won't work when it does.

twin plank Apr 5, 2019, 12:21 AM

#

But the whole point is to check before using the db

#

So my code also prints when a new user is added and thats typically like 1 a minute at this point

#

but this is how many checks are done

#

Added: Xua - 455289384187592704
Added: Emote Collector - 405953712113057794
Added: Jonny™ - 170619078401196032
Added: mellowmarshe - 300088143422685185
Added: Xua - 455289384187592704
Added: Jonny™ - 170619078401196032
Added: Kira Miki - 538344287843123205
Added: Jonny™ - 170619078401196032
Added: Kira Miki - 538344287843123205
Added: Xua - 455289384187592704
Added: Xua - 455289384187592704
Added: Lorenzo - 371741730455814145
Added: Takuru - 552259110192545813
Added: GetBeaned - 492797767916191745
Added: Xua - 455289384187592704
Added: Xua - 455289384187592704
Added: Xua - 455289384187592704
Added: Jonny™ - 170619078401196032
Added: Xua - 455289384187592704
Added: Jonny™ - 170619078401196032
Added: Jonny™ - 170619078401196032
Added: Jonny™ - 170619078401196032
Added: Xua - 455289384187592704
Added: Adventure! - 482373088109920266
Added: Jonny™ - 170619078401196032
Added: Jonny™ - 170619078401196032
Added: Lorenzo - 371741730455814145
Added: Takuru - 552259110192545813
Added: Jonny™ - 170619078401196032
Added: Lorenzo - 371741730455814145
Added: Xua - 455289384187592704
Added: Xua - 455289384187592704
Added: Xua - 455289384187592704
Added: Adventure! - 482373088109920266
Added: Lorenzo - 371741730455814145
Added: Takuru - 552259110192545813
Added: Xua - 455289384187592704

#

That wasn't even a minute

#

see all the doubles too

#

all of those people are already in my data base but this is just to show how inefficient it is

nova hawk Apr 5, 2019, 12:26 AM

#

Why do you have users in there multiple times?

twin plank Apr 5, 2019, 12:26 AM

#

because it reads every message

#

ignore the "Added:"

#

This is showing all the messages, not just new users

nova hawk Apr 5, 2019, 12:29 AM

#

Doing the checking in sql is way faster then looking them up in a list.

twin plank Apr 5, 2019, 12:42 AM

#

So it won't overwhelm it...?

nova hawk Apr 5, 2019, 12:43 AM

#

Checking if the pk exists is O(1) because of the indexing.

twin plank Apr 5, 2019, 12:43 AM

#

what

nova hawk Apr 5, 2019, 12:44 AM

#

It's similar to how set and dictionary lookups work.

twin plank Apr 5, 2019, 12:44 AM

#

I just don't understand your point aha

nova hawk Apr 5, 2019, 12:45 AM

#

Oh, it's a single operation. It doesn't need to search.

twin plank Apr 5, 2019, 12:55 AM

#

Oh?

#

So my database or bot won't crash if too many users are talking?

nova hawk Apr 5, 2019, 12:57 AM

#

I don't know that.

twin plank Apr 5, 2019, 12:58 AM

#

Oh well that's kind of the whole potential issue...

proper copper Apr 5, 2019, 2:34 AM

#

So I have a loop that is currently doing a select & check for 7000 items in sqlalchemy...
e.g. SELECT hash FROM some_table WHERE hash = some_hash

some_hash is a pre-calculated value I get from doing hash(NewModel(...some_properties)
I do this because I want to do a "get or create" action where if I find the hash, I return the object from the database, but if I don't I make a new one.

Would I be better off doing this comparison in memory?
e.g. SELECT hash FROM some_table

Get all the hashes
Calculate all the hashes from the new objects I'm about to make
Performing a set operation in Python to see which ones I need to make

The only thing I'm afraid of is this won't scale if the hash table gets too large

torn sphinx Apr 5, 2019, 1:16 PM

#

Are you guys uing sqlite to access your databases?

#

How can I get started with it

plain radish Apr 5, 2019, 1:17 PM

#

sqlite is a single-file sql database, and it's definitely useful for a lot of projects

#

you won't lose out by learning SQL regardless though

#

since it's relatively standardised across multiple sql servers also, such as mysql, mariadb, postresql, mssql

#

(there's only small differences to adjust to or new features to learn about beyond the scope of sql itself)

#

just keep in mind though that sqlite is a single-file based db

#

so you can't do much concurrency

#

files lock when editing, afterall

#

you can get started with it by looking at any sql course, or by looking at sqlite specific tutorials

eager nest Apr 5, 2019, 1:41 PM

#

I started learning with SQLite and then switched to MySQL and it was relatively painless transition. 😃

#

So glad SQL makes sense to me because learning new things is fun but no way around it being a difficult and sometimes painful process so any sense of comfort is something I appreciate XD

#

BTW I was able to figure out my earlier question but then found out I was heading in the wrong direction. So I nixxed the whole thing. But hey I mean it was a learning experience and nobody ccan take that away.

plain radish Apr 5, 2019, 2:43 PM

#

that's it mate

#

gotta dive it, give attempts and see where it goes

twin plank Apr 5, 2019, 6:10 PM

#

Is this bad?

📎 unknown.png

ionic pecan Apr 5, 2019, 6:34 PM

#

i don't know what i'm looking at. what is this, the disk of your databae?

#

postgres stats?

toxic rune Apr 6, 2019, 5:29 AM

#

So I have a table with 11 million different combinations (it's the only solution I found that gives me results in a few ms (~50ms or less since it's indexed.) as I can't calculate them on the fly it takes its few minutes to do all the calculations) This table was generated by itertools.combinations() Of course, if you actually have a better method for this it's also welcome since each new character is quite a bit new amount of combinations.

The user can input between 1 - 3 characters and it will run a query that searches the 50 highest total_morale teams that include that character on any of the 4 columns. How can I query that?

As seen in the picture, the first query will search for cidd on the first column and tenebria on the second, it's an issue since it gives wrong results. The highest total_morale team with those two members is the first one seen in the second query but because it searches on the first two columns some results will be omitted. How can I write a query that will search in every column?

📎 unknown.png

reef musk Apr 6, 2019, 8:09 AM

#

@merry wind There are a variety of ways. Some of the most common are mysql-python and SQLAlchemy

#

https://stackoverflow.com/questions/372885/how-do-i-connect-to-a-mysql-database-in-python

Stack Overflow

How do I connect to a MySQL Database in Python?

How do I connect to a MySQL database using a python program?

ionic pecan Apr 6, 2019, 11:55 AM

#

i‘d go for pymysql, its written in pure python and doesn‘t need external dependencies due to that

tacit dagger Apr 6, 2019, 2:48 PM

#

hey guys. I want to make my db only available to the application itself so that only the app could read it, and the users won't be able to read or modify or copy it for themselves. i've been told that sqlite is not good for this purpose cause shipping sqlite with app means giving it to the user actually. so should i use online db like mongoDB or something else? and how should i encrypt it and only make it readable for the app itself? can you suggest some tutorials about this?

unkempt sable Apr 6, 2019, 2:52 PM

#

offline application?

tacit dagger Apr 6, 2019, 3:41 PM

#

@unkempt sable it's offline now, but i don't know maybe i'll make it online.

reef musk Apr 6, 2019, 5:16 PM

#

@tacit dagger if you're talking about a python application it's going to be very hard to do in a way that isn't trivial to defeat. Because the source code of your Python application will be available to the user, all they will have to do is look at how your application accesses the data and do it themselves directly. Even if you encrypt it, your program will have to decrypt the data to use it, and so the key will have to be embedded in the code somewhere. Again, all they have to do is inspect the source code to extract the key.

#

Why are you wanting to prevent your users from accessing the data anyway?

frank rover Apr 6, 2019, 6:33 PM

#

Do you need to keep your IDs as integers or can you make then other things?

nova hawk Apr 6, 2019, 6:34 PM

#

Anything as long as they are unique

frank rover Apr 6, 2019, 6:34 PM

#

So I can make like my post_id = admin then tag_id = 1 and it should be fine later in a filter?

nova hawk Apr 6, 2019, 6:35 PM

#

Oh I thought you were refering to primary keys. If it's not duplicates also don't matter.

frank rover Apr 6, 2019, 6:36 PM

#

Ahh ok will have to look at what commands change that. This book makes use of db.interager for the storing and processing of it

#

I just put it all in a comment until my skills grow past the basics of databases. I am looking forward to trying to automate data processing

tacit dagger Apr 6, 2019, 10:20 PM

#

@reef musk you are right. I want to prevent the users from accessing the data, because i've translated the words (a lot of words like 10 000) in it myself, and i don't want anybody else to abuse my created db. so unfortunately there is absolutely no way to prevent user from accessing it? 😦

wind pelican Apr 6, 2019, 10:23 PM

#

@tacit dagger correct. it is not feasible to hide data from a user but also allow that users cpu to do stuff with it.
if you dont want that information available to the user you have to just not send it to them. for example by making a web app instead or at least making the translation system a web service that your program queries

#

but just assume any data you send to the user is freely available to that user

tacit dagger Apr 6, 2019, 11:00 PM

#

@wind pelican thanks a lot 👍

reef musk Apr 6, 2019, 11:43 PM

#

@tacit dagger - One thing that might be worth considering is releasing the database under Creative Commons license with attribution requirement, so that people can use and share it, but have to give you credit, link to your site, etc

#

Since you're probably not going to stop people from using it if they really want to, might as well encourage people to cite your work and give you credit 😃

#

... or just hide it behind a web API as suggested by @wind pelican, so users don't have a local copy. But even then, you're going to have to defend against automated scraping of the DB. If it's available as a public service, and there are only 10k entries, it's going to be trivial to write a bot to scrape the data anyway (even if you try to use rate limiting). Again, you're probably just better releasing under some sort of license that allows sharing with attribution.

wind pelican Apr 7, 2019, 12:10 AM

#

if you have money, you can attempt to use a restrictive license and sue people that break it, but generally raw data like that doesnt actualy have a lot of value and in some cases 'information' cant actually be copywritten.
for example the scores of a series of baseball games.
the actual page or document you release them in would be protectable, but the actual numbers and which team had them at what time are generally not

#

of course there are other issues with trying to publish baseball scores but the data itself is a reasonable example

proper field Apr 7, 2019, 8:48 AM

#

How would I be able to create a sort of warnings database when someone is warned in a guild? How could this be structured if I were to use a SQL database.

#

This is going to be for a discord bot and right now I use JSON.

tacit dagger Apr 7, 2019, 1:09 PM

#

@reef musk yeah i guess using Creative Commons license is better 🤔

ionic pecan Apr 7, 2019, 1:58 PM

#

for JSON you can use any structure you want

#

i use regular SQL with a infractions table to track warnings & other jam, I can send a schema dump later but it boils down to:

id int pk autoincrement
guild_id bigint not null
actor_id bigint not null
target_id bigint not null
type enum (warning, ban, ...) not null
reason varchar (2000)
created_at timestamp default now at utc not null
updated_at timestamp not null (updated through trigger)
expired_at timestamp
additional_data jsonb (things like role ids for temporary roles & other unstructured jazz)

subtle flax Apr 7, 2019, 6:47 PM

#

I asked about selecting embedded objects in postgres recently and I was adviced to use views. I created one: ```sql
CREATE VIEW messages_with_author AS
SELECT
msg.id,
msg.edit_id,
msg.channel_id,
msg.content,
msg.pinned,

usr.id AS _author_id,
usr.name AS _author_name,
usr.bot AS _author_bot

FROM existing_messages msg
INNER JOIN users usr
ON msg.author_id = usr.id;After fetching this query I look for `_{a}_{b}` (where `a` is embedded object name and `b` is embedded object field) and insert them into nested json. Example:
{
"id": 9,
"edit_id": null,
"channel_id": 1,
"content": "sage",
"pinned": true,
"author": {
"id": 53,
"name": "Cheryl",
"bot": true
}
}``` Is this the best way of doing this? I don't like how I have to type all variables from both tables into view

final shale Apr 8, 2019, 3:32 AM

#

Hi all, can someone tell me how to show your index ? is it show index from table ?

final shale Apr 8, 2019, 3:53 AM

#

nvm 😄 , figured it out

torn sphinx Apr 8, 2019, 4:57 AM

#

What do we get with fetchall() from an SQL database? A list of rows where each row is a list of values?

#

And if query returned nothing, will fetchall() give us an empty list?

#

Apparently, the answer is yes to both questions

torn sphinx Apr 8, 2019, 10:42 AM

#

will there be a difference in index look up time between integer wrapped into string and just regular integer

#

meaning, will iterating through the db for index 57744987222113 be faster than "57744987222113"

pliant canyon Apr 8, 2019, 11:12 AM

#

Hello, it seems like a simple task but im not too sure how to do it. I want to print off a google sheets page say 10 times but it would change the dates on each page it prints so each page will show 1 week

#

Can anybody help?

bronze crane Apr 8, 2019, 1:25 PM

#

Hey guys, I had a question about SQL Alchemy. I'm using the execute() method, but as far as I can tell that means your pass your query as a string? This could be a bit of a pain for large queries with multiple joins. Is there any way around this?

pure scroll Apr 8, 2019, 1:35 PM

#

avoid what? Joins and large queries? you need to de-normalize your data then. also execute method does accept query objects of SQL Alchemy Query API, see: https://docs.sqlalchemy.org/en/latest/orm/query.html

faint herald Apr 8, 2019, 2:02 PM

#

I've been watching some yt videos and read something about postgres backup and restore but i can't understand which file should i use for that. Sometimes i see backup.sql, backup.dump, and some others

#

which one should i choose?

ionic pecan Apr 8, 2019, 4:20 PM

#

the filename?

#

or what do you mean

inner pecan Apr 8, 2019, 4:50 PM

#

for keys,values in differencesdict.items():
    c.execute("INSERT INTO SymbolErrors(symbol, Errors) (?,?)",(keys,values))```

Can anyone explain my error? 
```python
   c.execute("INSERT INTO SymbolErrors(symbol, Errors) (?,?)",(keys,values))
sqlite3.OperationalError: near "(": syntax error```

novel gust Apr 8, 2019, 5:09 PM

#

you need a space between symbolerrors and the column names

dawn pulsar Apr 8, 2019, 9:12 PM

#

  File "C:\Users\ME\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\network.py", line 509, in open_connection
    self.sock.connect(sockaddr)
ConnectionRefusedError: [WinError 10061] No connection could be made because the target machine actively refused it

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\ME\Downloads\Python\Bot Discord Code\MYSQL.py", line 8, in <module>
    port=3306
  File "C:\Users\ME\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\__init__.py", line 179, in connect
    return MySQLConnection(*args, **kwargs)
  File "C:\Users\ME\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\connection.py", line 95, in __init__
    self.connect(**kwargs)
  File "C:\Users\ME\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\abstracts.py", line 716, in connect
    self._open_connection()
  File "C:\Users\ME\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\connection.py", line 206, in _open_connection
    self._socket.open_connection()
  File "C:\Users\ME\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\network.py", line 512, in open_connection
    errno=2003, values=(self.get_address(), _strioerror(err)))
mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on '127.0.0.1:3306' (10061 No connection could be made because the target machine actively refused it)

#

What do I need to do to fix this?>

severe cobalt Apr 8, 2019, 9:14 PM

#

open the port probably

dawn pulsar Apr 8, 2019, 9:15 PM

#

It's not my database, but could it be the host IP that's wrong?

severe cobalt Apr 8, 2019, 9:16 PM

#

that error is telling you the server is misconfigured. it found a server, but the server is denying the connection.

#

the host ip in that error is 127.0.0.1 which is your computer

#

so you probably have the wrong ip set up in MYSQL.py

dawn pulsar Apr 8, 2019, 9:19 PM

#

Thanks :D

inner pecan Apr 8, 2019, 10:28 PM

#

dumb questions incomin:

25     PILL.             FID_PREVIOUS_ASK            24.7           24.67
26    PCGpI.              FID_CLOSING_BID            11.5           12.17
27    PCGpI.              FID_CLOSING_ASK                            23.1
28     NTSX.              FID_CLOSING_BID           18.79           24.39
29     NTSX.              FID_CLOSING_ASK           33.52           27.95
..       ...                          ...             ...             ...
42  GGOpA.EA  FID_PREVIOUS_PERCENT_CHANGE           -0.47           -0.76
43      FXY.     FID_CLOSING_ASK_EXCHANGE              DX              PA
44     ELMD.             FID_PREVIOUS_BID            5.15            5.13```

this is an output for a SELECT * for my sql table when I run my command in terminal - is the inbetween row 29 and 42 not really a gap but just for readability or something?

#

also how to see my full db? (how to open .db file)

marsh oriole Apr 8, 2019, 10:55 PM

#

looks very much like the output has been truncated in the middle, yes

#

..for readability, like you said

#

look into the tool that you're using for how to not do that.

torn sphinx Apr 9, 2019, 1:45 PM

#

display output will always be truncated.. are you looking to dashboard your data or just view it for simple queries..

torn sphinx Apr 9, 2019, 7:19 PM

#

How can I store a list into a postgresql db

#

Ik its noob

patent glen Apr 9, 2019, 7:44 PM

#

@torn sphinx depending on the data you could use a json or xml column, or a separate table with a 1:N relationship to the main table

torn sphinx Apr 9, 2019, 7:44 PM

#

Its just a list containing some user ids entrys = ['4342342342', '34234234234']

#

Could you examplify with some code

patent glen Apr 9, 2019, 7:45 PM

#

it's not just what the data is, it's a question of what you want to do with it

#

and i'm not an expert on this aspect of postgresql

ionic pecan Apr 9, 2019, 7:55 PM

#

postgres has built-in support for entries

#

but the go-to wawy is to use a separate table

#

@torn sphinx how does your schema look so far?

torn sphinx Apr 9, 2019, 7:56 PM

#

I'm pretty noobish @ postgre

#

Could you use a more dumbed down term

#

please

ionic pecan Apr 9, 2019, 7:58 PM

#

your tables in postgres

#

how did you define those

#

so far

torn sphinx Apr 9, 2019, 8:06 PM

#

                      UserID BIGSERIAL,
                      Xp INTEGER,
                      Tokens INTEGER,
                      Bans INTEGER,
                      Level INTEGER,
                      Kicks INTEGER)""")

#

I was looking to create a new table tho

#

Named data

#

that would contain stuff like this

#

@ionic pecan

ionic pecan Apr 9, 2019, 8:09 PM

#

well you need to think about what kind of data you want to associate with the user IDs

torn sphinx Apr 9, 2019, 8:18 PM

#

I don't want to

#

I want to store a list

#

a list of ppl who joined a giveaway

#

@ionic pecan

ionic pecan Apr 9, 2019, 8:20 PM

#

how do you store the giveaway?

torn sphinx Apr 9, 2019, 8:34 PM

#

wdym

#

its a reaction

ruby tangle Apr 9, 2019, 9:10 PM

#

anyone using https://python-gino.readthedocs.io/en/latest/ , where do i set login parameters

unkempt sable Apr 10, 2019, 2:57 AM

#

TEXT or VARCHAR?

serene thicket Apr 10, 2019, 4:58 AM

#

Apologies in advance if i format or post something incorrectly, new to the server and first time asking a question!

#

I have a Python script that runs a bunch of API calls and in short generates a table of data, assigns the results to variables and then inserts them into a table in a MSSQL server database.

for company in data:
    outcome = insertCompanyToTable(
        companyId = company['company_id'], 
        lastContactDate = company['last_contact_date'], 
        lastContactConsultantId = company['last_contact_consultant_id'], 
        lastestNote = company['latest_note'], 
        AccountId = company['accountid']
    )```
This is currently how handling the results from the first SQL query (select statement).

What i am currently doing is then inserting the data row by row, with the following SQL query handling deduping.

```sql
MERGE INTO looker.looker_scratch.latestcontact WITH (HOLDLOCK) AS target 
USING (SELECT '{companyId}' AS company_id,'{lastContactDate}' AS lastest_contact_date,'{lastContactConsultantId}' AS last_contact_consultant_id,'{lastestNote}' AS latest_note,'{AccountId}' AS accountid) AS source (company_id, latest_contact_date, last_contact_consultant_id, latest_note, accountid) ON (target.latest_note = source.latest_note) 
WHEN MATCHED 
    THEN UPDATE SET latest_note = source.latest_note,accountid = source.accountid 
WHEN NOT MATCHED 
    THEN INSERT (company_id, latest_contact_date, last_contact_consultant_id, latest_note, accountid) 
    VALUES ('{companyId}', '{lastContactDate}', '{lastContactConsultantId}', '{lastestNote}', '{AccountId}');```

This is currently slow and inefficient in just so many ways, but most significantly is that i am SMASHING the API i am hitting. In any given run of my script i could be trying to potentially insert around 10k rows of data which would be 20k API calls roughly

#

I was looking to swap my handling of the data to use a list and then bulk insert. So i was looking at using something like this:

for company in data:
    list_to_insert = []
    one_row_to_insert = [] one_row_to_insert.append(company['company_id'])
    one_row_to_insert.append(company['last_contact_date'])
    one_row_to_insert.append(company['last_contact_consultant_id'])
    one_row_to_insert.append(company['latest_note'])
    one_row_to_insert.append(company['accountid'])
    list_to_insert.append(one_row_to_insert)
    outcome = insertCompanyToTable(list_to_insert)```

This leads me to my question.

Currently, my insert SQL query cant handle bulk inserts, I was wondering if anyone had any suggestions around how i could structure my insert query to handle the bulk inserts?

#

Quick side note to this, my SQL skills are pretty good, i am quite new to Python

plain radish Apr 10, 2019, 5:08 AM

#

Welcome to the server! I'll try look over the question a bit, and I'm sure others might be able to help out too over time. MSSQL isn't exactly something I've had to work with much at all, so I may be a bit slow to make sure of some things.

serene thicket Apr 10, 2019, 5:09 AM

#

thanks! its a pleasure to be here 😄 and really appreciate it! any and all assistance is greatly appreciated

#

for better context here is my full script

#

https://mystb.in/veluqozejo.py

#

please be kind to me, i know there are probably a lot of things i could do WAY better yoj

plain radish Apr 10, 2019, 5:11 AM

#

much appreciated. and don't worry, we'll be kind for sure. you're not exactly total newbie material, so it's not like we'll be picking too much apart normally other than some suggestions here and there occasionally

serene thicket Apr 10, 2019, 5:11 AM

#

@plain radish feel free to answer the SQL question in more or less any flavour of SQL, i am pretty good with converting between the SQL languages

plain radish Apr 10, 2019, 5:11 AM

#

it's more that I've literally never had to use Merge before, so I'm just trying to translate it's behaviour first before making sure I've got a good understanding.

serene thicket Apr 10, 2019, 5:12 AM

#

haha funny you say that, this was my first time using merge

#

and i wish i could take more credit for the script, but ive had a decent amount of guidance from @hidden otter

plain radish Apr 10, 2019, 5:12 AM

#

Ah, there's your first mistake

#

😛

serene thicket Apr 10, 2019, 5:12 AM

#

ye thats what i said, but he got angry at me

plain radish Apr 10, 2019, 5:12 AM

#

hahaha

serene thicket Apr 10, 2019, 5:12 AM

#

lol

hidden otter Apr 10, 2019, 5:13 AM

#

squint

serene thicket Apr 10, 2019, 5:13 AM

#

adureBanana

plain radish Apr 10, 2019, 5:13 AM

#

well, gimme a few mins then and i'll come back to you hopefully with something useful

serene thicket Apr 10, 2019, 5:13 AM

#

awesome! thanks mate 😃

plain radish Apr 10, 2019, 5:14 AM

#

btw, while testing this, you may want to save the api data in a json file to load from instead temporarily

#

large api requests are frustring to wait for in testing

serene thicket Apr 10, 2019, 5:14 AM

#

so the API call itself is really really quick, and i have a table of data that is really small to test with

#

so its not to bad

#

but ill read up on how to do what you just mentioned

plain radish Apr 10, 2019, 5:15 AM

#

oh that's good. was thinking you'd have some 15k+ entries or something

#

lol

serene thicket Apr 10, 2019, 5:15 AM

#

so each call is < 1sec, but because i need to make so so many calls its really slow. every row of data i insert is 2 api calls

plain radish Apr 10, 2019, 5:15 AM

#

not bad then

hidden otter Apr 10, 2019, 5:15 AM

#

but you have thousands of rows jeremy

serene thicket Apr 10, 2019, 5:16 AM

#

ye

#

but not when im testing

hidden otter Apr 10, 2019, 5:16 AM

#

thats what he means

#

ah ok

plain radish Apr 10, 2019, 5:16 AM

#

ye he did say it's a test table earlier lol

serene thicket Apr 10, 2019, 5:16 AM

#

i know, but not when i test, i can just make a dummy table in the database to test with (which i have)

#

sigh... see what i have to deal with scragly

hidden otter Apr 10, 2019, 5:16 AM

#

lol shut up

serene thicket Apr 10, 2019, 5:16 AM

#

😛

plain radish Apr 10, 2019, 5:17 AM

#

every row of data i insert is 2 api calls
anyway to avoid this?

#

i feel like that's most of your pain in the above question, right?

#

bulk inserting doesn't prevent the extra api calls unless you're fetching bulk also

#

oh wait, the api is where you're sending the sql

serene thicket Apr 10, 2019, 5:20 AM

#

unfortunately not, the reason being, and there is a lot of back story to why i am even trying to do this, but the first API call runs the query and fails, but it generates a ID for me, then when i call a different API it uses that ID and actually runs the same query but successfully

#

so that is why i need 2 api calls per insert

#

which is why if i can do a bulk insert and i can lower the number of API calls

#

so from my understanding and correct me if i am wrong but

    list_to_insert = []
    one_row_to_insert = [] one_row_to_insert.append(company['company_id'])
    one_row_to_insert.append(company['last_contact_date'])
    one_row_to_insert.append(company['last_contact_consultant_id'])
    one_row_to_insert.append(company['latest_note'])
    one_row_to_insert.append(company['accountid'])
    list_to_insert.append(one_row_to_insert)
    outcome = insertCompanyToTable(
    list_to_insert
    )```
with this i can save maybe something like 20 rows at a time to insert?

#

so then i only need 2 api calls per 20 rows of data

plain radish Apr 10, 2019, 5:23 AM

#

you're still only sending one at a time there

serene thicket Apr 10, 2019, 5:24 AM

#

but i could alter that slightly right?

#

as in that above, is the right approach to do something like a bulk insert?

#

opposed to what is currently in my script?

plain radish Apr 10, 2019, 5:25 AM

#

instead of feeding a single company set of values by argument to insertCompanyToTable, you're just putting the same set of args for that one company into a list and passing it to the same function

#

in the end it's effectively the same thing

#

this is primarily because you're sending the insert on each company iteration, and the list to insert is reset to and empty list each time

serene thicket Apr 10, 2019, 5:27 AM

#

🤦 of course i am

plain radish Apr 10, 2019, 5:28 AM

#

list_to_insert = []

for company in data:
    list_to_insert.append([
        company['company_id'], 
        company['last_contact_date'], 
        company['last_contact_consultant_id'], 
        company['latest_note'], 
        company['accountid']
    ])

outcome = insertCompanyToTable(list_to_insert)

#

if you wanted a list of lists of the data, you'd do that instead i guess

#

the new list is out of the loops scope then, so won't reset to an empty list each time, and the attributes would be accessible via positions, but it's not very readible when you go to actually read those attributes

#

from typing import NamedTuple

class Company(NamedTuple):
    company_id: str
    last_contact_date: str
    last_contact_consultant_id: str
    latest_note: str
    account_id: str

list_to_insert = []

for company in data:
    list_to_insert.append(
        Company(
            company['company_id'], 
            company['last_contact_date'], 
            company['last_contact_consultant_id'], 
            company['latest_note'], 
            company['accountid']
        )
    )

outcome = insertCompanyToTable(list_to_insert)

#

something like this would be more readible when you go to use it elsewhere as you'd be able to access the attributes with standard dot notation

#

for company in list_to_insert:
    print(company.company_id)

serene thicket Apr 10, 2019, 5:50 AM

#

yo sorry, got pulled into a call with my manager, just reading what you sent

#

ok, i think i follow what your saying and what your code is doing

#

and that is definitely way better, and sorts me from the how i get the bulk insert data, so i guess i just need to figure out the SQL query to accept the bulk insert

#

one idea i had, was to insert the data, and then have another step that then goes and deduped afterwards

#

but i think that will be worse in terms of performance

#

because it may insert it really fast, but for a temporary period of time there will be huge bloat in the database, and then the dedupe step still has to run row by row, and i think that will then offset the time i save with the bulk insert

pure scroll Apr 10, 2019, 7:05 AM

#

and deduped afterwards why don't you use unique constraint + upsert

#

most if not every SQL database has a special syntax for upsert
in case of postgres you can do sometihng like that

INSERT INTO my_table (id, row1) VALUES (1, 'foo') ON CONFLICT (my_unique_constraint) DO NOTHING;

#

bulk insert works best if you first create prepared statement to DB and then do pass all the values to it, you can google for it. but psycopg2 postgres driver does have it out of the box, it's called executemany

#

also in case of postgres 11+ I think you can even go further and instead of making prepared statements you can have complied ones (here i'm not 100% sure, but you should check it out, might also save lots of time when inserting values if your insert statement has certain pattern)

serene thicket Apr 10, 2019, 7:14 AM

#

appreciate the response!
so in regards to upsert that is actually kinda what merge is

#

its MSSQL new version of it, but having said that, it certainly has upsert

#

but it allows you to do more matching within a single query statement

#

excuse my ignorance, but what do you mean by using a unique constraint?

pure scroll Apr 10, 2019, 7:16 AM

#

well if you have a way to define what is duplicate then basically there is a definition of uniqueness of your row record, right?

serene thicket Apr 10, 2019, 7:17 AM

#

ye, so this row sql ON (target.latest_note = source.latest_note) is effectively defining what is considered "unique"

pure scroll Apr 10, 2019, 7:17 AM

#

now, having that said, SQL databases do have contraints, which you can define in a way that before the row is inserted it will check if is this record can pass constraint function

serene thicket Apr 10, 2019, 7:17 AM

#

and then after that it is defining what to do when that resolves to true or false which is the WHEN MATCHED and the WHEN NOT MATCHED

pure scroll Apr 10, 2019, 7:17 AM

#

http://www.mysqltutorial.org/mysql-unique/

MySQL Tutorial

Using MySQL UNIQUE Index To Prevent Duplicates

In this tutorial, you will learn how to use the MySQL UNIQUE index to prevent duplicate values in one or more column in a table

serene thicket Apr 10, 2019, 7:18 AM

#

ill check out the link you just sent though, and see if i can do the same sort of thing in mssql

pure scroll Apr 10, 2019, 7:19 AM

#

oh shit, mssql

#

well of course you have it there

serene thicket Apr 10, 2019, 7:19 AM

#

id assume so

#

so i had a look at that link, and my current query effectively does that already

#

the issue with my current query, is that it can only handle a single record at a time, not so much that it doesnt handle deduping well. Because its not technically deduplicating per say, it is actually just preventing the data from being duplicated in the first place

#

but i like something you mentioned soosleek, and you might be onto something. I could make a stored procedure, and rather than run my insert from the script, i run a query that calls my stored procedure

#

I can do a lot more then

unkempt sable Apr 10, 2019, 8:47 AM

#

If let's say I have a list of classes and subclasses:

Class 1: <-text->
Subclass 1a: <-text->
Subclass 1b: <-text->
...
Class 2: <-text->
Subclass 2a: <-text->
...
Class 15: <-text->

and then I have products that have multiple subclasses, so Product A might be under Subclass 2c, Subclass 4d and Subclass 15f. How should I go about storing the classes and subclasses?

hazy peak Apr 10, 2019, 9:57 AM

#

Hello there. I am creating "followers" table with "id(PK), user_id(FK), follower_id(FK), is_following(boolean)" columns. 2 FK are from the same user table "id(PK), username, email" both using same PK. Can you tell me if I correctly set it up?

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    followers = db.relationship("Followers", backref="followers", lazy=True)

class Followers(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
    follower_id = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
    is_following = db.Column(db.Boolean, unique=False, default=True)

hazy peak Apr 10, 2019, 11:20 AM

#

Figured it out. Added relationships to the Followers class.

class Followers(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
    follower_id = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
    is_following = db.Column(db.Boolean, unique=False, default=True)
    user = db.relationship("User", foreign_keys=[user_id])
    follower = db.relationship("User", foreign_keys=[follower_id])

ruby tangle Apr 10, 2019, 12:05 PM

#

anyone using https://python-gino.readthedocs.io/en/latest/ , where do i set login parameters

ionic pecan Apr 10, 2019, 4:45 PM

#

i‘d guess in the set_bind here: http://gino.fantix.pro/en/latest/tutorial.html

#

as part of the connection string

dawn pulsar Apr 10, 2019, 6:58 PM

#

If I have select access to 2 tables in a database, is there any exploit which will give me other privileges?

#

Or access to the other tables?

subtle flax Apr 10, 2019, 7:10 PM

#

I need to define a returning insert rule in postgres: ```sql
-- messages_with_author is a view for messages
CREATE RULE messages_with_author_ins AS ON INSERT TO messages_with_author
DO INSTEAD
INSERT INTO messages (
id,
channel_id,
author_id,
content
) VALUES (
NEW.id,
NEW.channel_id,
NEW._author_id,
NEW.content
) RETURNING (
SELECT
msg.id,
msg.edit_id,
msg.channel_id,
msg.content,
msg.pinned,

  usr.id AS _author_id,
  usr.name AS _author_name,
  usr.bot AS _author_bot
FROM existing_messages msg
INNER JOIN users usr
ON msg.author_id = usr.id);``` It gives error: `subquery must return only one column` because `RETURNING` doesn't want to accept select .

How could I make this work?

#

Seems like it can be solved by doing several commands in DO INSTEAD: INSERT; SELECT

quasi dew Apr 10, 2019, 7:32 PM

#

hi, I'm tryin to make a sqlite3 db with my xml file. I used beautiful soup and created my tables. I printed the xml data but I can not put them into my db. where do I do wrong?

from bs4 import BeautifulSoup
import sqlite3 

class Emre(object):

    def __init__(self):
        self.process_soup()
        self.create_connection()
        self.create_table()

    def create_connection(self):
        self.conn = sqlite3.connect("emree.db")
        self.curr = self.conn.cursor()

    def create_table(self):
        self.curr.execute("""DROP TABLE IF EXISTS kelimeler_tb""")
        self.curr.execute("""create table kelimeler_tb(
                        rzeczownik text,
                        przymiotnik text, 
                        czasownik text
                        )""")
    def process_soup(self):

        infile = open("random.xml","r", encoding='utf-8')
        contents = infile.read()
        soup = BeautifulSoup(contents,'xml')
        for item in set(soup.findAll('string')[1:]):
            print(item.text)
        return item.text

    def store_db(self, item):
        self.curr.execute("""INSERT INTO kelimeler_tb VALUES (?, ?, ?)"""(
            item.text['rzeczownik'][0]
            ))
        self.conn.commit()
        self.conn.close()

wind pelican Apr 10, 2019, 7:50 PM

#

i see two potential issues.
you for item in set, but then you return only the last items text
then in store db, you use item.text, but your process_soup returns a single items text.
you prob want to build a list of values in process_soup thats just a simple block of data
eg

[ ['a','b','c'], ['g','h','r'] ]```
then loop over that list in store_db and insert each item in to your db

#

def afun():
    for x in [1,2,3]:
        pass
    return x

this returns the last value of x, which would be 3

quasi dew Apr 10, 2019, 7:58 PM

#

I am confused, you are guessing right I am trying to do that. my item.text is only words so I want to grab each of them to my db so I am already using a loop at process_soup

#

@wind pelican

wind pelican Apr 10, 2019, 7:58 PM

#

you are looping to print each item, but you are not doing anything else with the data

#

you likely want to extract the actual information you want in that loop and store it in a more simple form in a list

#

data = []
for item in set ...:
    print(item.text)
    item_data = item.text.split() # or whatever you need to do to turn item in to a list with 3 entries
    data.append(item_data)
return data```

#

then if you set item_data to a list of the form ['a','b','c']
you can just do this in store_db:

def store_db(self, data):
    for item in data:
        self.curr.execute("INSERT INTO kelimeler_tb VALUES (?, ?, ?)", item)
    # rest of your code here

subtle flax Apr 10, 2019, 8:03 PM

#

RETURNING
    messages.*,
    (SELECT
      usr.id AS _author_id,
      usr.name AS _author_name,
      usr.bot AS _author_bot
    FROM users usr WHERE messages.author_id = usr.id)``` Is there a way around `subquery must return only one column` error here?
I tried following the `RETURNING` example from <https://www.postgresql.org/docs/10/rules-update.html>, but in my case it does not work

#

I tried adding LIMIT 1 or using hardcoded values in WHERE condition (user.id is unique), but nothing solves this

wind pelican Apr 10, 2019, 8:05 PM

#

well a column is the stuff like

      usr.id AS _author_id,
      usr.name AS _author_name,
      usr.bot AS _author_bot

LIMIT 1 changes the number of rows you get

subtle flax Apr 10, 2019, 8:05 PM

#

oh

#

but how I can unpack them?

quasi dew Apr 10, 2019, 8:10 PM

#

def process_soup(self):
            

        infile = open("ann_words2.xml","r", encoding='utf-8')
        contents = infile.read()
        soup = BeautifulSoup(contents,'xml')

#
        data = []
        for item in set(soup.findAll('string')[1:]):

            
            print(item.text)
            item_data = item.text.split() # or whatever you need to do to turn item.text in to a list with 3 entries
                    data.append(item_data)
        return data

sorry but I'm having an indent error when I write data = []
I could not figure it out how to solve it

#

@wind pelican

wind pelican Apr 10, 2019, 8:15 PM

#

you have a # randomly in there, it might be ending your function early causing the rest of the indented code to be misinterpreted

#

@subtle flax i dont know a ton about that particular sql syntax, but if it requires that you only return one column, you might need to either have multiple returning statements, one for each column. or find a different sql method to fill in your values

quasi dew Apr 10, 2019, 8:17 PM

#

📎 nede.png

#

I can not see the indent problem here, I even put it on the top but its still giving me the problem @wind pelican

wind pelican Apr 10, 2019, 8:27 PM

#

if you copy pasted out of discord you likely have a mix of spaces and tabs, youll need to make sure the whole file is either using spaces for indent or tabs for indent

quasi dew Apr 10, 2019, 8:31 PM

#

ah sorry, I fixed it and ran it. but my db folder is still empty

#

from bs4 import BeautifulSoup
import sqlite3 

class Emre(object):

    def __init__(self):
        self.process_soup()
        self.create_connection()
        self.create_table()

    def create_connection(self):
        self.conn = sqlite3.connect("emree.db")
        self.curr = self.conn.cursor()

    def create_table(self):
        self.curr.execute("""DROP TABLE IF EXISTS kelimeler_tb""")
        self.curr.execute("""create table kelimeler_tb(
                        rzeczownik text,
                        przymiotnik text, 
                        czasownik text
                        )""")
    def process_soup(self):

        infile = open("words.xml","r", encoding='utf-8')
        contents = infile.read()
        soup = BeautifulSoup(contents,'xml')
        data = []

        for data in set(soup.findAll('string')[1:]):
            print(data.text)
            item_data = data.text.split() # or whatever you need to do to turn item.text
        return data

    def store_db(self, item):
        for item in data:

            self.curr.execute("""INSERT INTO kelimeler_tb VALUES (?)"""(
                item.text['rzeczownik'][0]
                ))
            self.conn.commit()
            self.conn.close()
        return

@wind pelican

wind pelican Apr 10, 2019, 8:33 PM

#

is it possible that its just putting it in a different folder? you did not actually specific an absolute path to put it in. you just selected a file name. it will likely end up in whatever your 'current directory' is when python runs

quasi dew Apr 10, 2019, 8:34 PM

#

I do not think it is, cause I changed the folder name and I created a new db folder but it is still empty. I can see the name of the tables though

#

@wind pelican

wind pelican Apr 10, 2019, 8:36 PM

#

you did not append anything to the data list

#

also in your store_db function you are commiting and closing the database in the for loop, rather than after it

#

oh ALSO your store_db parameter is still item, your for loop expects it to be data

#

you also didnt update the sql query line to use item itself. take another peek at how i formatted that line

#

the syntax for execute is execute(query, sequence_of_values)
if your item variable contains a list like ['a','b','c'] then you can pass three values in to your sql query
you seem to have tried to switch it over to passing a single item but your table has three columns, so you need to pass it a list or tuple with three items in it and keep values as VALUES (?, ?, ?)"

#

take a step back and think about how you want your program to work.
if beautifulsoup is an xml file, and you want its data in a db, you need to transform it in to a format that you can pass to sqlite.
if each entry in the xml file has 3 values, you need to create an object in python representing all three. a list or tuple can do that.
sqlites execute statement allows you to pass a list or tuple in so that works perfectly.
BUT since you have multple entries in your xml file, you need to make sure the program is operating on all of them
each entry has three values, so you prob want a list that contains a bunch of lists
the list you create in process_soup takes the xml and generates this list of lists which should contain each entry from your xml file
store_db should take that list and insert each entry in to your database

#

now i am just kind of assuming what your xml file looks like, so if its not a series of entries with three values each, then you might need a different plan

#

i think you are creating the database, but just not putting any data in it. you wouldnt see any table names or anything if it had not created it

quasi dew Apr 10, 2019, 8:49 PM

#

yes thats why I'm confused cause I have no plan how to separate them. I just want to see them on my db so I'm trying to add them all in one

wind pelican Apr 10, 2019, 8:50 PM

#

well lets look at your xml file, maybe using it as a direct example will help

quasi dew Apr 10, 2019, 8:59 PM

#

I want to create an amateur dictionary so I need the every word separately but for now I can put them all in one

#

I put an append code but I got an error
AttributeError: 'list' object has no attribute 'parent'

def process_soup(self):

        infile = open("ann_words2.xml","r", encoding='utf-8')
        contents = infile.read()
        soup = BeautifulSoup(contents,'xml')
        data = []

        for data in set(soup.findAll('string')[1:]):
            print(data.text)
            item_data = data.text.split() # or whatever you need to do to turn item.text
            data.append(item_data)
        return data

#

@wind pelican

wind pelican Apr 10, 2019, 9:03 PM

#

woops we used the same variable name twice there. you have data =[] and also for data in set

#

we wanted those to be two different variables

#

the list data is where we are storing the entries from the xml file
the variable the loop uses should be the current entry in the xml file

#

so tbh soup isnt really the best way to read an xml file like this.
xml files are very regular and precise, you can basically navigate them like a directory.
you can use something like https://docs.python.org/3/library/xml.etree.elementtree.html#module-xml.etree.ElementTree to precisely extract just the values you want.

#

though overall most of your issues are coming from the regular python syntax, exactly what data needs to be pulled out could be figured out later

quasi dew Apr 10, 2019, 9:14 PM

#

now I'm having a a type error
TypeError: store_db() missing 1 required positional argument: 'liste'

 def process_soup(self):

        infile = open("ann_words2.xml","r", encoding='utf-8')
        contents = infile.read()
        soup = BeautifulSoup(contents,'xml')
        liste = []

        for data in set(soup.findAll('string')[1:]):
            print(data.text)
            item_data = data.text.split() # or whatever you need to do to turn item.text
            liste.append(item_data)
        return liste

    def store_db(self, liste):
        for item in liste:

            self.curr.execute("""INSERT INTO kelimeler_tb VALUES (?)"""(
                liste.text['rzeczownik'][0]
                ))
            self.conn.commit()
            self.conn.close()
        return

wind pelican Apr 10, 2019, 9:14 PM

#

you need to pass the returned value from process_soup to store_db when you call store_db

#

this is an issue with whatever is calling this function in your class

#

also a different issue, you need update your store_db function with those tips i gave earlier

#

here is the example i gave, updated to use your variable names

def store_db(self, liste):
    for item in liste:
        self.curr.execute("INSERT INTO kelimeler_tb VALUES (?, ?, ?)", item)
    self.conn.commit()
    self.conn.close()

quasi dew Apr 10, 2019, 9:19 PM

#

I could not understand the answers that u gave me

#

I'm kinda tired and need to sleep I guess cause I have not slept today. I'll be back tomorrow and I will try to understand more. thanks for your help @wind pelican

inner pecan Apr 10, 2019, 9:52 PM

#

Can anyone help me -

📎 unknown.png

#

The above is my table - Each row here is an 'error' where there is a discrepancy between l1 and l2 value for different fields of a 'Symbol'. From this I want to make a table with only two columns - Symbol | Error
so it would be: (ignore the ErrorCount column it is unneccesary i now realise)

VFMF 2
UTL 1
SVTG 1
...
PQSV 4

#

(basically how many times it shows up in the original table... VFMF. appears twice, PQSV appears 4)

#

how can I do this?

#

(sqlite3)

#

(I know I could make a new table with python variables, but can I instead make the table directly from this table with some kind of SQL statements?)

#

Would SELECT SYMBOL, COUNT(SYMBOL) do this, then how to get rid of duplicates?

gleaming frost Apr 10, 2019, 10:10 PM

#

@fickle saddle probs delete this message aswell

inner pecan Apr 10, 2019, 10:28 PM

#

you can ignore my question.. i found out how to do it with group by statement

bold cliff Apr 11, 2019, 1:00 AM

#

Does anyone use Emby and use python to query their DB|?

toxic rune Apr 11, 2019, 1:00 AM

#

A question, how can I set up my PSQL instance on a server to allow a remote connection from ONLY my home IP? (Basically all guides weren't helpful.)

torn sphinx Apr 11, 2019, 1:11 AM

#

@toxic rune did you read https://stackoverflow.com/questions/11753296/configure-postgresql-to-work-for-only-localhost-or-specified-ip-port

Stack Overflow

Configure PostgreSQL to work for only LOCALHOST or specified ip + port

I want to configure PostgreSQL to accept connections only from a specified IP. It should not accept requests from any other IP.

toxic rune Apr 11, 2019, 1:12 AM

#

Oh, haven't found that post somehow

#

I'll give that a try

torn sphinx Apr 11, 2019, 1:12 AM

#

You can technically filter all connections except from localhost too if you're lazy

#

But that's the professional™ way

#

Or whatever home ip is

toxic rune Apr 11, 2019, 1:12 AM

#

Professional = lazy?

#

Should go that way then heh

torn sphinx Apr 11, 2019, 1:12 AM

#

No no 😂 I meant the one I linked js professional

#

Is

toxic rune Apr 11, 2019, 1:13 AM

#

lol I'll give that one a try, hope it works

torn sphinx Apr 11, 2019, 1:14 AM

#

The other way haters would be like no that's bad because what if <insert unlikely scenario that will never happen and its the same as a guy pointing a gun at your head asking for dB access> happens what then huh

#

I never even used postgresql lol

#

Might use a fork of it sometime

toxic rune Apr 11, 2019, 1:21 AM

#

Still doesn't work, hmm I get connection timed out

torn sphinx Apr 11, 2019, 7:57 AM

#

LOL wait

#

Your PCs ip

#

Is it the same as localhost

#

Try https://blog.bigbinary.com/2016/01/23/configure-postgresql-to-allow-remote-connection.html

BigBinary Blog

Configure PostgreSQL to allow remote connection | BigBinary Blog

Sometimes we need PostgreSQL to allow remote connection. This blog describes how to get that done.

#

Why is postgresql so weird at allowing ips

ionic pecan Apr 11, 2019, 4:52 PM

#

it really isn‘t, host-based auth is very useful for security

onyx seal Apr 11, 2019, 9:08 PM

#

hi, small question about the mysql connector

#

as i know, the connector will convert mysql data types to python data types, so a DATETIME column is a datetime.datetime object

#

for boolean columns, does that convert it to True and False or something else? can't find any docs about it and just want to be sure that this is correct before doing a conditional with is True if it won't be true.

wind pelican Apr 11, 2019, 10:21 PM

#

looks like mysql uses 0 and 1 for true and false, so you should prob use == True or just if x: syntax so that python will check if its a true like value instead of specifically if its the exact True 'constant' object

covert cedar Apr 12, 2019, 12:32 PM

#

Hello guys, i'm stuck at database on Django and "twig language". So i'm looking for help.. TY very much

terse stump Apr 12, 2019, 1:03 PM

#

you should try to ask a more in depth question @covert cedar

#

!ask

delicate fieldBOT Apr 12, 2019, 1:03 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.

terse stump Apr 12, 2019, 1:04 PM

#

what are you stuck on, what have you tried, what does the relevant code look like. and so on

covert cedar Apr 12, 2019, 1:04 PM

#

oh ok

terse stump Apr 12, 2019, 1:04 PM

#

what do you want it to do, what are you trying to do

#

so we do not get into a XY Problem

covert cedar Apr 12, 2019, 1:04 PM

#

So my problem is, I have in my html <tr> with data inside. I want when I enter new data to load a new row under the previous one.
Right now, the data update on the same row so the previous data just get replaced by the new one.

#

And I want my row with data inside to be saved

#

I dont know what im supposed to do ...

#

This is my row after I entered my data (isin and place)

📎 Capture_decran_2019-04-06_a_10.27.36.png

#

this is my view.py

📎 Capture_decran_2019-04-06_a_10.22.59.png

#

and in my html

📎 Capture_decran_2019-04-06_a_10.35.52.png

subtle flax Apr 12, 2019, 2:04 PM

#

Is something like select * from (insert into my_table values('whatever') returning *); not possible in postgres?

pure scroll Apr 12, 2019, 2:21 PM

#

something like this should work:

with inserted_rows as ( insert into my_table values (...))
select * from inserted_rows;

subtle flax Apr 12, 2019, 2:45 PM

#

@pure scroll I can't use WITH statement unfortunantely because it happens in insert rule, NEW can't be passed into WITH

vapid ferry Apr 12, 2019, 4:38 PM

#

hey guys any ideas on how to get the current insert id in mysql, similar to LAST_INSERT_ID() but for the current insert operation, can't find any info

#

or if its even possible..

smoky kiln Apr 12, 2019, 10:28 PM

#

Hey
does anyone know how to setup automatic db failover on Django
So lets say for example I had two db hosts proxy1.db.com and proxy2.db.com that connect to the same MySQL clusters. But proxy1.db.com was to fail - how can I set it so it will start using proxy2.db.com?

hazy mango Apr 12, 2019, 10:29 PM

#

Possibly use try/except?

smoky kiln Apr 12, 2019, 10:29 PM

#

its not like that

severe iris Apr 12, 2019, 10:56 PM

#

So I was using SQLite3 for a database, but someone recommended me mySQL, which I was trying to use. The programs don't install, I have tried a bunch of different solutions for my problems which I found on the internet, but none could help me. Does anyone know any other way of visualizing your database without using mySQL?

#

I will try PostgreSQL

smoky kiln Apr 12, 2019, 11:11 PM

#

check out pythonanywhere.com with the free plan you can access your own MySQL database

split owl Apr 12, 2019, 11:27 PM

#

hey guys

#

does JSON belong here?

#

or somewhere else?

#

because i need a ton of help with some JSON stuff

#

so

#

my question

#

this is my JSOn file:

{
        "users": {
                "@split owl": [
                        "My favorite video game of all time is Pokemon Platinum.",
                        "My favorite cuisine is South Indian. Second is Mexican."
                ],
                "@timid flame": [
                        "I swam competitively for 16 years."
                ],
                "@trail lark": [
                        "My mom and I share a birthday, October 28th."
                ]

#

this is my code:

#turning the facts.json file into a dictionary
with open("facts.json") as f3:
    facts = json.load(f3)
    #storing the dictionary values
    users = facts["users"]

#

and this is the error:

Traceback (most recent call last):
  File "main.py", line 34, in <module>
    facts = json.load(f3)
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\json\__init__.py", line 296, in load
    parse_constant=parse_constant, object_pairs_hook=object_pairs_hook, **kw)
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\json\__init__.py", line 348, in loads
    return _default_decoder.decode(s)
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\json\decoder.py", line 337, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\json\decoder.py", line 353, in raw_decode
    obj, end = self.scan_once(s, idx)
json.decoder.JSONDecodeError: Expecting ',' delimiter: line 12 column 18 (char 502)

#

so my question is

#

do you see anything wrong there that I did?

#

and if you DO see something

#

what is it?

#

and how do i fix it?

split owl Apr 13, 2019, 12:26 AM

#

somebody?

#

anybody?

vivid cave Apr 13, 2019, 1:26 AM

#

reading

#

@split owl did you forget to close the { ? you opened 2 but closed none

split owl Apr 13, 2019, 1:29 AM

#

wow

vivid cave Apr 13, 2019, 1:30 AM

#

meep

split owl Apr 13, 2019, 1:34 AM

#

@vivid cave that was it lol

#

but

#

now i need actual help

vivid cave Apr 13, 2019, 1:34 AM

#

sup

split owl Apr 13, 2019, 1:35 AM

#

same json file

#

but

#

this is my code:

    try:
        facts["users"][author].append(fact)
    except KeyError:
        facts["users"][author] = []
        facts["users"][author].append(fact)

    with open("facts.json", "w") as f:
        json.dump(facts, f, indent=4)

#

author is the author of a message

#

in this case, a key

#

the error comes at the json.dump line

#

am i doing something wrong there?

vivid cave Apr 13, 2019, 1:36 AM

#

hmm, im not familiar with the json module for python... what error does it give you ?

split owl Apr 13, 2019, 1:37 AM

#

  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\ext\commands\core.py", line 63, in wrapped
    ret = await coro(*args, **kwargs)
  File "main.py", line 260, in addfact
    json.dump(facts, f, indent=4)
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\json\__init__.py", line 179, in dump
    for chunk in iterable:
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\json\encoder.py", line 431, in _iterencode
    yield from _iterencode_dict(o, _current_indent_level)
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\json\encoder.py", line 405, in _iterencode_dict
    yield from chunks
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\json\encoder.py", line 376, in _iterencode_dict
    raise TypeError(f'keys must be str, int, float, bool or None, '
TypeError: keys must be str, int, float, bool or None, not Member

#

that last line is the one that matters

vivid cave Apr 13, 2019, 1:39 AM

#

sec

#

idk what keys it's referring to. the documentation uses skipkeys, and sort_keys, which are booleans

split owl Apr 13, 2019, 1:46 AM

#

what

#

im confused

#

whatwhatwhat

#

@vivid cave could u explain?

vivid cave Apr 13, 2019, 1:48 AM

#

there's a type error, idk what your parameters mean. can you explain to me what facts is, and f ?

split owl Apr 13, 2019, 2:13 AM

#

@vivid cave this is the whole function:

@bot.command()
async def addfact(ctx, *args):
    """
    Author can add a fact about themself to the list.
    """
    #author of the message
    author = ctx.author

    fact = ""
    for i in args:
        fact += i + " "

    #adds the fact to the list
    try:
        facts["users"][author].append(fact)
    except KeyError:
        facts["users"][author] = []
        facts["users"][author].append(fact)

    with open("facts.json", "w") as f:
        json.dump(facts, f, indent=4)
    
    await ctx.send("Added!")

facts is a dictionary made with facts.json

vivid cave Apr 13, 2019, 2:16 AM

#

ok gimmie a sec

split owl Apr 13, 2019, 2:19 AM

#

sure

vivid cave Apr 13, 2019, 2:20 AM

#

@split owl can you try doing
json.dump(str(facts), f, indent=4)
i think str(facts) will fix that part, but idk if f and indent=4 will raise another error

split owl Apr 13, 2019, 2:21 AM

#

um hm

#

ok...

#

Traceback (most recent call last):
  File "main.py", line 34, in <module>
    facts = json.load(f3)
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\json\__init__.py", line 296, in load
    parse_constant=parse_constant, object_pairs_hook=object_pairs_hook, **kw)
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\json\__init__.py", line 348, in loads
    return _default_decoder.decode(s)
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\json\decoder.py", line 337, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\json\decoder.py", line 353, in raw_decode
    obj, end = self.scan_once(s, idx)

#

it didnt work

vivid cave Apr 13, 2019, 2:25 AM

#

ok, undo the part i said then. comment out json.dump and do, print(type(facts)) and print(type(f)), just to see what types they are

split owl Apr 13, 2019, 2:35 AM

#

oh ok

split owl Apr 13, 2019, 3:02 AM

#

@vivid cave it's not letting me, cuz of that same error

vivid cave Apr 13, 2019, 3:03 AM

#

the same one as the most recent error?

split owl Apr 13, 2019, 3:03 AM

#

yee

vivid cave Apr 13, 2019, 3:04 AM

#

can you open your json file and check that it's all correct

split owl Apr 13, 2019, 3:04 AM

#

goddamn it

#

why

#

oh goodie, it passed

#

now, lemme see if it prints

vivid cave Apr 13, 2019, 3:05 AM

#

alright

#

was it an issue with the json file ?

split owl Apr 13, 2019, 3:05 AM

#

<class 'dict'>
<class '_io.TextIOWrapper'>

#

the top if facts

#

and the bottom is f

vivid cave Apr 13, 2019, 3:06 AM

#

alright thx

#

try 1 last thing, remove indent=4 and run it and see what you get. if that doesnt work then sry im out of ideas. i havent used the json module before but im reading the documentation and looking at examples

split owl Apr 13, 2019, 3:17 AM

#

hm ok

#

with str(facts)

#

or just facts

#

?

vivid cave Apr 13, 2019, 3:18 AM

#

just facts

split owl Apr 13, 2019, 3:34 AM

#

@vivid cave

Ignoring exception in command addfact:
Traceback (most recent call last):
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\ext\commands\core.py", line 63, in wrapped
    ret = await coro(*args, **kwargs)
  File "main.py", line 260, in addfact
    json.dump(facts, f)
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\json\__init__.py", line 179, in dump
    for chunk in iterable:
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\json\encoder.py", line 431, in _iterencode
    yield from _iterencode_dict(o, _current_indent_level)
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\json\encoder.py", line 405, in _iterencode_dict
    yield from chunks
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\json\encoder.py", line 376, in _iterencode_dict
    raise TypeError(f'keys must be str, int, float, bool or None, '
TypeError: keys must be str, int, float, bool or None, not Member

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

Traceback (most recent call last):
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\ext\commands\bot.py", line 860, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\ext\commands\core.py", line 698, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\harba\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\ext\commands\core.py", line 72, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: keys must be str, int, float, bool or None, not Member

#

idk what's wrong this time!

#

it's so confusing

vivid cave Apr 13, 2019, 3:45 AM

#

youre getting a type error, something is being passed on as a Member but idk what is because facts is fine, it's a dict, f is fine, but idk

dull scarab Apr 13, 2019, 4:50 AM

#

How much data is 20MB in a postgres system?

proper copper Apr 13, 2019, 4:51 AM

#

20mb?

dull scarab Apr 13, 2019, 4:52 AM

#

mhm, was just looking at https://www.elephantsql.com/plans.html and wonder how much the free service actually is

Database plans and pricing | ElephantSQL

proper copper Apr 13, 2019, 4:54 AM

#

Looks pricy, are you looking for a cloud provider?

dull scarab Apr 13, 2019, 4:54 AM

#

I was just looking at the free option

proper copper Apr 13, 2019, 4:54 AM

#

I see

#

but 20mb is 20mb

dull scarab Apr 13, 2019, 4:54 AM

#

Seems to be ~100,000- 200,000 simple rows

#

It's not for anything advanced. Just curious if it will support my development process

proper copper Apr 13, 2019, 4:55 AM

#

Don't see why not

dull scarab Apr 13, 2019, 4:55 AM

#

Might as well just try it ¯_(ツ)_/¯

dull scarab Apr 13, 2019, 5:35 AM

#

Looking back at my previous use of asyncpg I've been executing and fetching directly of a pool. What's the difference between acquiring a connection from the pool manually with py async with pool.aquire() as con: ... vs just executing on the pool?

#

Just more control, or is there an underlying benefit to either

dull scarab Apr 13, 2019, 7:09 AM

#

excuse me 👀 py async with pool.aquire() as conn: AttributeError: 'Pool' object has no attribute 'aquire'

dull scarab Apr 13, 2019, 7:40 AM

#

Ok, I have no idea why it keeps saying it's an attribute

#

(i saw the typo, but still found it weird its saying it's an attribute 🤔 )

gilded narwhal Apr 13, 2019, 7:48 AM

#

methods are just callable attributes

#

hence why you can look them up using object.[name]

#

>>> class Test:
    def some_method():
        pass

    
>>> t = Test()
>>> t.some_method
<bound method Test.some_method of <__main__.Test object at 0x000001E2BCEE26D8>>
>>> t.some_method = 2
>>> t.some_method
2
>>>

#

you can even assign a new object to their identifiers like any other attribute

dull scarab Apr 13, 2019, 8:04 AM

#

I.. totally knew that

#

I mean, ... It must be the lack of morning coffee

#

🤷🏽

unkempt sable Apr 13, 2019, 9:34 AM

#

are you using asyncpg with a GUI by any chance floppy? I’m looking to see if I should use that or just go with psycopg2/pyside2’s qsql stuff

dull scarab Apr 13, 2019, 9:34 AM

#

pgadmin is what i use normally

#

unless you mean my own dbms

unkempt sable Apr 13, 2019, 9:35 AM

#

I’m going to build an inventory/stock system + reports thingy, hence I’m asking

#

just in general, the last time I asked the question nobody had any input

dull scarab Apr 13, 2019, 9:35 AM

#

Depends if your gui system is asyncronous or not

unkempt sable Apr 13, 2019, 9:35 AM

#

Should be using Pyside2

dull scarab Apr 13, 2019, 9:36 AM

#

if it's async asyncpg should be find i'd imagine

#

not too familiar with it tbh

unkempt sable Apr 13, 2019, 9:47 AM

#

I might be overthinking/confusing things, but would the way to use asyncpg and pyside2 be to have the gui in a main thread, then spawn another thread to maintain a pool with asyncpg? I think I’m a bit confused

dull scarab Apr 13, 2019, 9:53 AM

#

I don't like to mix the thinking of threads with async

#

If it's async, keep it on the same thread

#

else stick to threads

#

so asyncpg might not be what you want GWqlabsSweats

torn sphinx Apr 13, 2019, 3:45 PM

#

conn = sqlite3.connect("database.db")
c = conn.cursor()```
```sqlite3.OperationalError: unable to open database file```
created the database with DB Browser (SQLite)

hazy mango Apr 13, 2019, 3:56 PM

#

Is it currently opened?

torn sphinx Apr 13, 2019, 3:56 PM

#

nope

hazy mango Apr 13, 2019, 3:56 PM

#

Which of the two lines raise the error?

torn sphinx Apr 13, 2019, 3:56 PM

#

first

hazy mango Apr 13, 2019, 3:57 PM

#

Is the database saved in the same folder?

torn sphinx Apr 13, 2019, 4:06 PM

#

yes

#

but i give full path

hazy mango Apr 13, 2019, 4:06 PM

#

Show full code @torn sphinx

torn sphinx Apr 13, 2019, 4:06 PM

#

thats the full code

#

just the import is missing

hazy mango Apr 13, 2019, 4:07 PM

#

How are you importing?

torn sphinx Apr 13, 2019, 4:07 PM

#

import sqlite3

hazy mango Apr 13, 2019, 4:07 PM

#

Try googling the error

#

See what comes up

torn sphinx Apr 13, 2019, 4:07 PM

#

did already

swift raft Apr 13, 2019, 4:08 PM

#

have you tried deleting the .db and then running the code?

#

because then it'll work

torn sphinx Apr 13, 2019, 4:09 PM

#

does not work

#

also renamed to .sqlite3

swift raft Apr 13, 2019, 4:12 PM

#

wdym

#

import sqlite3

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

#

did you do this?

torn sphinx Apr 13, 2019, 4:14 PM

#

oh

#

now it works

#

but it does not find my json now

#

now it works thanks

swift raft Apr 13, 2019, 4:19 PM

#

ok what's the json problem

dry obsidian Apr 13, 2019, 4:42 PM

#

is the shelve module a good database system? I hardly ever see people use it, what are advantages and disadvantages of it compared to something like a python sql wrapper?

harsh osprey Apr 13, 2019, 10:54 PM

#

can someone give me some links to good saving and loading to/from external JSON file? i can save to it once before fuckin it up

dry obsidian Apr 13, 2019, 11:18 PM

#

you can try the docs

#

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

#

but also

#

loading:

with open("file.json", "r") as f:
    data = json.load(f) # Loads json into data

data
with open("file.json", "w") as f:
    json.dump(data, f) # Saves data (usually dict/list) to file

# Loading, mutating, and saving
data = [{"a": 1}, 2, 3]
# Save data
with open("file.json", "w") as f:
    json.dump(data, f)
# Reset and load data for examples sake
data = None
with open("file.json", "r") as f:
    data = json.load(f)
data[0]["a"] = 4
# data == [{"a": 4}, 2, 3]
# Resaving with modification
with open("file.json", "w") as f:
    json.dump(data, f)

#

@harsh osprey

#

the important thing is that json is not really a database

harsh osprey Apr 13, 2019, 11:23 PM

#

oh?

dry obsidian Apr 13, 2019, 11:23 PM

#

if you dump something new, it overrides the old stuff

harsh osprey Apr 13, 2019, 11:24 PM

#

yea figured that

dry obsidian Apr 13, 2019, 11:24 PM

#

okay

harsh osprey Apr 13, 2019, 11:24 PM

#

so

#

what can i use as a db?

dry obsidian Apr 13, 2019, 11:24 PM

#

well i didnt say json is bad

#

just remember that to add data to a json file, you first have to load it, append to the loaded data, and then resave

harsh osprey Apr 13, 2019, 11:24 PM

#

ok

dry obsidian Apr 13, 2019, 11:24 PM

#

that said

#

shelve looks very interesting

#

though i have never used it personally

harsh osprey Apr 13, 2019, 11:25 PM

#

hmm. ill look into it.

#

thanks

dry obsidian Apr 13, 2019, 11:25 PM

#

its a bundled library, and very native to python

#

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

proper copper Apr 14, 2019, 12:17 AM

#

If the foreign key is referencing a BigInt id, should the foreign key column also be BigInt?

subtle flax Apr 14, 2019, 11:23 AM

#

I'm storing user cookies in redis using uuid as key and json as value. There is a user_id field
What is the best way of getting cookies of spicific user (by id) (deleting all of them to be more specific)

I found 2 possible ways, but none of them seem good enough

Storing a set of user cookies at user_cookies:{user_id}:
It would be hard to track expired cookies and delete them from this set, it would accumulate garbage over time
(at least I'm not sure how to implement it properly)
Iterating over all cookies in database, decoding each of them and getting user id. If it matches, delete:
Huge amount of computation

subtle flax Apr 14, 2019, 11:59 AM

#

I could probably add a script that executes on every user login and checks all keys in a set. Is this a way of solving it?

subtle flax Apr 14, 2019, 2:58 PM

#

I ended up writing this sript: ```lua
local tUserKey = "user_cookies:" .. KEYS[1]
local tCookies = redis.call("SMEMBERS", tUserKey)
local tExpired = {}

for tKey, tValue in pairs(tCookies) do
if redis.call("EXISTS", "AIOHTTP_SESSION_" .. tValue) == 0 then
table.insert(tExpired, tValue)
end
end

-- check if table is empty
if next(tExpired) == nil then
return 0
end

return redis.call("SREM", tUserKey, unpack(tExpired))```

ruby tangle Apr 14, 2019, 3:03 PM

#

anyone know how to propery run sqlalchemy with aiohttp web server

ionic pecan Apr 14, 2019, 4:38 PM

#

what do you mean with "run"

vital yarrow Apr 14, 2019, 4:49 PM

#

I have this table structure for my sqlite db

                duration integer,
                is_buy_order integer,
                issued text,
                location_id integer,
                min_volume integer,
                order_id integer primary key unique,
                price real,
                range text,
                system_id integer,
                type_id integer,
                volume_remain integer,
                volume_total integer
               )```
And this is my insert statement:
```python
for order in results:
    table_name = 'orders'
    attrib_names = ", ".join(order.keys())
    attrib_values = ", ".join("?" * len(order.keys()))
    sql = f"INSERT INTO {table_name} ({attrib_names}) VALUES ({attrib_values})"
    c.execute(sql, list(order.values()))
conn.commit()
conn.close()```
I am wondering how to do an update/replace if exists on my primary key though. 
If an order ID already exists I want to update all the other values for that row, or delete the existing one and replace with the new row.

#

Do I simply use REPLACE INTO instead of INSERT INTO?

pseudo pollen Apr 14, 2019, 4:59 PM

#

https://hastebin.com/ikezohufuk.sql

#

ERROR: Error 1215: Cannot add foreign key constraint
SQL Code:
        -- -----------------------------------------------------
        -- Table `Bot`.`mod_actions`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `Bot`.`mod_actions` (
          `unique_id` BIGINT(21) NOT NULL AUTO_INCREMENT,
          `guild_id` BIGINT(21) NOT NULL,
          `action` VARCHAR(100) NULL,
          `target_user_id` BIGINT(21) NULL,
          `moderator_id` BIGINT(21) NULL,
          `reason` VARCHAR(2000) NULL,
          INDEX `guild_id_idx` (`guild_id` ASC),
          PRIMARY KEY (`unique_id`),
          CONSTRAINT `guild_id`
            FOREIGN KEY (`guild_id`)
            REFERENCES `Bot`.`guild_settings` (`guild_id`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB

SQL script execution finished: statements: 7 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

#

Not sure what the error is.

verbal laurel Apr 14, 2019, 6:13 PM

#

what would be the most optimal way to update a postgres db (asyncpg) with a dict's contents? i doubt looping through the dict's items and executing on every loop is a good idea

#

basically ```py
for k, v in some_dict.items():
await pool.execute(
"""
UPDATE table
SET column = $1
WHERE othercolumn = $2
""",
v, k
)

#

this should happen every 15 or so seconds

#

(also, if it matters, the dict changes constantly)

severe iris Apr 14, 2019, 7:49 PM

#

Hi guys! I downloaded postgreSQL and I have pgAdmin 4, too.
Whenever I try to create a "Server" on the pgAdmin 4, I get this:

FATAL: password authentication failed for user "postgres"

severe iris Apr 14, 2019, 8:07 PM

#

I'm going to try SQLite 3 instead.

torn sphinx Apr 14, 2019, 8:09 PM

#

you use pgadmin to connect to databases

ionic pecan Apr 14, 2019, 8:16 PM

#

@severe iris IIRC the postgres user is a superuser and doesn't have a password set by default and you can't connect to it via remote

#

I recommend playing around with the CLI before diving into pgadmin what is your server hosted on?

vital yarrow Apr 14, 2019, 8:28 PM

#

Hey guys, I am having real trouble with timestamps in sqlite3.
I have a default column that puts in the current timestamp when a record is created, I want to create a DELETE statement that deletes any records older than one hour.
I have tried this:

hour_ago = datetime.datetime.now() - datetime.timedelta(minutes=60)
delete = f"DELETE from orders WHERE last_seen < {hour_ago}"
c.execute(delete)```
But I keep getting this error, or a variation of it with everything I try. 
```Traceback (most recent call last):
  File "scripts/query.py", line 14, in <module>
    c.execute(delete)
sqlite3.OperationalError: near "20": syntax error```
My timestamp saved in the DB is in this format `'2019-04-14 20:17:36'`

ionic pecan Apr 14, 2019, 8:48 PM

#

you should use parameter substitution instead of this

#

hour_ago = datetime.datetime.now() - datetime.timedelta(minutes=60)
query = "DELETE from orders WHERE last_seen < ?"
c.execute(query, (hour_ago,))

vital yarrow Apr 14, 2019, 8:59 PM

#

Thanks Volcy that isnt throwing any errors now so I assume it is working.
Appreciate the help.

ionic pecan Apr 14, 2019, 9:40 PM

#

👍🏻

distant hull Apr 14, 2019, 10:17 PM

#

I'm having a weird problem with mysql python. I have an execute command that supposed to update some values in a database, but it ends up doing nothing. It does not produce any errors.

#

fetcher.execute("UPDATE rivens_1 SET avg = %s, median = %s; WHERE item_Type = %s AND compatibility = %s AND rerolled = %s;", (str(row.get("avg", "")) + "(Stable at " + str(avgres) + ")", str(row.get("median", "")) + "(Stable at " + str(medianres) + ")", row.get("itemType", "none"),str(row.get("compatibility", "")), str(row.get("rerolled", ""))), multi=True)
                        mydb.commit()
                        print(row.get("itemType", " ") + " " + row.get("compatibility", " ") + " " + str(avgres))

distant hull Apr 14, 2019, 11:55 PM

#

It worked by removing the multi = True, gonna have to read why, but im happy that I found it

buoyant breach Apr 15, 2019, 7:20 AM

#

👍

fringe tiger Apr 15, 2019, 2:31 PM

#

I'm still confused on how would I aproach my problem

#

I have a bot that will save user points (for each message) to sqllite database. I've got it working but I'm not sure if it's a recommended practice.

#

For each message I:
open the connection
execute update statement
commit
close

#

Is it recommended to open/close for EACH database modification? I could have hundreds of them

pure scroll Apr 15, 2019, 2:52 PM

#

you can have a single connection or even a connection pool open always and only do commit/rollback at the end of each transaction

fringe tiger Apr 15, 2019, 2:52 PM

#

Yea I had that idea

#

But what if my program doesn't close the connection? Example crash?

#

Database will remain locked no? And when restarting the program it won't be able to open

pure scroll Apr 15, 2019, 2:56 PM

#

you delegate that to the database. That means it depends on the database you would be using. But most of the times they can realise when connection has died and would rollback or commit pending transaction themselves

fringe tiger Apr 15, 2019, 3:01 PM

#

Ah I see

#

Thanks

soft saffron Apr 15, 2019, 4:34 PM

#

What error does sqlite3 raise if you try to SELECT data WHERE id doesnt exist

patent glen Apr 15, 2019, 4:43 PM

#

it should just return an empty data set

#

@earnest radish you never followed up on your question, did you figure it out?

earnest radish Apr 15, 2019, 4:44 PM

#

I'm just trying it out right now

#

are you familiar with joining tables, filtering, and group by stuff?

patent glen Apr 15, 2019, 4:44 PM

#

yeah

earnest radish Apr 15, 2019, 4:44 PM

#

i can DM you if you are and willing to help

patent glen Apr 15, 2019, 4:44 PM

#

we prefer to keep stuff in the channels, not DM

earnest radish Apr 15, 2019, 4:44 PM

#

okok

#

📎 unknown.png

#

Here's my SQL table data

soft saffron Apr 15, 2019, 4:45 PM

#

What does an empty data set look like

earnest radish Apr 15, 2019, 4:45 PM

#

I need to filter write a SQL query to
determine the number of unique patients dispensed each drug as an outpatient, as well as the total quantity
dispensed to these patients, in CY2017 and CY2018. And I need to report results by drug name and by year.

#

This is what I have so far:

#

📎 unknown.png

#

Joining three tables, filtering patient_status = outpatient and dates containing 2018/2017

patent glen Apr 15, 2019, 4:48 PM

#

sum(disp.quantity), right?

earnest radish Apr 15, 2019, 4:49 PM

#

i think, I don't have the data to look at to be sure

patent glen Apr 15, 2019, 4:49 PM

#

and you'll need to figure out how to slice up date_dispensed to give you the year as a value, and then include that and d.name (is there a d.id?) as group by columns

earnest radish Apr 15, 2019, 4:50 PM

#

okay I'll try that

#

there's drug name and ndc for identifying which drug

patent glen Apr 15, 2019, 4:50 PM

#

you might have to group by both in some sql dialects if you want both in the results

#

if they're unique 1:1 it shouldn't matter

#

on that note i need to go for a while, i'll be on later if you have any problems

earnest radish Apr 15, 2019, 4:58 PM

#

gotcha

#

thanks for your help, i'll DM ya

soft saffron Apr 15, 2019, 5:08 PM

#

If I try to update a row that doesn't exist in sqlite, what happens

cold plume Apr 15, 2019, 5:09 PM

#

how can i see my database and what i wrote to it

#

cause my db rn is an empty file

soft saffron Apr 15, 2019, 5:09 PM

#

Depends on what type of db it is

cold plume Apr 15, 2019, 5:09 PM

#

sqlite

#

but im new to databases so i dont know how i can see my sqlite database itself

soft saffron Apr 15, 2019, 5:10 PM

#

You have to download a viewer

#

Theres no way to "just see it"

ionic pecan Apr 15, 2019, 5:11 PM

#

@soft saffron nothing will happen

soft saffron Apr 15, 2019, 5:11 PM

#

No error, just nothing?

ionic pecan Apr 15, 2019, 5:11 PM

#

you update rows matching a filter and if none match then shrug

soft saffron Apr 15, 2019, 5:12 PM

#

Well then

#

ok thanks

#

What happens if I try to insert into a duplicate primary key

ionic pecan Apr 15, 2019, 5:16 PM

#

in a sane database, you get a constraint violation error

soft saffron Apr 15, 2019, 5:18 PM

#

And in sqlite you get...?

#

Their tutorials don't help... 😢

patent glen Apr 15, 2019, 5:35 PM

#

@soft saffron while the issue was that values should have been where, you shouldn't be using f-strings to create sql statements anyway

soft saffron Apr 15, 2019, 5:35 PM

#

Why?

patent glen Apr 15, 2019, 5:35 PM

#

because it creates sql injection vulnerabilities

#

it's much safer to use parameters e.g.

soft saffron Apr 15, 2019, 5:35 PM

#

But using .format doesn't?

patent glen Apr 15, 2019, 5:35 PM

#

don't do that either

#

cur.execute('update table set column = ? where id = ?', (value, id))```

soft saffron Apr 15, 2019, 5:36 PM

#

Oh ok

#

And they're always in order?

patent glen Apr 15, 2019, 5:36 PM

#

or in your case you could probably have done column = column + 1 without even a parameter, just doing the addition within the sql

#

yeah

#

you can also pass a dict and do them by name i think

soft saffron Apr 15, 2019, 5:37 PM

#

Ok

#

What does that actually do?

#

Using params

patent glen Apr 15, 2019, 5:41 PM

#

it makes the database driver take care of everything so it won't cause any problems even if your value has quotes in it etc

#

e.g. if you have f"update users set lastname = '{name}' where..." and name is "O'Brien" it's a syntax error. And if it's "';drop table users--" you have a bigger problem.

soft saffron Apr 15, 2019, 5:43 PM

#

ValueError: parameters are of unsupported type

#

I know what SQL injections are

#

I was just kinda ignoring them rn tbh

#

When I tried to change it I got that

patent glen Apr 15, 2019, 5:43 PM

#

well, what is your statement now

soft saffron Apr 15, 2019, 5:44 PM

#

Its not a tuple

patent glen Apr 15, 2019, 5:44 PM

#

ok you need to show me your code so i can figure out the problem

soft saffron Apr 15, 2019, 5:44 PM

#

I figured it out

#

Its not a tuple, I was just using an int

static kindle Apr 15, 2019, 7:41 PM

#

I am using a MySQL DB and I have this line at this top of the file:


import mysql.connector

I need to escape a string before inserting it, but most of my Googling shows me how to do that when using MySQLdb

#

man, I do not understand how to format things properly yoj

#

import mysql.connector

#

if I use this code:

#

MySQL.escape_string(value)

#

I get this error:

#

NameError: name 'MySQL' is not defined

#

but if I use this instead:

#

conn.escape_string(value)

#

then I get this:

#

AttributeError: 'CMySQLConnection' object has no attribute 'escape_string'

#

any suggestions?

severe iris Apr 15, 2019, 10:38 PM

#

Good luck with MySQL

#

If your project isn't that big or "important", I would recommend using something like SQLite3

#

I've tried MySQL, PostgreSQL, and another one I forgot the name, and all of them had some kind of issue, all but SQLite.

plain radish Apr 15, 2019, 10:40 PM

#

That's a bit of a strange recommendation. Pithink

#

SQLite has it's strengths as a highly portable file based db, but it falls short in performance and features compared to proper sql servers, especially for cases where concurrency is important. I'm unsure what troubles you've come across, but unless you can confirm for sure the issue you encountered is relevant to this issue and that the issue is either a bug or purposeful limitation of the sql server, then I'd prefer it if you'd avoid discouraging others from using it as there's no constructive basis otherwise.

river barn Apr 16, 2019, 7:43 AM

#

INSERT INTO users
    (id, name, description, date) 
    VALUES 
    ($1, $2, $3, $4);```
In Postgresql, using asyncpg, can this be exploited/dangerous?

gilded narwhal Apr 16, 2019, 8:17 AM

#

keep in mind that sqlite also has something called 'WAL' mode that addresses a lot of concurrency issues
https://www.sqlite.org/wal.html

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

#

it has to be manually enabled using PRAGMA journal_mode=WAL; either during the creation of the table, or, i think, as just a plain old query at any other time.

plain radish Apr 16, 2019, 8:19 AM

#

sure, but concurrent write performance isn't resolved

gilded narwhal Apr 16, 2019, 8:21 AM

#

well i haven't done any benchmarks on it so idk about speed if that's the issue, but so far i haven't noticed any hangups when doing concurrent writes across multiple processes using wal mode

#

it seems to happen pretty much instantly/in a nonblocking fashion behind the scenes after a commit

#

in any case it solves a lot of problems ive seen people encounter with locking errors and sqlite

rancid root Apr 16, 2019, 3:44 PM

#

anyone with experience using python/kafka here?

delicate fieldBOT Apr 16, 2019, 4:44 PM

#

ask

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

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

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

ionic pecan Apr 16, 2019, 4:56 PM

#

@river barn doesn‘t look like anything wrong. what did you have in mind?

river barn Apr 16, 2019, 4:56 PM

#

I don't really know what are the best ways to prevent sql injection

ionic pecan Apr 16, 2019, 5:03 PM

#

using parametrized queries goes a long way

river barn Apr 16, 2019, 5:13 PM

#

parametrized queries? GWqlabsGarThink

ionic pecan Apr 16, 2019, 5:18 PM

#

what you did, with $1, ...

torn sphinx Apr 16, 2019, 8:02 PM

#

Okay so I use sqlite and I've been using it for a while now with no problems on my discord bot. But I'm trying to use it for a program I'm making but when I try to get WHERE it looks up columns instead of rows like it normally does

cs.execute(f"SELECT * FROM contacts WHERE name={recipient}")```
```    cs.execute(f"SELECT * FROM contacts WHERE name={recipient}")
sqlite3.OperationalError: no such column: Jared```

#

does anyone know why it's trying to search columns with WHERE instead of rows

unreal tartan Apr 16, 2019, 8:04 PM

#

You didn't put quotes around the recipient. So it assumes it's a column like name is a column.

torn sphinx Apr 16, 2019, 8:05 PM

#

ah okay

#

figured it was something small i missed

ionic pecan Apr 16, 2019, 9:03 PM

#

FWIW you should really be using parametrized queries instead of string interpolation

dull scarab Apr 17, 2019, 7:26 AM

#

What is the prefer way to insert something into a postgres db, or if it already exists return the row?
Do i query first for the row and just return it if it exists, else continue to an insert statement, or is there a inset into ... on conflict error clause I can catch or make it return the instance on insert if it exists?

#

Kind of want to avoid having 2 queries for one job kind of deal

woeful ivy Apr 17, 2019, 1:31 PM

#

asyncpg im guessing?

#

@dull scarab

#

or just general pg

dull scarab Apr 17, 2019, 1:52 PM

#

asyncpg in this case yes

woeful ivy Apr 17, 2019, 1:56 PM

#

asyncpg should raise a UniqueViolationError if it exists. so you could catch that.

#

guessing you want the thing you inserted returned as well?

try:
    record = db.fetchrow("INSERT INTO ... VALUES ... RETURNING *")
except asyncpg.UniqueViolationError:
   record = db.fetchrow("SELECT * FROM ... WHERE ... = ...")

#

something to that effect might be what you want.

dull scarab Apr 17, 2019, 1:59 PM

#

It would still do the query then i'm assuming

#

So might as well just fetchrow first, if it's empty insert?

woeful ivy Apr 17, 2019, 2:08 PM

#

Yeah that'd work as well

dull scarab Apr 17, 2019, 2:09 PM

#

Yeah, I'm doing that but was wondering if there was a smarter way to just query the db once

woeful ivy Apr 17, 2019, 2:09 PM

#

only way i could think of doing it would be an ON CONSTRAINT

#

record = await db.fetchrow("INSERT INTO ... (...) VALUES ... ON CONSTRAINT DO NOTHING; SELECT * FROM ... WHERE ... = ...;")

kind of thing might work

#

not sure how asyncpg deals with 2 queries in one call like that.

#

you could also do like a ON CONSTRAINT (PK_col_name) DO UPDATE SET and set the PK to itself then do RETURNING *
but that would look disgusting, and there's no way that's ever reccomended.

dull scarab Apr 17, 2019, 2:14 PM

#

idk, i think ill just keep it as is

#

Thanks though

soft saffron Apr 17, 2019, 3:29 PM

#

When using a postgres database, what goes in this index?

#

"database": "DATABSE"

soft saffron Apr 17, 2019, 4:01 PM

#

nada?

young pier Apr 17, 2019, 4:33 PM

#

I have a DB that initially will have 1 column in the table which the value of every row will be 0, then will change one row at a time to 1. How do I grab JUST the first row where column is 0?

river barn Apr 17, 2019, 4:35 PM

#

SELECT ... FROM ... WHERE column=0;

#

You might want to learn sql

soft saffron Apr 17, 2019, 7:05 PM

#

For an inventory system, whats the best way to go about storing this?

#

Have a table for inventory one column for id, and then 30 columns with numbers for each slot which corresponds to an id in another table with all the items?

ionic pecan Apr 17, 2019, 7:39 PM

#

do all entries have a fixed amount of slots?

soft saffron Apr 17, 2019, 7:51 PM

#

A total fixed amount

#

Like 30 or so

dapper terrace Apr 17, 2019, 8:32 PM

#

is there a quick way to improve UNION performance in a redshift db?
i'm used to mysql where there's drastic improvement by creating an index on the tables being joined but i don't think redshift supports indexes

torn sphinx Apr 17, 2019, 10:35 PM

#

if you dont care about duplicates, UNION ALL should go faster

#

UNION in redshift has a DISTINCT by default i believe

#

@dapper terrace

dapper terrace Apr 17, 2019, 11:11 PM

#

Thanks. But I was using it for the distinct functionality

#

Is there another way to shave off some more time?

torn sphinx Apr 18, 2019, 1:24 AM

#

not sure, in my experience union has been pretty slow on redshift

cloud cipher Apr 18, 2019, 9:45 AM

#

Hey

#

Man

#

I am building a website using Django and I got to the point where I want to add a database to my site

#

How can I know which database will be the most suitable for my site

near cradle Apr 18, 2019, 11:18 AM

#

django supports sqllite, postgres, mysql and oracle.

#

if you can live with sqllite, it's by far the easiest alternative, but also the slowest and least scalable, since it's just a flat file.

#

if not, just do yourself a favor and choose postgres.

#

I think you'll be hard pressed to find anyone here who prefers oracle or mysql over postgres.

#

but honestly any one of those three will probably suit your purposes just fine. they're tried and tested workhorse databases.

cloud cipher Apr 18, 2019, 11:20 AM

#

Ok

#

One more question if you dont mind

#

Lets say I go with postgres will we be able to share data between the data bases

near cradle Apr 18, 2019, 11:22 AM

#

technically you could, but it's not worth the hassle. if mysql is already being used in your project, stick with that. a homogenous system is convenient.

#

projects should strive to have as few databases as absolutely possible in order to prevent ending up with duplicate data that needs to be synced and stuff like that

#

it's a huge timesink and very hard to get out of once you start doing it.

#

lots of huge companies have that problem

cloud cipher Apr 18, 2019, 11:25 AM

#

So should I like make another My sql database

near cradle Apr 18, 2019, 11:26 AM

#

actually I'm gonna need to stop here because making private maplestory servers is a breach of TOS and we can't help you with that.

#

good luck with your project.

#

!rule 5

delicate fieldBOT Apr 18, 2019, 11:26 AM

#

Rules

5. We will not help you with anything that might break a law or the terms of service of any other community, site, service, or otherwise - No piracy, brute-forcing, captcha circumvention, sneaker bots, or anything else of that nature.

cloud cipher Apr 18, 2019, 11:26 AM

#

I see

polar osprey Apr 18, 2019, 12:37 PM

#

Hey guys, I thought I'd try here as I'm kinda stuck.

I need to save the username of a discord user to a MySQL database (I need to display the username on a website therefore the ID doesn't work). Unfortunately the username contains this: 𝓤𝓷𝓴𝓷𝓸𝔀𝓷

I've tried a lot of things now and the closest I've gotten is with this:

mydb = mysql.connector.connect(
                host = db_host,
                user = db_user,
                passwd = db_pw,
                database = db_name,
                )
                print("user_name:       {}".format(member.display_name))
                my_cursor = mydb.cursor(prepared=True)
                querry = "INSERT INTO Users (join_date, user_id, user_name, discord) VALUES (%s,%s,%s,%s) ON DUPLICATE KEY UPDATE user_name=%s, discord=%s"
                args = member.joined_at, member.id, member.display_name, str(member), member.display_name, str(member)
                my_cursor.execute(querry,args)
                mydb.commit()

Expected result:
user_name: 𝓤𝓷𝓴𝓷𝓸𝔀𝓷 test
[('𝓤𝓷𝓴𝓷𝓸𝔀𝓷 test',)]

Acutal result:
user_name: 𝓤𝓷𝓴𝓷𝓸𝔀𝓷 test
[('???????????????????????????? test',)]

Checking the database with phpMyAdmin shows "???????????????????????????? test" as the username. Trying to manually enter via phpMyAdmin gives "Warning: #1300 invalid utf8-String" (which is weired because in the Database I use utf8mb4_general_ci as collation.

I tried changing the collation to utf16 and utf32, same result.
I tried to add this to my code: # -*- coding: utf-8 -*-
and this:

cursor.execute('SET NAMES utf8mb4')
cursor.execute("SET CHARACTER SET utf8mb4")
cursor.execute("SET character_set_connection=utf8mb4")

this site says it is valid utf 8 input: https://onlineutf8tools.com/validate-utf8

How can I get that stupid username in my database (and why the fuck do people have to use those fucked up symbols)

Validate UTF8 - Online UTF8 Tools

World's simplest online utility that validates UTF8 data. Free, quick, and powerful. Import UTF8 – validate UTF8.

knotty parcel Apr 18, 2019, 3:29 PM

#

How do I add a new column to an existing table using python?

#

^ sqlite 3

knotty parcel Apr 18, 2019, 3:52 PM

#

@carmine heart

carmine heart Apr 18, 2019, 4:17 PM

#

I've never user sqlite3 before, so I can't help you there

idle gate Apr 18, 2019, 4:30 PM

#

hey guys, i have tried to do alot of research for this specific problem but i cant seem to find anything so i go ahead and ask you guys:

Is it possible to make a search from 1 table in a mysql database to several diffrent tables in another mysql database?

unborn sentinel Apr 18, 2019, 4:35 PM

#

I feel like it's possible but I don't know the finer points of how

lilac mirage Apr 18, 2019, 5:39 PM

#

Hey everyone. I've learned a bit on databases but I haven't put it into practice yet. I'm making an inventory system for a Discord bot. Would the best way to handle this be storing the master item list in one table and everyone's inventory in a many to many table with userID, itemID, and count?

#

I just want to make sure my planning is appropriate and I won't end up having to redo everything in the future

ionic pecan Apr 18, 2019, 5:53 PM

#

yes, that sounds solid

lilac mirage Apr 18, 2019, 5:56 PM

#

Awesome thank you.