#databases | Python | Page 68

torn sphinx Jun 30, 2019, 7:27 PM

#

buc the file has a space

#

ill removve it

#

21 sewc

#

same error

#

Traceback (most recent call last):
  File "C:\Users\conno\Desktop\NiteSmells\idlebot.py", line 6, in <module>
    data = json.load(open(" C:/Users/conno/Desktop/NiteSmells/users.json", "r"))
OSError: [Errno 22] Invalid argument: ' C:/Users/conno/Desktop/NiteSmells/users.json'
>>>

gilded narwhal Jun 30, 2019, 7:29 PM

#

also conno. is that supposed to be connor?

torn sphinx Jun 30, 2019, 7:29 PM

#

nope

#

lool

gilded narwhal Jun 30, 2019, 7:29 PM

#

oh this is windows

torn sphinx Jun 30, 2019, 7:29 PM

#

it was a typo ages ago

gilded narwhal Jun 30, 2019, 7:29 PM

#

try backslashes

torn sphinx Jun 30, 2019, 7:29 PM

#

and idk how to change iut

#

lol

#

wdum?

gilded narwhal Jun 30, 2019, 7:30 PM

#

r'C:\Users\conno\Desktop\NiteSmells\users.json'

#

the r is important

#

when you use backslashes for paths

torn sphinx Jun 30, 2019, 7:30 PM

#

ok

#

📎 unknown.png

gilded narwhal Jun 30, 2019, 7:31 PM

#

those are still forward slashes

#

and the space is still there

sudden solstice Jun 30, 2019, 7:31 PM

#

pretty sure the space is what's breaking it

#

I tried reproducing on my PC

#

with space, broke

#

without space, worked

torn sphinx Jun 30, 2019, 7:31 PM

#

noo

#

ive removed trhe space

#

and removed the space in the folder

#

i removed the space?

gilded narwhal Jun 30, 2019, 7:32 PM

#

your screenshot says otherwise

sudden solstice Jun 30, 2019, 7:32 PM

#

the picture you sent still has it

#

Your picture says:
" C:"
it should be:
"C:"

torn sphinx Jun 30, 2019, 7:32 PM

#

data = json.load(open(r"C:\Users\conno\Desktop\NiteSmells\users.json", "r"))

#

like this?

gilded narwhal Jun 30, 2019, 7:33 PM

#

yea

torn sphinx Jun 30, 2019, 7:33 PM

#

a diff error

gilded narwhal Jun 30, 2019, 7:33 PM

#

which is?

torn sphinx Jun 30, 2019, 7:33 PM

#

  File "C:\Users\conno\Desktop\NiteSmells\idlebot.py", line 6, in <module>
    data = json.load(open(r"C:\Users\conno\Desktop\NiteSmells\users.json", "r"))
  File "C:\Users\conno\AppData\Local\Programs\Python\Python37\lib\json\__init__.py", line 296, in load
    parse_constant=parse_constant, object_pairs_hook=object_pairs_hook, **kw)
  File "C:\Users\conno\AppData\Local\Programs\Python\Python37\lib\json\__init__.py", line 348, in loads
    return _default_decoder.decode(s)
  File "C:\Users\conno\AppData\Local\Programs\Python\Python37\lib\json\decoder.py", line 337, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "C:\Users\conno\AppData\Local\Programs\Python\Python37\lib\json\decoder.py", line 355, in raw_decode
    raise JSONDecodeError("Expecting value", s, err.value) from None
json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)
>>>

sudden solstice Jun 30, 2019, 7:33 PM

#

that just means you've formatted your json the wrong way

gilded narwhal Jun 30, 2019, 7:34 PM

#

try adding this as a param at the end of open: encoding='utf-8'

torn sphinx Jun 30, 2019, 7:34 PM

#

param?

#

im dumb

gilded narwhal Jun 30, 2019, 7:34 PM

#

short for parameter

#

or argument

#

open(blah, blah, encoding='utf-8')

torn sphinx Jun 30, 2019, 7:35 PM

#

were do i add that

sudden solstice Jun 30, 2019, 7:35 PM

#

wait connor, is there anything in your users.json? if so, can you send it? I'm 99% sure that's the reason it's erroring

gilded narwhal Jun 30, 2019, 7:35 PM

#

instead of this:
open(r"C:\Users\conno\Desktop\NiteSmells\users.json", "r")
youd do this:
open(r"C:\Users\conno\Desktop\NiteSmells\users.json", "r", encoding="utf-8")

torn sphinx Jun 30, 2019, 7:36 PM

#

  File "C:\Users\conno\Desktop\NiteSmells\idlebot.py", line 6, in <module>
    data = json.load(open(r"C:\Users\conno\Desktop\NiteSmells\users.json", "r", encoding="utf-8"))
  File "C:\Users\conno\AppData\Local\Programs\Python\Python37\lib\json\__init__.py", line 296, in load
    parse_constant=parse_constant, object_pairs_hook=object_pairs_hook, **kw)
  File "C:\Users\conno\AppData\Local\Programs\Python\Python37\lib\json\__init__.py", line 348, in loads
    return _default_decoder.decode(s)
  File "C:\Users\conno\AppData\Local\Programs\Python\Python37\lib\json\decoder.py", line 337, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "C:\Users\conno\AppData\Local\Programs\Python\Python37\lib\json\decoder.py", line 355, in raw_decode
    raise JSONDecodeError("Expecting value", s, err.value) from None
json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)
>>> 
Traceback (most recent call last):
  File "C:\Users\conno\Desktop\NiteSmells\idlebot.py", line 6, in <module>
    data = json.load(open(r"C:\Users\conno\Desktop\NiteSmells\users.json", "r", encoding="utf-8"))
  File "C:\Users\conno\AppData\Local\Programs\Python\Python37\lib\json\__init__.py", line 296, in load
    parse_constant=parse_constant, object_pairs_hook=object_pairs_hook, **kw)
  File "C:\Users\conno\AppData\Local\Programs\Python\Python37\lib\json\__init__.py", line 348, in loads
    return _default_decoder.decode(s)
  File "C:\Users\conno\AppData\Local\Programs\Python\Python37\lib\json\decoder.py", line 337, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "C:\Users\conno\AppData\Local\Programs\Python\Python37\lib\json\decoder.py", line 355, in raw_decode
    raise JSONDecodeError("Expecting value", s, err.value) from None
json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)
>>>

sudden solstice Jun 30, 2019, 7:36 PM

#

can you show the file

#

pls

#

users.json

#

open it and send what it has

#

if it has nothing, just put a {} in it

#

and run your code

#

that will fix it

#

that error occurs when the json file is empty, or there's some really bad formatting

#

you can also use online json validators to check if your json file is formatted correctly: https://jsonformatter.curiousconcept.com/

JSON Formatter & Validator

Format and validate JSON data so that it can easily be read by human beings.

torn sphinx Jun 30, 2019, 7:39 PM

#

ohh

#

omg

#

im dumb

#

i forgot i didnt add it

#

HashTagFunBOTToday at 20:40
:white_check_mark: - Online
Developed by Connor#3969```

#

yeet

#

then another question

#

@sudden solstice

sudden solstice Jun 30, 2019, 7:42 PM

#

whats up?

torn sphinx Jun 30, 2019, 7:42 PM

#

so im doing a balance system

#

how do i make it so it outputs a balance

sudden solstice Jun 30, 2019, 7:42 PM

#

let's move to a help channel, 2 sec

#

I'll @ you

torn sphinx Jun 30, 2019, 7:42 PM

#

kk

nova hawk Jul 1, 2019, 4:09 PM

#

What flavour of sql is this?

patent glen Jul 1, 2019, 5:02 PM

#

you can't use placeholders for columns

#

where is the column name coming from? if it's external input you need to make sure it's an actually valid name

#

and then... well tbh this is an unusual thing to want to do, and maybe rethink your database schema, but you could include the column name with an f-string or other form of string formatting

cerulean pendant Jul 1, 2019, 5:58 PM

#

sqlalchemy can build queries like this for you, even without using the orm part

lucid fulcrum Jul 1, 2019, 6:22 PM

#

@pliant cedar We need to know the latter part ||sorry for the ping @I'm Vengeance||

#

If you, for example, inserted a string value without quotes

torn sphinx Jul 1, 2019, 7:27 PM

#

@lucid fulcrum no problem

dawn pulsar Jul 1, 2019, 7:59 PM

#

I've got comfortable with using SELECT on someone elses database (MariaDB) but I'd like to host my own, any one have a suggestions on where to start?

#

It'd just have like 3 columns, discord_id, reason and datetime

dawn pulsar Jul 1, 2019, 9:59 PM

#

Ok then, guess I'll just die

torn sphinx Jul 1, 2019, 11:56 PM

#

SQLAlchemy

cerulean pendant Jul 1, 2019, 11:56 PM

#

... is pretty good

torn sphinx Jul 1, 2019, 11:56 PM

#

indeed

zenith dragon Jul 2, 2019, 12:28 AM

#

What's a good library for interfacing with DB, where I want to allow the user, at runtime, to add tables or columns to tables? There will still be schemas and relationships, so it's far from unstructured

cerulean pendant Jul 2, 2019, 3:33 AM

#

sqlalchemy

void otter Jul 2, 2019, 8:44 AM

#

depends what db you have @zenith dragon

zenith dragon Jul 2, 2019, 10:52 AM

#

@void otter haven't settled on anything yet. Leaning towards psycopg2/postgreSQL, but I'm still flexible. @cerulean pendant I've used SQLAlchemy, but dont my Pythin objects need to match the DB schema for that? I dont know how to dynamically add columns/tables with SQLAlchemy

cerulean pendant Jul 2, 2019, 10:53 AM

#

@zenith dragon there's always a level of compromise

#

adding columns in a structured rdbms table has a huge cost

#

but they are really good at relationships and joins

zenith dragon Jul 2, 2019, 10:55 AM

#

The user-defined fields will be a pretty necessary part of this

void otter Jul 2, 2019, 10:56 AM

#

i have used mysql and postgres and i'd recommend postgres cuz it's easier to setup

zenith dragon Jul 2, 2019, 10:57 AM

#

Psycopg2 still the best library for interfacing with Postgres?

void otter Jul 2, 2019, 11:03 AM

#

yes

#

best and only lol

surreal venture Jul 2, 2019, 11:59 AM

#

speaking of postgres, anyone had this problem with google cloud and cloudsql:

django.db.utils.OperationalError: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/cloudsql-proxy-socket/.s.PGSQL.5432"?

void otter Jul 2, 2019, 11:59 AM

#

nope

#

make sure your ORM is up to date and your URI is correct

surreal venture Jul 2, 2019, 12:00 PM

#

they are, I have 2 other clusters running with this config but the third craps out with this error, I'm out of ideas

void otter Jul 2, 2019, 12:01 PM

#

2 workers can connect but 3rd can't?

surreal venture Jul 2, 2019, 12:02 PM

#

I have 3 different clusters for production, staging and now I wanted to created 3rd for dev

#

I have separate databases for each

#

but all in the same region

#

I'm using gke should've started with that

void otter Jul 2, 2019, 12:04 PM

#

i think your uri is incorrect

#

try connecting manually

surreal venture Jul 2, 2019, 12:05 PM

#

I have cloudsql container in my pod creating the websocket file in a volume shared with django instance, django can see the file but can't connect with it

void otter Jul 2, 2019, 12:06 PM

#

umm

#

never used sockets with django

#

but my guess is permissons or uri

#

try googling i'm out of ideas

surreal venture Jul 2, 2019, 12:06 PM

#

cloudsql seems to connect fine cause when I change the uri it gives errors

#

yeah I did 😦

void otter Jul 2, 2019, 12:07 PM

#

i'm sorry ask on reddit/SO

surreal venture Jul 2, 2019, 2:27 PM

#

ok for posterity I have all my passwords encoded in base64 yaml and the db password had a newline at the end which doesn't show up with printenv, I'm gonna take a walk of shame now

void otter Jul 2, 2019, 2:53 PM

#

Gg

wintry aspen Jul 2, 2019, 4:36 PM

#

Hello, How can I update my pgadmin from my terminal ? is it possible ?

#

I'm on mac

ancient warren Jul 2, 2019, 6:53 PM

#

Hi,
i have a table containing items that is structured like this:

CREATE TABLE items(
    item_id tinyint AUTO_INCREMENT PRIMARY KEY,
    price_quantity mediumint,
    price_item_id smallint,
    trader_id tinyint,
    FOREIGN KEY (trader_id) REFERENCES traders(trader_id)    
);

Every item has a reference to another item_id that is its price. Now i need some items to have two separate price_item_ids and price_quantities as its price. How can i account for that?

Sorry if the question is unclear or the answer is obvious. I am very new to SQL.

ancient warren Jul 2, 2019, 7:12 PM

#

Never mind i am just going to create an extra table for the price references. Who cares.

#

Thanks anyway.

astral geode Jul 2, 2019, 8:45 PM

#

@undone viper hello gamer

surreal venture Jul 2, 2019, 11:07 PM

#

holy crap theres another Ben Dover

pure scroll Jul 3, 2019, 8:02 AM

#

does anyone got experience with Gino? is there a way to explicitly pass connection to query statements in it ?
e.g when i'm doing

await User.create(id='foo', name='bar')

how do I specify which connection to use?

pure scroll Jul 3, 2019, 8:23 AM

#

to clarify
e.g i can do the following

async with db.acquire() as connection:
   async with connection.transaction():
       await User.create(id='foo', name='bar')

how do I know that this User.create statement is executed within the same connection which I've created?
I mean in the end it has a connection pool, and somehow there should be a way to specify which connection to use, just having issues to find it.

#

okay, nevermind it's using python 3.7 feature of contextvars for coroutines

pliant pendant Jul 4, 2019, 7:18 PM

#

oi bimbles

#

or anyone*

#

i want postgreSQL to select multiple items within a column table

#

that matches multiple results

#

lets say the column contains

#

[stone, iron, gold, copper, diamond, scott]

#

if i search for [gold, scott] i want the result to appear

#

but if i search [gold, scoop], the result shouldn't be existant

rich trout Jul 5, 2019, 10:09 AM

#

https://www.postgresql.org/docs/9.3/functions-matching.html

viral crag Jul 6, 2019, 8:05 AM

#

Has there been any movement towards a proper asyncio ORM lately?

#

The best I can find is tortoise but it doesn't do migrations

void otter Jul 6, 2019, 9:16 AM

#

Idk if SQLAlchemy supports async
I know it does with postgres

viral crag Jul 6, 2019, 9:17 AM

#

No

#

I don't want an ORM that is synchronous-only and has somehow been retrofitted to asyncio

void otter Jul 6, 2019, 9:18 AM

#

Ah okay

#

I am not aware of any proper async orm

solid void Jul 6, 2019, 9:27 AM

#

do you have real concerns with how sqlalchemy does asyncio, or just assume it's not able to do it properly because it wasn't designed to do it in the first place?

#

(i don't do asyncio, but sqlalchemy is quite an awesome ORM, if you have a good reason not to use it, sure, but make sure it's a good reason)

viral crag Jul 6, 2019, 9:40 AM

#

It's more that I'd rather everything is designed to work together

solid void Jul 6, 2019, 10:26 AM

#

don't we all, but part of the job of a programmer is to take things that were designed to work well on their own, and make them work together, so i would either, look for feedback from people who did try to use sqlalchemy in an asyncio program, and how that worked, or try to build a simple proof of concept of it and see if there is anything seriously wrong about it

viral crag Jul 6, 2019, 10:50 AM

#

Oh, I know, I've been doing this for 11 years now

#

As far as I know, sqlalchemy-asyncio only covers the query builder and not the ORM

solid void Jul 6, 2019, 11:13 AM

#

yeah saw that, it's a pity, the orm part is nice

celest perch Jul 6, 2019, 12:20 PM

#

Hey everyone , I have a small question for you . I have a big data around over 10 milion row. So Some row contain only 1 array and some of them 2 or 3 . I cant look all of them. How can I find max array with SQL. So I need max array size. Thanks a lot

oak pebble Jul 6, 2019, 7:47 PM

#

im getting a strange error with sqlalchemy

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: user
[SQL: SELECT user.id AS user_id, user.username AS user_username, user.email AS user_email, user.password_hash AS user_password_hash
FROM user
WHERE user.username = ?
 LIMIT ? OFFSET ?]
[parameters: ('foo', 1, 0)]
(Background on this error at: http://sqlalche.me/e/e3q8)
127.0.0.1 - - [06/Jul/2019 12:47:40] "POST /signup HTTP/1.1" 500 -

#

i have this

@app.route("/signup", methods=["GET", "POST"])
def signup():
    if request.method == "GET":
        return render_template("signup.html")
    elif request.method == "POST":
        username = request.form["username"]
        email = request.form["email"]
        password = request.form["password"]
        confirm_passsword = request.form["confirm_password"]

        if not all((username, email, password, confirm_passsword)):
            flash("You must fill in all fields", "danger")
            return render_template("signup.html")
        elif password != confirm_passsword:
            flash("Passwords do not match", "danger")
            return render_template("signup.html")
        elif User.query.filter_by(username=username).first():
            flash("Username is already taken")
            return render_template("signup.html")

        user = User(username=username, email=email)
        user.set_password(password)
        db.session.add(user)
        db.session.commit()

        flash("Signup successful! You may now log in with your new credentials", "success")
        return redirect(url_for("login.html"))

#

also this

from werkzeug.security import generate_password_hash
from werkzeug.security import check_password_hash
from flask_login import UserMixin
from app import db
from app import login

class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), index=True, unique=True)
    email = db.Column(db.String(120), index=True, unique=True)
    password_hash = db.Column(db.String(128))

    def set_password(self, password):
        self.password_hash = generate_password_hash(password)

    def check_password(self, password):
        return check_password_hash(self.password_hash, password)

    def __repr__(self):
        return f"User(username={self.username}, email={self.email})"

@login.user_loader
def load_user(id):
    return User.query.get(int(id))

and this

import os
from flask import Flask
from flask_login import LoginManager
from app.config import Config
from flask_sqlalchemy import SQLAlchemy

flask_app = Flask(__name__)
flask_app.config.from_object(Config)
db = SQLAlchemy(flask_app)
db.create_all()
login = LoginManager(flask_app)

from app import routes, models

def run():
    port = int(os.environ.get("PORT", 5000))
    flask_app.run(host="0.0.0.0", port=port)

void otter Jul 6, 2019, 8:16 PM

#

In models

#

Try __tablename__ = 'user'

#

Under class User()...

solid void Jul 6, 2019, 9:53 PM

#

did you create a migration (for example, with alambic) to create the missing table in the db, or at least use create_all from sqlalchemy for that?

void otter Jul 6, 2019, 9:53 PM

#

oh yeah

#

if you have no or irrelevant data, do :db.drop_all();db.create_all()

#

and also, in your run file don't put db.create_all()

oak pebble Jul 6, 2019, 10:00 PM

#

thanks that fixed it

#

i put it at the end of the models instead

void otter Jul 6, 2019, 10:30 PM

#

don't put db.drop_all() or db.create_all() in your files at all

#

once you create your modeled database, it's there and there to stay

celest perch Jul 7, 2019, 10:31 AM

#

#

I have two question with sparkSQL its kind of SQL anyway

#

When I do like ; sqlContext.sql("SELECT tags[0].value FROM osmWay WHERE tags[0].key='amenity'").distinct().collect()

#

I have output ; [Row(tags[0].value='bench'), Row(tags[0].value='marketplace'), Row(tags[0].value='arts_centre'), Row(tags[0].value='porters_cubicle'), Row(tags[0].value='bicycle_parking'), Row(tags[0].value='college')] ...

#

Fİrst I want count that distinct variable like bench 100 , marketplace 80 , arts_centre 67

#

And second things due to structure how can I want check every column nt only tags[0] how can I Do that

torn sphinx Jul 8, 2019, 1:54 AM

#

hi

#

does LIKE cover partial file patterns?

gilded narwhal Jul 8, 2019, 8:48 AM

#

@torn sphinx do you mean like using wildcards? eg *.txt?

torn sphinx Jul 8, 2019, 10:03 AM

#

no like.. if I had part of the file path

#

WHERE pattern LIKE 'partial/path'

#

would this cover it?

void otter Jul 8, 2019, 10:07 AM

#

Like if he wants hammer and types amme would it return hammer

pure scroll Jul 8, 2019, 10:54 AM

#

like does cover that, but index won't work in such case. Means if your table grows too much every time you do such a query it will iterate over every record to find the result

#

which might get too slow

#

if you are using postgres there is way to build such an index, it will require pg_trgm extension which allows you to build trigram index. Those can handle a case of full text search

torn sphinx Jul 10, 2019, 2:12 AM

#

it's one column that contains filepaths, so just wondering if LIKE is sufficient for this

gilded narwhal Jul 10, 2019, 2:14 AM

#

thats what im using in one of my projects

#

it's very fast

#

across ~100k files

pure scroll Jul 10, 2019, 8:38 AM

#

@torn sphinx like would work with it, if you don't have a wildcard on the left side of the statement e.g you are looking for something% then normal index would also work fine with thtat

#

but if you need to also have a wildcard on the left side and maybe have a case insensitive like (ilike) then you might need this special index type

#

also you should consider the number of records you have, if we are talking about numbers that a less than 1 million, you should not be worry at all

#

most likely it will work fine without any index, but you should test it first and see if you hardware can handle amounts of data you have

dawn pulsar Jul 10, 2019, 5:02 PM

#

Will someone help me with a SELECT query?

#

I have 2 tables, one called players and one called approved_users
approved_users holds their uuid and time (when they joined)
players holds their uuid and name

I'd like to get all the people who joined within the last day, but get their names, rather then their uuid
Anyone know how I can do that?

patent glen Jul 10, 2019, 5:04 PM

#

join

dawn pulsar Jul 10, 2019, 5:04 PM

#

I tried this
"SELECT players.name, approved_users.time FROM approved_users LEFT JOIN players ON approved_users.uuid=players.uuid WHERE approved_users.time>%s", (one_day_ago,)

#

But I don't really know what I'm doing

patent glen Jul 10, 2019, 5:04 PM

#

players.name not approved_users.name

dawn pulsar Jul 10, 2019, 5:05 PM

#

But it's FROM approved_users?

#

Is that not how this works?

#

¯_(ツ)_/¯

patent glen Jul 10, 2019, 5:05 PM

#

it's from both

#

the whole thing with the joins is the from clause

dawn pulsar Jul 10, 2019, 5:05 PM

#

Oh ok

#

I got itttt

#

@patent glen ❤ Thanks!

floral verge Jul 11, 2019, 10:36 AM

#

anyone here could explain me why

def get_db():
    if 'db' not in g:
        g.db = sqlite3.connect('happy.db')
    return g.db

@app.teardown_appcontext
def teardown_db():
    db = g.pop('db', None)
    if db is not None:
        db.close()

gives an error saying that
TypeError: teardown_db() takes 0 positional arguments but 1 was given

#

working with flask

#

the code is a copy paste from flask docs

#

or maybe a better question is
how do i close my db when i close my webapp?
my app is very small and is only for me so i dont open/close it every time a user does something, i just keep it running

pastel kraken Jul 11, 2019, 10:43 PM

#

Hello! Is SQLite the recommended module for SQL database queries? It seems like there are a few different options to choose from. Sorry if this is the wrong place to ask

cerulean pendant Jul 11, 2019, 10:48 PM

#

@pastel kraken sqlite is a database engine. sqlite3 is the module to use sqlite databases

#

there are other sql database engines, like postgresql, mysql, sql server

#

for each one you would use a different module

#

And there is sqlalchemy which is a module that abstracts SQL so that you deal with python objects and it uses the correct module for you under the hood

pastel kraken Jul 11, 2019, 10:54 PM

#

Does it help that I'm not looking to edit or alter any databases, I just need to query them? Currently using Microsoft SQL Server to make my queries, so would sql server be my best bet?

sharp vector Jul 11, 2019, 10:58 PM

#

Anyone have any experience with MongoDB?

#

I started up a cluster on MongoDB Atlas and am trying to connect to it using their driver

#

and it keeps timing out

#

I have very little experience with Mongo (or databases in general) and would really appreciate any help!

#

The specific error I'm getting is pymongo.errors.ServerSelectionTimeoutError: connection closed,connection closed,connection closed

#

all I'm running is

client = pymongo.MongoClient(
    "MY CONNECT URL")
db = client.admin
pprint(db.command("serverStatus"))

#

The URL also seems to be properly encoded (I just used an online URL encoder)

sharp vector Jul 12, 2019, 2:54 AM

#

Figured it out. I hadn't whitelisted my IP address

torn sphinx Jul 12, 2019, 10:47 AM

#

Is there a condition for WHERE that would fit any row? I have a criteria for search that could be omitted

#

Nevermind, just wrote two separate queries

stable violet Jul 12, 2019, 1:04 PM

#

Does anyone have a guide for DB layout and structure or mind taking a look at a test outline I've made? I'm trying to ensure I have a better design than past iterations for the refactor I'm currently doing

sharp cypress Jul 12, 2019, 1:27 PM

#

Anyone know SQLite syntax?
I have an insert like this:

And I want to only insert if the user_id doesn't already exist in the table
How would I do it?

#

Tag me if you know the answer cheers

floral verge Jul 12, 2019, 1:39 PM

#

@sharp cypress you would have to do this on the level between the sql and the user i believe

gleaming frost Jul 12, 2019, 2:33 PM

#

https://i.mcaq.me/rr89.png

#

I'm just starting to learn SQL and I am planning by table structure

#

My issue is the classes and teachers, it seems really inefficient to do it this way

#

But is it acceptable?

floral verge Jul 12, 2019, 5:41 PM

#

@gleaming frost im not exactly sure what you mean. sql works with relational databasees so you can split your table if it feels inefficient and connect them on a certain id

stable violet Jul 12, 2019, 6:12 PM

#

@gleaming frost could you have simply a teacher column and class column? From there each student is associated with Teacher# (or teacher name) and Class# (or class name)

gleaming frost Jul 12, 2019, 6:12 PM

#

i went with a dual table structure

#

Year, id, name etc in one

#

And id, classes and teachers in another

#

Where the id is a foreign key

severe iris Jul 13, 2019, 2:21 AM

#

does MongoDB have a time limit for the free cluster?

lucid fulcrum Jul 13, 2019, 4:23 PM

#

You would likely have a table classes, a table users and a table teachers

frozen osprey Jul 13, 2019, 7:14 PM

#

Hey. What is this error?

raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: WriteError: The field 'good_answers1' must be an array but is of type object in document {_id: ObjectId('5d2a20cc94635848c4cb1402')}

#

I used this

answer_author = message.author.id

                    await db.contest_coll.update_one({'guild': message.guild.id},
                                                     {"$push":
                                                          {f"good_answers{contest}":
                                                               {str(answer_author): 1}

                                                            }
                                                        }
                                                    )

#

contest = 1

#

@severe iris As far as I'm concerned, no, but u can have only 1 free cluster per organization

bright ivy Jul 13, 2019, 7:55 PM

#

Anyone here has experience with async postgresql and SQL Alchemy? would you guys say that its ready for prime time?

cerulean pendant Jul 13, 2019, 8:23 PM

#

@bright ivy sqlalchemy's architecture is composed of two things, expression language and ORM

#

the expression language works nicely with async

#

ORM doesn't

#

so if you stick to the expression language part, you can use it; it is worth it already

bright ivy Jul 13, 2019, 8:26 PM

#

I see, thanks

oak pebble Jul 14, 2019, 6:14 PM

#

@floral verge try doing def teardown_db(arg): and then in the first line of the function do print(arg)

floral verge Jul 14, 2019, 6:29 PM

#

yeah, i fixed it a while ago, it expected an argument since it can get passed an error

#

in the function i added if not error

blissful cradle Jul 15, 2019, 8:56 PM

#

hi, trying to turn long dataframe into wide dataframe in pandas, unclear how to do so...
Each row of my dataframe is a record of a product, store name, region, and then associated price for that specific record
I'm trying to create another dataframe that uses just product and store name as the "primary key", and then shows the price of that product and store across every region

df.pivot won't runs because it contains duplicate entries, and a lot of the grouping or melting resources I've been googling have been about aggregating numbers, when what I want to do is just list numbers from multiple regions.. would appreciate even a suggestion for the right keywords to google how to learn this
i.e I'd like my final table columns to look like
| UPC | Store name| California price | NY Price | Texas Price |

#

(I copied over from #help-croissant , apologies if I need to delete this from here and stick to help channels

arctic badger Jul 15, 2019, 10:04 PM

#

Is it bad using pymysql for a MariaDB?

nova hawk Jul 15, 2019, 10:07 PM

#

Their docs mention this package. https://pypi.org/project/MySQL-python/

PyPI

MySQL-python

Python interface to MySQL

hasty hinge Jul 15, 2019, 10:51 PM

#

If I'm making a DB for my Discord bot, and I need to have a value with the moment where a warn was made, should I use Timestamp or Datetime?

#

I don't understand the difference between Timestamp and Datetime

polar badge Jul 15, 2019, 11:31 PM

#

@hasty hinge you could use datetime.

timestamp = datetime.now()
print(timestamp.isoformat())```

hasty hinge Jul 15, 2019, 11:31 PM

#

Oh, so it is better to use datetime because I can convert it to timestamp easily?

polar badge Jul 15, 2019, 11:32 PM

#

Now if you wanted to format it different you can use timestamp.strftime() youc an find how to use it here http://strftime.org/

Python strftime reference

A quick reference for Python's strftime formatting directives.

#

So for example 07/15/2019 would be print(timestamp.strftime('%m/%d/%Y'))

#

or print(timestamp.strftime('%m/%d/%Y %H:%M:%S')) would do 07/15/2019 16:31:17

#

You can choose your format, or use something like iso format, or others.

#

@hasty hinge I wouldn't say its easier, and not really sure the best way, just how I use it for all my timestamps. Its fairly straight forward to me. And since I am not comparing dates in this example its fairly easy

hasty hinge Jul 15, 2019, 11:36 PM

#

But do you use timestamps or datetimes @polar badge?

polar badge Jul 15, 2019, 11:41 PM

#

i use datetime

#

timestamp = dt.now()
print(timestamp.isoformat())

2019-07-15T16:42:23.043512```

#

so

['MAXYEAR', 'MINYEAR', '__builtins__', '__cached__', '__doc__', '__file__', '__loader__', '__name__', '__package__', '__spec__', 'date', 'datetime', 'datetime_CAPI', 'sys', 'time', 'timedelta', 'timezone', 'tzinfo']```

#

So I am importing datetime. to get to timestamps we need to load datetime again. thus the from datetime import datetime

#

from here

dir(datetime)
['__add__', '__class__', '__delattr__', '__dir__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__le__', '__lt__', '__ne__', '__new__', '__radd__', '__reduce__', '__reduce_ex__', '__repr__', '__rsub__', '__setattr__', '__sizeof__', '__str__', '__sub__', '__subclasshook__', 'astimezone', 'combine', 'ctime', 'date', 'day', 'dst', 'fold', 'fromisoformat', 'fromordinal', 'fromtimestamp', 'hour', 'isocalendar', 'isoformat', 'isoweekday', 'max', 'microsecond', 'min', 'minute', 'month', 'now', 'replace', 'resolution', 'second', 'strftime', 'strptime', 'time', 'timestamp', 'timetuple', 'timetz', 'today', 'toordinal', 'tzinfo', 'tzname', 'utcfromtimestamp', 'utcnow', 'utcoffset', 'utctimetuple', 'weekday', 'year']```

#

Now we have access to timestamp

hasty hinge Jul 15, 2019, 11:46 PM

#

Fine, thanks a lot! @polar badge

small snow Jul 16, 2019, 2:04 AM

#

Hello,
Have a question when it comes to a course I should take. My major is Software Development but I have yet to narrow it down to stuff after that. I have the option to either take MySQL or Database implementation and design. I have never interacted with databases or anything along those lines and I have no idea which to choose. I asked a friend who took MySQL and he told me its super hard while another told me it was ok, I do know that differnt people find different things hard. I am also taking C# as a language that same semester if that helps. I know a bit of python but nothing really advanced which I hope to change.

Just wanted some advice be it general or in depth as I am stumped. Is one relatively easier than the other? Should I do one before the other? There is also SQL but it is only offered the semester after this one and it would be an elective i believe. Thanks if you do decide to help!

golden glacier Jul 16, 2019, 2:07 AM

#

going to entirely depend on your program. what youre describing sounds atypical, as computer science programs are generally about theory of computation, algorithms, etc. not learning a particular technology.

#

unless "software development" is much more focused on tech

small snow Jul 16, 2019, 2:09 AM

#

It seems to be in the program that I am in

golden glacier Jul 16, 2019, 2:10 AM

#

there is much less variation between relational dbs than there are between programming languages

#

ideally you want to learn how to use a relational db, you shouldnt particularly care which one

#

so youd want to look at the course descriptions and see which class addresses that

small snow Jul 16, 2019, 2:14 AM

#

I see. Will try to do that and see if I can find out which one will be more suited then and if I can't then I hope they are not too different as it is an introductory class to it I believe. Thanks for the help grev!

golden glacier Jul 16, 2019, 2:17 AM

#

as an example, any basic query or schema is going to be identical across any relational database with the exception of minor difference in syntax.

small snow Jul 16, 2019, 2:19 AM

#

That's fairly reassuring to hear then.

golden glacier Jul 16, 2019, 2:27 AM

#

sqlite is also included in python, and you can also just use it directly from a client like dbeaver by creating an empty .db file

left sentinel Jul 16, 2019, 6:25 AM

#

I'm fairly certain you can create AWS sql databases for free as well

#

Relatioal Modeling is better for business analysts

#

that have to translate business requirements to technical requirements

#

having that skill is useful for data science , if you're strictly querying /creating
and inserting data into a database , you should probably learn the technical skills to do that

#

understanding the principles of normalization and being aware of the various tables and relationships is key too

hollow sentinel Jul 16, 2019, 6:54 AM

#

Anybody here is experienced in using ms access 2016?

torn sphinx Jul 16, 2019, 9:13 AM

#

how do I drop all tables again

#

I need an unsafe way

brave bridge Jul 16, 2019, 12:18 PM

#

maybe DROP TABLE *?

dawn pulsar Jul 16, 2019, 12:23 PM

#

I'm trying to use prepared statements, and I saw about (SELECT * FROM table WHERE x=%s', (var,))
Which means its prepared

#

Buttt

#

I wrote a function that will make that easier, so I don't have to do all the connection code every time

#

def get_data(*args):

    database_creds = open("database_key.txt", "r").read().split("\n")
    mydb = mysql.connector.connect(host=database_creds[0], user=database_creds[1], password=database_creds[2], database=database_creds[3], port=3306)
    cursor = mydb.cursor()

    cursor.execute(*args)
    records = cursor.fetchall()
    cursor.close()

    return records

if I do that, and then do
a = get_data('SELECT * FROM table WHERE x=%s', (var,))
Is it still prepared?

pure scroll Jul 16, 2019, 12:44 PM

#

yeah

#

you could also use a connection pool (if mysql lib supports that)

#

just to remove part where it need to establish actual db connection

oak pebble Jul 16, 2019, 1:21 PM

#

you should use a context manager or try/finally so the db gets closed every time

rustic fox Jul 16, 2019, 1:51 PM

#

anyone here use ponyorm?

#

i mean this https://ponyorm.org/

knotty parcel Jul 16, 2019, 4:34 PM

#

Anyone any good with monogdb?

near solar Jul 16, 2019, 6:17 PM

#

Hello all I am back for the second time today 😃 I am creating a database in Flask-SQLAlchemy with two tables inheriting from a base class. I am stuck on figuring out how to add a one-to-many relationship to each inherited class, but not sure how to do that.
here is my code:

class User(db.Model):
    __abstract__ = True
    email = db.Column(db.String(120), unique=True, nullable=False)
    password = db.Column(db.String(60), nullable=False)
    first_name = db.Column(db.String(255), nullable=False)
    last_name = db.Column(db.String(255), nullable=False)
    balance = db.Column(db.Numeric(precision=10, asdecimal=True))
    logs = db.relationship('TransactionLog', backref='user', lazy=True)


class MurasakiUser(User):
    __tablename__ = 'murasakiuser'
    id = db.Column(db.Integer, primary_key=True)


class SosekiUser(User):
    __tablename__ = 'sosekiuser'
    id = db.Column(db.Integer, primary_key=True)


class TransactionLog(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    amount = db.Column(db.Numeric(precision=10, asdecimal=True))
    from_account = db.Column(db.String(120), unique=True, nullable=False)
    to_account = db.Column(db.String(120), unique=True, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

I am not sure how to handle the ForeignKey
I guess it cannot go to user.id, because that doesn't exist

#

**copied from help-3

rustic fox Jul 16, 2019, 7:58 PM

#

you have user.id in TransactionLog but in table "User" nothing @near solar

#

and you have Soseki and Murasaki with only id( for what?)

#

so probably need to create "id"

#

and ye,for database i will actually recomend ponyorm @near solar bcs its better then Flask-SQLAlchemy

#

from pony.orm import *
from flask_login import UserMixin

db = Database()


class TvSeries(db.Entity):
    title = Required(str, unique=True)
    description = Required(str)
    image = Required(str)
    seasons = Set('Episode')
    start = Required(date)
    score_titles = Set('ScoreTitle')


class User(db.Entity, UserMixin):
    id = PrimaryKey(int, auto=True)
    mail = Required(str, unique=True)
    login = Required(str, unique=True)
    pwd = Required(str)
    score_title = Set('ScoreTitle')
    admin = Required(bool, default=False)
    activated = Required(bool, default=True)

    def is_admin(self):
        return self.admin


class Episode(db.Entity):
    name = Required(str)
    episode = Required(str)
    season = Required(str)
    actors = Set('Actor')
    title = Required(TvSeries)
    producer = Required('Producer')
    date = Required(date)


class Actor(db.Entity):
    id = PrimaryKey(int, auto=True)
    first_name = Required(str)
    last_name = Optional(str)
    episodes = Set(Episode)


class Producer(db.Entity):
    id = PrimaryKey(int, auto=True)
    first_name = Required(str)
    last_name = Optional(str)
    episodes = Set(Episode)


class ScoreTitle(db.Entity):
    score_title = Optional(int)
    user = Required(User)
    series = Required(TvSeries)``` example of my model on pony

hasty hinge Jul 16, 2019, 11:31 PM

#

How can I check if the last 3 entries ordered by INSERTDATE where in a range of 30 minutes?

📎 unknown.png

eternal aspen Jul 17, 2019, 4:37 AM

#

Using only sql? Or check in Python?

velvet flume Jul 17, 2019, 6:50 AM

#

import mysql.connector
from mysql.connector import Error
from datetime import datetime
now = datetime.now()
current_time = now.strftime("%H:%M:%S")
try:
   mySQLconnection = mysql.connector.connect(host='localhost',
                             database='python_db',
                             user='vraj.shah',
                             password='********')
   date1 = "select insertdate from tablename"
   cursor = mySQLconnection .cursor()
   cursor.execute(sql_select_Query)
   records = cursor.fetchall()
   print("Total number of rows in python_developers is - ", cursor.rowcount)
   for i in records :
    db_time= now.strftime("%H:%M:%S")
    if(current_time  - dbtime > 30):
        print("True")
   cursor.close()
   
except Error as e :
    print ("Error while connecting to MySQL", e)
finally:
    #closing database connection.
    if(mySQLconnection .is_connected()):
        connection.close()
        print("MySQL connection is closed")

#

something like this I guess

#

@hasty hinge

night charm Jul 17, 2019, 8:22 AM

#

I'm using pypyodbc.connect to connect to my SQL server as one of the first lines in each of my functions that retrieves a bit of data. Is it bad practice to do this? I'm just wondering if things are going to slow down if I end up calling a function hundreds of times in a short period or if using pypyodbc.connect() so many times is okay

pale crest Jul 17, 2019, 8:46 AM

#

If you know that you are going to perform a lot of database operations in quick succession, maintaining an open connection is probably a good idea, generally speaking.

#

I'm not familiar with pypyodbc specifically, but this is usually the case.

#

Looking at this https://code.google.com/archive/p/pypyodbc/wikis/A_HelloWorld_sample_to_access_mssql_with_python.wiki, it seems to be the case.

night charm Jul 17, 2019, 8:52 AM

#

Thanks. I'll think about doing that.

gilded narwhal Jul 17, 2019, 7:47 PM

#

say youve got two tables, a and b

#

you want to add more tables containing metadata about the rows in them, let's say a_metadata and b_metadata

#

but you have some columns that are common to both a_metadata and b_metadata

#

what should be done? just grin and bear the redundancy? or try and merge a and b into one table so theyll all share a common id list?

#

ideally id like to avoid rewriting the schema to merge them because that would also mean rewriting a whole lot of code which relies on the current schema

golden glacier Jul 18, 2019, 2:03 AM

#

are a and b joinable via unique values in a column?

gilded narwhal Jul 18, 2019, 3:17 AM

#

yes

#

though id still have to provide some extra info to distinguish them

#

the merge is doable, id just like to avoid it if theres a better way 😛

golden glacier Jul 18, 2019, 3:19 AM

#

can you flesh out your example more

gilded narwhal Jul 18, 2019, 3:20 AM

#

sure. here's the schema:

pragma journal_mode=wal;

drop table if exists files;
drop table if exists folders;

create table folders(
    id integer primary key,
    path text,
    parent integer,
    constraint unique_path unique (path)
    foreign key (parent) references folders(id) on delete cascade
);'

create table files (
    id integer primary key,
    path text,
    parent integer,
    constraint unique_path unique (path)
    foreign key (parent) references folders(id) on delete cascade
);

#

basically it's two separate tables of file and folder paths

#

i want to add extra tables for storing metadata about them

#

i.e. date created, size, attributes, etc

#

one way would be to create both file_metadata and folder_metadata tables

#

but both of them would share some metadata columns

#

so im trying to think of how to approach that

golden glacier Jul 18, 2019, 3:21 AM

#

is there a reason you cant just create one metadata table

#

and have a flag for file or directory

#

F or D

gilded narwhal Jul 18, 2019, 3:22 AM

#

definitely could yeah

#

just not a very elegant solution imo

#

also files and folders could have different metadata columns

#

so therell be a lot of empty cells

golden glacier Jul 18, 2019, 3:23 AM

#

back to the example of creating 1 table for each

#

what is the concern about them having the same columns

#

youre not actually duplicating rows, right?

#

so its not like theres more data being stored

gilded narwhal Jul 18, 2019, 3:23 AM

#

redundancy. just feels like ive gone wrong somewhere in the schema design here

#

yeah that is true

golden glacier Jul 18, 2019, 3:24 AM

#

i mean you already have a separate table for files vs folders, seems to fit your model to have a separate table for each metadata

gilded narwhal Jul 18, 2019, 3:27 AM

#

create table file_metadata(
    id integer,
    size integer,
    date_created integer,
    attributes integer,
    
    foreign key (id) references files(id) on delete cascade 
);

create table folder_metadata(
    id integer,
    size integer,
    date_created integer,
    attributes integer,
    
    foreign key (id) references folders(id) on delete cascade
);

#

this just feels kinda gross

#

the idea is to also allow adding new metadata columns on the fly, but these will be the default columns

#

so say for example youre building a db of audio files, you can add a duration column to file_metadata

#

(it's all a part of this project for reference https://github.com/0xf0f/sqlite-file-index)

golden glacier Jul 18, 2019, 3:41 AM

#

seems fine to me

torn sphinx Jul 18, 2019, 8:31 AM

#

Hello!

I've never actually worked with MySQL (databases in general), but I'm working on a Discod bot, and would like to set up a database functionality for it. I've created a MySQL database, but as I said, I've never really worked with it before. I've been reading online about connections, executing commands, etc. But I've got a lot of questions and I'm not really sure about where to start...

First of, should I create a connection and then close it for every command called? Or do I keep an open connection?
If someone could help me implement the most basic functionality I'd be very grateful. Thanks!

floral verge Jul 18, 2019, 10:38 AM

#

@torn sphinx General idea is to close the connection after every action

#

depending on the framework you are working with, it can happen automatically

pale crest Jul 18, 2019, 10:43 AM

#

I would say that if you know that you're going to perform a sequence of operations in quick succession from a single thread, it's better to reuse the same connection. Opening and closing connections for every single operation is an unnecessary performance overhead.

#

However, you should probably close the connection when you're done.

floral verge Jul 18, 2019, 11:04 AM

#

Concerning your sql experience, its not a hard language to learn for simple database operations

tropic kayak Jul 18, 2019, 3:21 PM

#

why do i get this error whenever running my discord bot?

pymongo.errors.ServerSelectionTimeoutError: main:27017 : [Errno 11001] getaddrinfo failed

#

websockets.exceptions.ConnectionClosed: WebSocket connection is closed: code = 1000 (OK), no reason

#

or im just dumb and my other pc isnt on

graceful nimbus Jul 18, 2019, 4:14 PM

#

Hey, so I'm currently searching for a site or topic to find a solution for the following:
I'm searching for a way to like fetch all the data of every row of a specific column within a database. My goal is to be able to get a total count of like all product prices. So let's say you have this in your DB:

("apple", 1)
("pineapple", 10)

And now able to get the total price, so in this instance 11

narrow mist Jul 18, 2019, 4:59 PM

#

There is a python library for mongoDB. Does it mean I don't need to learn SQL (by the way is it sql for nosql database ?? :D) to interact with my database ?

indigo mason Jul 18, 2019, 5:02 PM

#

Pymongo

narrow mist Jul 18, 2019, 5:09 PM

#

So no SQL ?

indigo mason Jul 18, 2019, 5:31 PM

#

What do you mean

#

Mongo doesn't use SQL

void otter Jul 18, 2019, 5:32 PM

#

Mongo is noSQL, it doesnt use sql for querying

floral verge Jul 18, 2019, 6:04 PM

#

@graceful nimbus one option would be to use the sql query like "select price from table" in python
i've used sqlite3 and im not sure about other analogs but in my case a query like that returned a list of tuples. from that point its python code to retrieve the elements you want

static raven Jul 18, 2019, 8:42 PM

#

hey

#

would ("INSERT INTO user (username, email, password) VALUES (%s, %s, %s)", (username, email, password)) be a safe sql statement?

#

or is it vulnerable

torn sphinx Jul 18, 2019, 9:02 PM

#

doesnt nosql mean "not only sql" as in it can use sql but doesnt have to?

#

nope

golden glacier Jul 18, 2019, 10:00 PM

#

nosql = not a relational db

#

flkXI its safe if youre using it with something like https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.execute

solid void Jul 18, 2019, 10:31 PM

#

sql looks kind of bad until you see what requests in nosql look like, then you look at sql again and wow, it's not bad at all 😄

harsh pulsar Jul 18, 2019, 10:33 PM

#

the biggest problem with sql is non-composability

#

i find it weird that we haven't managed to replace it yet

#

the nosql movement was ass-backwards imo. they replaced the good part (relational data), not the bad/outdated part (the sql language)

#

graphql is a nice idea but its too abstract and hard to reason about performance. also nobody uses it for "backend" stuff or data analysis, just frontend API things

golden glacier Jul 18, 2019, 11:12 PM

#

even stranger is all sql is nonstandard. use 3 different databases and youll be writing the same thing slightly differently 3 times.

severe iris Jul 18, 2019, 11:50 PM

#

So I am trying to delete a value from a table at a database using SQLite 3.
Some information that may be important:
-authorID is of type INTEGER.
-shardName is of type TEXT.
-shardDescription is of type TEXT.
-shardContent is of type TEXT.

Here is the code:

def deleteShard(authorID, shardName):
    """Deletes a Shard from the database."""
    conn = sqlite3.connect("databases/shards.db")
    c = conn.cursor()
    c.execute("""DELETE FROM shards WHERE (authorID=:authorID, shardName=:shardName)""",
             {"authorID":authorID, "shardName":shardName})
    conn.commit()
    conn.close()

When I use only one expression to delete it, such as authorID=:authorID it works perfectly, but when I use multiple expressions it gives me this error:
sqlite3.OperationalError: row value misused.

Can someone help?

golden glacier Jul 19, 2019, 12:06 AM

#

and

#

you are doing where a, b instead of where a and b

arctic badger Jul 19, 2019, 12:55 AM

#

So I need help improving... all my 7 months of python programming and using databases, I've always followed the same format:

db = pymysql.connect(host="", port=,  user="", passwd="", db="")
cursor = db.cursor()
sql = f"""SELECT RowName
          FROM Table
          WHERE Condition;"""
cursor.execute(sql)
db.commit()
``` Specifically, right now I am just trying to see if a row exists in a table, given Condition. I know there's code such as:
```sql
SELECT EXISTS(SELECT 1 FROM table_1 WHERE id = 1) AS mycheck;
``` and I have been trying to figure all this out lmao but I don't think I'm capable and would like some advice for time-sake and to keep my sanity. <....>

jolly stirrup Jul 19, 2019, 1:30 AM

#

i dont think you need a commit for a select statement, you only need commit if you're doing inserts/updates/deletes, the cursor should get the data if you do a select

#

there should be an option for you to just do cursor.select or something

#

but im not sure what your question is 😃 are you checking for a condition and you want to know how to check?

harsh pulsar Jul 19, 2019, 1:38 AM

#

@arctic badger you have you fetch the results after you execute

#

https://www.python.org/dev/peps/pep-0249/#cursor-methods
specifically one of .fetchone(), .fetchmany(), or .fetchall()

Python.org

PEP 249 -- Python Database API Specification v2.0

The official home of the Python Programming Language

#

and correct you dont need the commit for a select

#

so the general pattern is:

connect to database
create a cursor
execute a query using the cursor
fetch results

arctic badger Jul 19, 2019, 1:40 AM

#

Oh no, I know how to do the current method I am using @jolly stirrup
I am trying to use a better method.
Such as 5x3. You wouldn't do 5 + 5 + 5, you'd do 5*3 since it's simpler/more efficient.

harsh pulsar Jul 19, 2019, 1:40 AM

#

what are you asking about? the query itself?

arctic badger Jul 19, 2019, 1:40 AM

#

So yea @harsh pulsar , I do fetch it:

getRow = cursor.fetchone()
level = getRow[0]

#

Yes, because I am trying to check to see if a row exists in a table... and I don't think using a SELECT statement is the best route to go. Like I said, I know there's some command like

SELECT EXISTS(SELECT 1 FROM table_1 WHERE id = 1) AS mycheck;
``` I just don't reallly know how to use it, because that'd return either 1 or 0.

#

So basically, what I am doing right now is working perfectly fine, however, I'd prefer to use the more appropriate ways of doing things, if there is a better solution.

harsh pulsar Jul 19, 2019, 1:42 AM

#

@arctic badger what's wrong with that? seems reasonable to me

arctic badger Jul 19, 2019, 1:43 AM

#

I don't know how I'd use it. Lol, ig that's what I am asking about.

harsh pulsar Jul 19, 2019, 1:43 AM

#

the only other obvious solution is to use WHERE and check for the number of results in python

#

maybe one is slightly more efficient than the other, idk

#

not much to improve imo

arctic badger Jul 19, 2019, 1:43 AM

#

so for the:

SELECT EXISTS(SELECT 1 FROM table_1 WHERE id = 1) AS mycheck;
```What's the most basic way to implement this?

harsh pulsar Jul 19, 2019, 1:44 AM

#

that is the most basic way i can think of

arctic badger Jul 19, 2019, 1:44 AM

#

like... what is mycheck? is that the boolean value that gets returned?

harsh pulsar Jul 19, 2019, 1:44 AM

#

depends on the db engine probably

arctic badger Jul 19, 2019, 1:44 AM

#

No, I am asking, how do I use it lmao

harsh pulsar Jul 19, 2019, 1:44 AM

#

oh

#

this is mysql?

arctic badger Jul 19, 2019, 1:45 AM

#

I am using PyMySql for library. I am using MariaDB for DB.

harsh pulsar Jul 19, 2019, 1:46 AM

#

SELECT 1 FROM table_1 WHERE id = 1

#

if you know id is unique, that will do the same

#

im confused

#

you said you knew how to do the python part

#

so im answering about SQL

#

can you clarify your question

#

so far all the code youve posted is correct

#

and doesnt have an obvious improvement to be made

arctic badger Jul 19, 2019, 1:48 AM

#

Haha allow me to attempt to clarify...

#

I haven't had much experience with SQL as I do Python. The following code, I consider all SQL.

db = pymysql.connect(host="", port=,  user="", passwd="", db="")
cursor = db.cursor()
sql = f"""SELECT RowName
          FROM Table
          WHERE Condition;"""
cursor.execute(sql)
db.commit()
``` This is how I do it for all statements I've done. I have seen people on github do things way differently compared to what I'm doing and I'd like to expand my knowledge and try out different ways to run these statements.

The following SQL statement I found on Google and I don't know how to implement it. 
```sql
SELECT EXISTS(SELECT 1 FROM table_1 WHERE id = 1) AS mycheck;
``` All I have is this statement. 

I'm **assuming** it may be something like this, however if I do it like this:
```sql
db = pymysql.connect(host="", port=,  user="", passwd="", db="")
cursor = db.cursor()
sql = f"""SELECT EXISTS(SELECT 1 FROM table_1 WHERE id = 1) AS mycheck;"""
cursor.execute(sql)
db.commit()
``` would I now have `mycheck` equal to either 0 or 1?
or how'd I get the result from this statement? Would I do `result = cursor.execute(sql)` or something

harsh pulsar Jul 19, 2019, 1:54 AM

#

instead of db.commit() you would write result = cursor.fetchone()

arctic badger Jul 19, 2019, 1:56 AM

#

o ok.
I'll have to try that out ^^
thank you

#

oh, and so what is AS mycheck then? lol should I just remove that part?

harsh pulsar Jul 19, 2019, 2:06 AM

#

it's an alias

#

it lets you refer to the result of that expression elsewhere in the query

#

in this case you don't need it

#

but in general it's good practice

arctic badger Jul 19, 2019, 2:09 AM

#

Ah, ok. Makes sense. Thanks again for all the info @harsh pulsar 👍 ❤

stoic kernel Jul 19, 2019, 1:55 PM

#

With connection objects is it better to keep one open or to constantly open and close the connection

floral verge Jul 19, 2019, 1:58 PM

#

open -> dome something or several connected requests -> close

#

depends a lot on the size of the application and users also

pure scroll Jul 19, 2019, 2:01 PM

#

it's better to have a connection pool instead of doing open/close everytime

stoic kernel Jul 19, 2019, 2:04 PM

#

at the moment it is a discord bot

#

and is using sqlite at the moment but i am about to switch to postgres

#

also what is a connection pool @pure scroll

pure scroll Jul 19, 2019, 2:06 PM

#

connection pool is a thing that keeps multiple connections with database alive.
Whenever you application will need one, instead of creating a connection database (which also takes time) it will just grab one from the pool.
Instead of closing a connection, it will just return it to the pool.

#

most of python database APIs will support connection pooling

#

postgres libraries definitely do, both asyncpg and psycopg2

stoic kernel Jul 19, 2019, 2:10 PM

#

is it just by default?

#

this it? https://pynative.com/psycopg2-python-postgresql-connection-pooling/

pynative

Vishal

Psycopg2 Python PostgreSQL Connection Pooling with Examples

Create and manage PostgreSQL connection pool using Psycopg2. Examples to Create Simple and threaded PostgreSQL Connection Pool to manage Python database aplications.

#

this looks like what i want Psycopg2’s SimpleConnectionPool

echo turret Jul 19, 2019, 2:38 PM

#

so you want to connect to a db using Python? @stoic kernel

#

if yes, Psycopg2 is a very good choice!

#

you can easily find yt tutorial how to do it

stoic kernel Jul 19, 2019, 2:45 PM

#

My one problem with psycopg2 is that cur.fetchall throws an error if there are no results which is really annoying

#

@echo turret

echo turret Jul 19, 2019, 2:47 PM

#

it should work

#

ohhh dude

#

I know what you did wrong

#

do

#

cur.fetchall()

#

it's a method dude

#

@stoic kernel

patent glen Jul 19, 2019, 3:07 PM

#

@echo turret nah, psycopg2 cur.fetchall() returns an error if the previous statement wasn't a select etc, unlike sqlite3. I think that's what they meant.

echo turret Jul 19, 2019, 3:07 PM

#

oohhh sure

raven tree Jul 19, 2019, 3:26 PM

#

I asked in #help-falafel but this is perhaps a more appropriate place. I have a lot of analysis done for audio files where each audio file has a yaml file corresponding to it. I want to aggregate all the analysis into one big file but JSON starts to balloon over 1GB in size and its very hard to parse (and slow). What are some alternatives in python that I can look at for quite heavily nested and dense data?

pure scroll Jul 19, 2019, 3:56 PM

#

You could use rapidjson for such a big file

#

Or something like protobuf as a binary alternative to json

raven tree Jul 19, 2019, 4:02 PM

#

I am using rapidjson to load and unload the files which works quite nicely

#

I am wondering if its just 'bad practice' to be using json for this kind of data

#

Why is something like protobuf good for big data?

harsh pulsar Jul 19, 2019, 7:17 PM

#

json is ok, you can gzip it and get pretty good compression

#

or xz or bz2 or lzma

#

idk which is better

#

really depends on the data format though

stoic kernel Jul 19, 2019, 11:46 PM

#

@echo turret I do use the brackets, its just if there is nothing in the criteria it throws an error

#

@patent glen it seemed to be happening to me if there was no data returned from the select

#

Also with cursors is it better to reuse those if i have multiple statements in quick succession or should i close and reopen those

golden glacier Jul 20, 2019, 12:36 AM

#

you can just make the name a unique key

#

or also just uniq id associated with each type

#

so something like

select count (distinct pokedex_id) from pokemon;

would return a maximum number of unique pokemon seen.

#

so if this was r/b/y the maximum value would be 151

#

so going by this you would use Ndex ids https://bulbapedia.bulbagarden.net/wiki/List_of_Pokémon_by_National_Pokédex_number

List_of_Pok%C3%A9mon_by_National_Pok%C3%A9dex_number

#

or both.

#

and

select * from pokemon where ndex = 1;

would return all copies of bulbasaur

patent glen Jul 20, 2019, 1:03 AM

#

@stoic kernel that's weird, how would you know there's no data

#

what error is it specifically

#

@pliant cedar i'd do three columns - user, pokemon [the name or ndex id or whatever], count.

#

or if you're tracking other data for each individual pokemon, you don't even need the count, since you can just sql count them

#

yeah

#

basically select where user == x and pokemon == y

#

and just don't have any rows where the count is 0

#

eh i don't think it really needs an id column

#

just make primary key over the user_id and pokemon columns

#

[probably have a pokemon id and import the national dex into another table]

#

you need a primary key, but the user id + pokemon id is a natural key here

#

look up compound primary key or composite primary key for the syntax

torn sphinx Jul 20, 2019, 2:20 AM

#

Can MongoDB Be used locally? Like SQLite?

#

I essentially want a Local JSON Database

golden glacier Jul 20, 2019, 2:40 AM

#

ven if you only care about incrementing the counter, you can just uniquely identify it by a userid and then increment some column that represents the count

#

but you intrinsically have a count if you end up storing all the data anyways

unborn coyote Jul 20, 2019, 2:45 AM

#

anyone familiar with mongoengine? I am getting the error "You have not defined a default connection"
i have this: mongoengine.register_connection(alias='core', name='trackdiction') but it doesn't seem to be adequate

#

the above is the only time i really try and define a default connection.

unborn coyote Jul 20, 2019, 3:06 AM

#

otherwise works fine with connect()

patent glen Jul 20, 2019, 5:32 AM

#

@pliant cedar sorry for not replying earlier - yeah you could do that... you can always add the other data later if you need it

torn sphinx Jul 20, 2019, 1:43 PM

#

Can MongoDB Be used locally? Like SQLite?
I essentially want a Local JSON Database

pure cypress Jul 20, 2019, 1:44 PM

#

You could just run the server locally

torn sphinx Jul 20, 2019, 2:33 PM

#

Could we run it like SQLite?

#

just as a file?

jolly stirrup Jul 20, 2019, 9:24 PM

#

i dont think it works like that unless I'm mistaken

#

sqlite is the only one I know of that works like that

#

but if you want a free instance for mongo mlab works for just testing around, otherwise if you are dealing with many records you could spin up the local version on your own machine

#

or use the mongodb atlas

torn sphinx Jul 20, 2019, 11:25 PM

#

Can you explain all of that?

#

I don't get it

pure cypress Jul 20, 2019, 11:30 PM

#

mLab is a service that offers free MongoDB hosting, albeit with limited capabilities. That is why it was suggested that you use it if you are testing things; it's not suitable for a production environment.

#

I don't know how unsuitable it is, but pandemicgameplay seems to suggest a limiting factor is how many records you can store in the database.

torn sphinx Jul 20, 2019, 11:36 PM

#

I found a really cool tool to map out/plan relational databases, figured you guys would be interested: https://dbdiagram.io

A Free Database Designer for Developers and Analysts

Quick and simple free tool to help you draw your database relationship diagrams and flow quickly using just keyboard

#

here's something I've been working on today for a project of mine. its nice to visualize how everything connects

📎 design.png

pure cypress Jul 21, 2019, 12:14 AM

#

This one looks nice. I've tried some others before but they were limiting or confusing to use

#

Thanks

golden glacier Jul 21, 2019, 12:45 AM

#

thats awesome. that would make a great visual aid for teaching design as well as joins

jolly stirrup Jul 21, 2019, 2:24 AM

#

very nice visuals

stoic kernel Jul 21, 2019, 3:00 AM

#

In psycopg2 is there something similar to sqlite3 where you can do something like cur.execute("INSERT INTO table VALUES (?)", ("321",))

#

looks like instead of a ? its %s

torn sphinx Jul 21, 2019, 3:15 AM

#

@pure cypress I just want it locally. and free.

#

Simple and free.

pure cypress Jul 21, 2019, 3:15 AM

#

Then you have to host a server locally

#

It has no means to work without one as far as I know

torn sphinx Jul 21, 2019, 3:27 AM

#

ok so how

#

The only database I have worked with is SQLite

golden glacier Jul 21, 2019, 3:34 AM

#

https://docs.mongodb.com/v3.2/administration/install-community/

#

sqlite is a bit different since its just a library and you can interface with disk or memory directly. for any other db you need to run the server application to interface with it.

slow bloom Jul 21, 2019, 11:50 PM

#

Howdy folks - I'm working on a small MVP and need to refactor away from SQLite, and I'm looking for some opinions on what to choose from whoever will share them. My priorities are as follows:

Must work with SQLalchemy
Must be free (or free-ish for reasonably small use cases)
Should be easy to install/maintain/administer (I want to spend time coding, not DevOps'ing)
Should be capable of running on an AWS EC2 instance and storing data in EFS (probably doesn't rule much out, but wanted to mention it in case)

I can provide more information about my use case if desired, but I'm trying to keep this brief. Anyone got any thoughts?

#

Hmm, on second thought, SQLalchemy only supports ~5 DB types natively. I was under the impression it was more. Nevermind!

torn sphinx Jul 22, 2019, 2:50 AM

#

Totally new to databases

#

I am finding it a bit hard to navigate the MongoDB website

#

I want to get a local database running, how can I do that?

#

I was trying to follow this: (https://www.w3schools.com/python/python_mongodb_getstarted.asp) tutorial but it doesn't talk about the downloading of the database. Just the using of it in python.

#

If someone could guide me through on setting up a local mongo database, I would really appreciate that. Thanks!

#

Update: I believe this: (https://www.mongodb.com/download-center/community) is the correct page I am supposed to download from. However they do not have Fedora.
Just to make sure, is that the right place?

pure cypress Jul 22, 2019, 3:11 AM

#

You should be able to just download it with Fedora's package manager

#

I don't use Fedora though

void otter Jul 22, 2019, 5:54 AM

#

@slow bloom
Postgres is meant for big apps and big data but works good with small apps too

midnight verge Jul 22, 2019, 9:00 AM

#

@slow bloom yeah, use Posgtres unless you have a really good reason not to

#

Also consider using a managed RDBMS service (like Amazon RDS) instead of EFS, it will save you much, much time and sanity

slow bloom Jul 22, 2019, 12:51 PM

#

Okay, thats helpful, thanks @void otter

#

@midnight verge thanks, I did stumbled onto Amazon's RDS while researching and I will definitely be using that! Can you explain your comment about essentially defaulting to PostgreSQL? From the reading I've done it seems like MySQL is sort of the unofficial "default", though I recognize it offers somewhat less features than PostgreSQL

midnight verge Jul 22, 2019, 12:57 PM

#

@slow bloom PostgresSQL is the closest DB to implenting “strict“ SQL, also much more features-packed and more performant in general. MySQL was the “default” RDBMS back in the days when PostgresSQL was a pain to setup, but it is not true anymore for years now

slow bloom Jul 22, 2019, 1:02 PM

#

@midnight verge okay, so maybe just inertia keeping MySQL as comparatively popular then. Are you aware of any significant difference in ease of administration of the two? Even with RDS for production, I will probably choose to keep an offline development-only DB instance on my laptop.

midnight verge Jul 22, 2019, 1:03 PM

#

@slow bloom honestly they are both easy-to-use nowadays, PostgresSQL also giving you more fine-grained control on settings if you need so.

slow bloom Jul 22, 2019, 1:06 PM

#

@midnight verge Cool, I think I'll take it for a spin. Thanks for your advice!

pure scroll Jul 23, 2019, 6:37 AM

#

I would prefer postgres over mysql. It supports non blocking indexing, altering of tables and many other operations which will result in a blocking statement on MySQL

sweet wolf Jul 23, 2019, 1:58 PM

#

Transaction support for schema altering in postgres makes it superior imho. If migrations goes wrong you don't have a disaster 😄

#

Nothing wrong with picking mysql/mariadb is that's what you like. There are strengths and weaknesses with any choice.

strong ferry Jul 23, 2019, 6:54 PM

#

Has anyone here done any work with Kdb+?

craggy coyote Jul 24, 2019, 4:23 PM

#

Anyone familiar with sqlalchemy know if it's possible to create a not nullable constraint for relationship, where the foreign key is on the other table.

e.g. user has author.id but I want to make sure that author.user relationship always exists.

frozen osprey Jul 25, 2019, 7:55 PM

#

Hey. I have a question. Can I use MongoDB Atlas and Heroku?

gloomy bone Jul 25, 2019, 8:53 PM

#

yeah, you can

frozen osprey Jul 25, 2019, 8:58 PM

#

And how to do this?

#

I add MongoDB's uri and it doesn't show any errors, but when I try to use a cmd which uses database it doesn't work.

#

I use the free version

velvet flume Jul 26, 2019, 6:47 AM

#

Hey

#

I have 6GB of single csv file

#

What is the best way to extract subset of data from particular column

#

is Pandas a good option ?

#

or is there any better database thing available

#

That is efficient af.

gleaming quest Jul 26, 2019, 9:11 AM

#

Hi, I had a problem with my database - I'm owner of Minecraft server, and data is stored on MySQL DB. One plugin doesn't work good. 😕 After contacting to plugin's support I've got response:

- Auto incrementing primary keys appear as 'NULL' instead of numbers```
Is it possible, outdated database OS causes this issue?
> 5.7.24-27 - Percona Server (GPL), Release '27', Revision 'bd42700'
> nginx/1.14.2
>  libmysql - mysqlnd 5.0.12-dev - 20150407
> PHP: 7.0.33-1~dotdeb+8.1
> phpMyAdmin 4.6.4

void otter Jul 26, 2019, 9:15 AM

#

Try updating

#

If that doesn't fix it, contact them again

gleaming quest Jul 26, 2019, 11:37 AM

#

I can't update. I'm not administrator of db

strong ferry Jul 26, 2019, 6:08 PM

#

@velvet flume You could try Apache Arrow or Vaex if you're looking for something that won't load the whole table into memory

proud stirrup Jul 26, 2019, 10:18 PM

#

im trying to remember this conditional in sql i hope u guys can help me... I remember there was something that lets me start at N row. select * from tables (something) 1000; ?

#

nvm i got it.. limit then offset

proven solstice Jul 27, 2019, 10:46 AM

#

I have the following database setup: https://pastebin.com/QvWibdjg

Each Game contains 10 entries of PlayerHero in Game.playehero. When I query a Game, then the result g will have 10 entries in g.playerhero. These entries obviously have hero_id and player_id, because they are from PlayerHero class/table, but I would also like them to have Player.account_id and Hero.localized_name from these tables. This should be possible because they are connected via Hero.id == PlayerHero.hero_id (same for Player.id == PlayerHero.player_id), but unfortunately I don't know how I have to write my query using Sqlalchemy. I'm trying around for hours now but can't figure it out. Could someone help me?

Pastebin

[Python] from flask import Flask from flask_sqlalchemy import SQL...

void otter Jul 27, 2019, 3:02 PM

#

@proven solstice will look when i get home

proven solstice Jul 27, 2019, 3:03 PM

#

@void otter thank you!

torn sphinx Jul 27, 2019, 3:50 PM

#

Am I doing something wrong

indigo mason Jul 27, 2019, 3:50 PM

#

There's a bigger problem here

torn sphinx Jul 27, 2019, 3:51 PM

#

?

indigo mason Jul 27, 2019, 3:51 PM

#

You cannot use Python formatting to construct your SQL queries

torn sphinx Jul 27, 2019, 3:51 PM

#

Yes I can

#

Lol?

#

i need command say

indigo mason Jul 27, 2019, 3:51 PM

#

As this opens you up to an SQLInjection attack

torn sphinx Jul 27, 2019, 3:52 PM

#

It works for literally every other command

indigo mason Jul 27, 2019, 3:52 PM

#

Which means anyone has control over your database

#

Sure it "works"

torn sphinx Jul 27, 2019, 3:52 PM

#

Mate, I have it setup safely to only insert safe variables

indigo mason Jul 27, 2019, 3:52 PM

#

Oh well good at least

#

But then why would you bother

#

When you could use the proper formatting

#

And anyway, no amount of user input cleaning is safer than just doing it properly

#

People are infinitely smart. Some filter is not

torn sphinx Jul 27, 2019, 3:53 PM

#

Its an integer.

#

And trust me, its safe.

indigo mason Jul 27, 2019, 3:53 PM

#

It doesn't matter the contents

torn sphinx Jul 27, 2019, 3:54 PM

#

Look mate im not here to argue, I'm just here for a little help

indigo mason Jul 27, 2019, 3:54 PM

#

Which you're rejecting right now

torn sphinx Jul 27, 2019, 3:54 PM

#

You're talking to me about inputs

#

Lets put it this way then

#

Shall we

#

LOL I got it sorted

#

Im so dumb sometimes

void otter Jul 27, 2019, 4:00 PM

#

Ggwp

misty lichen Jul 27, 2019, 10:15 PM

#

Instead of hardcoding an enum. can i unpack a serialized file into it?

void otter Jul 27, 2019, 10:16 PM

#

i think no

#

but don't quote me on that

ionic igloo Jul 27, 2019, 10:53 PM

#

when do you guys think is the red-line for "ok, you should no longer be using sqlite for this, move to a real DB"

smoky radish Jul 27, 2019, 11:55 PM

#

when you either need multiple clients/remotes to access it or you start feeling performance impact

void otter Jul 28, 2019, 8:10 AM

#

when you are going to production
pretty much

#

and when you are starting to have over 1000 records in your db

carmine tide Jul 28, 2019, 10:53 AM

#

await db.execute("UPDATE users SET pet_health = CASE WHEN pet_hunger <= 15 AND pet_health > 20 THEN pet_health = pet_health - 20 ELSE pet_health = 0 END WHERE pet = (?);", (whr))

#

is this a correct statement?

#

after putting AND it is giving me error

torn sphinx Jul 28, 2019, 6:16 PM

#

im learning databases, and the first database i want to make is one that will take a username and password and store it, as well as checking that the user hasnt been taken before. what's a noob-friendly database module that i could learn to begin with?

patent glen Jul 28, 2019, 6:57 PM

#

@carmine tide don't put pet-health = in the cases.

#

other than that it looks ok

#

though, why are you setting it to 0?

#

@torn sphinx sqlite is built in and doesn't require any server setup

#

sqlite3

silent roost Jul 28, 2019, 8:30 PM

#

i think this is probably the best place to ask about gspread?

#

if i have this cell list ```python
cell_list = {
"normal_IGNS":sheet.range('C4:C33'),
"heroic_IGNS":sheet.range('H4:H33'),
"mythic_IGNS":sheet.range('M4:M28'),
"normal_ILVLS":sheet.range('D4:D33'),
"heroic_ILVLS":sheet.range('I4:I33'),
"mythic_ILVLS":sheet.range('N4:N28'),
"normal_ROLES":sheet.range('E4:E33'),
"heroic_ROLES":sheet.range('J4:J33'),
"mythic_ROLES":sheet.range('O4:O28')
}

is it possible to update all of those cells whilst only calling `update_cells()` once? as it seems like it would take a lot longer to do multiple `update_cells()` rather than condensing it into one

gleaming quest Jul 28, 2019, 10:28 PM

#

What is best method to store Python data? I want to make a solid (small) database

fringe tiger Jul 28, 2019, 10:44 PM

#

sqlite3?

gleaming quest Jul 28, 2019, 10:48 PM

#

Um I mean "easy to use" :P
Something like... YAML?
But I don't know what text-files are OK to Python

patent glen Jul 29, 2019, 1:34 AM

#

python has built-in support for json, xml, ini, and csv files, and there are modules available for yaml

velvet flume Jul 29, 2019, 6:47 AM

#

@strong ferry Thanks Pal !

#

Will definitely look into that

carmine tide Jul 29, 2019, 7:56 AM

#

@patent glen it's actually when pet hunger will become 15 or less then pet health will get decrease day by day because it's getting weak

#

And it should not get below 0 which is minus value so I'm setting it to 0

patent glen Jul 29, 2019, 2:28 PM

#

@carmine tide but it looks like you're setting it to 0 always when you don't decrease it, not just when it would be negative

#

this statement sql UPDATE users SET pet_health = CASE WHEN pet_hunger <= 15 AND pet_health > 20 THEN pet_health - 20 ELSE 0 END WHERE pet = (?);
is more or less equivalent to

if pet_hunger <= 15 and pet_health > 20:
    pet_health = pet_health - 20
else:
    pet_health = 0```
there's no case that leaves it alone

#

I'd probably do something like sql UPDATE users SET pet_health = CASE WHEN pet_health > 20 THEN pet_health - 20 ELSE 0 END WHERE pet = (?) AND pet_hunger <= 15;

carmine tide Jul 29, 2019, 3:36 PM

#

Yeah thanks it worked

polar badge Jul 29, 2019, 7:11 PM

#

i know shelve isn't much of a database, but can anyone tell me why my database in unix is 235MB, and on windows its 900KB?

📎 unknown.png

#

I generated the top one in unix using the same import file, as i generated in windows.

#

in wsl ubuntu

📎 unknown.png

patent glen Jul 29, 2019, 7:28 PM

#

@polar badge it's probably created as a sparse file

#

du badgy.pag

polar badge Jul 29, 2019, 7:45 PM

#

what's a spare file?

#

$ du badgy.pag 3344 badgy.pag

#

So thats its actual space?

patent glen Jul 29, 2019, 7:58 PM

#

sparse

#

it means that there's only real data in certain locations of the file, probably because the location is used as part of the hash table layout

#

yeah, that's measured in... kilobytes, i think, by default

#

or maybe in units of half a kilobyte

#

du -h if you want it to say for sure

polar badge Jul 29, 2019, 9:09 PM

#

Thanks, I appreciate the explanation.

civic rover Jul 29, 2019, 10:20 PM

#

I have a database with some information about songs. Is there a service which allows my users to explore the table the songs are stored in without entering any credentials or sql?

jolly stirrup Jul 29, 2019, 10:38 PM

#

you would have an API that just has no auth, and returns the songs, or you would have the users have their own database user logins and have a view available to them to view @civic rover

#

generally speaking

civic rover Jul 29, 2019, 10:44 PM

#

hmm okay ty guess i'll dump it to a file

charred wedge Jul 30, 2019, 5:55 AM

#

Anyone around for a thought exercise? I've got an issue where i'm trying to sift out entitlements from a billing ledger and create a set of data tables so i can find entitlements per customers, but my regular billing data is part of it

#

and i've got 50+ entitlements i'm working with

#

thankfully i'm working with bigquery, so processing power isn't an issue, but i'm at a loss where to start, this isn't really a python question, but i couldn't find a discord based on sql and i was already in this one

ebon gate Jul 30, 2019, 10:32 AM

#

📎 unknown.png

#

Does anyone understand what this means in SQL code?

#

(just the relations)

#

I'm really not seeing how this matches ER diagram tutorials online.

#

The symbols just don't look alike.

rich trout Jul 30, 2019, 10:33 AM

#

Yes

#

City ID is a primary key referenced from city_id in hotel_city

#

and hotel_id is a primary key referenced from hotel_id in hotel_city

ebon gate Jul 30, 2019, 10:33 AM

#

I'm new to relations, unfortunately. Could you in any way write just the relation for the hotel city > city :/

#

It's all confusing what links to what, and my test application depends on it.

#

With that tiny part I can figure out the rest.

rich trout Jul 30, 2019, 10:34 AM

#

I'm not exactly sure what you're asking for

ebon gate Jul 30, 2019, 10:34 AM

#

If I was doing this in code, and not in designer, what would it be?

rich trout Jul 30, 2019, 10:35 AM

#

FOREIGN KEY, and REFERENCES

#

So, hotel_city's hotel_id is a foreign key that references hotel_id under hotel

ebon gate Jul 30, 2019, 10:35 AM

#

Reading, stupid, slept few hours last few days.

#

❤

#

Thanks

rich trout Jul 30, 2019, 10:36 AM

#

You're welcome

ebon gate Jul 30, 2019, 10:52 AM

#

Is this correct?

#

ALTER TABLE `booing_hotel_city`
  ADD CONSTRAINT FK_Hotel_City__Hotel FOREIGN KEY (hotel_id)     
      REFERENCES dbo.booing_hotel (hotel_id)
      ON DELETE NO ACTION    
      ON UPDATE NO ACTION
  ,
  ADD CONSTRAINT FK_Hotel_City__City FOREIGN KEY (city_id)     
      REFERENCES dbo.booing_city (city_id)
      ON DELETE NO ACTION    
      ON UPDATE NO ACTION
  ;

rich trout Jul 30, 2019, 10:59 AM

#

At a glance, yes

ebon gate Jul 30, 2019, 10:59 AM

#

Thanks, will try it.

ebon gate Jul 30, 2019, 11:27 AM

#

Thanks, you helped tons.

#


ALTER TABLE booing_hotel_city
  ADD FOREIGN KEY (hotel_id) REFERENCES booing_hotel(hotel_id),
  ADD FOREIGN KEY (city_id) REFERENCES booing_city(city_id);


ALTER TABLE `booing_hotel_room`
  ADD FOREIGN KEY (hotel_id) REFERENCES booing_hotel(hotel_id);

--
-- Indexes for table `booing_hotel_room_booking`
--
ALTER TABLE `booing_hotel_room_booking`
  ADD FOREIGN KEY (booking_id) REFERENCES booing_booking(booking_id),
  ADD FOREIGN KEY (room_id) REFERENCES booing_hotel_room(room_id);


ALTER TABLE `booing_booking`
  ADD FOREIGN KEY (guest_id) REFERENCES booing_guest(guest_id);

#

This is what I added after my primary keys and auto increments

serene slate Jul 30, 2019, 11:35 AM

#

Am I correct in thinking that if I have an InnoDB MySQL database the maximum number of connections to it I can have is 151? Can this be increased?

I want to have multiple (> 151) python scripts connected and subbmitting at the same time.

ebon gate Jul 30, 2019, 11:36 AM

#

https://www.percona.com/blog/2013/11/28/mysql-error-too-many-connections/

Percona Database Performance Blog

MySQL Error: Too many connections

We have always received quite a few questions here at Percona Support on how to avoid the dreaded “Too many connections” error, as well as what is the recommended value for max_connecti…

#

Might help

#

You may change the max_connections?

serene slate Jul 30, 2019, 12:05 PM

#

hm

#

📎 unknown.png

#

I get this when I run show status like '%onn%';

#

So I need to aim to get Threads_connected down to 151 or lower

#

?

tired kelp Jul 31, 2019, 7:32 PM

#

Does anyone have any good tips on how to handle multiple ids? I'm an information security analyst and im creating a threat logging database. In my tool, you first create an incident, you add a threat to that incident, and then you add your Indicators of compromises (ip, email info, etc..) to those threats. Currently, I im just adding the ids to a list, and popping them off once the back out of whatever i am logging.

#

It works for now, but is there a better way?

#

I can explain more if needed.

#

Thanks!

jolly stirrup Jul 31, 2019, 11:02 PM

#

ids, you mean like auto-increment primary key? or? and also you arent using a db currently just a list? @tired kelp ?

tired kelp Aug 1, 2019, 1:19 AM

#

Yes, auto-inc primary keys and foreign keys. I am using a sqlite database. The list for the key is kind of keeping track of what incident im logging a threat to, or what threat I'm logging an indicator of compromise IOC to.

If i create an incident, i take the new incidents key and store it into a list. Then when I create a threat for that incident, i read from that incident list to see what incident that I'm logging to and add it to the threat as a foreign key. I then do the same for that new threat added and any IOCs that I add to that threat.

After I'm done logging this data, I just pop the values out of the list. It works, but I'm sure there's a better way. @jolly stirrup

#

I can explain more on how the tool works if that helps?

rich trout Aug 1, 2019, 1:31 AM

#

Define "list for the key"

#

Generally you manage these as "relationships"

#

IE, one table has "incidents", one has "threats"

#

An incident row might have a single column labeled "threat", that can associate one threat with many incidents, but one incident to only one threat

tired kelp Aug 1, 2019, 2:39 AM

#

It's not really used as a list, it just the solution that I came up with at the time.

#

incidentprimkey = []

#

threatprimkey = []

#

they will only hold the one key to the current incident and threat that I'm logging. Once done, I clear the list.

#

An Incident can have many Threats, and each Threat can have many IOCS.

#

for example, if a system was hit by a type of Malware called Emotet. I would create an incident and probably call it Emotet Infection.

#

I would then, under that new incident create Threat, where I place files found, notes, rating how bad the threat was.

#

then, under that threat, I add all of the IOCS found from that threat. (IPs, Hashes, Servers, etc..)

#

Now, if down the line I see another system infected with Emotet, I would then go back to that Incident, add a new threat, and add that threats IOCs.

#

📎 unknown.png

#

I made this really fast lol.

cobalt cipher Aug 1, 2019, 2:48 AM

#

Sounds like a good candidate to make a custom incident class

#

then put them into a dict of incident id / name : incident

#

them incient object will have list properties to store history, threats, occurrences etc, you just need to look up the 'Emotet' s the key in that dict to find the "Incident" then add to "Incident.history" for example

tired kelp Aug 1, 2019, 2:59 AM

#

And that is probably where my problem is. I'm not a Dev, I just write scripts to automate and speed up my tasks. I started this Database with no classes and all functions. I didn't need them at the time. I've added so much throughout the time, that I am now looking back and hating myself.

#

There's a lot to it, and it works great, but buggy.

frozen osprey Aug 1, 2019, 10:18 AM

#

If I'd like to create few modules for my bot and in every of them there will be connecting to mongodb. Will I have to setup this db in every of them or is there any easier way?

rich trout Aug 1, 2019, 10:20 AM

#

If you're using cogs, then you can pass a database initializer into each cog as you create them. If not, then its up to how you run them. Sometimes its a good idea for them to be separate though.

frozen osprey Aug 1, 2019, 10:20 AM

#

I mean, I want to create few modules in which I'll use my db.

cobalt cipher Aug 1, 2019, 10:20 AM

#

I'd make a DbController class, whenever you initialize it it will create a new connection to the db

#

then you can define all of the db interactions there, as generic as possible

frozen osprey Aug 1, 2019, 10:21 AM

#

ohh

#

great ide

#

ty

cobalt cipher Aug 1, 2019, 10:21 AM

#

in other modules you just import it over, assign it to self.controller or smth

frozen osprey Aug 1, 2019, 10:21 AM

#

*idea

cobalt cipher Aug 1, 2019, 10:21 AM

#

then self.controller.read / write

frozen osprey Aug 1, 2019, 10:21 AM

#

ok

#

ty

tired kelp Aug 1, 2019, 3:57 PM

#

By the way, I forgot to thank you guys for helping with my question yesterday. I appreciate the help. Thanks!

gilded narwhal Aug 2, 2019, 5:41 AM

#

ive got an sqlite database of files and folders

create table folders (
    id integer primary key,
    path text,
    parent integer,
    constraint unique_path unique (path)
    foreign key (parent) references folders(id) on delete cascade
);

create table files (
    id integer primary key,
    path text,
    parent integer,
    constraint unique_path unique (path)
    foreign key (parent) references folders(id) on delete cascade
);

#

currently im searching the paths for specific keywords using LIKE with two wildcards on either end

#

i want to improve this by using fts tables but am concerned about the added cost of maintaining consistency between the files/folders tables and their corresponding fts tables as it doesnt seem like fts tables allow working with foreign keys

#

are there any good/clean ways of going about this that wouldnt involve a ton of extra work?

#

im seeing some examples of triggers on the sqlite docs, suppose thats one option

#

this is the example from the docs btw

-- Create a table. And an external content fts5 table to index it.
CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);
CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a');

-- Triggers to keep the FTS index up to date.
CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;
CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
END;
CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;

#

really dont like the look of that

#

will go ahead and try it out and do some benchmarks to see if the change is worth it

gilded narwhal Aug 2, 2019, 6:13 AM

#

i dont really get why you need an external content table if youve got to keep the values updated yourself anyway

#

are the triggers duplicating the data or what?

#

confused

gilded narwhal Aug 2, 2019, 7:44 AM

#

ok, scratch all this, fts can only do prefix matching. it cant do the same thing as like %thing%

ocean coral Aug 2, 2019, 8:32 PM

#

can someone help me with sqlite3 ?

nova hawk Aug 2, 2019, 8:33 PM

#

Depends on the question.

ocean coral Aug 2, 2019, 8:34 PM

#

how can i check if the value that comes back with "SELECt ........." equals Null

#

in other word there is nothing found

#

does that work if (cursor.fetchone == 'null')

nova hawk Aug 2, 2019, 8:37 PM

#

You can do

result = cursor.fetchone()
if not result:
    code```

ocean coral Aug 2, 2019, 8:38 PM

#

thanlkksss

#

c:

hazy monolith Aug 2, 2019, 9:21 PM

#

Is anyone familiar with Flask SQLAlchemy?

sleek cosmos Aug 3, 2019, 2:47 AM

#

is there any reason that, with sqlite3, a row wouldn't be deleted? it has 4 fields, 1 primary key and 3 are text. syntax has worked with another database where the only difference was it didn't have a primary key. So i'm thinking there must be a different way to delete rows when they have a primary key?

cursor.execute('''DELETE FROM entries WHERE title=?''', (title))

it's in a try and except and this line seems to be the problem

final lion Aug 3, 2019, 2:56 AM

#

I don't know off the top of my head, but if you want that to be a single-element tuple, it needs to be (title,)

patent glen Aug 3, 2019, 3:11 AM

#

what do you mean "it's in a try and except" exactly, are you not printing what the error is?

sleek cosmos Aug 3, 2019, 3:41 AM

#

try:
    db = sqlite3.connect(file)
    cursor = db.cursor()


    cursor.execute('''DELETE FROM entries WHERE title=?''', (title))
    db.commit()
            
except:
    print('Error deleting item {0} from database: {1}'.format(title, file))
    return 0

cobalt cipher Aug 3, 2019, 3:46 AM

#

Did you try what switchy suggested @sleek cosmos - change to (title,)

#

shouldnt matter idt

#

worth a try

hidden bramble Aug 3, 2019, 3:47 AM

#

oh if thats meant to be a tuple, it absolutely does matter

#

tiny bit heavier, but alternatively: tuple([title])

sleek cosmos Aug 3, 2019, 3:48 AM

#

oh that worked. can i ask why it needed to be that? it worked fine without that when it was another database that was identical except for the existence of a primary key. why would a primary key make that difference?

#

And also thanks @final lion

hidden bramble Aug 3, 2019, 3:50 AM

#

a single expression inside parentheses is interpreted as just being the expression, but isolated; as in this:python (5 + 3) * 2

#

that also applies when the expression is a single term, like this:python (8) * 2because anything else would be inconsistent

#

the comma tells the interpreter that even though there isnt, there could be a second expression there, and the parentheses mean a tuple

cobalt cipher Aug 3, 2019, 3:52 AM

#

print(type((8)))
print(type((8,)))

#

<class 'int'>
<class 'tuple'>```

sleek cosmos Aug 3, 2019, 3:52 AM

#

so why did it work when the database didnt have a primary key? the old database was just "title, username, password", it worked then, and now its "primary key, title, username, password". Why would this be fine when the only difference is an extra column?

#

what your explaining, it seems like it shouldnt have worked with either

cobalt cipher Aug 3, 2019, 3:53 AM

#

I guess answer lies in execute docs

sleek cosmos Aug 3, 2019, 3:53 AM

#

will read. Thank you all, i was close to sucking my own eyeballs out!

cobalt cipher Aug 3, 2019, 3:54 AM

#

which doesnt say either, but both of their answer contains collection type like dict / tuple

#

https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.execute

hidden bramble Aug 3, 2019, 3:54 AM

#

np

cobalt cipher Aug 3, 2019, 3:54 AM

#

guess we need to dive into source code lol

hidden bramble Aug 3, 2019, 3:55 AM

#

honestly i often find source code much more helpful than documentation

cobalt cipher Aug 3, 2019, 3:55 AM

#

same lol

sleek cosmos Aug 3, 2019, 3:56 AM

#

i find it more confusing but i guess the only way to get over that hurdle is to keep trying.
Again, Thanks so much! can finally move forward 😃

cobalt cipher Aug 3, 2019, 3:57 AM

#

👍

#

📎 unknown.png

#

I guess that's the answer

#

Iterable

#

They tried to unpack

#

And unpacking int will raise exception, or got more than what it needed when unpacking "string"

patent glen Aug 3, 2019, 5:23 AM

#

it doesn't have to be a tuple @hidden bramble, you can just do [title]

#

tbh i feel like the use of tuples over lists for parameters is a microoptimization that costs much more in frustration for this one-item case than it saves

hidden bramble Aug 3, 2019, 5:25 AM

#

well if all it needs is an iterable/sequence then yeah, but if it checks for a tuple specifically then it needs to be a tuple; there are use cases for single element tuples so its worth knowing how to make them

patent glen Aug 3, 2019, 5:27 AM

#

anyway @sleek cosmos sorry for not following up - using just except: like that is bad practice, you can do except Exception as e and then print e to show what the actual error was

#

(also that might be a bad place to catch the exception at all, i'd have to know more about the structure of your program)

sleek cosmos Aug 3, 2019, 5:28 AM

#

@patent glen
Thanks! thats awesome! absolutely going to add that now!

earnest saffron Aug 4, 2019, 4:32 AM

#

mycursor.execute(f"CREATE TABLE '{user}' ("f"{userid}"" VARCHAR(255), tanks VARCHAR(255))")

the varible "user" has a space in the name but the databse only catches the first name and not the last name like John Doe it says john

crystal echo Aug 4, 2019, 4:33 AM

#

tables can't have spaces in them

#

you shouldn't have a separate table for each person anyway

#

make a table called People or whatever

#

store people in that table

earnest saffron Aug 4, 2019, 4:34 AM

#

I see, makes more sense that way.

crystal echo Aug 4, 2019, 4:34 AM

#

Database design can be though to get as a self learner

#

but as a rule of thumb

#

less tables = better

#

tables should be like

#

data types

earnest saffron Aug 4, 2019, 4:34 AM

#

lol I'll take that advice, I'm switching from googlesheets

crystal echo Aug 4, 2019, 4:35 AM

#

say your app has Users, Movie tickets and Car makes

#

well make a table for each

#

so 3 tables

final lion Aug 4, 2019, 6:23 AM

#

learn to love foreign keys 😉

upbeat lily Aug 4, 2019, 10:15 AM

#

If I'm making something like a full stack web app, is there any reason to run my create scripts via Python to save the source that way, vs just creating the tables directly via SQL?

rustic carbon Aug 4, 2019, 11:17 AM

#

what is the easiest database to use?

upbeat lily Aug 4, 2019, 11:40 AM

#

It depends on what you mean by "To use". MySQL is one of the most common ones to be able to find help/documentation for, SQLite is the easiest to set up and get started with because it is file based, MongoDB doesn't require any SQL which some might consider easy>

My suggestion would be to get started with SQLite if you're just getting started with databases because there is practically no config/setup issues blocking your way

indigo mason Aug 4, 2019, 11:41 AM

#

Yeah, it entirely depends

#

Consider what your project is too

#

SQLite or something like Postgres have different use cases

#

Even though you could get away with using it for the 'wrong' thing

rustic carbon Aug 4, 2019, 11:42 AM

#

oh

#

i use sqlite

indigo mason Aug 4, 2019, 11:42 AM

#

I'd also suggest using Postgres over MySQL

#

SQLite is a perfectly fine choice

rustic carbon Aug 4, 2019, 11:42 AM

#

I use it, apparently I think the code is right but it doesn't add anything to the database

#

📎 unknown.png

regal sleet Aug 4, 2019, 12:48 PM

#

How to create table and store UTF-8mb4 in it?!

#

Using phpmyadmin or mysql command

ionic pecan Aug 4, 2019, 1:50 PM

#

@rustic carbon show the full code please

#

@regal sleet what have you tried already

rustic carbon Aug 4, 2019, 1:51 PM

#

ok

regal sleet Aug 4, 2019, 1:51 PM

#

@ionic pecan i played around and got what i want

rustic carbon Aug 4, 2019, 1:52 PM

#

📎 unknown.png

#

📎 unknown.png

#

📎 unknown.png

regal sleet Aug 4, 2019, 1:52 PM

#

i just created normal DB/tables and specified the encoding the JDBC link

rustic carbon Aug 4, 2019, 1:52 PM

#

this

rustic carbon Aug 4, 2019, 2:10 PM

#

:c

rustic carbon Aug 4, 2019, 2:35 PM

#

semoene help me

#

:c

torn sphinx Aug 4, 2019, 3:07 PM

#

with cnnt:
    cur.execute("Command goes here")

#

You have to close the connection

#

Context managers do the job well.

#

@rustic carbon https://docs.python.org/2/library/sqlite3.html#using-the-connection-as-a-context-manager

rustic carbon Aug 4, 2019, 3:12 PM

#

uhm

torn sphinx Aug 4, 2019, 3:12 PM

#

Oh wait, you're using that in functions. You have to set parameters for the connections

rustic carbon Aug 4, 2019, 3:12 PM

#

oh

#

more more

torn sphinx Aug 4, 2019, 3:12 PM

#

Or use "global"

#

So,

rustic carbon Aug 4, 2019, 3:12 PM

#

which one is the best

torn sphinx Aug 4, 2019, 3:13 PM

#

Wait. I'm sorry. I actually have to go. But make a parameter in the functions that accepts the database connection.

rustic carbon Aug 4, 2019, 3:13 PM

#

so, before you go talk in a way that donkeys understand
;--------;

torn sphinx Aug 4, 2019, 3:17 PM

#

https://www.youtube.com/watch?v=o-vsdfCBpsU

YouTube

sentdex

Creating a database, table, and inserting - SQLite3 with Python 3 ...

Welcome to an SQLite mini-series! SQLite, as the name suggests, is a lite version of an SQL database. SQLite3 comes as a part of the Python 3 standard librar...

▶ Play video

unkempt sable Aug 4, 2019, 3:17 PM

#

@torn sphinx Let's not use the word reta**ded here please.

torn sphinx Aug 4, 2019, 3:17 PM

#

Watch the series by sentdex. I would've loved to help but really have to go

#

Oh, my bad.

rustic carbon Aug 4, 2019, 3:34 PM

#

oh, im brazilian

#

my inglish is bad

#

but.. thx

#

📎 unknown.png

#

📎 unknown.png

#

he still doesn't go to .db

sleek mango Aug 4, 2019, 7:44 PM

#

Hey, how can I sort documents using motor(async lib for mongoDB)? in pymongo DB.Collection.find().sort() does the job but that doesn't work in motor and for some reason docs search isnt working >.<

earnest saffron Aug 4, 2019, 9:22 PM

#

Is None = NULL in a database?
or will it print "None" if I get the colum with a None value.

#

if that makes sense.

#

📎 None.PNG

regal sleet Aug 4, 2019, 9:29 PM

#

in python: None in mysql :NULL

torn sphinx Aug 4, 2019, 11:19 PM

#

@rustic carbon So I'm finally back.

#

import sqlite3

connection = sqlite3.connect("RandDatabase.db")
cursor = connection.cursor()

def createTable():
    with connection:
        cursor.execute("CREATE TABLE IF NOT EXISTS something (name TEXT, age INTEGER)")
        print("[+] Succesfully created the table!")

def inputInformation(name, age):
    with connection:
        cursor.execute("INSERT INTO something VALUES (?, ?)", (name, age,))
        print("[+] Succesfully inserted {} and {} into the table".format(name, age))

def fetchInformation(nameToFetch):
    try:
        with connection:
                rows = cursor.execute("SELECT * FROM something WHERE name=?", (nameToFetch,))
                name, age = [row for row in rows][0]
                print(f"\nName: {name}\nAge : {age}")
    
    except IndexError:
        print("[-] Information on '{}' does not exist in the database".format(nameToFetch))

# createTable()

# inputInformation("Jack", 12)
# inputInformation("Conny", 31)
# inputInformation("Bob", 100)

fetchInformation('NAME GOES HERE') # Jack, Conny, Bob

#

So here's an example.

rustic carbon Aug 4, 2019, 11:20 PM

#

uhm

#

why uses with

#

in tutorials hes no use

torn sphinx Aug 4, 2019, 11:20 PM

#

Yeah. That's to show viewers that you have to close the database connection

#

With, does it for you. You don't have to explictly write code to close the connection

rustic carbon Aug 4, 2019, 11:21 PM

#

ah

#

ok

torn sphinx Aug 4, 2019, 11:23 PM

#

Were you having any other problems?

rustic carbon Aug 4, 2019, 11:25 PM

#

not for now

raven dew Aug 5, 2019, 2:11 AM

#

Hey

#

This is a mix of sql plus discordpy

#

this is my first time using sql

#

@client.event
async def on_message_delete(message):
    content = message.content
    self.cur.execute("INSERT INTO Serenity(WORDS,Message) VALUES('text',content)")
    self.con.commit()```
```py
class Snipe(commands.Cog):
    def __init__(self,bot):
        self.bot = bot
        self.con = sqlite3.connect('Serenity.db')
        self.cur = self.con.cursor()


    @commands.command()
    async def snipe(self,ctx):
        take = self.cur.execute("SELECT * FROM Serenity WHERE message=!",[content])
        await ctx.send(take)```

whenever I call the command it says `name content isnt defined`

final lion Aug 5, 2019, 2:16 AM

#

sure, you don't have a local variable in snipe called content

raven dew Aug 5, 2019, 2:16 AM

#

oh

#

how would I get the variable from on_message_delete

#

oh wait

#

nvm

#

i dont get it

final lion Aug 5, 2019, 2:21 AM

#

How are on_message_delete and snipe related? Why should snipe know anything about on_message_delete?

raven dew Aug 5, 2019, 2:24 AM

#

hm

#

you are correct

shrewd cloak Aug 5, 2019, 11:49 AM

#

Hey, I'm looking to start using postgresql with asyncpg and I'm looking at an example I found. I'm just wondering one thing. I'm looking at the select & update examples and I'm wondering one thing

query = 'SELECT * FROM test_table WHERE id = $1;'
row = await db.fetchrow(query, id)```
```py
con = await db.acquire()
async with con.transaction():
    query = 'UPDATE test_table SET "name" = $1 WHERE id = 1234;'
    await db.execute(query, new_name)
await db.release(con)``` Do I need the whole `await db.aquire()` and `async with con.transaction`? It's not in the select example

#

Does db.fetchrow() do all of that already and it's only needed for db.execute()?
please tag me if you answer

craggy coyote Aug 5, 2019, 2:45 PM

#

Hey very specific question. Using sqlalchemy anyone know how to inspect a query to see what it's been filtered on already?

ionic pecan Aug 5, 2019, 4:31 PM

#

why not evaluate it?

dawn pulsar Aug 5, 2019, 5:04 PM

#

Hey you clever people, I have a table with 4 columns, name, staff, duration and time
I'm trying to get key information from that table, such as the earliest row, or the longest row, I have this:

f'SELECT MAX(duration), MIN(duration), MAX(time), MIN(time), SUM(duration), COUNT(name), COUNT(DISTINCT name) FROM support_sessions WHERE staff="{player}"')

f'SELECT count(staff) FROM support_sessions WHERE staff="{player}" AND duration > 0')

f'SELECT name, count(name) FROM support_sessions WHERE staff="{player}" GROUP BY name ORDER BY count(name) DESC LIMIT 1')

But I have a big problem, it can get the longest duration, but it doesn't also get the other data, such as the name and the date, how would I get this?

patent glen Aug 5, 2019, 5:20 PM

#

so wait

#

what is the staff column and what is the name column

#

anyway

#

to get the value of one column associated with the max value of another column, various database engines have ways of doing this, but i'd need to know which one you are using

#

(the 'obvious' way works in sqlite, so I assume it's not that)

#

also, don't use string formatting to build sql queries, use parameters

dawn pulsar Aug 5, 2019, 5:46 PM

#

Yeah, I just use them to test, It's a MariaDB and it's a "session" between 2 people, the staff and the "user" or person

#

And I'm still pretty new so I might have missed the "obvious" way lmao

#

please ping me :D

ocean coral Aug 5, 2019, 7:30 PM

#

how do i insert only in one column?

patent glen Aug 5, 2019, 7:36 PM

#

@dawn pulsar look at this https://dev.mysql.com/doc/refman/8.0/en/example-maximum-row.html

#

@ocean coral what do you mean

dawn pulsar Aug 5, 2019, 7:37 PM

#

@patent glen Im not sure that's what I need, that'll get the row with the highest price

#

Well

#

Hmm

#

I used a different way, by ordering and using LIMIT 1

#

But it still works

#

But with the first example I used above, is there anyway to get the rest of the data for each bit, e.g. when I get the MAX(duration) can I also get the rest of the stuff for that row? all in the same query

ocean coral Aug 5, 2019, 7:39 PM

#

i found out how but thanks @patent glen

dawn pulsar Aug 5, 2019, 7:40 PM

#

So I get something like this:
[(name, staff, duration, time), (name, staff etc...)....]
Longest, shortest etc

patent glen Aug 5, 2019, 7:43 PM

#

no

#

even sqlite which makes it easy only lets you get one row (or one per group) for a single one, not both min and max in the same query

proven ridge Aug 5, 2019, 10:05 PM

#

I tried searching Google datasets and the usual suspects but can't find any real poker hands and betting datasets anyone have any knowledge in where I could get this?

torn sphinx Aug 6, 2019, 7:49 AM

#

Hi, i'm French, Sorry for my typo in english

        def step_save_database(self):
        # Dump the database
        print('\n',
                  "Use record module",
              '\n')
        self.db.query("""
                        mysqldump://OCP6:OC_STUDENT/Oc_Pizza>Oc_Pizza.backup.sql
                      """)
        self.next_step()
``` This function save my base, i have one error of syntax Mysql
Do you have solution please?

final lion Aug 6, 2019, 8:16 AM

#

what's the actual error message?

torn sphinx Aug 6, 2019, 4:16 PM

#

@torn sphinx I don't have knowledge on MySql, but for one, (I think) there isn't any .query in mysql module. It's .execute, https://www.w3schools.com/python/python_mysql_select.asp.

#

Second, your tab spacing, if it's like that. That's a big syntax error.

#

        def step_save_database(self):
                # Dump the database
                print('\n',
                      "Use record module",
                  '\n')
                self.db.query("""
                                mysqldump://OCP6:OC_STUDENT/Oc_Pizza>Oc_Pizza.backup.sql
                              """)
                self.next_step()

#

Ok, even that tab spacing's off cause of discord. But you have to space your code

#

tank you @torn sphinx

ocean coral Aug 6, 2019, 7:31 PM

#

hey i have a question about sql

#

i want to make a leveldiscordbot and i dont know how to find out which rank someone currently has

#

i mean if you would order everyone by his amount of xp where the person would be

upbeat lily Aug 6, 2019, 8:23 PM

#

Select * from users order by experience desc?

#

@ocean coral?

ocean coral Aug 6, 2019, 8:24 PM

#

eh

#

i tried this

#

but kind i get a syntax error

#

    cursor.execute('SELECT rank_xp, RANK() OVER(ORDER BY xp DESC) AS rank_xp FROM local_xp WHERE user_id=? AND guild_id=?', (ctx.message.author.id, ctx.message.guild.id))```

upbeat lily Aug 6, 2019, 8:26 PM

#

Does that insert the parameters into the query string correctly?

#

Assuming it does you could do a nested query, with the user id in the outer query, because I think the current one is ranking/ordering a result of 1 row

ocean coral Aug 6, 2019, 8:32 PM

#

i only want the rank for one person and nothin else

#

so only one row

#

but i get this weird error and i dont understand why

#

sqlite3.OperationalError: near "(": syntax error```

pure cypress Aug 6, 2019, 8:33 PM

#

Can you show your sql statement?

#

Oh you did

#

sorry

ocean coral Aug 6, 2019, 8:33 PM

#

sql statement?

pure cypress Aug 6, 2019, 8:34 PM

#

nevermind you pasted it above

upbeat lily Aug 6, 2019, 8:34 PM

#

But you need the rank relative to everyone else

ocean coral Aug 6, 2019, 8:34 PM

#

yes

#

ahh

#

ik what you mean

upbeat lily Aug 6, 2019, 8:34 PM

#

So your inner query gets everyone, the outer gets just the 1 guy

ocean coral Aug 6, 2019, 8:34 PM

#

but wat means a nested query?

#

im not used to sql😅

upbeat lily Aug 6, 2019, 8:36 PM

#

So the result of a query is in of itself a table. It might not be permanent, but you can query it. I'd give you a more full example but I'm on mobile.

The basic idea is:
Select * from (other query in brackets) where rank=1

#

Where userid=userid

ocean coral Aug 6, 2019, 8:37 PM

#

so i need to have like

#

more than one line?

upbeat lily Aug 6, 2019, 8:41 PM

#

So I haven't used the rank syntax, but your query should roughly be:

    (SELECT user_id, guild_id, rank_xp, RANK() OVER(ORDER BY xp DESC) AS rank_xp FROM local_xp)
WHERE user_id=? AND guild_id=?
', (ctx.message.author.id, ctx.message.guild.id))```

ocean coral Aug 6, 2019, 8:41 PM

#

or not?

#

argh

#

just found out

#

i get that error cause sqlite3 does prob does not support rank()

upbeat lily Aug 6, 2019, 8:46 PM

#

http://www.sqlitetutorial.net/sqlite-window-functions/sqlite-rank/

SQLite Tutorial

admin

SQLite RANK()

This tutorial shows you how to use SQLite RANK() function to calculate the ranks for rows in a query's result set.

#

should be fine

ocean coral Aug 6, 2019, 8:47 PM

#

mh

#

but why do i get that error

#

:c

upbeat lily Aug 6, 2019, 8:49 PM

#

Try removing the AS?

amber hamlet Aug 7, 2019, 9:46 PM

#

Hi, I tried googling for this. I know there is a way to convert a python file(s) into a Windows executable, what I'd like to know is that can you add a database server/client to the executable as well? I don't want to have to require the user to download a database server/client in order to use the executable.

mystic bough Aug 7, 2019, 9:52 PM

#

I wanna create table with using sql file, i tried to write \i path/file.sql but it doesnt work. I have No such file or directory error. I used to use this command on ubuntu, now i am using windows and this command doesn't work. How can i fix that ?

torn sphinx Aug 8, 2019, 2:50 AM

#

@mystic bough In use, look for another help channel that isn't in use.

dull scarab Aug 8, 2019, 4:40 AM

#

Topical channels do not act as help channels.

#

@amber hamlet you could do something lile that with a docker container, or make the executeable connect to a cloud hosted db, or use sqlite as it creates a file it connects to if it does not exist alreadt

pure cypress Aug 8, 2019, 4:47 AM

#

Well docker containers aren't exactly end-user friendly 😄

#

Depends on who the users are

dull scarab Aug 8, 2019, 4:56 AM

#

True, very not end user friendly 😏

dull scarab Aug 8, 2019, 11:15 AM

#

ArangoDB, yay nay?

torn sphinx Aug 8, 2019, 9:22 PM

#

what is ArangoDB?

#

sounds like mongoDB

harsh osprey Aug 8, 2019, 9:41 PM

#

oh whoops read the channel name wrong

torn sphinx Aug 9, 2019, 10:50 AM

#

anyone knows how to add data from JSON file to DB with python?

harsh pulsar Aug 9, 2019, 1:10 PM

#

@torn sphinx there isn't any one correct way to translate json into a tabular database format. in general you just open the json file, parse it as json, then write whatever you want to the database. for anything more specific you will need to describe more about your situation

chilly slate Aug 9, 2019, 2:59 PM

#

If I have a table of values with 2 identifier and a Year/Month Date. How can I label the first occurrence each month different from the subsequent visits and have it reset every month. So the first time a value appears it is 1 then the next time the value it appears its 2 and it resets back to 1 next month

chilly slate Aug 9, 2019, 3:17 PM

#

nvm got it

hearty iron Aug 9, 2019, 3:50 PM

#

can't we run multiple statements with pool.execute in asyncpg?
asyncpg.exceptions.PostgresSyntaxError: cannot insert multiple commands into a prepared statement coz i am getting this on two DELETE statements

somber prism Aug 9, 2019, 6:55 PM

#

I'm building a web app using Flask and am having trouble querying and filtering the proper data. There are 3 select fields with which the user can filter the data to be queried (County, City, & Sale Date). Here's a sample URL:

http://127.0.0.1:5000/table_data?county=Atlantic&city=Atlantic+City&sale_date=09-26-2019

Now this works properly and extracts the data as follows:

(County=Atlantic, city=Atlantic City, sale_date=09-26-2019)

The problem occurs when the user wants to select all the values in a given column to filter from. The default value of the option for all is == "" which obvious will not work as I don't have any empty string values. The question is, what's the best solution in building a query in which the filters could either be specified or return all options of the select form?

def table_data():

_county = request.args.get("county")

_city = request.args.get("city")
_sale_date = request.args.get("sale_date")

_query = 
    SheriffSaleDB.query.filter(
        SheriffSaleDB.county == _county,
        SheriffSaleDB.city == _city,
        SheriffSaleDB.sale_date == _sale_date,
    )
    .all()

_results = 
    SheriffSaleDB.query.filter(
        SheriffSaleDB.county == _county,
        SheriffSaleDB.city == _city,
        SheriffSaleDB.sale_date == _sale_date,
    )
.count()

return render_template("table_data.html", query=_query)```

rich trout Aug 10, 2019, 12:24 AM

#

If you can chain .filter() methods, then that might be a way to go

torn sphinx Aug 10, 2019, 3:13 AM

#

i want to create a database for an imaginary company
how do people store their data? I want to store the variables all in one dictionary in a text file. is this what most people do?

it will look something like this:

imaginary_company = {
    'suppliers' : {
            'supplier_one' : {'another_nested_dict'},
            'supplier_two' : {'another_nested_dict'}
        },
    'customers' : {#just like supplier, this will have nested 
                           # dicts},
    'inventory' : {'list_of_inventory'}
    }

karmic sparrow Aug 10, 2019, 10:54 AM

#

most people use an SQL based database

#

in my experience *

ionic pecan Aug 10, 2019, 11:59 AM

#

Yeah

#

PostgreSQL is the recommended way to go, usually. SQLite is also a great pick

crimson ibex Aug 10, 2019, 2:53 PM

#

I am using django with postgresql. I have created user 'bob' for the database 'wedpost'.  
In "setting.py" file,

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'wedpost',
        'USER': 'bob',
        'PASSWORD': '***',
        'HOST': 'localhost',
        'PORT': ''
    }
}
-

So After hitting the command,
 python manage.py makemigrations
I am getting the error,
---------------------------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/ruth-psych/Documents/wedpost/demo2/env/lib/python3.7/site-packages/django/db/backends/base/base.py", line 216, in ensure_connection
    self.connect()
  File "/home/ruth-psych/Documents/wedpost/demo2/env/lib/python3.7/site-packages/django/db/backends/base/base.py", line 194, in connect
    self.connection = self.get_new_connection(conn_params)
  File "/home/ruth-psych/Documents/wedpost/demo2/env/lib/python3.7/site-packages/django/db/backends/postgresql/base.py", line 178, in get_new_connection
    connection = Database.connect(**conn_params)
  File "/home/ruth-psych/Documents/wedpost/demo2/env/lib/python3.7/site-packages/psycopg2/__init__.py", line 126, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL:  Ident authentication failed for user "bob"
-------------------------------------------------------------------------------------------
Can someone help me out with this?

azure chasm Aug 10, 2019, 9:30 PM

#

With sqlite on python it says that it cannot write to a read only db when if i regularly put in info it allows it?

#

What do i do to fix this

sleek cosmos Aug 11, 2019, 7:42 AM

#

Hey quick check in with something if anyone has the time. I saw this code:

con.execute("SELECT * FROM " + table + ";"):

but im pretty sure that i've read that it's bad practise and should be done like this

con.execute("SELECT * FROM ?;", (table)):

or are both the same?

final lion Aug 11, 2019, 8:13 AM

#

@sleek cosmos you're correct, the second form is better because it's resistant to sql injection attacks

sleek cosmos Aug 11, 2019, 8:14 AM

#

thanks!

ionic pecan Aug 11, 2019, 7:23 PM

#

in general, yes, but you can't parametrize table names

#

usually if you need to do that somehow you're doing something wrong

#

>>> import sqlite3
>>> db = sqlite3.connect(':memory:')
>>> c = db.cursor()
>>> c.execute('INSERT INTO ? VALUES (1, 2, 3)', ('something',))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: near "?": syntax error
>>> c.execute('SELECT * FROM ?', ('something',))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: near "?": syntax error

#

those are meant for query parameters, like stuff in WHERE or insert values

timber lily Aug 11, 2019, 9:47 PM

#

Is anyone using Cassandra with Tornado? For some reason I can't find anything in the way of async python drivers for cassandra