#databases

restive violet Jun 22, 2018, 8:12 AM

#

through hashing

#

very important!!!

hollow tiger Jun 22, 2018, 8:13 AM

#

that's true

restive violet Jun 22, 2018, 8:13 AM

#

it's O(1) to get an item

#

in a list it's O(n)

hollow tiger Jun 22, 2018, 8:14 AM

#

O(n)?

#

what does that mean?

restive violet Jun 22, 2018, 8:14 AM

#

big o notation

#

it means you scale n times with however big input list size is to find result

#

so len 5 is 5 times going through

#

len 50 is 50 times.. etc

#

because you have to iterate

#

https://en.wikipedia.org/wiki/Hash_table @hollow tiger it's called a hash table (or rly map with a dict) outside of python

Hash table

In computing, a hash table (hash map) is a data structure that implements an associative array abstract data type, a structure that can map keys to values. A hash table uses a hash function to compute an index into an array of buckets or slots, from which the desired value ca...

#

sorry if i went too much, but hashing is extremely important concept in CS/interviews/efficiency. 😃

hollow tiger Jun 22, 2018, 8:26 AM

#

sry i was doing smth

hollow tiger Jun 22, 2018, 9:15 AM

#

can i store a dictionary into a column for a table? :o

dull scarab Jun 22, 2018, 9:20 AM

#

Not that I'm aware of, but you could break it up into

dict name/ id | key | value```

hollow tiger Jun 22, 2018, 9:22 AM

#

uh

#

cuz i'm doing a dict in a dict

#

and i have to have the dict in the table

#

would json.loads() and json.dumps()ing everything work?

#

not recommended rite

dull scarab Jun 22, 2018, 9:24 AM

#

will the structure always look the same?

hollow tiger Jun 22, 2018, 9:24 AM

#

no

dull scarab Jun 22, 2018, 9:24 AM

#

"Testing": {
    "apps": {},
    "prefix": [
        "/"
    ],
    "members": {}
}```

hollow tiger Jun 22, 2018, 9:24 AM

#

well that's the base structure

#

so i have "testing" as a table and "apps", "prefix", and "members" as columns

dull scarab Jun 22, 2018, 9:25 AM

#

will the apps dict have more dicts?

hollow tiger Jun 22, 2018, 9:25 AM

#

yes

#

dicts in dicts

#

and lists

dull scarab Jun 22, 2018, 9:27 AM

#

Well, I guess you could create a table with those keys and parse jsonstring to it

#

I can't tell you if it's bad or good unfortunately

hollow tiger Jun 22, 2018, 9:27 AM

#

yea so that's why I was kinda hesitant in choosing sql

#

i'm not sure about hte best way to store the data

dull scarab Jun 22, 2018, 9:28 AM

#

There's probably some relational setup you could do to make it work, but I don't know too much about em 🤷🏾

hollow tiger Jun 22, 2018, 9:28 AM

#

i'm really inexperienced with databases

#

i only use a big json file

#

:(

dull scarab Jun 22, 2018, 9:28 AM

#

Like have a dict and a list table

#

And have them reference rows within the same table if their nesting dicts/lists

#

and a 3rd table which is just your app name having references to dicts/lists/values

#

But again, I don't know this stuff :P

hollow tiger Jun 22, 2018, 9:30 AM

#

that's

#

so hard to manage lmao

#

should i just do multiple json files in a folder?

#

emulate a db

#

instead of one big json file

ionic pecan Jun 22, 2018, 9:43 AM

#

Postgres has a JSONB column type which supports storing JSON

#

There‘s also NoSQL databases for storing it directly

hollow tiger Jun 22, 2018, 9:45 AM

#

ppl told me not to use nosql

ionic pecan Jun 22, 2018, 9:46 AM

#

It really depends on what you store in that members dict.

#

Is it a mapping of user ID -> some values that don‘t change?

midnight verge Jun 22, 2018, 9:47 AM

#

@hollow tiger if your data is structured, there is no reason to use NoSQL

hollow tiger Jun 22, 2018, 9:47 AM

#

well

#

members could mean py "members": { "TheDutchSwagBoi": {}, "Many_Roblox2": {}, "gagi12": {} }

#

but sometimes within those can be multiple dicts

#

            "Voidlegend": {
                "moderator": [
                    "Voidlegend#5150 and JohnApocalypse",
                    "13",
                    "USA and UTC 05:00",
                    "Yes, and it will help because I won't have to be taught as many commands",
                    "By ridding it of hackers and jerks",
                    "I work hard and  won't give up"
                ]
            },```

#

sometimes there's multiple items within that dict tho

midnight verge Jun 22, 2018, 9:49 AM

#

try to figure out a relational structure

hollow tiger Jun 22, 2018, 9:50 AM

#

uh

#

whats that?

pallid schooner Jun 22, 2018, 2:51 PM

#

@fair laurel I have in mind to store user data to a db

fair laurel Jun 22, 2018, 2:52 PM

#

Well, that is what relational databases do

pallid schooner Jun 22, 2018, 2:52 PM

#

Hmm

#

Can you suggest a tutorial

fair laurel Jun 22, 2018, 2:54 PM

#

This one uses sqlite: https://www.pythoncentral.io/introduction-to-sqlite-in-python/

Python Central

Andres Torres

Introduction to SQLite in Python | Python Central

A look at Python's SQLite module. We show you how to create tables, perform selects, and update rows. Also how to delete (drop) a table.

#

Do you have any experience with SQL yet?

pallid schooner Jun 22, 2018, 3:15 PM

#

@fair laurel Yes I do, thanks btw

tacit dagger Jun 22, 2018, 4:47 PM

#

hey, how can i select the same row in two different fields in sqlite?

fair laurel Jun 22, 2018, 4:52 PM

#

What do you mean?

#

Selecting two columns from the same row?

tacit dagger Jun 22, 2018, 4:52 PM

#

yes

fair laurel Jun 22, 2018, 4:53 PM

#

SELECT Column1, Column2
FROM MyTable
WHERE MyCondition = True

tacit dagger Jun 22, 2018, 4:54 PM

#

👍🏿

#

for MyCondition part i should write WHERE rowID = ?

#

not just 1 row, multiple rows

fair laurel Jun 22, 2018, 4:57 PM

#

If you have multiple rows, you could do WHERE rowID IN ("1", "2", "3")

tacit dagger Jun 22, 2018, 5:01 PM

#

how about when there are many rows like 1000, i can't write all of them like this: ("1", "2", "3")

#

and i want to select them seperately not all at once

patent glen Jun 22, 2018, 5:24 PM

#

then use a loop

#

i'm not sure why separately vs all at once (or in batches of 100 or whatever amount works) matters though

tacit dagger Jun 22, 2018, 5:26 PM

#

can you check this out? maybe this is better explained

#

https://stackoverflow.com/questions/48756708/how-to-fetch-one-row-and-display-it-on-textbox-and-increment-it-automatically

Stack Overflow

How to fetch one row and display it on textbox and increment it au...

i wrote this python 3 program, i want to for each row in field Esperanto, get it's corresponding row in the other field in database named English. for example: when i click on row 1 in listbox, i w...

patent glen Jun 22, 2018, 5:27 PM

#

i'm confused

#

where are multiple rows coming in

#

your question description implies you want one row

#

and WHERE rowID = ? works fine for that

#

what did you mean by "not just 1 row, multiple rows"

#

and I don't understand what you mean by increment it automatically at all - what are you incrementing

tacit dagger Jun 22, 2018, 5:29 PM

#

i mean how should i tell sqlite to do this, when row 1 in field 1 is clicked, row 1 from field 2 being fetched

patent glen Jun 22, 2018, 5:29 PM

#

and honestly if you want to select the corresponding english word for a given esperanto word, it probably makes more sense to select by the esperanto word instead of rowid

#

ok what does this have to do with multiple row

#

if each query is for just one row

#

look i can explain how to do what it looks like you're trying to do, but your actual questions are all over the place with stuff like "not just 1 row, multiple row" and "increment it automatically" that don't seem to have anything to do with that

tacit dagger Jun 22, 2018, 5:31 PM

#

oh i didn't know i could fetch english column according to Esperanto word. how should i do that?

#

i've read many tutorials but couldn't figure it out

patent glen Jun 22, 2018, 5:31 PM

#

"SELECT English FROM Words WHERE Esperanto = ?" - and you have to get the word from the tkinter listbox somehow

#

what does self.listbox.curselection return

tacit dagger Jun 22, 2018, 5:32 PM

#

what it returns is that it should write the English word which is fetched and write it in textbox

patent glen Jun 22, 2018, 5:38 PM

#

Try something like this: python def enter_meaning(self, tag): for index in self.listbox.curselection(): esperanto = self.listbox.get(index) results = self.cur.execute("SELECT English FROM Words WHERE Esperanto = ?", (esperanto)) for row in results: self.textbox.insert(tk.END, row)

#

(I wouldn't trust the rowid to match the index anyway, tbh)

tacit dagger Jun 22, 2018, 5:39 PM

#

it works 😄

patent glen Jun 22, 2018, 5:39 PM

#

[though might be better to check len(self.listbox.curselection()) == 1 etc, since this won't actually do anything useful with multiple items]

tacit dagger Jun 22, 2018, 5:41 PM

#

when someone else for example runs the app on their pc, is it garanteed that the rows come in this order? or i should order them to ensure that?

patent glen Jun 22, 2018, 5:41 PM

#

you should probably avoid caring about the order at all

#

or do you mean for having the list sorted alphabetically?

tacit dagger Jun 22, 2018, 5:42 PM

#

now the list is alphabetically ordered, cuz i wrote alphabetically

#

in db

patent glen Jun 22, 2018, 5:42 PM

#

I don't think it's guaranteed to stay stable, you should add an order by clause to your select esperanto query

#

i mean, if you never make any further changes to this db file it won't matter but

#

it's just good practice, if you had a real database with rows being deleted and inserted all the time you'd need it

tacit dagger Jun 22, 2018, 5:44 PM

#

no i won't make changes to db

patent glen Jun 22, 2018, 5:44 PM

#

in general it's not good practice to rely on the order of rows from a query with no order clause

tacit dagger Jun 22, 2018, 5:44 PM

#

i wrote this self.cur.execute('SELECT Esperanto FROM Words ORDER BY rowID') now it is ordered 😃

patent glen Jun 22, 2018, 5:44 PM

#

...lol

tacit dagger Jun 22, 2018, 5:44 PM

#

no :/

patent glen Jun 22, 2018, 5:45 PM

#

i think it'll always be ordered by rowid, for sqlite, but again if you were using this database for real there's no guarantee that the rowid would stay related to the order you want

#

honestly i'd just order by esperanto

tacit dagger Jun 22, 2018, 5:45 PM

#

oh. i got it.

#

thanks a bunch. i've been stuck for God knows how many years xD

orchid charm Jun 24, 2018, 3:46 AM

#

How to make SQLite not Give a Tuple in a fetchall

#

Like, all parts of it are ('SomeValue',),

#

How to make it not do that

torn sphinx Jun 24, 2018, 3:50 AM

#

you could do fetchone

#

or fetchrow

#

it depends on the situation

orchid charm Jun 24, 2018, 3:52 AM

#

while Give in Item:

Item is a List Fetchall

#

But everything in it comes in this format
('SomeValue',)

#

@torn sphinx So what do you think could fix this Issue, cause i have no idea. I am trying to make it Turn Give into something else if it is in Item

torn sphinx Jun 24, 2018, 3:56 AM

#

I have no idea what your code is

#

No idea what your row looks like

#

No idea what you are doing

#

How do you expect me to help?

#

Oh, I see. You want to iterate over a tuple ('SomeValue', )

#

you can do that

#

>>> tup = (5,)
>>> for i in tup:
    print(i)

5

#

look up python tuples

orchid charm Jun 24, 2018, 3:58 AM

#

Yeah.
If Give is Grizzco Employee
And a item in Item is ('Grizzco Employee',)
They are not equal

#

Which means this won't work

torn sphinx Jun 24, 2018, 3:58 AM

#

oh

#

It's a tuple

#

I said look up python tples

#

they are nearly* identical to lists (immutable whilst lists are mutable)

#

Also, again

#

send all code

orchid charm Jun 24, 2018, 4:00 AM

#

What else am i not showing you

#

The Await is just a Message the bot sends if it can't find the users ID in the Database

torn sphinx Jun 24, 2018, 4:01 AM

#

What does your database look like? (e.g. is it ```
id | name | something_else |

5 | hi | some |

#

and what is your command

orchid charm Jun 24, 2018, 4:02 AM

#

ID | Item
ctx.message.author.id | Thing "Command" Gave the User

torn sphinx Jun 24, 2018, 4:02 AM

#

I can't help if I don't know what you are doing btw

#

What's your SQL statement

#

And paste all your code in a codeblock

orchid charm Jun 24, 2018, 4:02 AM

#

Its just a RNG Shop

#

But i am trying to make it not give the user the same item multiple times

#

Also, What do you mean by SQL Statement

#

All SQL stuff that goes with the command is in the command

torn sphinx Jun 24, 2018, 4:05 AM

#

So give me the command

#

I can't help until I see it

orchid charm Jun 24, 2018, 4:05 AM

#

.metro vending-machine

torn sphinx Jun 24, 2018, 4:05 AM

#

besides this is databases not #303906096458891264 if this is for a bot command

#

no

#

the code

orchid charm Jun 24, 2018, 4:05 AM

#

I showed you it?

torn sphinx Jun 24, 2018, 4:05 AM

#

no your didn't

#

yougave snippets

orchid charm Jun 24, 2018, 4:06 AM

#

I thought i already gave you the link, anyways here it is
https://hastebin.com/pacacivumu.py

#

Also, this is a Database Related Problem. It just happens to be in Discord.py This would not change any Database stuff anyways

orchid charm Jun 24, 2018, 4:29 AM

#

@torn sphinx So why does the Fetchall Give Tuples in the first place

torn sphinx Jun 24, 2018, 4:54 AM

#

because you can't modify the data

#

is immutable

#

tuples are immutable

#

this is basic python

torn sphinx Jun 24, 2018, 6:38 PM

#

hello guys

#

is there a way

#

to delete only a certain field in my table? I'm using sqlite syntax with python module

#

I have like a countdown program sending alerts each X hours until deadline is reached for a certain project name

#

and once the reminder message has been sent to the user i'd like to delete this alert in my line

#

so only this alert

#

which query would correspond to my needs?

#

@torn sphinx

torn sphinx Jun 24, 2018, 7:36 PM

#

Im not sure but you can add another column to show if the job is done preferably a boolean value. Then you can delete it with where

#

it's like

#

imagine having

#

id | name | something_else |
----------------------------
5  | hi   | some           |```

#

like DELETE FROM users WHERE mail_sent = 1

#

and wanting to delete only some

#

see what imean

#

but keeping the id and name

#

Yep i do add a fourth column change the respecive column after you do your job

#

then alter the column you wanna change

#

or just change the column you wanna change would be way simpler

#

UPDATE x SET something_else = 'lol'

#

of course add a where according to your needs

#

I'd probably do the 4 column thingie because i like having neat stuff

#

oh pretty good

#

like when the alert is sent

#

change the datetime object in the column by a 0

#

for examplre

#

to say it's done

#

UPDATE users SET something_else = 0 WHERE alert_available = 0 would be my way

#

But you can also go with UPDATE users SET something_else = 0 WHERE user_id = variable

#

and put execution after the alert function

broken linden Jun 24, 2018, 7:43 PM

#

Deleting only single fields in tables is not the point of tables

#

You either delete a row update a field or add a row

#

Or get a row/field

torn sphinx Jun 24, 2018, 7:44 PM

#

He is updating a field

#

Turns datetime into 0

#

But id really prefer using a tag for active/alert available column

broken linden Jun 24, 2018, 7:45 PM

#

to update a row do
UPDATE table_name SET myfiield = 0 WHERE awesome_id=35545;

cosmic gulch Jun 27, 2018, 11:21 PM

#

Hi im getting started with mongoDB

#

by defualt you have a db

#

test

#

and i inserted some keys and values

#

but how would i see them

#

using python

#

like make a get request to the db?

#

if I dont make sense

#

i apologize

viral crag Jun 27, 2018, 11:29 PM

#

You need a mongodb library

#

Eg pymongo

cosmic gulch Jun 29, 2018, 12:41 AM

#

gg

#

i Have questions about MongoDb but im pretty sure if i ask them people will get annoyed

#

😄

vestal apex Jun 29, 2018, 12:53 AM

#

that's an odd thing to be sure about

steady loom Jun 29, 2018, 3:52 AM

#

Hey there

#

I also asked in #help-coconut earlier

#

Oof internet connection issues

#

Anyway

#

I'm trying to set up a Google firebase database

#

Except I can't read the database

#

I've made sure that I have the perms, and can write and everything

#

But for some reason, reading won't work

#

I'm on mobile atm, but will post code etc later

#

Just asking in case this is a common problem

orchid charm Jun 29, 2018, 5:22 AM

#

In SQLite how to get the newest item added?

#

NVM Figured it out

#

        cur.execute('SELECT SplatNet2 FROM Splatoon2')
        Query = cur.fetchall()
        C = len(Query) - 1

steady loom Jun 29, 2018, 7:55 AM

#

re google firebase - i managed to thoroughly search google and found my answer

patent glen Jun 29, 2018, 3:24 PM

#

@orchid charm I don't think sqlite guarantees that rows come back in insertion order, you should include a timestamp column and order by it if you need it.

ornate abyss Jun 29, 2018, 3:50 PM

#

Or order by primary key descending and limit 1

patent glen Jun 29, 2018, 6:38 PM

#

sure, that'd work if there's a primary key that's increased with each new row

orchid charm Jun 29, 2018, 10:13 PM

#

https://hastebin.com/ibosepagur.py

#

Ok this is a d.py rewrite question

#

and a Database question

#

This is not adding the information to the database when it needs to

#

And it is not sending to multiple channels like it is supposed to

#

It is only sending to the first one

#

Actually it just decided to send to the second one, but it took like a minute or so to do. and i figured out some of my problem

#

I accidentally put the asnycio.sleep in the wrong place

#

But it still is not updating the database

#

Wait NVM it just worked too?

ionic pecan Jun 29, 2018, 11:31 PM

#

i have no idea what you mean with your question. not adding the information to the database when it needs to, well, when is it supposed to do that? I see two hundred conditions here and have no idea what's going on, and I have no clue what you're trying to accomplish with the now.minute == 12 either, is this some sort of cron clone?

gusty spindle Jun 30, 2018, 12:45 AM

#

@orchid charm might this be of use? https://stackoverflow.com/questions/6242756/how-to-retrieve-inserted-id-after-inserting-row-in-sqlite-using-python

Stack Overflow

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

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

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

orchid charm Jun 30, 2018, 12:45 AM

#

Possibly

#

I figured the thing out

#

and it now works

gusty spindle Jun 30, 2018, 12:46 AM

#

fair

orchid charm Jun 30, 2018, 12:46 AM

#

What i do is i do
C = len(Query) - 1

#

Cause the final Result is almost always the newest

gusty spindle Jun 30, 2018, 12:47 AM

#

yes but doesnt that mean youre loading your entire database every time?

orchid charm Jun 30, 2018, 12:49 AM

#

I am going to be doing other stuff with the fetchall

cosmic gulch Jul 1, 2018, 3:35 AM

#

need help with pymongo

#

I added data in the DB

#

but i want to print the info in console

torn sphinx Jul 1, 2018, 4:50 AM

#

@cosmic gulch Can i get help

vestal apex Jul 1, 2018, 7:43 AM

#

to both of you:

#

bot.tags['ask']

delicate fieldBOT Jul 1, 2018, 7:43 AM

#

ask

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

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

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

wind nexus Jul 1, 2018, 4:22 PM

#

Hi

#

im working on making a backend with flask and pyodbc (Azure Sql server)
does this seem like a good way to add a new user :

def Create(User,Email,pwHash):
    """Create New User, Return 422 if username or email in use!"""
    try:
        cursor.execute("SELECT count(*) FROM Accounts WHERE Username=? OR Email=?",(User,Email)) # Check Username or Email is in use
        data=cursor.fetchone()[0]
    except pyodbc.Error:
        return "DB Error"

    if data==0:
        # NOTE: When including any input from user in the query, use ? Mapping. in the function below, First ? is User, second ? is pwHash and third ? is Email
        try:
            cursor.execute("INSERT INTO Accounts(Username, PasswordHash, Email) values (?, ?, ?)",(User,pwHash,Email))
            cursor.commit() # Used to "save" changes to database.
        except pyodbc.Error:
            return "DB Error"
        return "200"
    else:
        return "422"

#

also, what would be a "best practice" way of using return with pyodbc and flask, i dont think that my current way of returning those number codes is a good idea

dull scarab Jul 1, 2018, 5:23 PM

#

Can you not use the placeholder ? in place for tablenames in sqlite

wind nexus Jul 1, 2018, 5:24 PM

#

why

dull scarab Jul 1, 2018, 5:24 PM

#

async def execute(query, *args):
    async with aiosqlite.connect(db_path) as db:
        await db.execute(query, *args)
        await db.commit()```

```py
    @commands.command()
    async def cleartable(self, ctx, table:str):
        await db.execute("DELETE FROM ?;", table)```

Traceback (most recent call last):
File "C:\Users\Admin\AppData\Local\Programs\Python\Python36-32\lib\site-packages\discord\ext\commands\core.py", line 60, in wrapped
ret = await coro(*args, **kwargs)
File "E:\Python\Bot Projects\boiletplate\cogs\moderation.py", line 27, in cleartable
await db.execute("DELETE FROM ?;", table)
File "E:\Python\Bot Projects\boiletplate\utils\database_manager.py", line 25, in execute
await db.execute(query, *args)
File "C:\Users\Admin\AppData\Local\Programs\Python\Python36-32\lib\site-packages\aiosqlite\core.py", line 200, in execute
cursor = await self._execute(self._conn.execute, sql, parameters)
File "C:\Users\Admin\AppData\Local\Programs\Python\Python36-32\lib\site-packages\aiosqlite\core.py", line 159, in _execute
raise result
File "C:\Users\Admin\AppData\Local\Programs\Python\Python36-32\lib\site-packages\aiosqlite\core.py", line 136, in run
result = fn()
sqlite3.OperationalError: near "?": syntax error```

#

wait, old traceback

#

(i tried with delete * from ? earlier )

wind nexus Jul 1, 2018, 5:26 PM

#

the code above is working, and i understood that using the placeholders is more secure than passing raw values to the query

#

oh wait

#

facepalm

#

i misread that question

dull scarab Jul 1, 2018, 5:27 PM

#

oh wait, i have to pass table as a list

wind nexus Jul 1, 2018, 5:27 PM

#

i thought you asked whyt im using ? in the query

dull scarab Jul 1, 2018, 5:27 PM

#

no no

wind nexus Jul 1, 2018, 5:27 PM

#

Too much coffee

dull scarab Jul 1, 2018, 5:28 PM

#

GWqlabsNotLikeNoot I'm confuss

#

You simply can not use placeholders for column or table names. welp

#

await db.execute(f"DELETE FROM {table};")``` Solved it, so i guess `?` is just not allowed for column/table names

wind nexus Jul 1, 2018, 5:30 PM

#

is there a limited list of table names that you want to be able to be passed to funtion

#

or is that a variable that user types in

dull scarab Jul 1, 2018, 5:31 PM

#

It's an utility method for me to wipe it as i test

#

So i mean, no security issues

#

But I was just bamboozled that i couldn't use the ? placeholder

#

dont mind me, wrong discord

gusty spindle Jul 2, 2018, 12:02 AM

#

ooh, since when did aiosqlite exist? :o

deft badge Jul 2, 2018, 12:13 AM

#

📎 image.png

#

Since the 25th May 2017

gusty spindle Jul 2, 2018, 5:07 PM

#

very well

cosmic gulch Jul 2, 2018, 8:37 PM

#

Hi

#

i was wondering how i can host my REST on ubuntu vps

#

i made a little rest

#

that connects to mongoDB mlab

#

but it only hosts on localhost:5000

#

i just want to listen to a port

#

not bound to localhost

ionic pecan Jul 2, 2018, 8:46 PM

#

you make it listen on all interfaces i'd guess

#

that is, make it bind to the host 0.0.0.0

#

and allow 5000 through your firewall

#

but i have never used mongodb, god knows if that's secure

cosmic gulch Jul 2, 2018, 8:51 PM

#

@ionic pecan how do i bind it to 0.0.0.0

#

pymongo automatically binds to

ionic pecan Jul 2, 2018, 8:51 PM

#

that's something that you'll have to consult your documentation for, i don't use mongodb

cosmic gulch Jul 2, 2018, 8:51 PM

#

Ah

#

ok

ionic pecan Jul 2, 2018, 8:51 PM

#

pymongo is a client library as far as i'm aware

olive geyser Jul 2, 2018, 9:59 PM

#

hey

#

hello

#

how can u select data from mysql workbench? from 2 different tables

#

<@&462664414559076362>

viral crag Jul 2, 2018, 10:03 PM

#

is that really something worth mentioning 6,000 people over?

#

you want a JOIN query

#

you'll have to look that up yourself

olive geyser Jul 2, 2018, 10:04 PM

#

thank you

orchid charm Jul 2, 2018, 10:14 PM

#

How to get the greatest value in a DB?

#

SQLite

torn sphinx Jul 2, 2018, 10:16 PM

#

SELECT max(column) from table_name``` ?

orchid charm Jul 2, 2018, 10:16 PM

#

Cause i am trying to get the "Newest" Row in a Database

#

And a idea i had was to put a number row

#

SELECT * FROM * WHERE max(*)

#

So would that work?

torn sphinx Jul 2, 2018, 10:17 PM

#

uh

#

explain better what youre trying to do again

orchid charm Jul 2, 2018, 10:18 PM

#

So i have a Script that needs to find the newest Row

#

And a idea i had was to have a Number Column on each row

torn sphinx Jul 2, 2018, 10:18 PM

#

so

#

the highest number is the newest row/

orchid charm Jul 2, 2018, 10:18 PM

#

Then get a Specific Value from the row where a specific column has the greatest value

#

Example:
Insert - Item
1 - XXX
2 - YYY
3 - ZZZ

#

I would want to get ZZZ

#

Cause 3 is greater than the rest

torn sphinx Jul 2, 2018, 10:20 PM

#

you can do sql SELECT * FROM table_name ORDER BY column_name DESC LIMIT 1;

#

i think that works

orchid charm Jul 2, 2018, 10:20 PM

#

Oh?

#

Ok

torn sphinx Jul 2, 2018, 10:21 PM

#

thatll give the first row sorted by column_name

orchid charm Jul 2, 2018, 10:25 PM

#

cur.execute('SELECT Item FROM Splatoon2 ORDER BY Index DESC LIMIT 1;')

#

sqlite3.OperationalError: near "Index": syntax error

#

📎 Screen_Shot_2018-07-02_at_3.25.49_PM.png

torn sphinx Jul 2, 2018, 10:27 PM

#

what happens if you put back ticks around Index

orchid charm Jul 2, 2018, 10:27 PM

#

?

torn sphinx Jul 2, 2018, 10:28 PM

#

SELECT Item FROM Splatoon2 ORDER BY `Index` DESC LIMIT 1;```

orchid charm Jul 2, 2018, 10:31 PM

#

That gives the one with the lowest value

#

('XXX',)

torn sphinx Jul 2, 2018, 10:31 PM

#

change DESC TO ASC

orchid charm Jul 2, 2018, 10:32 PM

#

cur.execute("SELECT SplatNet2 FROM Splatoon2 ORDER BY 'Index' ASC LIMIT 1;")

#

('XXX',)

torn sphinx Jul 2, 2018, 10:32 PM

#

use backticks

#

not quotation marks

orchid charm Jul 2, 2018, 10:32 PM

#

?

torn sphinx Jul 2, 2018, 10:32 PM

#

literally you couldve copy pasted my code

orchid charm Jul 2, 2018, 10:33 PM

#

cur.execute('SELECT SplatNet2 FROM Splatoon2 ORDER BY 'Index' ASC LIMIT 1;')

#

So like that?

torn sphinx Jul 2, 2018, 10:34 PM

#

copy

#

what

orchid charm Jul 2, 2018, 10:34 PM

#

It gives a invalid syntax

torn sphinx Jul 2, 2018, 10:34 PM

#

i

#

wrote

#

above

orchid charm Jul 2, 2018, 10:34 PM

#

Oh i thought you were using '

torn sphinx Jul 2, 2018, 10:34 PM

#

no sir

orchid charm Jul 2, 2018, 10:34 PM

#

Well hopefully this works

#

Ok it might have worked

#

It gives White V-Neck Tee

#

Which is Number 9

#

Whats with that?

#

The "Index" Goes from 0 to like 40+

torn sphinx Jul 2, 2018, 10:37 PM

#

huh thats weird lol

orchid charm Jul 2, 2018, 10:37 PM

#

📎 Screen_Shot_2018-07-02_at_3.37.23_PM.png

#

conn = sqlite3.connect('/Users/shanehawkins/Desktop/Bot/bot/tartar.db', timeout=8)
cur = conn.cursor()
cur.execute("SELECT SplatNet2 FROM Splatoon2 ORDER BY `Index` DESC LIMIT 1;")
X = cur.fetchone()
cur.close()
print(X)

torn sphinx Jul 2, 2018, 10:43 PM

#

i dont see why it shouldnt work 🤔

orchid charm Jul 2, 2018, 10:43 PM

#

Is it cause the type of Index is Text?

torn sphinx Jul 2, 2018, 10:43 PM

#

oh lol maybe

#

change it to int or bigint or something

orchid charm Jul 2, 2018, 10:44 PM

#

Hopefully this works cause i got 16 mins left to figure this out

#

I think it works now!

#

Thanks

torn sphinx Jul 2, 2018, 10:46 PM

#

np

mellow kindle Jul 3, 2018, 12:13 AM

#

anyone know if there's any way to add a couchdb instance as a data source in PyCharm?

orchid charm Jul 3, 2018, 6:03 AM

#

https://hastebin.com/etemonexaf.py

#

So this loop is not Inserting like it is supposed to, why?

olive geyser Jul 3, 2018, 10:59 AM

#

hey

#

someone to help?

dull scarab Jul 3, 2018, 11:07 AM

#

bot.tags.get ask

delicate fieldBOT Jul 3, 2018, 11:07 AM

#

ask

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

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

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

olive geyser Jul 3, 2018, 11:11 AM

#

im a bit noob, but here i haave 4 screenshots from ym database,. At table "Horarios" we can find a "cod_dia" that is on table "horariosdia", then we can find "cod_hora" that is on table "horarioshora" and then we have "cod_disciplina" that we can find on table "disciplinas"

📎 2018-07-03-113639_1824x984_scrot.png

#

📎 2018-07-03-115851_1824x984_scrot.png

#

📎 2018-07-03-113649_1824x984_scrot.png

#

📎 2018-07-03-120447_1824x984_scrot.png

#

i wanna build something like this

📎 horrio-escolar-1-638.png

#

but with the subjects (that means disciplinas on my project)

#

can u guys help me with this?

#

<@&462664414559076362>

viral crag Jul 3, 2018, 11:15 AM

#

THE ELUSIVE EVERYONE MENTION

#

mentioning 6,000 people for help with your problem

#

you still haven't really asked a question though

olive geyser Jul 3, 2018, 11:15 AM

#

i need to select data from 3 different table and build something like on the last picture

#

can someone help me?

viral crag Jul 3, 2018, 11:16 AM

#

I mean, can they? Maybe

#

But you're not asking a question

#

like

#

You're asking for help, but you're not being specific about the help that you need

#

@olive geyser Are you looking for a designer? Or do you not know where to start? Do you have any code?

#

We need specifics

olive geyser Jul 3, 2018, 11:19 AM

#

i just have the database

#

i need someone that can help me design this

#

i need to select data from 4 different tables

#

i dont know where to start

#

i need to build a "school schedule"

viral crag Jul 3, 2018, 11:22 AM

#

OK, well you have a few options

#

First of all though, you need to decide what medium you'll use to generate the table

#

you have Pillow, if you want to generate an image directly

#

You could generate an Excel spreadsheet

#

or, and this is the preferred method, you could generate some HTML that displays it

olive geyser Jul 3, 2018, 11:22 AM

#

i want html that displays it

viral crag Jul 3, 2018, 11:22 AM

#

OK

#

You're already using MySQL, so you'll need to look into the SQL modules available for Python

#

I would also suggest Jinja2, which will let you write a HTML template - much easier and cleaner than outright generating HTML yourself

#

You're also going to need to know HTML and CSS

olive geyser Jul 3, 2018, 11:24 AM

#

but my question is

#

how can u select data from the same database but from different tables

viral crag Jul 3, 2018, 11:24 AM

#

You need to structure your database in a way that allows for that

olive geyser Jul 3, 2018, 11:24 AM

#

well i did

viral crag Jul 3, 2018, 11:24 AM

#

OK

#

then you need to learn how to use JOIN queries

#

INNER JOIN/OUTER JOIN/JOIN

#

they're a bit tricky, but that is what you need

olive geyser Jul 3, 2018, 11:25 AM

#

i dont knwo how to join, i already search on google but i did not understand

viral crag Jul 3, 2018, 11:25 AM

#

unfortunately, not many people do know how to use those queries

#

because, well, they're tricky

#

It seems like your dataset is small though, if it's small enough to fit into memory then you can probably just get all the relevant information in separate queries, and filter it in python

olive geyser Jul 3, 2018, 11:28 AM

#

my brain has stopped

viral crag Jul 3, 2018, 11:30 AM

#

if you don't really understand, then you might need more experience before tackling this

clear delta Jul 3, 2018, 12:10 PM

#

If you're more of a visual learner, this might help

#

http://joins.spathon.com

#

@olive geyser

olive geyser Jul 3, 2018, 12:11 PM

#

thank you

clear delta Jul 3, 2018, 12:12 PM

#

There are a ton of other 'visual' SQL join explainers out there, try them till one of them clicks

dense basin Jul 3, 2018, 1:30 PM

#

sqlalchemy won't create my databases. any ideas?
https://pastebin.com/fVSQ0r3C

Pastebin

[Python] import configparser import string import random from...

#

alright, it only happens when using an in-memory database

quartz mortar Jul 3, 2018, 9:24 PM

#

are there any books or resources on how to work with databases for beginners

ionic pecan Jul 3, 2018, 9:28 PM

#

with databases in general or with databases from python?

quartz mortar Jul 3, 2018, 9:28 PM

#

from python

ionic pecan Jul 3, 2018, 9:30 PM

#

Sqlalchemy has a great tutorial if you‘re using an SQL database

#

If you want to keep at rather raw SQL, there‘s libraries for that, sqlite3 is built-in and works with sqlite, and you can look for a database adapter for the others

quartz mortar Jul 3, 2018, 9:35 PM

#

thanks! bookmarked

#

im trying to work on a project where theres data stored in a database about how many reportable incidents happen under a certain medical staff, typically a nurse, and want to have that data graphable

#

its kinda hard bc i also need to include recent procedures theyve had and drugs theyve taken from a database bc those could be contributing factors

ember dew Jul 5, 2018, 3:07 AM

#

Anyone got an opinion on formatting incredibly long SQLAlchemy query chains?

versed coyote Jul 5, 2018, 4:55 AM

#

No

ember dew Jul 5, 2018, 5:27 AM

#

Clearly lol

versed coyote Jul 5, 2018, 5:29 AM

#

Yes

wind nexus Jul 5, 2018, 6:54 PM

#

is anyone here atm?

#

📎 Capture.PNG

#

does this look like a good database for user information and login etc?

#

@viral crag you seem to know about databases?

ionic pecan Jul 5, 2018, 7:03 PM

#

password salt?

wind nexus Jul 5, 2018, 7:04 PM

#

im using argon 2 and the output from it sort of has the salt and hash in same string

ionic pecan Jul 5, 2018, 7:04 PM

#

ah

wind nexus Jul 5, 2018, 7:05 PM

#

im wondering if the relations etc are used in a proper way and if the database looks like someone could maintain it after i get hit by a bus

#

📎 Capture1.PNG

#

or does this look better

wind nexus Jul 6, 2018, 12:51 PM

#

class User(db.Model, UserMixin):
    __tablename__ = "User"
    user_id = db.Column(INTEGER, primary_key=True)
    email = db.Column(VARCHAR(255), index=True, unique=True)
    password_hash = db.Column(VARCHAR(255))
    activated = db.Column(BIT)
    skills = db.relationship('Skills', backref='user', lazy=True)
    details = db.relationship('Details',uselist=False,back_populates='user')

class Details(db.Model):
    __tablename__ = "Details"
    id = db.Column(INTEGER, primary_key=True)
    first_name = db.Column(VARCHAR(255))
    last_name = db.Column(VARCHAR(255))
    trial = db.Column(BIT)
    subscription = db.Column(BIT)
    signup_date = db.Column(DATETIME)
    subscription_id = db.Column(VARCHAR(255))
    profile_picture = db.Column(VARCHAR(255))
    user_id = db.Column(INTEGER, db.ForeignKey('user.user_id'))
    user = db.relationship("User", back_populates="details")

class Skills(db.Model):
    __tablename__ = "Skills"
    id = db.Column(INTEGER, primary_key=True)
    skill_name = db.Column(VARCHAR(255))
    user_id = db.Column(INTEGER, db.ForeignKey('user.user_id'))

does this look like a proper use of one to one and one to many?

#

@near cradle

near cradle Jul 6, 2018, 12:53 PM

#

that looks promising, yes

wind nexus Jul 6, 2018, 1:27 PM

#

hmm its giving me

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship User.skills - there are no foreign keys linking these tables.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

wind nexus Jul 6, 2018, 2:18 PM

#

YAAY i managed to make it work with the azure sql server

wind nexus Jul 6, 2018, 4:33 PM

#

so, for my dev enviroment, i wanted a quick way to clear the database, but didnt want to do it accidentally, so

print("/////////////////////////////")
print("// WARNING WARNING WARNING //")
print("/////////////////////////////")
print(" ")
print("THIS WILL RESET THE ENTIRE DATABASE!")
print("!!ALL DATA WILL BE LOST FOREVER!!")
print("IF THIS IS WHAT YOU WANT, TYPE 'CONFIRM'")
conf = input("Do you want to reset the database?> ")

if (conf == "CONFIRM"):
    print("Importing database...")
    from app import db
    print("Import Completed!")
    print("These tables will be removed:")
    meta = db.metadata
    for table in reversed(meta.sorted_tables):
        print(table)
    print("Do you want to proceed?")
    print("There is no turning back after this! All data WILL BE LOST!")
    stage2 = input("Type 'YES' to wipe everything: ")
    if(stage2 == "YES"):
        print("Starting db.reflect()...")
        # db.reflect()
        print("Reflection Done, Wiping starts now")
        print("ERASING ALL DATA...")
        # db.drop_all()
        print("Database has been erased!")
    else:
        print("CANCELED")
else:
    print("CANCELED")

#

commented out until i want to actually have the script functioning

deft badge Jul 6, 2018, 4:34 PM

#

Nice

clever viper Jul 6, 2018, 4:35 PM

#

Seems like in the time that you could open the script, uncomment it, and then run it, you could just wipe the DB?

wind nexus Jul 6, 2018, 4:36 PM

#

no need to uncomment anymore, just uncommented while i tested the safeguards

clever viper Jul 6, 2018, 4:36 PM

#

Ohh

#

Okay

sturdy orbit Jul 6, 2018, 7:59 PM

#

Whats the easiest option to store objects in a database, we're using sqlalchemy at the moment, and i HATE it!

#

is zodb any good?

broken linden Jul 6, 2018, 9:34 PM

#

one doesnt store objects in databases, those objects just represent normal SQL and get translated to SQL. Personally I'd either use psycopg2 (pure SQL) or sqlalchemy

wind nexus Jul 6, 2018, 11:15 PM

#

@near cradle im getting

flask_login\mixins.py", line 39, in get_id
    raise NotImplementedError('No `id` attribute - override `get_id`')

when trying to do a login, my table does not have id column, instead its called user_id
do i need to somehow override it?

near cradle Jul 6, 2018, 11:16 PM

#

when you do what

wind nexus Jul 6, 2018, 11:17 PM

#

site-packages\flask_login\mixins.py", line 37, in get_id
    return text_type(self.id)
AttributeError: 'Users' object has no attribute 'id'

#

trying to use the mixin stuff and flask login

ionic pecan Jul 6, 2018, 11:17 PM

#

that error sounds fairly clear

#

"No id attribute - override get_id"

wind nexus Jul 6, 2018, 11:18 PM

#

but how do i do it

ionic pecan Jul 6, 2018, 11:18 PM

#

I suppose the default is using the .id attribute, but you seem to be able to customize it using get_id

#

Check the docs for flask_login

wind nexus Jul 6, 2018, 11:26 PM

#

so this is in the mixins source:


    def get_id(self):
        try:
            return text_type(self.id)
        except AttributeError:
            raise NotImplementedError('No `id` attribute - override `get_id`')

#

do i just copy part of that to my User class and change self.id to self.user_id

wind nexus Jul 7, 2018, 2:02 AM

#

how should i implement linkedin login with flask?

cinder pelican Jul 7, 2018, 11:58 AM

#

https://github.com/lepture/flask-oauthlib

GitHub

lepture/flask-oauthlib

flask-oauthlib - If you are a company, you should use https://github.com/lepture/authlib instead.

#

there's an example for LinkedIn

woven stag Jul 7, 2018, 12:43 PM

#

Whats a good way of Learning databases related to Python?

ionic pecan Jul 7, 2018, 12:44 PM

#

As in, using databases with Python?

woven stag Jul 7, 2018, 12:50 PM

#

Seems good

ionic pecan Jul 7, 2018, 12:52 PM

#

bannyThink I'll take that as a yes

#

I recommend looking into SQLAlchemy, their tutorial is great: http://docs.sqlalchemy.org/en/latest/core/tutorial.html

wispy fable Jul 8, 2018, 3:49 AM

#

(or if you aren't really concerned about all the server and permission related stuff, you can use sqlite3, which is part of the standard library)

jade topaz Jul 8, 2018, 5:11 AM

#

ok this is a bit of a longshot but does anyone here use neomodel? OGM for neo4j databases? I can't figure out how to set UNIQUE constraints on a property, e.g given a Person node with an email property, no two Person nodes can share the same email address .

the docs don't say anything about unique constraints on properties (apart from the UniqueIdProperty), and the source code for both the Property and StringProperty classes don't seem to accept a 'unique' kwarg. I'm kinda lost here

jade topaz Jul 8, 2018, 8:27 AM

#

ok so it looks like unique_index might be what I need. more testing is required

fringe seal Jul 8, 2018, 9:21 AM

#

I was wondering how I should be using SQL on python

#

would anyone know how it would work?

#

or recommend a library?

patent glen Jul 8, 2018, 7:44 PM

#

@fringe seal sqlite is built in, and there are libraries for other database engines that use basically the same interface. Also look at sqlalchemy and maybe pandas [pandas is more for table-like objects to load it into, it's compatible with sqlite and sqlalchemy]

celest barn Jul 8, 2018, 8:08 PM

#

also please use prepared statements

ember dew Jul 9, 2018, 6:28 AM

#

I would mention that if you are being serious about your database

#

then you probably want to do migrations

#

which are quite convenient with SQLAlchemy + Alembic

#

but that sqlite3 doesn't support a lot of the migration mechanisms without workarounds

fringe seal Jul 9, 2018, 6:58 AM

#

ok thanks!

jade topaz Jul 9, 2018, 7:31 AM

#

just want to add, in case you don't find the built-in libraries to your liking there are ORMs like SQLAlchemy and Peewee that make it easier to work with SQL databases

torn sphinx Jul 9, 2018, 12:38 PM

#

Fairly new to Python and MySQL and i'm wondering why this code returns the error 'Lost connection to MySQL server during query':

#

import pymysql

connection = pymysql.connect(user='no', passwd='no', host='no',port=0000, database='no')
cursor = connection.cursor()
query = ("SELECT * FROM discordtable")

cursor.execute(query)

ionic pecan Jul 9, 2018, 12:40 PM

#

that's a bit weird, but that somewhat like a one-time issue, did you try this multiple times?

torn sphinx Jul 9, 2018, 12:40 PM

#

Yeah, I have tried running the code multiple times.

viral crag Jul 9, 2018, 12:41 PM

#

What's in the table?

#

And are your details correct?

#

What happens if you run the query using mysql directly?

torn sphinx Jul 9, 2018, 12:42 PM

#

The table contains name & verification.

#

Yes, my details are correct. I have no issue with connecting to the database.

#

Oh, I do have some issues with connecting.

#

This is weird..

rancid pier Jul 9, 2018, 6:23 PM

#

I suggest the typical approach: do it with CLI, if it works it's either you or the module (mostly you) 😁

stable violet Jul 10, 2018, 1:19 AM

#

c1.execute("SELECT ? FROM Hours WHERE UID=?", (keyword, uid)) looking to do something fairly simple, how do you put the column in to be defined later? I was thinking it would be using ? still but that doesn't look to be the case

#

And I'm having trouble finding it in the python docs (sqlite3)

quick marlin Jul 10, 2018, 1:24 AM

#

i remember this being asked before

#

i think they came to the conclusion that you cant

#

so probably just c1.execute("SELECT ? FROM {} WHERE UID=?".format(column), (keyword, uid))

#

and safeguard against sql injection yourself i guess

#

¯_(ツ)_/¯

neat reef Jul 10, 2018, 1:26 AM

#

one thing you can do it's just py c1.execute("SELECT * FROM Hours WHERE UID=?", (uid,)) and then only use the row you want

stable violet Jul 10, 2018, 1:27 AM

#

thank you guys

viral crag Jul 10, 2018, 9:45 AM

#

If you need to dynamically pick a table to use, then you're using any SQL database wrong

keen cedar Jul 10, 2018, 3:30 PM

#

So, my bot provides a wallet system for every server.
In every server, each member may be given a wallet, and every wallet has a certain amount of coins.
The wallets aren't uniform, meaning the member's balance in server A might be different from their balance in server B, so, as I understand it, this is a many to many relationship...

How do I even implement that? I'm new to SQL and as far as I've gathered using many to many relationships is considered a bad practice but I don't know how else to implement this. And it's a roleplay bot, so I can't exactly make the wallets uniform. I'm using MySQL's workbench to edit the tables, by the way, not sure if this is important 🤔

patent glen Jul 10, 2018, 3:47 PM

#

wallet table with both a server id and member id as key columns

#

that's one to one: one "server+member" to one wallet.

#

(many to many relationships aren't inherently bad either, imagine you have a non-unique item that can be in many players' inventory. but that's not what this is.)

#

fundamentally "servers" to "members" is a many to many relationship as well

keen cedar Jul 10, 2018, 3:53 PM

#

I'm a bit confused... So, let's say we have three members. Their ID's are 1111, 2222, 3333. There are two servers, their ID's - 111111, 222222.
1111 has a wallet in server 111111 with 500 coins, and another one in server 222222 with 300 coins.
2222 has a wallet in server 111111 with 300 coins, and another one in server 222222 with 100 coins.
3333 only has a wallet in server 222222 with 700 coins.
What would the table look like?

patent glen Jul 10, 2018, 3:59 PM

#

member server coins
1111   111111 500
1111   222222 300
2222   111111 300
2222   222222 100
3333   222222 700```

#

create table wallet (
  member int not null, server int not null, coins int not null default 0,
  constraint member_server unique (member, server)
);```

keen cedar Jul 10, 2018, 4:05 PM

#

Not sure what the third line does since I'm pretty new to SQL, but thanks, I'll try to figure the rest out from here

open gull Jul 10, 2018, 4:06 PM

#

im guessing the third line makes it so you can't have two records with the same member and server

wispy fable Jul 10, 2018, 4:30 PM

#

Why not make a table for every server?

tight ravine Jul 10, 2018, 5:15 PM

#

I currently have a website built in python that is using SQLite . Is it worth switching to MySQL?

ionic pecan Jul 10, 2018, 6:08 PM

#

@wispy fable because that's not how you SQL

#

you don't programmatically create tables

#

@tight ravine see https://sqlite.org/whentouse.html

#

specifically:
*SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.

The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Dynamic content uses about 200 SQL statements per webpage. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time.*

#

but if you want e.g. redundancy with multiple servers then mysql is probably better, although i never used it so i wouldn't know

tight ravine Jul 10, 2018, 6:34 PM

#

My biggest issue right now is that SQLite uses two temp files with data that seem to not update the main database so I can't make up to date backups of the main database

neat reef Jul 11, 2018, 2:42 AM

#

So, I have the current table in SQLite

create table if not exists `notes`
(
    user TEXT PRIMARY KEY,
    id INTEGER NOT NULL,
    content TEXT NOT NULL,
    CONSTRAINT _unique UNIQUE (user, id)
);

I want to do the following when deleting and inserting into the table.
Lets say I have

'user1', 1, 'First user1 note'
'user2', 1, 'First user2 note

And now user1 adds two notes, but then decides to delete the second one, instead of

'user1', 1, 'First user1 note'
'user2', 1, 'First user2 note'
'user1', 3, 'Third user1 note'

(there's a gap between 1 and 3)
I want

'user1', 1, 'First user1 note'
'user2', 1, 'First user2 note'
'user1', 2, 'Third user1 note'

removing any gap between

#

How could I do it?

quick marlin Jul 11, 2018, 2:54 AM

#

you want the id to change?

#

that sounds like a really bad idea

neat reef Jul 11, 2018, 2:55 AM

#

for what im trying to do isn't a big deal

quick marlin Jul 11, 2018, 2:55 AM

#

it just conceptually makes no sense

neat reef Jul 11, 2018, 2:55 AM

#

is more like an index more than in id

quick marlin Jul 11, 2018, 2:55 AM

#

an id is supposed to refer to something uniquely

#

well i dont see why you'd care

neat reef Jul 11, 2018, 2:55 AM

#

yeah, the name is actually bad

quick marlin Jul 11, 2018, 2:55 AM

#

just pull all user notes

#

into a list

#

and you have a list of all notes that you can access by index

#

or something

neat reef Jul 11, 2018, 2:56 AM

#

well, the main purpose of id is to take track of the insertion order

#

because, i can't rely on the order of the the data or time

quick marlin Jul 11, 2018, 3:00 AM

#

relay?

neat reef Jul 11, 2018, 3:00 AM

#

yep

quick marlin Jul 11, 2018, 3:00 AM

#

if you want insertion order make sure the id is always incrementing

#

like, in sqlite the id has to have the AUTOINCREMENT thingy

#

or it'll re-use ids

neat reef Jul 11, 2018, 3:01 AM

#

oh right, i completely forgot that

loud spade Jul 11, 2018, 7:40 PM

#

why does my database insert things like this? https://gyazo.com/3bb513be01262d36c0d1af3c857ed8ae

Gyazo

broken linden Jul 11, 2018, 7:48 PM

#

because you are telling it to?

summer sable Jul 11, 2018, 7:55 PM

#

IDENTITY in sql is often used for incrementing uniquely, not deleting later, but also not losing order of insertion, @neat reef
Also, hi @quick marlin from another life!

neat reef Jul 11, 2018, 7:56 PM

#

yeah, i changed it

loud spade Jul 11, 2018, 9:00 PM

#

@broken linden im telling it to put it in row 1, I assume, but it creates a new row

broken linden Jul 11, 2018, 9:00 PM

#

well how are you telling it to do that

loud spade Jul 11, 2018, 9:14 PM

#

I’ll send it in a bit xD, atm on my phone

loud spade Jul 11, 2018, 9:37 PM

#

@broken linden

    c.execute("SELECT * FROM Bannedwords")
    print(c.fetchall())
    if not c.fetchall():
        if word in c.fetchall():
            await client.send_message(ctx.message.author, "`{}` is already being censured!".format(word))
        else:
            while True:
                listword = ''.join(random.choice(string.ascii_uppercase) for _ in range(6))
                c.execute("CREATE TABLE IF NOT EXISTS Bannedwords(word1 TEXT)")
                try:
                    c.execute("ALTER TABLE Bannedwords ADD COLUMN {} TEXT".format(listword))
                    c.execute("INSERT INTO Bannedwords({}) VALUES(?)".format(listword), (word.lower(),))
                    await client.send_message(ctx.message.author, "I have succesfully cencured the word `{}`".format(word))
                    break
                except:
                    print('except')
                    c.execute("INSERT INTO Bannedwords(word1) VALUES (?)", (word,))
                    await client.send_message(ctx.message.author, "I have succesfully cencured the word `{}`".format(word))
                    break

#

database for my discord bot

ionic pecan Jul 11, 2018, 9:38 PM

#

never, ever, use format strings to insert into databases

#

although it's your own data, it's just bad

broken linden Jul 11, 2018, 9:38 PM

#

yeah you are gonna get hacked pretty quick if you do that

ionic pecan Jul 11, 2018, 9:38 PM

#

and you shouldn't be programatically altering your table either

loud spade Jul 11, 2018, 9:38 PM

#

oh frick

ionic pecan Jul 11, 2018, 9:38 PM

#

what's the grand scheme of things you want to accomplish with adding the column

loud spade Jul 11, 2018, 9:40 PM

#

if I add the words in the word1 column, the output would be like this: [('fuck',), ('fuck',), ('dick',), ('dick',), ('dick',), ('duck',), ('frick',)] like multiple lists, right?, and if I am trying to check if a word is in there, it got messed up

#

(sorry for the bad language, but it censors cursing words)

ionic pecan Jul 11, 2018, 9:44 PM

#

huh

#

so you add one column per censored word on a guild?

loud spade Jul 11, 2018, 9:44 PM

#

yeah

ionic pecan Jul 11, 2018, 9:44 PM

#

okay

#

there's two ways of doing this properly

#

way 1: have a colum for each censored word

#

guild_id | word
---------+------
15151515 | javascript
15151515 | porn
15151515 | nix

loud spade Jul 11, 2018, 9:46 PM

#

LOL!

ionic pecan Jul 11, 2018, 9:46 PM

#

then you just run ```sql
SELECT word FROM censored_word WHERE guild_id = ?;

#

and you have your censored words for that guild

#

as a list of tuples, like you shown above

#

way 2 assumes you're using postgres: postgres has an array type, so you can just do ```sql
guild_id | words
---------+------
15151515 | ["javascript", "porn", "nix"]

#

but i would use the first variant regardless for compatability reasons

loud spade Jul 11, 2018, 9:50 PM

#

okay, thanks for your advice. But I dont understand how to implement way 1, could you explain that more ?

ionic pecan Jul 11, 2018, 9:55 PM

#

instead of adding a new colum for the newly added word, add a new row

#

so in your else, run ```sql
INSERT INTO censored_word VALUES (?, ?);

with params `guild_id`, `word`

#

if you don't want to check for uniqueness yourself you can just place a uniqueness constraint on guild_id <> word

loud spade Jul 11, 2018, 9:58 PM

#

okay, thanks. I will work with that and if I have any troubles, which I hope I dont, I will ask for help 😃

#

thank you!

deft yacht Jul 11, 2018, 10:00 PM

#

is there someone who has worked with the cassandra and Python ?

ionic pecan Jul 11, 2018, 10:03 PM

#

bot.tags['ask']

delicate fieldBOT Jul 11, 2018, 10: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.

noble wasp Jul 11, 2018, 11:30 PM

#

how motivational

#

kinda applies to all things

viral crag Jul 11, 2018, 11:31 PM

#

You should read the page it links to

#

and the page that page links to

#

:P

warm hare Jul 12, 2018, 5:26 PM

#

Hey

#

What is the best way to store web stats?

#

Such as requests

#

But I do want to be able to grab them like requests per second

#

or

#

per day

#

shit like that

#

what would be the best way to store it and to grab it again to display to admins?

#

If someone answers please ping me

#

I am currently using MongoDB, I'm not moving away from it but I would look into using a second one

#

edis in use already too

deft badge Jul 12, 2018, 5:30 PM

#

You could use something like statsd and grafana if you want professional looking graphs & stats

#

You can also use datadog to tally things up for you

warm hare Jul 12, 2018, 5:49 PM

#

datadog does not have a good free plan

#

will look into grafana

#

How would I use Flask with Grafana tho

deft badge Jul 12, 2018, 6:05 PM

#

You'd run grafana seperately, they don't integrate by default, and you'd write some kind of before_request handler in flask to add something to the source of your grafana data

warm hare Jul 12, 2018, 6:06 PM

#

ugh this shit is confusing lol

warm hare Jul 12, 2018, 8:21 PM

#

oh

#

I was just looking into Elasticsearch

#

Elastic APM should do the job ig

low beacon Jul 13, 2018, 4:13 AM

#

So I have a Json that has nothing in it except the {} how would I dump a name to a dict as well as the value?

torn sphinx Jul 13, 2018, 5:25 AM

#

once you load it into a variable

#

just do something like

#

data = {}
data['mykey'] = 'myvalue'```

#

then you can dump it back into the file

low beacon Jul 13, 2018, 5:27 AM

#

Okay thanks

#

So that will dump the value

#

And the key thank you

torn sphinx Jul 13, 2018, 5:29 AM

#

well

#

it wont change it in the file

low beacon Jul 13, 2018, 5:29 AM

#

Yeah

torn sphinx Jul 13, 2018, 5:29 AM

#

youll have to change it in python, then dump it back into the file

#

using json.dump

low beacon Jul 13, 2018, 5:30 AM

#

Yyp

#

Yup

torn sphinx Jul 13, 2018, 5:30 AM

#

ok then yeah u got it

low beacon Jul 13, 2018, 5:32 AM

#

Yeah no

#

That didn't work

#

But it's a discord.py related error so I won't ask you

noble anchor Jul 13, 2018, 7:06 PM

#

This is my first time trying to dump csv data into postgres, and I am getting a connection error.
What is the correct way to initiate the connection?

conn = psycopg2.connect("host=localhost dbname=postgres user=postgres")

ionic pecan Jul 13, 2018, 7:07 PM

#

post the full error

#

also that's probably better done through psql

noble anchor Jul 13, 2018, 7:08 PM

#

Full error:

File "db.py", line 2, in <module>
    conn = psycopg2.connect("host=localhost dbname=postgres user=postgres")
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not connect to server: Connection refused
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 5432?
could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?

ionic pecan Jul 13, 2018, 7:16 PM

#

is the server running?

noble anchor Jul 13, 2018, 7:26 PM

#

Doesn't "host=localhost" start the server locally?

ionic pecan Jul 13, 2018, 7:27 PM

#

no, that connects to a local server

#

postgres works based on client / server

#

unlike e.g. sqlite

noble anchor Jul 13, 2018, 7:30 PM

#

do you happen to know any tutorials that include this step?

ionic pecan Jul 13, 2018, 7:33 PM

#

are you on windows or unix?

#

i found this: https://wiki.postgresql.org/wiki/Detailed_installation_guides

Detailed installation guides

noble anchor Jul 13, 2018, 7:43 PM

#

ok cool, thanks!

hidden siren Jul 16, 2018, 1:37 AM

#

so guys,

#

is python the best back end language?

gusty spindle Jul 16, 2018, 1:40 AM

#

is yellow the best colour?

hidden siren Jul 16, 2018, 1:46 AM

#

yes

wispy fable Jul 16, 2018, 1:48 AM

#

The answer is maybe

#

It really depends on what you're doing and hat you're willing to learn to do the thing most effectively

#

Any reason why you're asking in #databases, @hidden siren ?

hidden siren Jul 16, 2018, 1:53 AM

#

Because i want to learn a good language for backend. is database the wrong chat?

#

pepe

wispy fable Jul 16, 2018, 1:54 AM

#

#web-development would be more general backend stuff

#

#databases is for stuff like mongoDB and sqlite3

#

It really depends on what you mean by backend

hidden siren Jul 16, 2018, 1:55 AM

#

mongoDB sounds funny.

#

Abrakadabra teleport to #web-development

#

so mongoDB in german means literally retardDB.

wispy fable Jul 16, 2018, 1:59 AM

#

lol

ornate abyss Jul 16, 2018, 2:27 AM

#

Not wrong

#

Mongo has the big rart

torn sphinx Jul 16, 2018, 11:13 AM

#

hi

#

how can i decode a sqlite 3 database ?

ionic pecan Jul 16, 2018, 12:11 PM

#

bot.tags['ask']

delicate fieldBOT Jul 16, 2018, 12:11 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 Jul 16, 2018, 12:11 PM

#

what does "decode" even mean

daring cairn Jul 16, 2018, 12:29 PM

#

So I'm having a weird issue, with rethinkdb. This line works in my debug command. But not in actual code:

#

    async def _unpunish_data(self, ctx, member):
        """Removes punish data entry and cancels any present callback"""
        gid = ctx.guild.id
        gdb = await r.table('mute').get(str(gid)).run(self.bot.conn)
        await r.table('mute').get(str(gid)).replace(r.row.without(str(member.id))).run(self.bot.conn)
        gdb['muted'] -= 1
        await r.table('mute').insert(gdb, conflict="update").run(self.bot.conn)

#

And I'm not sure why, I know the function is being called and is functioning through that line because it removes 1 from 'muted'. Just seems to ignore that line with no errors.

#

http://b1nzy-banned.me/TfoSV.png

#

I've printed all of the IDs to make sure they are correct, and they are.

#

http://b1nzy-banned.me/VHME4.png Also printing it says it's replacing, but doesn't seem to? Do I need to save this somehow?

viral crag Jul 16, 2018, 12:37 PM

#

Why are you storing "muted" as an integer?

#

Feels like a boolean

#

Anyway, set conflict="replace"

#

"update" is generally not what you want

daring cairn Jul 16, 2018, 12:47 PM

#

@viral crag I might not have explained my issue enough, there is no issue with that line, it works fine. And update is what is needed there. This is the line that isn't functioning:

        await r.table('mute').get(str(gid)).replace(r.row.without(str(member.id))).run(self.bot.conn)

#

I see the first link for the image showing that info ^ was broken so I removed it. Apologies

#

Probably just placement because I'm saving after actually, I put it in between so I would know the code was touched but now I see how that could be an issue, let me try moving it lower

#

Yep, all about placement. Sorry I was really tired when working on this haha, problem solved 😅 I'm so stupid sometimes

patent glen Jul 16, 2018, 2:24 PM

#

@torn sphinx What are you trying to do?

torn sphinx Jul 16, 2018, 4:58 PM

#

mh decoding an sqlite 3 encoded file

patent glen Jul 16, 2018, 5:24 PM

#

that's not really an answer

#

i already showed you how to open a sqlite3 database... is this the same one you were working on last week, with the key/value columns with utf-16 encoded JSON?

rough river Jul 16, 2018, 11:41 PM

#

I got this error when trying to open pgAdmin4 postgres application server could not be contacted

#

I added the postgres bin and lib path to my system environments and that didn’t help

torn sphinx Jul 17, 2018, 12:32 AM

#

hey

#

using pymongo

#

is there a way to get info from the database back

#

in format so that one value is descending from greatest to leasst

#

in order

#

so if it's like

#

#

if i have that kind of thing

#

how would i get it so the info for Steve comes first, then Pete, then Bob

#

since that's the greatest to least order of age

vestal apex Jul 17, 2018, 12:35 AM

#

believe that would be this one http://api.mongodb.com/python/current/api/pymongo/cursor.html#pymongo.cursor.Cursor.sort

torn sphinx Jul 17, 2018, 12:37 AM

#

ahhhhh

#

thank you

rough river Jul 17, 2018, 12:51 AM

#

How would I add an value to a database

vestal apex Jul 17, 2018, 12:52 AM

#

that depends 200% on the database in question

rough river Jul 17, 2018, 12:53 AM

#

postgres

vestal apex Jul 17, 2018, 12:53 AM

#

how are you interacting with it?

rough river Jul 17, 2018, 12:53 AM

#

What do you mean?

vestal apex Jul 17, 2018, 12:54 AM

#

I imagine you're using a library

rough river Jul 17, 2018, 12:54 AM

#

asyncpg

vestal apex Jul 17, 2018, 12:56 AM

#

you're gonna want an UPDATE query in a Connection.execute call
https://www.w3schools.com/sql/sql_update.asp
https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg.connection.Connection.execute

#

let us know if you need help from there

torn sphinx Jul 17, 2018, 12:57 AM

#

for doc in botUsers.find().sort('bal', pymongo.DESCENDING):
            print(doc)

why is this not printing anything?

vestal apex Jul 17, 2018, 12:58 AM

#

presumably there are no docs to be found

torn sphinx Jul 17, 2018, 12:59 AM

#

but there are

vestal apex Jul 17, 2018, 12:59 AM

#

are you certain? try iterating over botUsers.find() without sorting it

torn sphinx Jul 17, 2018, 1:01 AM

#

still nothing

vestal apex Jul 17, 2018, 1:02 AM

#

that sounds an awful lot to me like it's not finding anything. is botUsers the right collection?

torn sphinx Jul 17, 2018, 1:03 AM

#

wait

#

ohhhhhh

#

also

#

if i have a list of values

#

is there a way i can do botUsers.find()

#

but like

#

find all values of name that are in the list

#

so if i have a list with Bob and Steve in it

#

it only gives me the docs for Bob and Steve

vestal apex Jul 17, 2018, 1:13 AM

#

I believe you would use this operator https://docs.mongodb.com/manual/reference/operator/query/in/

torn sphinx Jul 17, 2018, 1:15 AM

#

thanks

rough river Jul 17, 2018, 1:29 AM

#

db = await asyncpg.create_pool(**credentials)```

```py
@bot.event
async def on_command():
    async with connection.transaction():
        await connection.execute("INSERT INTO cmdcount VALUES(1)")
``` That does not seem correct at all but am I closer to getting it. I have a table named cmdcount.

#

How would I connect to my database because I don't think that is

vestal apex Jul 17, 2018, 2:23 AM

#

you should rename db something like pool or connection_pool for clarity, you get a connection from that pool temporarily with py async with pool.acquire() as connection:and then you can use your transaction

#

also are you sure you want an INSERT query and not an UPDATE query?

#

@rough river

rough river Jul 17, 2018, 2:27 AM

#

Would I save the previous cmd count then add one to it

#

credentials = {"user": "no", "password": "no", "database": "no", "host": "no"}
pool = await asyncpg.create_pool(**credentials)

@bot.event
async def on_command():
    async with pool.acquire() as connection:
        UPDATE cmdcount
        SET count = ' '

#

I'm confused what to put in the set or what to change

vestal apex Jul 17, 2018, 2:42 AM

#

your previous code with the transaction was correct, you need to wrap it python async with pool.acquire() as connection: async with connection.transaction(): await connection.execute("UPDATE cmdcount SET count = count + 1 --etc.")

rough river Jul 17, 2018, 2:43 AM

#

Ok

#

Everytime I run pgAdmin 4 it says fatal error application server could not be contacted do you know how I can fix that

vestal apex Jul 17, 2018, 2:45 AM

#

I'm afraid I do not

rough river Jul 17, 2018, 2:45 AM

#

Ok

#

                       ^
SyntaxError: invalid syntax``` It says that when I try to run the file

hoary wharf Jul 17, 2018, 2:56 AM

#

Are you inside a coroutine?

rough river Jul 17, 2018, 2:59 AM

#

no

#

im using on_command

vestal apex Jul 17, 2018, 3:10 AM

#

what Python version are you using?

#

(on_command is a coroutine)

#

(assuming you defined it with async def)

#

wait... are you sure you're defining pool in on_command?

#

that seems like an odd place to do it

rough river Jul 17, 2018, 3:12 AM

#

no

#

Im defining it after all the imports in my general file and in my main after the prefix

#

I'm using 3.5

vestal apex Jul 17, 2018, 3:14 AM

#

it's gotta be done in an async context, somewhere like on_ready

rough river Jul 17, 2018, 3:15 AM

#

so in my on ready I put pool = await asyncpg.create_pool(**credentials)?

vestal apex Jul 17, 2018, 3:16 AM

#

you should make pool an attribute of your bot

#

bot.pool

#

but yes that's the general idea

rough river Jul 17, 2018, 3:22 AM

#

pool = await asyncpg.create_pool(**credentials) NameError: name 'asyncpg' is not defined That's what it says when I run it @bot.event async def on_ready(): print("uwu bot is ready!") await bot.change_presence(activity=discord.Activity(type=discord.ActivityType.watching, name='uwu')) pool = await asyncpg.create_pool(**credentials) theres the full onready

#

and I'm confused what do you mean by you should make pool an attribute of your bot

vestal apex Jul 17, 2018, 3:30 AM

#

assign to bot.pool instead, pool will only exist local to on_ready

#

did you import asyncpg?

rough river Jul 17, 2018, 3:32 AM

#

I thought I did apparently I didn't.

rough river Jul 17, 2018, 3:54 AM

#

For bot.pool if I have other files using it do I have to redefine it

#

I have a cog that uses it but I don't have an on ready for it

vestal apex Jul 17, 2018, 4:02 AM

#

the bot instance is passed to your cog module via setup, so it's actually the perfect place to store the pool

rough river Jul 17, 2018, 4:03 AM

#

So I shouldn’t have to redefine it?

vestal apex Jul 17, 2018, 4:05 AM

#

correct

rough river Jul 17, 2018, 4:58 AM

#

    @commands.command()
    @commands.guild_only()
    @commands.cooldown(1, 5, BucketType.user)   
    async def about(self, ctx):
        '''Gives information about the bot'''
        async with self.bot.pool.acquire() as connection:
            async with connection.transaction():
                query = 'SELECT count FROM cmdcount;'
                await ctx.send(f'{cmd_count}')
``` I used that and when I use that it says SELECT count FROM cmdcount;

#

@bot.event
async def on_command():
    async with bot.pool.acquire() as connection:
        async with connection.transaction():
            await connection.execute("UPDATE uwu.cmdcount SET count = count + 1")

For that it says anytime an command is used Traceback (most recent call last): File "C:\Users\tasty\AppData\Local\Programs\Python\Python36-32\lib\site-packages\discord\client.py", line 220, in _run_event await coro(*args, **kwargs) TypeError: on_command() takes 0 positional arguments but 1 was given

#

I thought about them but I couldn't fix it

torn sphinx Jul 17, 2018, 6:42 AM

#

it takes one argument ctx

torn sphinx Jul 17, 2018, 9:50 AM

#

@patent glen yes but i lost the file GWqlabsYaoLUL

rough river Jul 17, 2018, 3:41 PM

#

        async with self.bot.pool.acquire() as connection:
            async with connection.transaction():
                cmdcount = '''SELECT count FROM uwu.cmdcount;'''  
                await ctx.send(f'{cmdcount}')

I'm using that to read from my database the table name is uwu.cmdcount and It adds 1 to count on command use but it returns [] everytime

radiant helm Jul 17, 2018, 3:51 PM

#

Possibly super common question, but is there any python package that allows mssql connections and that supports ssl?

#

I've tried pymssql but in the setup.py a variable is set so that no linking is done to the OpenSSL libraries

patent glen Jul 17, 2018, 3:51 PM

#

don't know about ssl, but see https://docs.microsoft.com/en-us/sql/connect/python/python-driver-for-sql-server?view=sql-server-2017

Python Driver for SQL Server

#

(apparently microsoft wants you to use pyodbc)

radiant helm Jul 17, 2018, 3:52 PM

#

Yeah, I've seen that, I'm just worried about security and how easy it would be for someone to intercept it unencrypted

patent glen Jul 17, 2018, 3:54 PM

#

https://stackoverflow.com/questions/28724427/can-pymssql-have-a-secure-connection-ssl-to-ms-sql-server maybe? apparently for pyodbc you just put Encrypt=yes in the connection string

radiant helm Jul 17, 2018, 3:55 PM

#

Ooh! I had totally missed that last comment about the encrypt setting! Thanks so much haha, sometimes you just need a second pair of eyes.

tacit dagger Jul 17, 2018, 5:42 PM

#

Hi. In one sqlite cell, i want to write both left to right and right to left. Like this:
Some ltr text
Then a horizontal line
Then some rtl text
Is it possible to do that?
My db is premade with db browser for sqlite application.

#

And when i fetch my results from db, some of them have { } around them. How can get rid of that? 😞

patent glen Jul 17, 2018, 6:23 PM

#

you're gonna have to be more specific about how you're fetching them and how you're printing them that puts the { } around them

#

and what do you mean "one sqlite cell"? sqlite is a database, it doesn't have cells or visual layout

#

can you post a screenshot of what you're talking about

tacit dagger Jul 17, 2018, 7:46 PM

#

https://paste.pound-python.org/show/djFlcyzq3S5ej3HV4fkW/

#

📎 Capture.PNG

#

Cells i mean those which i've shown in pics

rough river Jul 17, 2018, 8:20 PM

#

My cmd that reads the number of cmds used from a table but it returns [] when I use the command

patent glen Jul 17, 2018, 9:19 PM

#

@tacit dagger I don't see where you're seeing {}, and I still don't know what you mean by right to left text - are you wanting to also translate into hebrew or arabic?

tacit dagger Jul 17, 2018, 9:32 PM

#

yes i will later want to add persian translation to those cells

#

persian is rtl

rough river Jul 18, 2018, 1:10 AM

#

    @commands.command()
    @commands.guild_only()
    @commands.cooldown(1, 5, BucketType.user)   
    async def about(self, ctx):
        '''Gives information about the bot'''
        async with self.bot.pool.acquire() as connection:
            async with connection.transaction():
                data = self.bot.pool.fetch('SELECT count FROM uwu.cmdcount;')
                await ctx.send(f'{data}')

I changed my code now it sends <coroutine object Pool.fetch at 0x04E7DED0> whenever I run the command

torn sphinx Jul 18, 2018, 1:22 AM

#

🇦 🇼 🇦 🇮 🇹

#

also pretty sure you would use connection.fetch

rough river Jul 18, 2018, 1:26 AM

#

take out self.bot.pool and replace with connection

#

I put await before self and replaced self.bot.pool with connection

#

and now it sends []

#

@bot.event
async def on_command(ctx):
    async with bot.pool.acquire() as connection:
        async with connection.transaction():
            await connection.execute("UPDATE uwu.cmdcount SET count = count + 1;")
``` That might be my problem because the command seems fine I don't think that is right

torn sphinx Jul 18, 2018, 1:32 AM

#

well

#

hmm

rough river Jul 18, 2018, 1:33 AM

#

Does it look wrong

#

I tried looking at asyncpg examples but it doesn't help

#

Should I close the connection the example closes it I think I should

#

It gave me more errors with the connection close

deft yacht Jul 18, 2018, 2:14 AM

#

I hope some one here has worked with the casssandra..
i am running into the trouble..

I have a huge table around (100+ columns) and i am tranferring the data from the csv file to the cassandra..

but i am getting stuck at this error

```AttributeError: 'float' object has no attribute 'encode'````

how can i pinpoint the column which is causing this trouble?

versed coyote Jul 18, 2018, 2:17 AM

#

@deft yacht Can you share your code? https://paste.pydis.com/

deft yacht Jul 18, 2018, 2:17 AM

#

company's code. not mine.

versed coyote Jul 18, 2018, 2:17 AM

#

Ah, had a feeling

#

How are you doing the query?

#

And reading the CSV?

deft yacht Jul 18, 2018, 2:18 AM

#

something like this

df = pd.read_csv(path/to/csv

versed coyote Jul 18, 2018, 2:19 AM

#

So is Python automatically determining the types then?

deft yacht Jul 18, 2018, 2:19 AM

#

and then passing it to the function to insert such as this

                " " \```

#

nope. I have made the table in the cassandra with the help of the data types in the dataframe from the csv file

versed coyote Jul 18, 2018, 2:21 AM

#

Sorry, what I meant is are you just putting the data raw from the CSV into the query?

#

You might need to do some type casting before hand

deft yacht Jul 18, 2018, 2:21 AM

#

higher point of view

 from cassandra.cluster import Cluster
    cluster = Cluster(ip_address)
    session = cluster.connect(keyspace_name)
    query = "INSERT INTO data(date,time,open,high,low,last) VALUES (?,?,?,?,?,?)"
    prepared = session.prepare(query)```

for item in dataFrame:
session.execute(prepared, (item[0],item[1],item[2],item[3],item[4],item[5]))```

versed coyote Jul 18, 2018, 2:21 AM

#

Also, found this: https://bluntfox.com/questions/5054/

deft yacht Jul 18, 2018, 2:21 AM

#

You might need to do some type casting before hand
How do i know which column typecast and which not?

versed coyote Jul 18, 2018, 2:22 AM

#

That would be based on the types of the table in Cassandra itself

#

so if it's a varchar, then it should be a str, if it's decimal, then float

deft yacht Jul 18, 2018, 2:23 AM

#

where can i find more info about this?
%s,%s

versed coyote Jul 18, 2018, 2:23 AM

#

Those are string thingys...word is escaping me one sec

deft yacht Jul 18, 2018, 2:24 AM

#

i need to know for the other data types also

versed coyote Jul 18, 2018, 2:24 AM

#

string format operators

deft yacht Jul 18, 2018, 2:25 AM

#

so as the commentor said on that thread


query = "INSERT INTO table_str (rowid,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,aa,ab,ac,ad,ae,af,ag,ah,ai,aj,ak,al,am,an,ao,ap,aq,ar,as,at,au,av,aw,ax,ay,az,ba,bb,bc,bd) VALUES (uuid(),%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
for row in cassandra_df_list:
    session.execute(query, row)```

So the string operator are cassandra specific or python?

versed coyote Jul 18, 2018, 2:25 AM

#

general actually, but in this case Python

#

They're used in printf in C, print in Python, e.g. print("Hello " % my_name)

#

http://infohost.nmt.edu/tcc/help/pubs/python25/web/str-format.html

#

So %s is just saying "a string-type thing goes here"

deft yacht Jul 18, 2018, 2:27 AM

#

so for text, varchar it would be %s
for decimanl it would be %d
and for float it would be %f

versed coyote Jul 18, 2018, 2:27 AM

#

Yes

#

Here's a handy reference on the Cassandra types: https://docs.datastax.com/en/cql/3.3/cql/cql_reference/cql_data_types_c.html

CQL data types

Built-in data types for columns.

deft yacht Jul 18, 2018, 2:28 AM

#

i think i will modify my query a bit and see the result
can i tag you in say 12-15 hours here if i come across any problem?

versed coyote Jul 18, 2018, 2:28 AM

#

Sure thing

deft yacht Jul 18, 2018, 2:29 AM

#

what would be the string thing for the integer?

versed coyote Jul 18, 2018, 2:29 AM

#

%d

deft yacht Jul 18, 2018, 2:29 AM

#

okay.

versed coyote Jul 18, 2018, 2:30 AM

#

%d is python int, %f is python float, %s is python str

#

So if you read the cassandra table, when it says "integers", use int and %d, etc.

deft yacht Jul 18, 2018, 2:31 AM

#

what about the timestamps?

versed coyote Jul 18, 2018, 2:31 AM

#

The python driver would usually do the conversion automatically, but it doesn't do that with prepared statements

#

Those are datetime.datetime, at least thats what the driver takes

#

There are a few exceptions like those

#

I don't know what they all are though, might be worth digging around the driver docs or code a bit

deft yacht Jul 18, 2018, 2:32 AM

#

correct i am using prepared statement..
i hope this won't go fruitless

#

for time being i can leave it as ?

versed coyote Jul 18, 2018, 2:33 AM

#

Yes I think so

#

Good luck, hope it works out tomorrow

deft yacht Jul 18, 2018, 2:34 AM

#

let see.

#

thanks for the help! 😃

versed coyote Jul 18, 2018, 2:35 AM

#

np

rough river Jul 18, 2018, 2:35 AM

#

<coroutine object Pool.fetch at 0x04E7DED0> Do you think you can help me with that?

versed coyote Jul 18, 2018, 2:35 AM

#

What about it?

#

What is that from?

rough river Jul 18, 2018, 2:36 AM

#

What does it mean

versed coyote Jul 18, 2018, 2:36 AM

#

It's a python object

#

Looks like a fetch query for something, so probably a function object

rough river Jul 18, 2018, 2:37 AM

#

I'm trying to get my command to send the amount of commands used but when I use the command it sends that. Can I change that to understandable text

versed coyote Jul 18, 2018, 2:38 AM

#

Can you post the code?

#

https://paste.pydis.com/

#

Also might be better to ask in a help channel

rough river Jul 18, 2018, 2:39 AM

#

https://paste.pydis.com/edujiripaf.py

versed coyote Jul 18, 2018, 2:39 AM

#

So this is a discord.py thing?

rough river Jul 18, 2018, 2:39 AM

#

I think it's the sql part though

#

Yes it's for my bot

#

It's using asyncpg

versed coyote Jul 18, 2018, 2:40 AM

#

I don't know anything about discord.py or what it's using for a SQL backing

#

Maybe someone else can help, sorry

rough river Jul 18, 2018, 2:41 AM

#

Ok. No problem.

deft yacht Jul 18, 2018, 2:08 PM

#

does any one know what is the string format operator for the double and the boolean ?

patent glen Jul 18, 2018, 2:40 PM

#

@tacit dagger the same cells as English? Why wouldn't Persian be another column? And/or why do you want to show both English and Persian at the same time? Also, the sqlite browser or whatever that is isn't really meant for end-user presentation.

#

It's not really good to define your data schema solely based on how it looks in the UI.

tacit dagger Jul 18, 2018, 5:31 PM

#

ok i got it. so i will write my Persian data in another column. that's much better.

patent glen Jul 18, 2018, 5:31 PM

#

well, maybe

#

the other possibility would be to have a column for what language

#

like instead of Esperanto, English, have Esperanto, Definition, Language, and let Language be 'en' for english or 'fa' for persian, that'd be more scalable

#

you'd need to add filters or select distinct to your queries

tacit dagger Jul 18, 2018, 8:04 PM

#

yep that's better. i will consider this in future editions when i improved more in programming.

versed coyote Jul 19, 2018, 1:12 AM

#

@deft yacht double in Cassandra is a float in Python, %f

#

Bool I'm not sure

deft yacht Jul 19, 2018, 1:16 AM

#

i just used ? in both the cases 😃

versed coyote Jul 19, 2018, 1:23 AM

#

Cool

deft yacht Jul 19, 2018, 1:42 AM

#

but thanks for the helping hand!
really thank full.
maybe in future i will ask a lot of questions here 😄

versed coyote Jul 19, 2018, 2:26 AM

#

Definitely

#

That's our purpose, to help 👍

wind nexus Jul 19, 2018, 8:40 PM

#

anyone here atm that can help me with sqlalchemy

ionic pecan Jul 19, 2018, 8:45 PM

#

bot.tags['ask']

delicate fieldBOT Jul 19, 2018, 8:45 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.

wind nexus Jul 19, 2018, 8:47 PM

#

if i have

class Users(UserMixin, db.Model):
    __tablename__ = "users"
    user_id = db.Column(INTEGER, primary_key=True)
    details = db.relationship('Details', uselist=False, back_populates='user')

class Details(db.Model):
    __tablename__ = "details"
    id = db.Column(INTEGER, primary_key=True)
    first_name = db.Column(VARCHAR(255))
    last_name = db.Column(VARCHAR(255))
    user_id = db.Column(INTEGER, db.ForeignKey('users.user_id'))
    user = db.relationship("Users", back_populates="details")

#

can i set the first name and last name with

User.details(first_name=first_name,last_name=last)

#


current_user.details(first_name=first_name,last_name=last)
db.session.add(current_user.details)
db.session.commit()

#

thats weird

ionic pecan Jul 19, 2018, 8:51 PM

#

details isn't callable, I believe

#

http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#working-with-related-objects

#

you need to do something like current_user.details = Details(first_name=first_name, last_name=last)

wind nexus Jul 19, 2018, 8:52 PM

#

hmm ho wabout the add/commit?

ionic pecan Jul 19, 2018, 8:58 PM

#

I don't know, what about it?

wind nexus Jul 19, 2018, 8:58 PM

#

do i need to do the add thing

#

as the user already does exists in db

#

and im just adding the details

ionic pecan Jul 19, 2018, 9:00 PM

#

If you modify the .details attribute on current_user, the session will notice and the user will be marked as dirty. On the next commit, it's going to write the changes you made to the database

#

See http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#adding-and-updating-objects

wind nexus Jul 19, 2018, 9:00 PM

#

ooh

#

hmm i made some changes to my db structure, added some fields to tables, how should i do the migration as when i try to do flask db migrate i get Table 'skills' is already defined for this MetaData instance. Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

ionic pecan Jul 19, 2018, 9:02 PM

#

did you put the same table in one migration twice?

wind nexus Jul 19, 2018, 9:02 PM

#

?

ionic pecan Jul 19, 2018, 9:03 PM

#

sorry, badly phrased. Your migrations are independent of your app, correct?

#

As in, they aren't loaded when you start your app

wind nexus Jul 19, 2018, 9:04 PM

#

yeah i do them manually

#

its a early testing version

ionic pecan Jul 19, 2018, 9:05 PM

#

well, the error sounds like you have two definitions of that table, which doesn't sound like a good idea. If you're in early testing then you can probably just add new columns and use create_all or whatever the function was named again, but I recommend just using migrations regardless

wind nexus Jul 19, 2018, 9:05 PM

#

hmm i reverted all changes on the models.py file that had all database classes and now it allowed my to run the db reset script i have

#

now to readd changes and run migrate

ionic pecan Jul 19, 2018, 9:06 PM

#

I didn't have my bot's migrations properly worked out when I started writing them either, but whether it executes 30ms faster or slower doesn't really bother me :P

📎 unknown.png

wind nexus Jul 19, 2018, 9:08 PM

#

im a idiot, i had a leftower class there that i used to copypaste stuff

ionic pecan Jul 19, 2018, 9:09 PM

#

haha

wind nexus Jul 19, 2018, 9:14 PM

#

YESYESYESYSE i got it working properly and adding the right DATA

#

hmm, it keeps adding a new row to the details table every time i submit a form and sets the userid on the latest only

#

how can i make it to edit the existing field instead

ionic pecan Jul 19, 2018, 9:30 PM

#

I think you can check if the reference (e.g. user.details) is None

#

and well, if it isn't, there's already an associated object and you can update that

wind nexus Jul 19, 2018, 9:32 PM

#

hmm maybe i could do it the same way i do the password:



class Users(UserMixin, db.Model):
    __tablename__ = "users"
    user_id = db.Column(INTEGER, primary_key=True)
    email = db.Column(VARCHAR(255), index=True, unique=True)
    password_hash = db.Column(VARCHAR(255))
    skills = db.relationship('Skills')
    details = db.relationship('Details', uselist=False, back_populates='user')


    def set_password(self, password):
        salt = secrets.token_urlsafe(16)
        argonType = argon2.low_level.Type.D
        self.password_hash = argon2.low_level.hash_secret(
            password.encode(), salt.encode(), 1, 8, 1, 64, argonType)

#

would it be like


def set_details(self, first_name, last_name):
    self.details= Details(first_name=first_name, last_name=last_name)

#

and have that under Users class?

ionic pecan Jul 19, 2018, 9:35 PM

#

That would create a new associated details object every time, even if there's already one

wind nexus Jul 19, 2018, 9:35 PM

#

hmm

ionic pecan Jul 19, 2018, 9:36 PM

#

I really don't know right now whether self.details would just be None if there's none associated with the user yet, but if yes, you could try:

def set_details(self, first_name, last_name):
    if self.details is None:
        self.details = Details(first_name=first_name, last_name=last_name)
    else:
        self.details.first_name = first_name
        self.details.last_name = last_name

deft yacht Jul 19, 2018, 9:36 PM

#

cross posting from the #help-croissant

alright.. got few questions...
i am trying to move my data from the CSV which is more or less treated as a dataframe..

I have a column which has int values.
and even when i did the dataframe.dtype it comes out the int64 for that column

but when i run the script to actually transfer the data from the dataframe (CSV) to the cassandra

I am getting this error

TypeError: Received an argument of invalid type for column "columnName". Expected: <class 'cassandra.cqltypes.FloatType'>, Got: <type 'str'>; (required argument is not a float)

any help

wind nexus Jul 19, 2018, 9:37 PM

#

OOOh so i can call those columns individually

deft yacht Jul 19, 2018, 9:40 PM

#

is that for me?

wind nexus Jul 19, 2018, 9:41 PM

#

sorry no

ionic pecan Jul 20, 2018, 11:24 AM

#

Is there a way to reduce PostgreSQL memory usage? My cluster is using around 200 MB of memory, but my database is like 20 MB big at most. IIRC postgres forks for connections, and one of my apps uses a pool of ten connections. Is it a smart idea to reduce pool size?

viral crag Jul 20, 2018, 11:28 AM

#

That seems like about the size you'd expect

ionic pecan Jul 20, 2018, 11:30 AM

#

Hmm

#

Not sure if I really need 10 connections though

#

The only somewhat expensive thing I'm doing is running a SELECT on each message event from Discord

#

but that's rather fast

torn sphinx Jul 20, 2018, 6:26 PM

#

Hello I use SQLite, how can I check if a record already exist in a table? for now I use

user_id_check = ctx.message.author.id
c.execute('SELECT * FROM users WHERE "user_id" = ?', (user_id_check,))
for row in c.fetchall():
    #my code
return await bot.say("{}, You are not registered".format(ctx.message.author.mention))```But I think its not right

ionic pecan Jul 20, 2018, 6:29 PM

#

https://stackoverflow.com/questions/4253960/sql-how-to-properly-check-if-a-record-exists#4254003

Stack Overflow

Sql: How to properly check if a record exists

Reading some SQL Tuning documentation I found this:

Select count(*) :

Counts the number of rows
Often is improperly used to verify the existence of a record

Is Select count(*) really that b...

torn sphinx Jul 20, 2018, 6:43 PM

#

c.execute('SELECT COUNT(1) FROM users WHERE user_id=?',(user_id_check,))
    for asd in c.fetchall():
        print(asd)```
So I have this it will print 0 if the userdoesn exist. and it will be 1 if the user exists So how can I make it say when the user doesnt exist "You are not registered" same if exists "You are already registered".

torn sphinx Jul 20, 2018, 7:25 PM

#

This wont work

user_id_check = ctx.message.author.id
c.execute('SELECT COUNT(1) FROM users WHERE user_id=?',(user_id_check,))
data = c.fetchall()
print(data)
if data is 0:
    return await bot.say("You are not registered.")
else:
    await bot.say("You are registered.")```

#

print(data)
[(0,)]

#

@ionic pecan You still there?

ionic pecan Jul 20, 2018, 7:33 PM

#

it returns a single row with a single column telling you how many matching rows it found

#

if you're working with discord.py then working with lists and tuples hopefully isn't an issue

torn sphinx Jul 20, 2018, 9:16 PM

#

btw why dont you do c.fetchone() instead

wind nexus Jul 20, 2018, 11:17 PM

#

what am i doing wrong here:

 currentSkills = current_user.skills.query.filter_by(skill_name=skillName).first()

ornate abyss Jul 21, 2018, 2:11 PM

#

What's the error

torn sphinx Jul 21, 2018, 2:59 PM

#

hi

#

how can i do to read a firebase database ç

#

?

patent glen Jul 21, 2018, 10:14 PM

#

@torn sphinx look at the python-firebase package on pypi

torn sphinx Jul 22, 2018, 10:17 AM

#

thx

torn sphinx Jul 22, 2018, 4:36 PM

#

I've created a dabase for an RPG game. I added a table called users and inside there I have peoples ID xp coins etc. Now I need to add weapons. and inside there I will add weapons but how?
How can I add more than one weapon inside weapons field? I only have item1 how can I add item 2 insde there too?

#

📎 unknown.png

dull scarab Jul 22, 2018, 4:38 PM

#

You could create an inventory table of sorts that links an item and an user

#

id        item id
1        1
1        4
2        1```etc

torn sphinx Jul 22, 2018, 4:42 PM

#

But if I want to print out someones items with

for items in inventory:
    print(items)
``` it will also print their id :/

#

or maybe I can use
print(items[1:]) idk if that will work

#

nope that will just remove 1 number from his id :/

dull scarab Jul 22, 2018, 4:49 PM

#

Well, I don't know how your databases are structures, nor what db you're using

torn sphinx Jul 22, 2018, 4:50 PM

#

SQLite

dull scarab Jul 22, 2018, 4:50 PM

#

Inventory would, in my example, link 2 other tables like user <-> items

#

So you could keep a database of all items (not connected to any user)

What does your database look like? (e.g. is it ``` id | name | something_else |

What does your database look like? (e.g. is it ```
id | name | something_else |