databases

[charred fractal]

like a counter?

[tidal field]

so my bot has a virtual currency

and i need the column coins updated +100 for every1 that has the job Engineer set in the job column

[charred fractal]

you want to select the column from the database, and then set currentCoins = result[0][0] then do newCoins = currentCoins + 100 and then use your f"UPDATE table SET job_coins = {newCoins} WHERE clientid = {ctx.author.id}"

[burnt turret]

Probably easier to just do

UPDATE table SET column = column + 100

I believe that just does it to every row because we don't specify a condition

[charred fractal]

f"UPDATE table SET job_coins = {newCoins} WHERE job_engineer = True", is probably wheat you want.

[burnt turret]

They want to update it for every row

[charred fractal]

"Wheat" you want lol.

[charred fractal]

where everyone has the job_Engineer set in the job column

[burnt turret]

Right didn't read that part

You shouldn't use f-strings for SQL queries

Refer third pin in this channel

[charred fractal]

especially when using text inputs.

[torn sphinx]

Anyone up?

with connection.cursor() as cursor:
    cursor.execute(create_movies_table_query)
    connection.commit()

Do i need to create cursor object every-time ?

[jovial quest]

hey all does anyone know if editing the elasticsearch domain on AWS disrupt any current uploads? I am currently trying to uploads ~10 million rows through several different virtual machines and I dont want to have to restart the uploads.

[charred fractal]

ummm, I have an issue that I can't connect to my database, and I have the correct password.

[charred fractal]

How can I fix my database connection? 😦

It says Cannot connect to local host (portnumber) 😦

[thorn geode]

Yes which is why it's best to do it in the more traditional, procedural manner.

[torn sphinx]

I was using mysql for api

So every ms depends..

Just asked which is faster way And better way

[shadow tinsel]

hello, im just starting to learn about databases

the data file that im currently using is a json file

and it rapidly changes...

this is my farm for my discord bot

my question is is a json file better than an SQL database, or whatever you call it

i just cant throw my code for json away

partly because i spent so much time on it before understanding what SQL is

and, it seems faster with SQL. it's right there

[green raptor]

Interesting question i am new too, i just know how sql works but why u need faster speed?

Which cases of use

[shadow tinsel]

it's a discord bot. there are sell commands, and it rapidly changes

with discord bots, i think speed is more important than size

obviously it cant be too small

im torn 😭

been thinking about this for weeks before joining this server xd

[green raptor]

Xd

[shadow tinsel]

the main problem is, right now, it's ok

right now, there's one user object - me

json is fine, but already it's experiencing lag

so if i want my bot to get big

it's not a good choice to use json

you know what talking to someone helped me solidify my decision

i cant think of a good argument for why json is better xd so sql is probably to go to

[burnt turret]

an SQL database will not be faster than a flat json file, especially as the data grows

you can use something like mongodb which is quite close to JSON in terms of how it stores the data if you're too entrenched in json now

i'd still recommend an SQL database like postgresql though

[burnt turret]

because there isn't - JSON shouldn't be used to store data that you're constantly planning on changing. It's meant for storing constants.

[tulip basin]

    [
        1,
        1,
        2,
        2,
        50
    ],
    [
        2,
        1,
        56,
        6,
        30
    ]
]``` Hey im receiving this informations from my sqlitedb. How can i convert it to a json format like this ```
[
    [
     "unique_id":   1,
     "user":   1,
      "excercise":  2,
      "weight":  2,
      "reps":  50
    ],
    [
     "unique_id":   1,
     "user":   1,
      "excercise":  2,
      "weight":  2,
      "reps":  50


    ]
]```

my keys can be the column name from my db

[charred fractal]

Apparently, Restarting my computer fixes the connection to my database.

[tidal field]

ill test that and let you know

[harsh night]

cursor.execute("DELETE TempbanTimeRemain FROM Logs WHERE PlayerID = ? and GuildID = ?", (user.id, guild.id)) this returns this error sqlite3.OperationalError: near "TempbanTimeRemain": syntax error

[prisma girder]

Why not DELETE FROM ...?

[harsh night]

i just wanna deleet a single value

[charred fractal]

    @commands.command()
    @commands.check(is_owner)
    async def delete(self, ctx, user: discord.Member):
        if ctx.guild.id != 671947279774777399:
            return
        cursor = mydb.cursor()
        cursor.execute(f"SELECT clientid FROM users WHERE clientid = {user.id}")
        result = cursor.fetchall()
        if(len(result)) == 0:
            await ctx.send("This user does not already exist in the database!!!")
        else:
            cursor = mydb.cursor()
            cursor.execute(f"DELETE from users WHERE clientid = {user.id}")
            mydb.commit()
            await ctx.send(f"User: {user.name} has been deleted from the database.")```, this is what the table is, and how I delete only one user.

[charred fractal]

Delete from <table> WHERE <column> =

[harsh night]

alright

[charred fractal]

the only way to do one single value, is to set the value to 0, from what I know, however, you can try looking at this, https://www.w3schools.com/sql ALTER TABLE table_name ALTER COLUMN column_name WHERE <column> =

[cedar surge]

I have a little bit of experience with SQL but i was trying out SQL alchemy and SQLite3. From what i've seen SQLite3 is alot easier than alchemy. what are the downsides and ups to each of them?

[charred fractal]

I personally like MySQL, I don't know what the difference between MySQL and SQLite3 is though.

[cedar surge]

is MySQL like raw SQL?

SQLite3 is basically sql but built for python. the syntax is very similar

sql alchemy is just, very big

[charred fractal]

https://www.educba.com/mysql-vs-sqlite/ Try looking through this.

so MySQL has more datatypes than SQLite, which can be helpful, so you can restrict it from non types of those datatypes.

SQLite seems to have a lot of downgrades from MySQL.

[primal hedge]

hey whats up

I need some help with my pymongo

    'mongodb+srv://Test:Test123c7zvn.mongodb.net/test?retryWrites=true&w=majority
')``` 
With this line of code I keep getting this error
```SyntaxError: EOL while scanning string literal```

[rich current]

Does anyone here know SQLAlchemy really well?

Please @ when responding! Thank you!

[sick perch]

There are a number, just ask

[rich current]

Ok so my question is a bit complicated but hopefully someone will know the answer. I currently have a declarative mapping which has a relationship in it, see the Thing class below.
Here's an example:

class Hello(Base):
  __tablename__ = 'hello'

  id = Column(Integer, primary_key=True)  

class Thing(Base):
  __tablename__ = 'thing'

  id = Column(Integer, primary_key=True)
  number = Column(Integer, ForiegnKey('hello.id'))
  hello = relationship(Hello, backref=backref('hello', uselist=False))

Whenever I instantiate a Thing object, SQLAlchemy executes an SQL query in the __init__ function (some of the time). See the example below

hello = Hello(id=1)
thing = Thing(id=1, hello=hello)
# In the __init__ method SQLAlchemy makes an SQL query

This is extremely problematic since I am using SQLAlchemy's async framework and all SQL queries must be within an awaited statement.

Is there anyway I can prevent the SQL query from happening during the initialization phase?

References:

• https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.html#preventing-implicit-io-when-using-asyncsession
• https://docs.sqlalchemy.org/en/14/errors.html#missinggreenlet
• https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html
• https://docs.sqlalchemy.org/en/14/orm/internals.html#sqlalchemy.orm.InstanceState

Additional information can be provided such as the real world use case and the entire error upon request.

Please feel free to ask any follow-up question

Also please @ when responding thank you!

[analog cove]

Hello, I am using SQLite in Python and have a few questions on how I can effectively pass variables/bindings into the query? Lets say I have 25 columns and I want to check if each column has a certain string. I have the string saved to a variable called string_search. Is the only way to run this statement written as below (Used 3 columns instead of 25 for simplicity)?

string_search='dog'
data=cur.execute("select column from table where C1 like ? or C2 like ? or C3 like ?",(string_search+'%',string_search+'%',string_search+'%'))

This way seems extremely tedious and hard to edit later on, is there a simpler way?

[sick perch]

what are those columns named like, it sounds like you inlined something that should have been a relation

[analog cove]

@velvet ridge Hi, well its like 75% of the columns I want to search. They are named similar but different columns, such as Spec1, Spec2, Spec3, Spec4 etc.

[sick perch]

why are that many spec columns?

[analog cove]

Not sure, it's just how the data was given. I suppose it was parsed out.

In the end I am looking to write a query that returns rows where the same value can be found in any one of these 25 similarly named columns. But I think the end query will look very tedious and hard to edit, especially if there are different filtering parameters involved.

I suppose I could concatenate columns?

[sick perch]

use sqlalchemy, generate the table object, add a loop

bascially you have to generate the sql or expression with something, sqlalchemy is a good to go tool

[analog cove]

Ah ok, I will definitely use that in the future. Unfortunately I am a bit tied for time on this current assignment and I have done all the coding for other parts in SQLite =/ So if I were to stick with SQLite my only option would be the one I mentioned above?

[sick perch]

just make a python function that loops and generates the ors and the likes

i guess you do have a list of the columns

and you can just do something like ' or '.join(f"{name} like ?" for name in SPEC_COLUMNS)

[analog cove]

Weirdly enough, I tried doing it another way before, that was also a bit tedious, that looked like this

string='dog%'
data=cur.execute("select column from table where C1 like " + string + " or C2 like " + string)

But for some reason this code that ran before, now errors on syntax for no apparent reason since it worked fine before. So thats why Ive been trying to implement the bindings method I first mentioned. I am very new to sqlite (Though I know SQL).

[sick perch]

thats not how you pass string, always use bind parameters, it was pur luck it ever worked

also its not valid python to have dog% outside of a striing literal

[analog cove]

Hahaha yeah I will stick to binds from now on. Sorry, fixed that piece.

I think my coding method is very poor practice but I am not yet sure how to do it more efficiently so I suppose I will just stick to having multiple bind parameters in this instance. The query is a bit complicated because there are some pieces that are repetitive on columns with similar names, but some other unique filters as well.

[sick perch]

just make a loop to make the or, and use [search_string]*len(SPEC_COLUMNS) to make a list of the belonging params

[analog cove]

Hmmm, so you're saying make a loop that generates a string saying D1 = ? or D2 = ? or D3 = ? ?

My final query will look something like this. (Made up string names)

string1=dog
string2=big

data=cur.execute("select column from table where (C1 in(?) or C2 in(?) or C3 in(?) or C4 in(?) or C5 in(?)) and type=?",(string1,string1,string1,string1,string1,string2))

However, if I ever want to modify parts of this, it would be annoying. Such as removing one of the filters.

[sick perch]

@analog cove with a loop in place anf putting in that part of the query only, the final code would not look like that, it would insert the generated bi

@analog cove this is python code, you can jsut put that bit into the query, no need to generate it to copy and paste it

with the dynamic sql gen in place, you would have a very easy time to change things up

[analog cove]

I see, could you perhaps give me a very simple example on how that would look? Would greatly appreciate it! And thank you so much for all of your help so far.

[sick perch]

# untested
string1="dog"
string2="big"
COLS= "C1 C2 C3 C4 C5".slit()
search = " or ".join(f"{col} like ('%' || ? || '%')" for col in COLS)
data=cur.execute(f"select column from table where ({search}) and type = ?",
  (string1,)*len(COLS) + (string2,))

[analog cove]

Ahh wow! Ok so I put the columns that I want to generate the repetitive statement for in the COLS variable. So if I wanted to do this for an IN statement instead of a like, it would look like this?

string1="dog"
string2="big"
COLS= "C1 C2 C3 C4 C5".slit()
search = " or ".join(f"{col} in ('(' || ? || ')')" for col in COLS)
data=cur.execute(f"select column from table where ({search}) and type = ?",
  (string1,)*len(COLS) + (string2,))

[sick perch]

yes, but for the in statement youd usually pass in a list of items

its not clear what you do there

[analog cove]

ah yes, correct. Sorry, my example is makeshift. So if string1="dog,cat"

In that case, I can use the in statement

Ok awesome! I am testing it right now and seeing what happens, thank you SO much for this, this makes things much easier Ronny

[sick perch]

@analog cove then the query is incorrect, youd have to use in (?, ?), and have string1 actually be a tuple to expand

please start with a basic sql tutorial those handle those initial questions very well

[burnt turret]

If you're looking for a tutorial I'd recommend https://sqlbolt.com

[analog cove]

Hm ok, well then let me start simple and with one type of statement and see if I understand. Lets say I have this basic setup. where I want to input the below variable containing 3 decimals, decimal_values, into an in statement. So my setup in this case would look like

decimal_values='2.5,1.5,1.6'
COLS= "C1 C2 C3 C4 C5".split()
search = " or ".join(f"{col} in ('(' || ? || ')')" for col in COLS)
data=cur.execute(f"select column from table where ({search})",
(decimal_values)*len(COLS))

Assuming you meant split on the .slit() part.

Ahh I think I am declaring my variable incorrectly now. my decimal_values.

Since it is saying 44 bindings lol.

Maybe I need to format them as an array?

[sick perch]

Nope, wrong syntax and wrong bind params, so yeah about massive amount of binds

Also bbl, the toddler wants me back

[analog cove]

Ok np ty Ill keep workin at it

Yeah

[sick perch]

That's one of the very recent additions, I strongly recommend stackoverflow.com, it may be necessary to flush, but no idea

[analog cove]

Looks ok, but I dont know much lol. Does it run?

You can see your result by adding
rows=data.fetchall()
print(rows)

How many do you expect

[analog cove]

@sick perch I figured out how to implement it for both the in and like case. My last question is how would I modify it to then expand it to two string inputs? So for instance, lets say I wanted to search select column from table where c1 like string1 or c2 like string1 or c1 like string2 or c2 like string2?

And then if I wanted to utilize it for an IN statement, how would I pass multiple values in without it saying I am supplying too many bindings? Since when I tried to pass in decimal_values='2.5,1.5,2.6' it fails? Shouldnt the binding # stay the same in this case? Technically I am just passing in a string of values to put inside the in statement, instead of one.

[sick perch]

@analog cove if you pass in a single string, sqlite takes it a sa single string, so yeah, if you have n values, use n bindings

[analog cove]

So on the case where decimal_values='2.5,1.5,2.6', I want SQL to read it as checking 3 separate values, where it will see if each column matches any of the three. So am I not supposed to pass it as one string like that? Should I be passing it as decimal_value=["2.5,1.5,2.6"]? But then it says I have three times the bindings I had before

[sick perch]

@analog cove ideally if you have multiple inputs for a in, use a tuple to put them, so you know the lenght

if you need 3 different values, you need 3 different binds right now, or you need to use a very modern sqlite (which may not be in your python) and use json

[analog cove]

So before we had 5 columns, 1 value. So there were 5 bindings. Now, it will say I am inputting 15, right?

[sick perch]

yup

btw, sqlalchemy has stuff to handle that nicely

i strongly suspect jsut starting to use sqlalchemy , leaving the rest of the queries be and chanigng that particular one will safe you a tonn of pain

[analog cove]

Is there a way to modify the current code to account for that? And yes I will 100% look into that, I unfortunately am super tight on time right now haha otherwise I would def make the switch.

decimal_values=["2.5","1.5","1.6"]
COLS= "C1 C2 C3 C4".split()
search = " or ".join(f"{col} in ('(' || ? || ')')" for col in COLS)
data=cur.execute(f"select column from table where ({search})",
(decimal_values)*len(COLS))
rows=data.fetchall()
print(rows)

The error I get is Incorrect number of bindings supplied. Current statement uses 4, there are 12 supplied.

[sick perch]

@analog cove simply have a different search value for the decimal values stuff

for the in, you dont need the concat, and you do need a number of ?,

so youd need the help of `,'.join("?"*len(decimal_values))

@analog cove based on what i know about sqlalchemy, the time you lost since i first suggested it instead of failing with the strings and now would have been sufficient to switch that particular query from plain text t sqlalchemy

[analog cove]

Fair enough. Ok so Ive just installed the package. However, I am using a db file as my database, so i will see if I can connect to it using SQLAlchemy

[sick perch]

sqlite db file?

just use a sqlite url

if you dodnt want to start with declaring metadata. either fetch it from the db or use plain colum statements

[analog cove]

Ah ok, yeah this DB file has everything I need in it. So I assume it looks something like this? Found this on stackoverflow

dbEngine=sqlalchemy.create_engine('sqlite:////home/stephen/db1.db')

[sick perch]

yup

[analog cove]

Ok, think I got it. But i input the db file name and path etc

[sick perch]

btw, ```pycon

print(select([column("col")]).where(column("type") == "bird").where(or_(column(x).contains("fun") for x in "C1 C2 C3 C3".split())))
SELECT col
WHERE type = :type_1 AND (("C1" LIKE '%' || :C1_1 || '%') OR ("C2" LIKE '%' || :C2_1 || '%') OR ("C3" LIKE '%' || :C3_1 || '%') OR ("C3" LIKE '%' || :C3_2 || '%'))
print(select([column("col")]).where(column("type") == "bird").where(or_(column(x).in_([1,2,3]) for x in "C1 C2 C3 C3".split())))
SELECT col
WHERE type = :type_1 AND ("C1" IN ([POSTCOMPILE_C1_1]) OR "C2" IN ([POSTCOMPILE_C2_1]) OR "C3" IN ([POSTCOMPILE_C3_1]) OR "C3" IN ([POSTCOMPILE_C3_2]))

[analog cove]

Ah ok, so is this how SQLAlchemy works?

I feed in the statement in that syntax and it generates it?

Definitely seems much easier to understand!

[sick perch]

exactly, and oyu can even ask it to load your db structure

so you can do `meta = Metadata(bind=db_engine, reflect=True), and then fetch table objects off it

oh, i just noted, the reflect=True is deprecated, one is to use metadata.reflect()

[analog cove]

So how are you calling the SQLAlchemy package? Im using Jupyter Notebook, and have imported the package. So I need to connect to the file, which I suppose is different from the create_engine code I posted above?

Sorry, trying to wrap my head around it

[sick perch]

my print calls are on stff i imported via from sqlalchemy import select, column, or_

[analog cove]

Ah ok perfect now it works!

So now, if I feed that print statement into SQLITE, it will work?

[sick perch]

@analog cove no, you dont print, you give the statement to the engine, and it makes propper sqlite out of it

[analog cove]

Sorry thats what I meant, so instead of printing the statement, I can send it right to the engine

[sick perch]

@analog cove so dbEngine.execute(statement)

[analog cove]

Ok cool, I am trying it now!

It needs the table too, right? So is SQLAlchemy just a code converter, or am I trying to actually connect SQLAlchemy to my DBfile? Sorry I am a bit confused.

Ah I get it now

dbEngine.table_names() gives me the table names

Is there a good document or guide I can follow for the syntax you were using for this? Trying to figure out where I select the table for my query.

and where are you specifying the table youre pulling from in that code?

[sick perch]

@analog cove i actually forgot to include a table

@analog cove import table from sqlqlchemy and use that for now

bbl

[analog cove]

Ok cool, thanks!

Apprecaite it

[sick perch]

@analog cove did it all work out?

[stray fulcrum]

when i'm updating a value in sqlite3 how can i use a string variable to choose the column i'm updating?

[mystic bronze]

Hello, I need a little help with connecting MySQL with Python. When I start my script I'm getting error "unknown database" and I don't know why. Here's my code: ```python
from flask import Flask, request, render_template
from flask_wtf import FlaskForm
from wtforms import StringField, SubmitField,IntegerField
from wtforms.validators import NumberRange, DataRequired, Length,Email,NumberRange
from flask_mysqldb import MySQL
import MySQLdb.cursors as cursors

app = Flask(name, template_folder='Templates')
app.config['SECRET_KEY']='LongAndRandomSecretKey'
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = 'blabla'
app.config['MYSQL_DB'] = 'uhp_data'

mysql = MySQL(app)```

[charred fractal]

How can I connect to my localhost database with mysql for .gs?

[bitter yoke]

so 'uhp_data' is the correct name for a mysql database on that host, but you're still getting that error?

[mystic bronze]

yes and I manage to somehow fix it, but now I'm getting error that my table doesn't exist

[bitter yoke]

do you have a way you are connecting to MySQL at the command line or in an App so you can see what's inside it for yourself?

[mystic bronze]

this is how I'm trying to connect to MySQL python @app.route('/', methods =["GET", "POST"]) def home(): cursor = mysql.connection.cursor(cursors.DictCursor) form = GreetUserForm() if form.validate_on_submit(): cursor.execute('INSERT INTO Users (fname, lname, email, city, address, zcode) VALUES ( % s, % s, % s, % s, % s, % s)',(form.fname.data,form.lname.data, form.email.data,form.city.data,form.address.data,form.zcode.data,)) mysql.connection.commit() #cursor.close() return 'success' return render_template('index.html', form=form)

[bitter yoke]

So is there definitely a table called “Users” in that db?

[mystic bronze]

i don't know is this a right way since

yes

[bitter yoke]

In the db that you used for MYSQL_DB?

[mystic bronze]

yeah the name of my table is Users

[bitter yoke]

and it's in the db with the name that you used for MYSQL_DB

?

[mystic bronze]

yep

[bitter yoke]

Are you connecting to MySQL "by hand", somewhere outside of Python, to create your databases and tables? And if so, what are you using for that?

[mystic bronze]

I created my table inside MySQL but connection is inside python script

[bitter yoke]

ok, meaning command line mysql?

[mystic bronze]

but for some reason error is: table uhp_data.users doesn't exist

but table name is Users

not uhp_data.users

[bitter yoke]

uhp_data.users is the "long name" for the table, so if your current db is uhp_data, both names work -- but uhp_data.users is the "full name" no matter what your current db is

that part should be fine for MySQL purposes

are you on linux? did you try just a lower-case U?

[mystic bronze]

this is how my database looks like```create database uhp_data default character set utf8 collate utf8_general_ci;
use uhp_data

create table Users(
id int(11) not null auto_increment primary key,
fname varchar(50) not null,
lname varchar(50) not null,
email varchar(100) not null,
city varchar(50) not null,
address varchar(50) not null,
zcode varchar(50) not null,

) engine=InnoDB auto_increment=2 default charset=utf8;

select * from Users;```

[bitter yoke]

on some OSes MySQL table names are case-sensitive

[mystic bronze]

nah w10

[bitter yoke]

interesting. so can I see the exact error?

[mystic bronze]

MySQLdb._exceptions.ProgrammingError: (1146, "Table 'uhp_data.users' doesn't exist")

[bitter yoke]

(also, to prove this other part, you can try "select * from uhp_data.users" by hand, too)

[mystic bronze]

i did try this but it didn't work

[bitter yoke]

but if you do

use uhp_data

and show tables;

it shows up?

[mystic bronze]

not rly

[bitter yoke]

ok that's what you want to find

just like if python said "file not found" you'd want to make sure the file was there, in the right folder

same with this table and database

so if you can see this, then Python should be able to see it too:

Your MariaDB connection id is 111
Server version: 10.3.25-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use uhp_data
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [uhp_data]> show tables;
+--------------------+
| Tables_in_uhp_data |
+--------------------+
| Users              |
+--------------------+
1 row in set (0.000 sec)

[mystic bronze]

Oh i didn't remove comma after zcode column

now is working

lol

@bitter yoke thx for help

[bitter yoke]

Ohhhh nice! 🙂

[sleek epoch]

im trying to increment a value and get this error

[burnt turret]

You started that string with 4 quotes

[stray pecan]

Hello. I want to use databse in my project but I dont understand how should I work with it. I found example how to connect to db, use cursor and close connection but how can I split it for few methods - connect to db, use cursor and disconnect from db.

Example which I found:

def connect():
    """ Connect to the PostgreSQL database server """
    conn = None
    try:
        # read connection parameters
        params = configdb()

        # connect to the PostgreSQL server
        print('Connecting to the PostgreSQL database...')
        conn = psycopg2.connect(**params)

        # create a cursor
        cur = conn.cursor()

        # execute a statement
        print('PostgreSQL database version:')
        cur.execute('SELECT version()')

        # display the PostgreSQL database server version
        db_version = cur.fetchone()
        print(db_version)

        # close the communication with the PostgreSQL
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()
            print('Database connection closed.')```

I can create connection method like that:
```py
def connect():
    """ Connect to the PostgreSQL database server """
    conn = None
    try:
        # read connection parameters
        params = configdb()

        # connect to the PostgreSQL server
        print('Connecting to the PostgreSQL database...')
        conn = psycopg2.connect(**params)
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)```
but I do not know how create cursor and disconnect method. How to access a connection from connect method to close it?

[harsh night]

cursor.execute("UPDATE Logs SET ban_Infractions = ? and TempbanTimeRemain = ? WHERE GuildID = ? and PlayerID = ?", (infractions, unbanTime, ctx.guild.id, user.id)) this sets ban_Infractions and TempbanTimeRemain to null when they are suppost to be int and datetime. am i doing something stupid?

[blissful knot]

Is it possible to use variables as table names in SQLalchemy ORM, and if so how would I implement it?

[delicate rune]

What is the best way to have keys that allow for joins when needed in PostgreSQL? I was thinking of using references table(id) for all of my tables with id being in my main table

Is there a better way of doing this?

[simple turret]

Subject: MongoDB
Question: How do I check if a single document has a field and value.
Example

{_id: 1, value: 25},
{_id: 2, value: 15},
{_id: 3, value: 25}

PsuedoCode:
if _id == 1 && value == 25:
     #do this...```
Please @simple turret if you can help

[lime echo]

How to DELETE a certain data only if it exists? using psql and python.

[burnt turret]

It'll only delete rows based on the condition you specify

DELETE FROM table WHERE condition

[sick perch]

https://docs.mongodb.com/manual/tutorial/query-documents/#specify-equality-condition ?

[halcyon fox]

how do I create a table-level trigger in MySQL?

one that gets fired only once for each query against the table, no matter the number of rows affected

[delicate bluff]

Can I store data locally on a singular file and manage it using SQL?
I can't find it anywhere.

[burnt turret]

An sqlite database would be file, which you can manage using SQL

[delicate bluff]

oh I see.
sqlite3 seems to exactly that, thanks.

[minor zealot]

items = [
    {'id': 1, 'name': 'Phone', 'barcode': '893212299897', 'price': 500},
    {'id': 2, 'name': 'Laptop', 'barcode': '123985473165', 'price': 900},
    {'id': 3, 'name': 'Keyboard', 'barcode': '231985128446', 'price': 150}
]
ids = [i_id['id'] for i_id in items if 'id' in i_id]
names =[i_name['name'] for i_name in items if 'name' in i_name]
barcodes =[i_barcode['barcode'] for i_barcode in items if 'barcode' in i_barcode]
prices = [i_price['price'] for i_price in items if 'price' in i_price]

SQL.execute('create table if not exists items ("id" integer primary key autoincrement,"name" text, "barcode" integer not null,"price" integer)')
SQL.execute('insert into items (id,name,barcode,price) values(?,?,?,?)', (ids, names, barcodes, prices))

can u help me here ?

  File "C:/Users/SIDALI/PycharmProjects/flask/demoapp.py", line 20, in <module>
    SQL.execute('insert into items (id,name,barcode,price) values(?,?,?,?)', (ids, names, barcodes, prices))
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

Process finished with exit code 1```
it shows me that error 
`

[torn sphinx]

Try printing the values all of them and show what they output

I see they are all lists

[halcyon fox]

how do I change the database directory on MySQL?

I don't wanna store it on program data, I don't even wanna store it on C drive

[wraith yoke]

How can i delete row of postgres database table, using async pg, I'm dealing with discord.py and connected postgres database

[minor zealot]

yes ty ❤️

[burnt turret]

https://sqlbolt.com/lesson/deleting_rows
A delete query is structured as DELETE FROM table WHERE condition

You'd just pass the query to connection.execute as usual

[wraith yoke]

Thanx a lot i know how to do it in raw sql but i think it should be different command in async pg

[burnt turret]

Why would it be?

[lime echo]

oh oki! how do I add a row only if it doesn't exist?

[wraith yoke]

Nice thanx a lot, i really don't know this is n_th time that you help me with database 😅 thanx

[wraith yoke]

Execute insert

[lime echo]

it will insert a duplicate

[burnt turret]

Look up UPSERT

Depending on your database the syntax might be different I think

[wraith yoke]

First you can fetch and check with if statement

[burnt turret]

You should probably have some kind of constraint which stops duplicates

[lime echo]

im using Postgresql with python.

[burnt turret]

Like a primary key or a unique constraint

[lime echo]

not sure if I have any.

ohh that makes sense

[wraith yoke]

I'm in the same scenario and I've done like i said

[lime echo]

it costs a lot like that.

[wraith yoke]

In terms of?

[burnt turret]

In postgres the syntax is something like INSERT ... ON CONFLICT DO UPDATE ...

[lime echo]

imagine if I do it for like 3k times per command...

[burnt turret]

But here the "conflict" refers to some conflicting constraint like a primary key

[lime echo]

that sounds interesting.

i'll look up for ON CONFLICT

[burnt turret]

@lime echo I made a mistake in my example, you don't want to be upserting right? You want to do nothing in case the data already exists?

The idea is the same - ... ON CONFLICT DO NOTHING; didn't mean to write update there

[high silo]

What's the best way to store the database password for a web app?

I'm using .env files and os.environ

[prisma girder]

As far as I know .env file is rather for development stage

[high silo]

I'm currently only on dev environment, but I will be running it on prod soon

What is the best practice for that?

[torn sphinx]

Env is also fine to use on prod

[prisma girder]

I am storing values directly inside environment variables

[high silo]

But that does not persist the values, right?

So if the server reboots, the exported var is not set again

Not automatically, at least

[cedar surge]

you dont need to put them into different colums

oh

goodbye

[lime echo]

thanks Anand!

[torn sphinx]

Hey I am looking into deployment my first Flask app, if I choose to use PostgresQL do I need to host it on a different server from where I host the Flask app?

[sinful condor]

this kind of goes here but also is web File "app.py", line 45, in dashboard collectionfound = await collection.find_one(ping_cmm) RuntimeError: Task <Task pending name='Task-40' coro=<ASGIHTTPConnection.handle_request() running at /opt/virtualenvs/python3/lib/python3.8/site-packages/quart/asgi.py:72> cb=[_wait.<locals>._on_completion() at /usr/lib/python3.8/asyncio/tasks.py:518] created at /opt/virtualenvs/python3/lib/python3.8/site-packages/quart/asgi.py:28> got Future <Future pending cb=[_chain_future.<locals>._call_check_cancel() at /usr/lib/python3.8/asyncio/futures.py:360]> attached to a different loop I am getting that error with this code @app.route('/dashboard', methods=['GET']) async def dashboard(): id = request.args.get('id') collection = app.db["main"] guild_id = id ping_cmm = {"_id":guild_id} collectionfound = await collection.find_one(ping_cmm) name = collectionfound["name"] return await render_template('dashboard2.html', gname=name, gid=id) and here is where I set up app.db app = Quart(__name__) mongo_url = os.environ.get("MONGO_URL") cluster = motor.motor_asyncio.AsyncIOMotorClient(mongo_url) app.db = cluster["discorddatabase"]

[minor zealot]

why it shows me like that in column

and string

[long slate]

it is trying to highlight it I don't use pycharm these days but for most of IDEs it when you select the same word

[minor zealot]

u mean when i'm using the same word in many times pycharm make it colored like that ?

[long slate]

No

I mean that when you select the same word

it highlights that

idk why it is the case that you didn't selected it but it highlighted

[minor zealot]

yes its like an error in flask_sqlalchmey

[long slate]

Like I selected one word called commands in highlighted other things

[long slate]

idk not sure why is that so

[minor zealot]

😢 mm ok anyway thanks

[long slate]

Maybe try restarting your Pycharm once

it might work lol

[minor zealot]

i had this problem from about 2days

[long slate]

[minor zealot]

#web-development

[balmy minnow]

I'm currently working with django and am working with two models, User and Game, so I have this sort of thing:

from django.contrib.auth.models import User

class Game(models.Model):
    ...
    users = models.ManyToManyField(User)

I've used a many to many as a user should be able to be in multiple games and a game should be able to have multiple users, what I am now wanting to do is to keep track of when a user last edited each game. I was thinking it would be neat if I could just store it in the many to many table, although I don't know if that would be a great idea/would be possible with the django ORM. Any ideas on how to approach this?

Aha, I think this is what I wanted https://docs.djangoproject.com/en/3.1/topics/db/models/#intermediary-manytomany

yeah that's perfect

[trim knoll]

can someone help me a lil :/

I have this sql query

            await con.executemany('INSERT INTO points(user_id, xp, lb_id, channel) '
                                  'SELECT val.user_id, val.xp, val.lb_id, val.channel '
                                  'FROM( '
                                  'VALUES($1, $2, $3, $4) '
                                  ')val(user_id, xp, lb_id, channel) '
                                  'JOIN leaderboards USING(lb_id, channel) '
                                  'ON CONFLICT (user_id, lb_id, channel)'
                                  'DO UPDATE SET xp = points.xp + $2', data)

but it raises JOIN/USING types text and integer cannot be matched

but when i run a sample in the psql cli:

bunkerbot=# insert into points(user_id, xp, lb_id, channel)
bunkerbot-# select val.user_id, val.xp, val.lb_id, val.channel
bunkerbot-# from(
bunkerbot(# values(378957690073907201, 1, 1, 772491743156240429)
bunkerbot(# )val(user_id, xp, lb_id, channel)
bunkerbot-# join leaderboards using(lb_id, channel)
bunkerbot-# on conflict (user_id, lb_id, channel)
bunkerbot-# do update set xp = points.xp + 1;
INSERT 0 1

it seems to work perfectly. how do i solve this in the python script

[torn sphinx]

whats this client = pymongo.MongoClient(os.getenv("DB_URL"))

[merry nymph]

def sql_make_our_tbls1(con):
    """Make all the tables we have so far."""
    cursor_obj = con.cursor()
    
    cursor_obj.execute(
        """
        CREATE TABLE IF NOT EXISTS BankAccounts (
        bank_account_id INTEGER PRIMARY KEY,
        server_id INTEGER NOT NULL,
        user_id INTEGER NOT NULL,
        balance INTEGER NOT NULL DEFAULT 0,
        
        CONSTRAINT BankAccounts_unique_per_member_chk
            UNIQUE(server_id, user_id),
        
        CONSTRAINT BankAccounts_no_negative_balance_chk
            CHECK (balance >= 0)
        )

My foreign key relationship isn't working for some reason

def sql_make_our_tbls2(con):
    """make our second table."""
    cursor_obj = con.cursor()
    
    cursor_obj.execute(
    """
    CREATE TABLE IF NOT EXISTS BankTransactions (
    transaction_id INTEGER PRIMARY KEY,
    bank_account_id INTEGER NOT NULL,
    occurred_at INTEGER TIMESTAMP NOT NULL,
    amount_withdrawn INTEGER NULL,
    amount_deposited INTEGER NULL,
     
     FOREIGN KEY (bank_account_id)
     REFERENCES BankAccounts(bank_account_id)
         ON UPDATE CASCADE
         On DELETE CASCADE,
         )"""
    )
    con.commit()

everything works fine. Making the tables adding values. When I delete a record in bank accounts it doesn't delete on the transactions table

[rich current]

Can anyone explain what the session.begin function does in SQLAlchemy?

Whenever I have two of them in a row
I sometimes get this error:

sqlalchemy.exc.InvalidRequestError: A transaction is already begun on this Session.

For example:

with Session(engine) as session:
    with session.begin():
      session.add(some_object)
      session.add(some_other_object)
    
    # Errors out here
    with session.begin():
      session.add(another_object)
      session.add(some_other_object_again)

How do I fix this?

Thank you! And please @ when responding

[whole coral]

Hi all. I'm getting an error:
asyncpg.exceptions.UndefinedColumnError: column "warns" of relation "discord_users" does not exist
in line
await db.execute('INSERT INTO discord_users (id, nickname, join_date, gold, warns) VALUES($1, $2, $3, 0, 0) ON CONFLICT (id) DO NOTHING;', member.id, member.display_name, member.joined_at)
maybe someone have any idea of how can I deal with it? Because I double checked and the column "warns" is present

[merry nymph]

Just fixed it!

Turns out you need to execute PRAGMAS before you start making your database tables

tried deleting the foreign keys and it worked

[whole coral]

hm...what if I just drop both tables and let the script recreate them?

[torn sphinx]

does anyone use mysql here

[vital orbit]

hello

[static brook]

what does your data look like?

do you have a model?

[torn sphinx]

no actually..

i am just starting to learn databases

but

mysql isnt starting up my server

[static brook]

I was talking to Thoth actually

[vital orbit]

wdym by model? that code before isnt it?

[torn sphinx]

oh

[static brook]

what kind of data do you have?

[vital orbit]

{
    "Wall Strobes":
    {
        "15cd":60,
        "30cd":83,
        "75cd":136,
        "95cd":155,
        "110cd":179,
        "135cd":209,
        "185cd":257
    },
    "Ceiling Strobes":
    {
        "15cd":60,
        "30cd":86,
        "75cd":142,
        "95cd":164,
        "115cd":191,
        "150cd":228,
        "177cd":264
    },```
rigth now it is json data

[static brook]

what is "Wall Strobes"?

[vital orbit]

two devices shown here, each with multiple settings

call it a device

[static brook]

what's a "device"?

[vital orbit]

wall strobe as in fire alarm strobe

[static brook]

Ah, okay

[vital orbit]

lemme get a screenshot of my desktop app quick to show you

the value (number in the json data) is used for calculation for circuit current totals

i am working on porting my apps to a flask app because loading these after using pyinstaller is just painful. too many minutes to load simple apps

so i am just wondering if continuing to use a json file for this data is better/faster/cleaner than sql database.

i have stuck with json files becuase they are easy to edit when the values change due to updates, but i could probably write an admin panel site to edit the values in flask

thoughts? @static brook
no wrong answer, just looking for opinions here

[static brook]

hm, are these the only kinds of data you are storing? or is there more?

because I think you can get by with something like mongodb

[vital orbit]

nope, thats it. It is just a calulator. I just have a lot of these

because i have not dealt with mongodb, what makes it different than something like sqlite or mysql?

[static brook]

it's a document database

stores things in JSON-like documents

[vital orbit]

so if i stored my json file in it, i could pull it out but then still have to parse the json?

is mongodb portable like sqlite?

my office uses sql server (microsoft stuff) and i would prefer to just stay away from that

[static brook]

nope, mongodb provides a python wrapper

https://pymongo.readthedocs.io/en/stable/tutorial.html

[vital orbit]

so mongodb might not speed up the data but would contain it to one central location

[static brook]

hm, I'm not very familiar with mongodb and I don't think it's portable

if you want portability I think you'd be better off with your original intuition to use sqlite

[vital orbit]

i could care less, but my IT dept wont let me install anything, so i am trying to stay within my limits

[static brook]

you can use a good ORM like sqlalchemy to interface with the database

https://www.sqlalchemy.org/

[vital orbit]

i might stick with sql as the one sysadmin who is on my side is a sql expert

but i should give mongodb a try. that looks damn interesting

@static brook thanks for you help, input and insight. I think i need to talk to my sql guy and see what he is willing to help me with. I have a basic understanding of sql but i would end up being the guy who writes DROPTABLES for some stupid reason

[static brook]

no problem!

[spare vapor]

Can anyone help me out with a problem with postgres?

I have a postgres server & a database with the same name vault_db created from inside a docker container. It is open on port 5400 outside the docker so I can add it from my local pgadmin by setting up the server and database. But when I go to the bash of the docker container and run psql and list all the database, it does not show vault_db and only shows postgres, template0 and template1

Why is this happening?

[rustic quarry]

i get the following error on my pgsql query

ERROR:  syntax error at or near "CREATE"
LINE 39:   CREATE INDEX email ON "xf_user" (email(191)),
           ^
SQL state: 42601
Character: 1908

what am i doing wrong in here?

mention me if anyone replies

[forest charm]

can somebody help with pandas
ValueError: If using all scalar values, you must pass an index

[spare vapor]

what are you trying to do?

[forest charm]

i solved it already

doesnt matter

[spare vapor]

okay

[torn sphinx]

hey, Is there any way to erase data from json? I need this for unmute command, beacause the mute command stores info about muted user in json, so while someone unmutes him, the data about muted user will be deleted

Here is how the json looks:

{"id": "812025974513860670", "when": "2021-03-30 16:50:49.064603", "by": "Agent 2022#3666", "reason": "test"}

[mental jewel]

                cursor.execute("SELECT ? FROM commands WHERE guild_id = ?", (comm, ctx.guild.id))
                command = cursor.fetchone()
                print(command[0])

why does this return the column's name instead of the value it has?

[burnt turret]

what you're essentially doing now is SELECT "string" FROM table ... which will always just give you the string back

the ? placeholder doesn't work like an f-string

they're only meant to be used for values (like the guild_id) in this example, not for column/table names

[rain plank]

is the json a list of dicts?

[ebon fjord]

Hey any mongo experts here, is there any way to easily setup replica mode in dev env.. I know i can do replica mode in single database. But if replicaset is defined then mongo wants that user runs rs.initiate() to start replica mode. One option was to use --eval but its deprecated. I just want to run rs.initiate(). Im using docker and docker-compose.

[south ruin]

Any idea how to add a unique constraint on 2 columns using SQLAlchemy?

class MyTable(Base):
    __tablename__ = 'MyTable'
    id = Column(Integer, primary_key=True)
    first_name = Column(String)
    surname = Column(String)

[thorn lark]

I am making a desktop application by using python it actually has a lot of sql queries which takes time to load in my GUI...is there any way for me to resolve the load time or use something like metadata, threads or any concept like that

I am familiar with these, metadata and threads terms but i dont know how to implement it in python yet..or is there any other way for me to reduce or eliminate the load time

can anyone help me with this? tag meeeee

[reef pilot]

@bot.event
async def on_message(message):
    counter = 0
    messageCheck = guildmessages.find_one({"_id": message.author.id, "guild": message.guild.id})
    oldCounter = messageCheck['counter']

    if messageCheck is None:
        counter +=1
        guildmessages.insert_one({"_id": message.author.id, "guild": message.guild.id, "counter":counter})
    else:

        oldCounter = counter + 1
        guildmessages.update_one({"_id": message.author.id, "guild":message.guild.id}, {"$set":{"counter": oldCounter}})

    await bot.process_commands(message)

oldCounter = messageCheck['counter']
TypeError: 'NoneType' object is not subscriptable

how do I fix this?

[delicate rune]

Anyone knows why PostgreSQL might be hanging from a basic query like create table?

My laptop is fine and has enough ram and space

[torn sphinx]

how to add a new row

[torn sphinx]

does sb know how i can get the number of write operations my redis db did?

[slow raven]

What database is good for repl.it?

[torn sphinx]

how to stop duplicate entry

[sturdy kestrel]

guys may i ask for some docs or some video for displaying or filtering all own uploads of the user?

[burnt turret]

if this is SQL, add some constraint on one of the columns, like UNIQUE or PRIMARY KEY

[neat juniper]

you can do python if obj in database_query_result: # do something else: # do something

[versed geode]

is there a sql for "select top 5 entries within the table where the column x is the closest to this value"?

so i don't know the definite value, but i just want the closest one to it

[pseudo flame]

hi! im using mongoDB and im wondering the best way to create a 8 digit, alphanumerical ID for each one of my documents

is there a way to make it autogenerate that ID

or would i have to create code to check if the ID is in the DB

and generate a new one

[torn sphinx]

If we have to make a inventory for each player

Do we have to make new table for each of them

[burnt turret]

Why does it have to be 8 digit?

Mongodb makes an ID automatically for every document

It'll have the key _id, why not just use that? It'll be unique as well

[pseudo flame]

because im using it for a URL

@burnt turret

i dont want a 20 digit ID for a URL

[torpid mountain]

looking at the mariadb connector

do i have to create the db myself?

the way the docs are setup make it seem like ive to create a db myself though i could do that thru python and not just connect to a existing database

is it just the mariadb library?

i just found that with the mysql connector you can completely create it from scratch

[native wyvern]

select x from table sort by abs(x - value) limit 5

[versed geode]

thanks

[south harbor]

Hello guys, im trying to learn about databases. Could some one tell me about which one is good for beginners?

[cursive yew]

I learnt mysql with python first. Its pretty good and basic. I recommend you start with that. But mysql is not a good option for producing apps. It is only good for learning databases

[versed geode]

sql: sqlite
nosql: tinydb

no need to worry about setting up servers etc, and both have excellent python libraries

[south harbor]

ok, nice! but what do you think about sqlite?

[south harbor]

nice!, im interested in learning sqlite!

[cursive yew]

i have used it many times. it is quite good too

[south harbor]

ok cool!

[versed geode]

wish you the best of luck :)

[south harbor]

thanks @versed geode

[torn sphinx]

can someone help me with this

i dont understand this

[broken bear]

So im running dockers using WSL. The docker contains a PostgresSQL and I'm having trouble copying a csv because of the my confusion in file directories.
How do I properly construct the absolute path?\\wsl$\Ubuntu\home\tomas1337\estateph\backend\app\alembic\versions\csv\mint_residences.csv

[torn sphinx]

hello

can i get some help with my data base

(sqlite3 btw)

[pseudo flame]

what do you need help with

@hybrid zephyr

whats your problem

[ebon skiff]

I do this too

Asking for help and then solve it on my own

[storm sierra]

I completely forgot how to insert something so I can select it and do “WHERE guild_id=?”, (ctx.guild.id, )

Rip

await cursor.execute(“INSERT INTO logchannel(logchan, guild_id) VALUES(?,?)”, (ctx.guild.id, ))? Oof cant really remember, can someone help me remember?

Aiosqlite btw

[river ice]

It’s a lot easier to start with the question when asking for help with something

[dusk junco]

Hey! I have a question: so, I'm trying to share a database with other developers by using Docker containers. So far, I've created a volume, but how do I store the database information inside that volume and I do I assure it stays stored inside the container? Should I copy the conf file from the database and mount it on the volume?

I've created the volume with docker volume create --name vol-name and this is my docker-compose (it's still missing the volume section I know):

version: "3.0"
services:
  db:
    image: postgres:11
    container_name: postgres_db
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=gitlab
    ports:
      - 5432
    networks:
      - db_net

networks:
  db_net:
    driver: bridge

[mild vector]

Hi, how can I store python class instances in my database please ? Thank you

[wooden urchin]

ahh... databases..... my wost nightmare.....

[torpid mountain]

well you did everything already

you create a volume with docker volume create NAME

and then just mount it either when you run the container or in your docker compose file

[dusk junco]

OK, but I should just add the volumes section inside my docker-compose, it that it? @torpid mountain

[torpid mountain]

yea

[dusk junco]

Sorry to be a pain in the ass, but to add it, I should just write my vol-name:/<path>?

[torpid mountain]

yea

YOURVOLUME:/var/lib/postgresql/data

[dusk junco]

Ok, but that way, isn't the database going to be stored only in my local machine? The bottom line here is to other developers to access it and change its content and I can view those changes as I said. Sorry for all the questions tho

[torpid mountain]

it depends where the container is hosted

well if you want that other people have access to it you shouldnt run it on your local machine

and run it on a server or in the cloud

[silk rose]

whats the best way to reconnect a mysql server after its gone away? im using it for a discord bot but i cant seem to figure out how to reconnect it after times out. Tried using a reconn() function that ran before every query calls but that didnt seem to help, any help?

def reconn():
  if not conn.open:
    conn.ping(reconnect = True)```

[surreal stream]

python just released a fully-developed official pep that'll change booleans forever?!?!?!? and it's gonna be implemented in 3.11

link -> https://docs.google.com/document/d/1gvq4rdMVzrEFAlVCSPvGs4mstI8831seOEDpwiRRWW4

[forest flower]

Hi everyone, I'm making a tool to make MySQL queries easier, and the project is called PySQL. Right now, I'm done with the Command-Line program, and will make a GUI for the same.
Link to source code: https://github.com/Devansh3712/PySQL
It would be a great help if anyone could give suggestions/reviews, and star & fork the repository!

[quaint lark]

Guys, hope you are doing well. I need some help. The value in the Size column are in different measurements, like ML, L, OZ etc. How can I convert them to ML?

Hope this is the right channel for my question.

[torn sphinx]

Hi

[royal widget]

quack

[torn sphinx]

Microsof sql server

While using AdventureWorks2017 as data source, make single table import ETL package into newly created database.
ETL has to succeed no matter how many times you will execute it.
Use stage table and “Execute SQL Task” for data merge (avoid using “Lookup task”).

I created database with schema but what about table?

ource table name: [AdventureWorks2017].[Sales].[SalesOrderDetail];

Target db name: “DB_Exercise” (use multiple filegroups if needed);
Target db objects schema name: “Sales”;
Target table name: [DB_Exercise].[Sales].[SalesOrderDetail] (add indexes (primary key is mandatory), partitioning if needed);

[swift fossil]

Postgres gud with Python ryt?

Worth learning?

[celest edge]

Hi! I recently encountered this web scraping script, and was wondering how one would go about modificating the XPaths to make it work on more sites :)
https://github.com/kivaar/long-live

[robust summit]

Hey, in databases, is there a concept of "summarizing" data to reduce the complexity of some queries? That probably doesn't make sense, but I'll give an example

Say I am making the database for a bank with an online banking website. I keep track of every transaction that someone makes from their checking account. There is a "Balance" listed that shows the current balance of this account. To calculate this balance, I need to look at every single transaction that someone has ever had; potentially tens of thousands of transactions. That seems like a waste.

My initial thought is maybe I could keep track of the account total at the end of each month. At the end of the year, sum those months and keep track of that as an end of year total. Then, to get the balance, for the past 10 years and 6 months, I would sum the 10 year summaries, and 6 month summaries to get the current balance. Is there a better way that this is handled

[torn sphinx]

Why not just Store the balance in the accounts table

So the actual balance as a value in a column

This way you don’t need to calculate it by looking through all transactions

[robust summit]

I guess that's a good point ha. I guess I was thinking it could become desynced from the actual value, but perhaps that's just up to the application logic to manage

[storm sierra]

Can someone help me remember?

[torn sphinx]

hi

I have a problem compressing bytes to a zip file, I thought that a zip file would make the content take up less memory than a json or utf-8 but it was not

[torn sphinx]

I already solved it, I was making a mistake, in a heavy string, I wrote in a json file and it occupies 85mb, while in zip only 3kb

[torn sphinx]

and the zip file read and write speed is faster than a json one, so json based databases are crap

[rich trout]

@inner sentinel

[rich trout]

So long as all your update queries fire atomically then the balance row won't become unsynced. If you need the extra concern, you can mix the two operations into a transaction with appropriate serialization, ensuring that the current balance row in a balance table matches the historical total exactly. But most use cases (afair) don't actually need the two balance/historical to match, they're used for completely separate purposes, eg, a balance total and a balance sheet.

Some systems also store balance historically, in which case you can view balance/transactions at a given "time"--but this is expensive, and maybe not what you want

In other cases, with rapid changes, it's more reasonable to hover the recent transactions/balance in something like redis, and grab them from there while the backend serializes them down cheaply wiht slight syncing misorder

That way you can ensure that together they remain synced, whereas on different systems/redis interfaces they may disjoint with the backend, but still synced together

[torn sphinx]

can someone please tell me why

c.execute("INSERT INTO quotes(quote, index) VALUES(:quote, :index)",
                            {"quote": quote, "index": index})```

raises a syntax error

near "index": syntax error

[torn sphinx]

@bot.event
async def on_ready():
    print("Bot is ready.")
    cluster = MongoClient("")    
    bot.vouches = cluster["discord"]["discordyz"]
    


@bot.command()
async def vouch(ctx,args):
    embed_add_vouch=discord.Embed(title="You added a vouch!", description="Vouch was added to " + args[5: ],color=0x00d9ff)
    await ctx.send(embed=embed_add_vouch)
    
    bot.vouches.insert_one({"name":"lennados","vouches":1})

Hey, why does this not insert a new document to my database?
I want it to be like this:
name: lennard
vouches: 1

[rough hearth]

!warn 512326089578774539 Advertising online courses is not appropriate for this server.

[delicate field]

:incoming_envelope: :ok_hand: applied warning to @plucky mantle.

[prisma girder]

If you want to get last record you should rather use ORDER BY

[burnt turret]

now it orders the results by userid

not by when the userid was inserted

no clue honestly I've never had to do that

oh then you can order by time

ORDER BY TIME DESC would give it to you in newest-to-oldest format i think

it will not sort the table, it will sort the result set that the database gives you

the data that you get will be sorted

the table will still be in its old order

[robust summit]

Thanks for the info!

[inner sentinel]

What? I don't have a DB q rn

[dusky iris]

I want to add a function which lets a user create a table but I don't know how many columns he will add, should I use a list for this or something else?

[torn sphinx]

pls help

[karmic solar]

postgres db

[true lantern]

i got a error can someone help me
https://paste.pythondiscord.com/agajazihux.sql

[crude trench]

Hey, I'm sorta wondering when it's a good idea to use classes and when to use databases, and how much those two things are related at all. I'm trying to make a spaced repetition program sorta like anki and i'll have approximately 5k words which each will have some data assigned to it. I'm totally new to classes and ive dabbled a little bit with sqlite. I'm not sure if i'm gonna learn about classes to have each item be represented as an instance or if i should simply let each word be a row on a database and the data be columns

my intuition is that classes are easier to work with in code but the downside is it's harder to store the information when theprogram is not running (which is essential). For classes i'd also have to learn about dynamically adding 5k instances, i've seen just a few questions on stackoverflow about that and it's confusing me a bit

anyway im putting it here instead of the dedicated question channels because i dont really have a clear question more a bunch of thoughts in my head and i would love to get some pointers from more experienced programmers about the way to go about this 😃

[charred fractal]

make sure you commit after inserting.

[burnt turret]

pymongo doesn't really follow the DB api

there is no "committing"

[true lantern]

i got a error can someone help me
https://paste.pythondiscord.com/agajazihux.sql

[burnt turret]

except in the case of transactions i think

[charred fractal]

you never created a table called guild, in your database.

[true lantern]

https://paste.pythondiscord.com/ofalahewaw.py
i do i think

[empty haven]

Depending on the db you are using you might have to make them from the db shell itself

[proven arrow]

Classes themselves don’t persist data. Although their instances can hold data, it’s only in memory. I don’t really see any relation and you have listed the main advantage of it which is it might make working with the data easier if you represent the data as a class/object. However, if you want the data to be persisted then you need to use some sort of storage, which can be a database.

[proven arrow]

Where are you creating the table ?

[proven arrow]

https://www.w3schools.com/sql/sql_view.asp

[crude trench]

ok i see, thank you! I'm looking into starting to do that now with sqlite. I learned about pickling today and i thought i could use that for classes but i suppose sqlite will be easier anyway

[blissful knot]

Opinion question for the channel: I am working on a website for my discord bot using Django, and I am wondering if there is an advantage to having Django and the bot interact with the same database. (right now I have a separate database system for the bot that Django can access through an API.)

[wooden basin]

Hello, i need help, how can i pass values in a update execute with psycopg2?

i try this but no works: ```py
cursor.execute("UPDATE example SET next_e = %s", (example_value,))

[burnt turret]

Did you commit as well?

That does look like the right format for passing parameters in psycopg2

[torn sphinx]

hi

[proven arrow]

The most common or easiest way is to use pattern matching with the LIKE operator. https://www.w3schools.com/sql/sql_like.asp

[storm sierra]

Can someone help me remember how to insert something where guild id is ctx.guild.id?

await cursor.execute(“INSERT INTO logchannel(logchan, guild_id) VALUES(?,?)”, (ctx.guild.id, ))? Oof cant really remember, can someone help me remember?

[serene pivot]

Can I get advice on the best way to insert into multiple tables simultaneously in postgres?

would this work and be efficient?

        query = '''WITH username_insert AS (
                    INSERT INTO usernames(user_id, username)
                    VALUES ($1, $2) RETURNING id
                   ),
                   nickname_insert AS (
                       INSERT INTO nicknames(serveruser, user_id, server_id, nickname)
                       VALUES ($3, $4, $5, $6) RETURNING id
                   )
                   INSERT INTO userroles(serveruser, user_id, server_id, nickname)
                   VALUES ($3, $4, $5, $7)
                '''

Or would using a begin statement be better?

        query = '''
        BEGIN
        INSERT INTO usernames VALUES ($1, $2) ON CONFLICT (user_id) DO NOTHING;
        INSERT INTO nicknames VALUES ($3, $1, $4, $5) ON CONFLICT (serveruser) DO NOTHING;
        INSERT INTO userroles VALUES ($3, $1, $4, $6) ON CONFLICT (serveruser) DO NOTHING;
        END;
        '''

haven't been able to test either one, but what's recommended?

[proven arrow]

Why do you want to insert with a where?

@serene pivot use a transaction given by the library https://magicstack.github.io/asyncpg/current/api/index.html#transactions

[serene pivot]

Thanks! I'll look into it. I got it working nicely using the with statements, but this is super useful in the future if I don't switch over now.

[torn sphinx]

hey guys
anyone familiar with postgres?
struggling to set it up
i'm running psql -u username
then it asks for my password?
but i'm just setting up my account ??
why does it need a password

[torn sphinx]

yeh i managed to get it working on the admin console for postgres @velvet ridge

but i still can't seem to get into the shell

no probs 🙂

[sterile mauve]

Hello, i have one question does anyone having experience working rest_framework_mongoengine search filters?

[woeful yoke]

so

i need some help

is a table in a databases difrent from rest?

[proven arrow]

Rest of what?

A database can have many tables. Each table is different, but can have the same structure.

[woeful yoke]

ok!

i need some help
has someone built a economy bot with databases? if yes how to?
please show me a sample code!

[woeful yoke]

why?

i use aiosqlite btw asyc version of sqlite\

for example my bot has 500k users is it a problem if i use sqlite

can you show example code like the beg cmd?

just send a basic cmd

i need to know like how to connect close and commit and stuff like that

no

i won't

copy paste code

just need sample code to see how a basic thing works then based of that make my own cmd

when do i need to start connection and when end?

already made my bot but i heard sql is faster and better for larger data bases so yeah here is my beg cmd in json

#beg command
@client.command(aliases=["Beg"])
@commands.cooldown(1, 30, commands.BucketType.user) 
async def beg(ctx):
    await open_account(ctx.author)
    users = await get_bank_data()
    user = ctx.author
    await exp_give(ctx.author, random.randint(0, 1))
    eraning = random.randint(0, 150)
    shame = [
        "I don't have any money bish", "My credit card loans need to be payed",
        "I don't have cash", "Poor begger owo", "I got family to take care of",
        "I need to feed my family",
        'Kid:"Mom see how unclean is he drinking and shit not money for you bish" Mom:*Scoffs*',
        "Go find work", "I only have card"
    ]
    if eraning == 0:
        await ctx.send(random.choice(shame))
        return
    await ctx.send(f"Some one gave you {eraning} coins!!!!!!!!!!!!!!")
    users[str(user.id)]["Wallet"] += eraning
    users[str(user.id)]["exp"] += 1
    with open("mainbank.json", "w") as f:
        json.dump(users, f)```

why?

i am swicth from json or sql

so need to start and end connection every cmd right?

yes

how?

better to keep connection or worse? or same?

ok

but

see got the solution

just needed to know this

ik ik

does anyone know a mesurement converter api?

[proven arrow]

Yes

DESC for datetime orders it so that latest will be first

[tough sage]

1

[woeful yoke]

does anyone know how to fix this?

@velvet ridge why is this happing

[torn sphinx]

Whats the best sql database?

[woeful yoke]

mysql

[torn sphinx]

You can only use await in async function

[woeful yoke]

for normies sqlite3

and async version of sqlite is aiosqlite

[torn sphinx]

this is why you got that error

Did you get it?

[torn sphinx]

thanks

[woeful yoke]

yes

[torn sphinx]

so you must do ```py
async def good_name():
#do await....

[torn sphinx]

nice

[woeful yoke]

now wtf

[torn sphinx]

You must await async function

dude, stop working with async if you dont know whats async

[woeful yoke]

hmm

i do

but

[woeful yoke]

then if i await it then await is outside of async

[torn sphinx]

yea

thats why you need another async function

[woeful yoke]

then i call that then what

[torn sphinx]

here https://realpython.com/python-async-features/

[woeful yoke]

it's a loop

[torn sphinx]

yeah you literaly dont know whats async

[woeful yoke]

how do i exeute it?

[woeful yoke]

yeah

i don;t

[torn sphinx]

take a day, and learn it

[woeful yoke]

i just need a quick fix

[torn sphinx]

You need this https://realpython.com/python-async-features/

[woeful yoke]

why?

ok

ok

then wtf do i do?

how to execute the code

yes but first create a table

[torn sphinx]

please

read https://realpython.com/python-async-features/ this

its running asynchronically

[woeful yoke]

ok

[torn sphinx]

Not gonna tell more, read, and learn

[woeful yoke]

i know

ok

ik

[torn sphinx]

why are you even using async there?

i dont think you need that

[woeful yoke]

made the execite shit with bot cmd

[woeful yoke]

better for discord.py

[torn sphinx]

well you can just do it in another module

yea

[woeful yoke]

i did

[burnt turret]

i think i'd explained this to you before

the query doesn't change the order of the data in the table

it'll just give you the data in the order you specified

[burnt turret]

you do

ORDER BY column ASC/DESC
will arrange the data in ascending/descending order with respect to column

you do

ORDER BY is a clause to SELECT

what exactly are you trying to do?

hadn't we already solved this last time?
SELECT user_id FROM tablename ORDER BY time DESC;

the first row that will be returned by this query will be the userid that was added last

but why?

how so?

show your code

the part that is relevant to this discussion

doesn't show me any line numbers

print out row and see if anything is being retrieved

how are you testing this?

what is the data type of userid in your table?

right, and what's the output?

when you run the command, does it always go to that bot has never gotten DM... part?

you don't seem to be sending any message though?

that's because the add_reaction for the tick mark is running regardless of whether the database returned something or not

i'm not talking about whether you sent messages to the bot, in your code you aren't sending anything to member

but before that itself you're adding the ✅ , right after the query, without checking anything

[torn sphinx]

Sqlite, how do i get values of one name from whole file

[burnt turret]

don't worry, not a waste of time 👍

[torn sphinx]

please

yea

like if i have 5tables, i want all values from some name

yea

SELECT * FROM file_name WHERE value = ?

😄

i dont know

i cant believe that theres no function for that

[proven arrow]

There is but it’s something that’s not normally done because why do you have a name in more than one table?

[torn sphinx]

Because i am collecting stats from site and giving it into database

[proven arrow]

So why is the same name appearing in multiple tables?

Just have one table for stats

[thorn geode]

Sounds like you want to join tables together?

For the query

[proven arrow]

You can use union for combining table results https://www.w3schools.com/sql/sql_union.asp

Use union all if you want to keep duplicates

[torn sphinx]

so it can be like this

?

and i will just add another sessions with stats

right?

[proven arrow]

Try it and you’ll see

[torn sphinx]

well i need to go trought sqlite database first...

@proven arrow sqlite is just type of database and i manipulate with thing by sql right?

[proven arrow]

Yeah

[torn sphinx]

How many columns can sqlite db have?

[proven arrow]

https://www.sqlite.org/limits.html read point 2 here

[devout girder]

Can you set SQLite Bool value defaulting to 'FALSE' ?

or does it need to be 0 or 1

[blissful knot]

I am looking to move from sqlite to something more robust, and I am wondering if there is any particular downside to sqlalchemy over other systems, and what the best use cases for sqlalchemy are

[charred fractal]

MySQL has tons of additional features compared to SQL-Lite so you may want to consider switching over to that.

[blissful knot]

@charred fractal I know that, just wondering what the tradeoffs are with sqlalchemy vs postgresql etc.

[proven arrow]

Sqlite doesn’t have a Boolean data type. But you can use 0, or 1 for it

[proven arrow]

You can’t compare Sql alchemy with Postgres. Postgres is a database. Sql alchemy is an orm that works with databases.

ORMs just allow you to write sql queries and interact with the database in an object oriented manner, where the tables are represented as classes.

[blissful knot]

What does sqlalchemy use under the Hood? Do I need to specify that?

[proven arrow]

It works with any database. That’s the benefit of it. So the same code you write with Sql alchemy will work with different databases.

For example, it’s quite common people use sqlite for dev/testing environment and then for prod they just switch it to another server based database.

[blissful knot]

So I have an sqlite database now, I want to switch it to something else for production. Do I need to remake the database file using something like postgresql before I use sqlalchemy?

[proven arrow]

How is your setup currently?

Are you using an orm or just writing raw queries?

[blissful knot]

Raw queries with sqlite.

[proven arrow]

For that you will need to make some changes because Postgres syntax is slightly different.

Also Postgres doesn’t use a file like SQLite. It’s server based, so you connect to it over a network.

[blissful knot]

Are there other sql libraries I could use, or is postgres the best one to migrate to before using sqlalchemy?

[proven arrow]

Did you read what I said before? Postgres is just a database.

Sql alchemy works with any database.

Pick a database you want, pick an orm you want. And work with that.

[blissful knot]

Ok got it.

[proven arrow]

You can. Use the LIKE operator like I said

It’s done through Sql query

Pseudo code and actual examples are in the link I sent https://www.w3schools.com/sql/sql_like.asp

What do you mean how?

It will match a pattern so if you entered 3020, it’ll try to find that pattern of characters in the column.

See this example https://www.db-fiddle.com/f/4CrY9c8MR3Rw7f9kaTTj6P/0

Show your table with data

Yeah so that will work like I said.

Have you even tried it?

Well then you don’t have anything matching that pattern

[torn sphinx]

sqlite, how to create something like this where {game} and int(stat) is added from py and session, name1, name2, name3, name4, name5 are constants

Yea, that was very deep but it wont help me right now

I dont get it? Wheres the point

[scarlet geyser]

Don't give up

[torn sphinx]

I dont

aight, so what should i do?

Should i find out by myself?

Yea, i asked question

[scarlet geyser]

https://www.tutorialspoint.com/sqlite/sqlite_python.htm

[devout girder]

isnt there BOOL?

[fluid agate]

miss click

[scarlet geyser]

It has bool i belive

[devout girder]

since it didnt give an error when

i did that

yeah

what are the true and false values

i just use FALSE and TRUE rn

[scarlet geyser]

true and false is basicly 0 and 1

[devout girder]

yeah ik

[scarlet geyser]

The computer only understands 0 and 1 give it 1 and it will start behaving give it 0 and it won't do anything

[devout girder]

but its more visible for me cause i dont remember 0 and 1

lol

[scarlet geyser]

Why am i in python discord i don't use python lol

[devout girder]

false is 0?

[scarlet geyser]

yes

[devout girder]

k

[torn sphinx]

Wdym by that? I never did

[scarlet geyser]

I never said you give up but you didn't understand the point of what the other guy basicly said and that was to never give up if stuff is difficult no mater what, if you have been working 5 hours and go no where take 2 hours off or more and come back with fresh new eyes to look at the issue.

[torn sphinx]

Yeah, i just asked question and that weird guy started giving me motivational quotes

[scarlet geyser]

I belive he did that beacuse asking before you done research is a bad habit, you could easly find the answer to that by doing some slight research but no harm to ask and that's fine but some peolpe preffer that you do research and when you really do have a error or simply don't know for the life of yours then ask and what he meant by that is you shouldn't give up doing research beacuse you can't figure out how by yourself beacuse everyone can it just takes a bit longer time.

[proven arrow]

SQLite has a dynamic type system so what you put as the data type doesn’t really make a difference. You could put some random letter and it would still work.

[torn sphinx]

which is best database for Django ?

[blissful knot]

Django has a built-in database.

[burnt turret]

django doesn't have a built-in database, it uses sqlite if you don't specify any database

it does have a built-in ORM though which you can use for making queries

[blissful knot]

Postgresql question: the database runs on a server, so when you run it locally you are essentially starting a server on lcoalhost, correct? If I want to use postgresql whwb hosting my application, I could install it on the same server?

[torpid mountain]

Yes

[proven arrow]

Add the % to the values and not the query

query = “SELECT * FROM table WHERE column LIKE $1”

await bot.db.fetchval(query, ‘%320%’)

[dapper idol]

can someone point me in the right direction, im trying to compare 2 tables on mysql but getting the wrong output. tried a little debug with print, i cant see 'current macs ' and 'original macs' but then it skips to 2 lines of 'current mac missing from original table' although both tables have the same content

i just notice my if statement should have 'line' instead of current_macs, but this only give me 1 match out of 6 - does it need to loop?

[south harbor]

Hey guys, so i was thinking about making a small instagram like social media app. Which database would the best in order to accomplish this?

[vagrant agate]

Best way to practice sql queries?

[solemn root]

how can I fetch an item in sqlite3 by id to compare the current item to the previous item?

I want to be able to do this at any two id's

[torn sphinx]

anyone fulfills the same function.

[jaunty galleon]

Hello, I am starting to learn sqlite3, what do I need to do/download? I saw a guide that you don't need to open a database manually from some website, is that correct?
all you need to do is:

import sqlite3
conn = sqlite3.connect('test.db')```
Is that  correct?

[burnt turret]

you don't need to download anything, sqlite3 is a built-in module

[burnt turret]

yes, test.db will be your database file now

[jaunty galleon]

How could I access what is in there?

[burnt turret]

https://docs.python.org/3/library/sqlite3.html this explains usage

[jaunty galleon]

Thank you

[burnt turret]

with SQL queries. I'd recommend you read the tutorial I linked

it doesn't teach you SQL though

so if you want to learn SQL, https://sqlbolt.com

[jaunty galleon]

Will I need this SQL queries?

[burnt turret]

you will

SQLite is an sql (relational) database

SQL is a language you use to interact with databases

so you need to know SQL to use sqlite

[jaunty galleon]

oh ok

[jaunty galleon]

Hi, I don't really understand how to get all of the titles?

[burnt turret]

Do you only need to get the data from the title column?

Okay yeah

So when you do a SELECT query on a table, you can specify which all columns you want

SELECT columnname, column2name FROM tablename;
Will select the data of just those columns

[jaunty galleon]

Do I need to be really advanced with this? Can't I just know:

SELECT * FROM Movies WHERE Title == "Title_lmao";```

Andd should I first try using sqlite and SQL with normal python inputs?

[south harbor]

Hey guys so im interested in learning sqlite for small project that i have in mind.. can someone tell me how to make my database secure and prevent hackers from taking data from it?

[edgy wolf]

Hi , so I have been reading about asyncpg pools and transactions, I have a doubt regarding that,
suppose I have a discord bot where I create a pool only once with create_pool , now I have an on_message event where I insert or update something every time the event is triggered, what i was doing is:

@Cog.listener()
async def on_message(self,message):
    await self.bot.db.execute("insert or update a table")

then I found acquire() which creates a connection instance from the pool, then I started doing this:

@Cog.listener()
async def on_message(self,message):
    async with self.bot.db.acquire() as con:
        await con.execute("insert or update a table")

now I am confused with acquire thing idk what I should do the first method or the 2nd one with acquire.
I want to know does acquire creates a separate instance each time the event is triggered or does it creates a pool instance once and updates the data within the prevously made instance.

[burnt turret]

You can but that will return all the columns where that condition is true, but the question just asks for all titles

SELECT title FROM movies;
Will do that

[jaunty galleon]

Yeah I know, just I want to first do something really easy, like login system(yes I know I can use for this snall thing with json, but I want to make bigger stuff in the future

[burnt turret]

Yeah sure

Practice always helps

[burnt turret]

Heyo, I need to store a rather long variable length string, should I use VARCHAR or TEXT?
I expect to retrieve the data often but never use it in any conditions, if that's important information for making this decision

[stray fulcrum]

I store a pretty long JSON file using TEXT, but probably shouldn't take it from me lol

[burnt turret]

🤔 I'm just really thinking about the performance here, which would be better (or if there's gonna be any difference at all)

[solemn root]

How can I retrieve and compare two different data entries from a db in a way that I can use the stored information to determine which emoji gets to be printed?

[mystic shore]

Is there a way to run a sync operation with the motor driver (as part of the startup sequence for my bot) or would I have to also have the official mongo driver installed?

[burnt turret]

technically you'd not have to "install" it, motor depends on pymongo

what are you trying to do?

[mystic shore]

my guild cache not necessarily being done being built before the bot is ready is causing issues so I want the bot to not even try to start processing commands or events before thats done

[burnt turret]

and why do you need it to be sync?

[mystic shore]

because things happening while the guild cache is still being built is causing problems. I want the entire startup process to be sync, then when the bot is actually ready and starts accepting events i want async

[burnt turret]

right

[mystic shore]

ah. so just import pymongo and use that for sync and use motor for async?

[median swift]

Hey Guys, I am new to using PostgreSQL as I have been using a different system for a few weeks.

Here is my code:

cur = conn.cursor
cur.execute("CREATE TABLE Users(ID INTEGER PRIMARY KEY, USER VARCHAR(100), USERID INT, SERVER VARCHAR(100), SERVERID INT, LEVEL INT, MESSAGES INT)")

Here is the error:
psycopg2.errors.SyntaxError: syntax error at or near "USER"
Thanks 🙂

[burnt turret]

you can, but I don't like that solution a lot

I'm thinking if there's some other way to have it run first

[mystic shore]

yeah, it'd be better if there is a way to just run a sync operation via motor but this is acceptable if not

[burnt turret]

possibly subclassing commands.Bot and override some functions so that your bot is "ready" only once your cache is ready

[mystic shore]

hmm.. thats a good idea. I could override the message handler to first check if the database is ready, and if not, it could send something like "Bot is still booting up - please try again later". not only would that fix the issue but it would also be a better user experience

[burnt turret]

yep

[burnt turret]

user is most likely a postgresql keyword

[median swift]

brill I think that works now 🙂 many thanks

[jaunty galleon]

#help-popcorn help?

[half shale]

hey ! what is the utility of the argument "quotechar" in csv ?

[dapper root]

I have a question
If I have two Entity sets and each entity of first entity has can relate to many entities of second entity set but it is only relating to one entity of second entity set so can I say that it is one to many relationship?

[median swift]

cur = conn.cursor()
cur.execute("INSERT INTO servers(servername, serverid) VALUES(?, ?)", str(guild), str(guild.id),)
conn.commit()

I am getting this error and I am a bit confused by it
TypeError: function takes at most 2 arguments (3 given

[torn sphinx]

lol

bad formatting

How to acces data from another file? sqlite.

[median swift]

mate you have no clue do you? I ditched sqlite for a project, I am new to postgresql. Instead of slagging me off why don't you help? Probably because you are a sad little low-life that doesn't know either

[torn sphinx]

lol, chill dude imma help you aright? just dont be mad

so

imma rewrite your code a little bit @median swift ```py
cur.execute("""INSERT INTO servers(servername, serverid)
VALUES (?, ?), str(guild), str(guild.id)
""")

[median swift]

ok

[torn sphinx]

but thats not all

ahh yes it is, sorry i didnt notice guild there

[median swift]

I shall try that now thanks

[median swift]

I am getting a different error with this now:
psycopg2.errors.SyntaxError: syntax error at or near ","
This is referring to the comma between the two ? in VALUES

[torn sphinx]

try removing this (?, ?)

[half shale]

how to remove empty rows in a csv file?

even if I put conditions to remove empty rows, they are added...

[dapper idol]

can someone shed some light on where i am going wrong here? I am trying to compare macs from present table and original table, if present table mac not in original table mac ADD TO ORIGINAL, if present IGNORE

[rose parrot]

 ____________       _______________________           _____________
|...parent...|     |....parent_children....| ------> |....child....|
|============|     |=======================|         |=============| 
|--int: id---|---> |----int: child_id------| ------> |---int: id---|
|------------|     |----int: parent_id-----|         |-------------|
|____________|     |_______________________| ------> |_____________|

• A parent can have multiple children
• A child can only have one parent

In pure SQL I'd have to do this:

-- Finding the parent of a child
SELECT p.* 
FROM child c
INNER JOIN parent_children pc ON pc.child_id = c.id
INNER JOIN parent p ON pc.parent_id = p.id
WHERE c.id = 2323 -- example

-- Finding parent childrens
SELECT c.* 
FROM parent p
INNER JOIN parent_children pc ON pc.parent_id = p.id
INNER JOIN child c ON pc.child_id = c.id
WHERE p.id = 32323 -- example****

Here's the minified model:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = # TODO: HOW TO populate the list of childrens here?

class ParentChild(Base):
   __tablename__ = 'parent_children'
   child_id = Column(Integer, ForeignKey('child.id'), index=True, nullable=False)
   parent_id = Column(Integer, ForeignKey('parent.id'), nullable=False, primary_key=True, unique=True)

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent = # TODO: How to populate the parent of this child here?

I've tried to follow this documentation considering ParentChild as an Associate Table but it ends up adding child_id and parent_id whenever I use it to associate those two, as I'm not associating Parent to Child directly, but rather Parent to ParentChild

Can anybody with some experience give a tip on how to code that?

[torn sphinx]

how would i fetch all values from a dict like this
{"guild_id": member.guild.id, "inviter_id": inviter.id, "invites": 0, "normal": 0, "left": 0, "fake": 0}
from mongo db using the guild id as a key value in the search

[rose parrot]

@jovial notch
maybe

SELECT twitter
       COUNT(twitter)
FROM revengepolice1
WHERE twitter LIKE '%1'
GROUP BY twitter

just add the group by clause @jovial notch

query = """
           SELECT 
               COUNT(rp.twitter), rp.twitter 
           FROM
               revengepolice1 rp
           WHERE
                rp.twitter LIKE $1 AND 
                rp.time > current_date - '31 days'::interval"
           GROUP BY
               rp.twitter""

[proven arrow]

You need to index the tuples, so it accesses the correct value

[rose parrot]

@jovial notch can you try that again naming ravengepolice1 as rp as I've edited above?

[proven arrow]

Show your code

Seems you probably copied it wrong

[rose parrot]

there's no whitespace between WHERE and rp.twitter (WHERErp.), furthermore rp.twitter is escaped by a whitespace (rp. twitter)

[torn sphinx]

hey, how would i make a leaderboard from mongo data under guild id... this is how im storing my data
{"guild_id": member.guild.id, "inviter_id": inviter.id, "invites": 0, "normal": 0, "left": 0, "fake": 0}
i want to make a leaderboard with the current guild... im using mongo for this

[rose parrot]

@jovial notch beautify your query string avoid that.
either that or fix your one-liner

query = """SELECT COUNT(rp.twitter), rp.twitter FROM revengepolice1 rp WHERE rp.twitter LIKE $1 AND rp.time > current_date - '31 days'::interval" GROUP BY rp.twitter"""

[torn sphinx]

can anyone help me please...

[rose parrot]

@jovial notch Well. I'm not sure, maybe someone else can help you further

@torn sphinx I'd love to help but I don't work with non-relational databases

@jovial notch Try naming your table columns

query = """SELECT COUNT(rp.twitter) AS count, rp.twitter AS twitter FROM revengepolice1 rp WHERE rp.twitter LIKE $1 AND rp.time > current_date - '31 days'::interval" GROUP BY rp.twitter"""

Then DEBUG to try to access result[0]['count'] and result[0]['twitter'] to see if it unpacks the variables correctly

make sure it has a result

[dapper idol]

can someone tell me whats wrong with the sql syntax here, i keep getting errors when trying to compare the tables

[rose parrot]

@dapper idol postgres?

[dapper idol]

mysql (mariadb)

@rose parrot current_macs is a tuple, original_macs is also a tuple. i want to compare current macs with original, if not present then insert into original, but keep getting a errors, latest one is syntax

[rose parrot]

I was not aware that you could var = cursor.execute(query) , (cursor.fetchall())
Can you debug to see if it works?

[dapper idol]

the fetchall gives me the output

[rose parrot]

I feel more secure with

cursor.execute(query)
result = cursor.fetchall()

After fetching you can't retrieve the values from that cursor again

[dapper idol]

im jus using python and seeing the results live testing, dont have an IDE

[rose parrot]

@dapper idol try using https://jupyter.org , it's very good for running smalls chunks of code until you figure that out

[dapper idol]

if i print current_macs i get the output

[dapper idol]

jupyter lab? - but it wont have access to the db?

@rose parrot this seemed to work fine, it gave the corrent prints. but as soon as i put the sql query everything went haywire

[torn sphinx]

i have an sql version but it has severe blocking soo...

u wanna look at that?

[rose parrot]

@torn sphinx I'd recommend you to use pure sql against your database until you find a query that contains every information you need for your leaderboard

I'ts very hard to have a good grasp of what you're building if you don't find the query you want

[torn sphinx]

yea true

[rose parrot]

@dapper idol you'll have access to your DB normally.
Just pip install jupyter notebook then jupyter notebook --port 97896 it will open in your browser. Then you just have to create your own jupyter notebook and you can start making c hunks of codes and running them individually knowing that everything that you've ran before is cached.

[dapper idol]

@rose parrot will have a look at that, thanks. i think the issue is with the insert + execute syntax. i was under the impression if : var = SELECT * FROM table, then var[1] or var[2] would signify the columns of the table? or would i have to do a for loop?

[rose parrot]

@dapper idol are you up to streaming your screen in voice chat?

[dapper idol]

sure thing

[rose parrot]

@dapper idol I'm in Code/Help 0, type !voiceverify in the voice chat verification channel if you're not verified yet

[dapper idol]

@rose parrot unable to verify - im new to this channel

[versed geode]

you're better off just using DMs so you won't have to go through their laborous process

[versed geode]

is it customary to make a prim_key column for each table, even if i will probably never use it?

[proven arrow]

The column name doesn’t necessarily need to be prim_key and you can have a table without a primary key. However it would be strange to have a table without a primary key.

So short answer is yes, have the primary key, as it also meets 1NF.

[versed geode]

i see. thanks

[proven arrow]

What doesn’t work? What happens if you print result

Show the full stack trace

So error is not with the query, like you have been saying

It’s with how you access the data from the result

Your trying to access a value from the list by an index that doesn’t exist

So see what print(result) gives and you’ll see

No it doesn’t. Which is why I’ve told you to print(result)

You have a list with 1 element, now you tell me what’s wrong with what your doing

You should be able to figure this out as it’s basic stuff, but another fix for it is to just change the function from “fetch” to “fetchrow”, and should fix it

Yeah because that’s not what I said

Try to understand what’s going on/ what error means

The library gives you record objects which can be in a list. If your expecting more than one row then use fetch.
Maybe you should go over how lists work, and how to index elements, and the add the appropriate code to handle these errors.

https://magicstack.github.io/asyncpg/current/api/index.html?highlight=fetch#record-objects

Because your grouping it

Remove the group by

[storm sierra]

    @commands.command()
    async def logchannel(self, ctx, logchan: discord.TextChannel):
        self.client.db = await aiosqlite.connect('logchannel.db')
        cursor = await self.client.db.cursor()
        await cursor.execute("INSERT logchan INTO logchannel(logchan, guild_id) VALUES (?,?)", (ctx.guild.id, ))
        await cursor.commit()
        await cursor.execute("SELECT logchan FROM logchannel WHERE guild_id=?", (ctx.guild.id))
        result = cursor.fetchall()
        print(result)```

Tf?

[proven arrow]

Remove logchan after insert

[storm sierra]

Ight

[rose parrot]

@storm sierra can you help me on [this](#databases message) ?

[storm sierra]

Xd

[storm sierra]

        await cursor.execute("INSERT logchan INTO logchannel(guild_id) VALUES (?,?)", (ctx.guild.id, ))
        await cursor.commit()```

Still invalid syntax

[proven arrow]

Because read what I said

[rose parrot]

depends on the engine. but for psycopg2 I this would be "INSERT INTO logchannel (guild_id) id VALUES (%s)"

[proven arrow]

That’s still incorrect

https://www.w3schools.com/sql/sql_insert.asp

[rose parrot]

Is that right?

[proven arrow]

Yeah

But they wanted to insert 2 columns

Remove all group by

[rose parrot]

@jovial notch search for array aggregation (postgres: ARRAY_AGG(t.id)) relative to your engine and wrap revengepolice1.twitter inside the aggregation function

[storm sierra]

@commands.command()
    async def logchannel(self, ctx, logchan: discord.TextChannel):
        self.client.db = await aiosqlite.connect('logchannel.db')
        cursor = await self.client.db.cursor()
        await cursor.execute("INSERT INTO logchannel(logchan, guild_id) VALUES (?,?)", (logchan, ctx.guild.id, ))
        await cursor.commit()
        await cursor.execute("SELECT logchan FROM logchannel WHERE guild_id=?", (ctx.guild.id))
        result = cursor.fetchall()
        print(result)```

[rose parrot]

Should populate that column with a list (sql: {1,2,3,4,5} ) of whatever you store in twitter.

[proven arrow]

Right I forgot you had count there. In that case you can use a window function. Or another way to get the count is to just use python len function on the result.

SELECT COUNT(*) over (partition by twitter), twitter, reason FROM revengepolice1 WHERE twitter LIKE '%9697%' AND time > current_date - '31 days'::interval

That will work

[storm sierra]

Why this doesn’t work

[proven arrow]

Because logchan is a type that’s not recognised by the database

In your case it’s a TextChannel object

[storm sierra]

Oh

[storm sierra]

@proven arrow

    @commands.command()
    async def logchannel(self, ctx, logchan):
        self.client.db = await aiosqlite.connect('logchannel.db')
        cursor = await self.client.db.cursor()
        await cursor.execute("INSERT INTO logchannel(logchan, guild_id) VALUES (?,?)", (logchan, ctx.guild.id, ))
        await self.client.db.commit()
        await cursor.execute("SELECT logchan FROM logchannel WHERE guild_id=?", (ctx.guild.id))
        result = cursor.fetchall()
        print(result)

[proven arrow]

Add a comma after ctx.guild.id to make it a tuple

Or pass the parameter as a list

[storm sierra]

@proven arrow

    @commands.Cog.listener()
    async def on_message_delete(self, message):
        self.client.db = await aiosqlite.connect('logchannel.db')
        cursor = await self.client.db.cursor()
        await cursor.execute(f"SELECT logchan FROM logchannel WHERE guild_id == {message.guild.id}")
        ID = await cursor.fetchone()
        print(ID)
        logger = self.client.get_channel(ID)
        await logger.send("hi")```

I printed ID so u can see

Its not supposed to have (‘ at the beginning and at the end so get_channel can actually work

Since get_channel wont take that and will only take the ID

But how I can do it?

[proven arrow]

You can index the first element, but you should only store the ID integer in the DB

[wicked kiln]

custom_sql = "SELECT * FROM files WHERE absolute_path LIKE '%mp4';"

So... I dont understand because Ive been doing this query in sqlite3 just a while ago and now its giving me
sqlite3.OperationalError: near "SELECT": syntax error

Is there an obvious syntax error?

I got it, it had to do with the way I was passing that string to a method.

[true lily]

Is there any tutorial for Discord economy bot with MongoDB?

[orchid apex]

Just learn how to make a Discord economy bot and how to use MongoDB, and then put them together

[torn sphinx]

Help

import discord
import asyncio
import aiosqlite
from discord.ext import commands

intents = discord.Intents.all()
bot = commands.Bot(command_prefix="w!", intents=intents)
TOKEN = "effe.YGVMWg.ththdmdcdfjkgbnibkbhirfchjgjt9ghjtgjgtigfhr8tjhy9tibvnm-fefeff"


async def init():
    await bot.wait_until_ready()
    bot.db = await aiosqlite.connect("word.db")
    await bot.db.execute("CREATE TABLE IF NOT EXISTS wordata (user_id int, word text, PRIMARY KEY (user_id, word))")

@bot.event
async def on_ready():
    print("Hello")

@bot.command()
async def add(ctx, word):
    cursor = await bot.db.execute("INSERT OR IGNORE INTO (user_id, word) VALUES (?,?)",(ctx.author.id, word))

    if cursor.rowcount == 0:
        await ctx.send(f"`{word}`, Is already in the Word Database!")
        return

    await bot.db.commit()

@bot.command()
async def word(ctx, word):
    word_in_db = True
    async with bot.db.execute("SELECT word FROM wordata WHERE word = ?",(word)) as cursor:
        try:
            data = await cursor.fetchone()
            wdata = data[0]
        except:
            word_in_db = False
    if word_in_db == False:
        await ctx.send(f"That word is not in the Database! \nAdd it with, `w!add {word}`")
    elif word_in_db == True:
        await ctx.send(f"Word is in Database! \n{wdata}")
    else:
        await ctx.send("Error!")

bot.loop.create_task(init())
bot.run(TOKEN)
asyncio.run(bot.db.close())

[charred fractal]

Help?... Who needs help?, What needs helping?, Where do I need to help?, When do I need to help?, Why do I need to help?, How can I help if you just say Help, and not explain your issue?

[wicked kiln]

Is it bad to store long absolute paths /like/this/right/here/32432432432432/3243232432432432432/432432432/3423423432.txt in sqlite3.

It tends to give me "problem at '/' OperationalError when I try to query those absolute paths, so I have to find some other field to select with for it to work.

When its not doing that its doing something like, it works for 90% of recrods, and then throws a sqlite3.OperationalError: near "m": syntax error... and I have no idea where m is coming from. SQL kinda sucks. I need to use an ORM i think lol.

[true lily]

Hi, I have a problem, when I use MongoDB it makes my bot too slow for respond

[burnt turret]

are you using pymongo?

the first step to speeding it up would be to use a non-blocking library

https://gist.github.com/anand2312/840aeb3e98c3d7dbb3db8b757c1a7ace

[gloomy bay]

Hello!
What is the right way to store translations for a unique app?

I want to be able to sync all the translations to a main one, so I can add a new language and have all the recorded messages automatically copied from the main one.

Currently I store in JSON, and if I add messages, I have to add them to each of the translation files to put them in translation queue. I wonder if it's a good idea to switch this to SQL!

[true lily]

It still not work

[burnt turret]

reduce the number of database calls you're making

cache when possible

[median swift]

Hi Guys, apologies if this is a stupid question but I am selecting info from a database, only one bit of info to be exact. Though the result is a tuple, how do I make it not select as a tuple if that makes sense? Can send code if that helps

cur = conn.cursor()
cur.execute("""SELECT welcomechannelid FROM servers WHERE serverid=%s""", (str(member.guild.id),))

channelid = cur.fetchone();
print(channelid)

And that prints: ('123456789',) as an example

[gloomy bay]

Hello, you can just add a [0] next to your fetch :

channelid = cur.fetchone()[0];
print(channelid)```

[median swift]

Brilliant! Thank you very much 🙂

[gloomy bay]

Glad I could help!

[torn sphinx]

how to convert sqlite values to list?

[thick moat]

Hey Guys my database index page is display HTTP 500 error

if anyone can help would be much appreciated

[rain plank]

You sent your bot's token

[proven arrow]

Which database index page?

[thick moat]

Basically I installed Xaamp local server and everything is working, I imported the database and put the website in HT docs and fixed the credentials, but it's just not working while everything else is being checked

[proven arrow]

So what makes you say it’s a database error

[torn sphinx]

the funny part is its a fake token

[rain plank]

Lol

[torn sphinx]

lol

[thick moat]

I searched it up and it's actually a connection with the server error. It outputs 500 HTTP ERROR. So don't know what is the problem still, everything checks out

[torn sphinx]

async def init():
    await bot.wait_until_ready()
    bot.db = await aiosqlite.connect("word.db")
    await bot.db.execute("CREATE TABLE IF NOT EXISTS wordata (user_id int, word text, PRIMARY KEY (user_id, word))")

@bot.event
async def on_ready():
    print("Hello")

@bot.command()
async def add(ctx, word):
    cursor = await bot.db.execute("INSERT OR IGNORE INTO (user_id, word) VALUES (?,?)",(ctx.author.id, word))

    if cursor.rowcount == 0:
        await ctx.send(f"`{word}`, Is already in the Word Database!")
        return

    await bot.db.commit()

@bot.command()
async def word(ctx, word):
    word_in_db = True
    async with bot.db.execute("SELECT word FROM wordata WHERE word = ?",(word)) as cursor:
        try:
            data = await cursor.fetchone()
            wdata = data[0]
        except:
            word_in_db = False
    if word_in_db == False:
        await ctx.send(f"That word is not in the Database! \nAdd it with, `w!add {word}`")
    elif word_in_db == True:
        await ctx.send(f"Word is in Database! \n{wdata}")
    else:
        await ctx.send("Error!")

Traceback (most recent call last):
  File "/home/pi/.local/lib/python3.7/site-packages/discord/ext/commands/bot.py", line 902, in invoke
    await ctx.command.invoke(ctx)
  File "/home/pi/.local/lib/python3.7/site-packages/discord/ext/commands/core.py", line 864, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "/home/pi/.local/lib/python3.7/site-packages/discord/ext/commands/core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "(": syntax error

[proven arrow]

It’s not connection error. It’s a server error. There will be an exception/error loading that page from your application side. Check the logs.

[proven arrow]

You didn’t specify a table to insert into

[thick moat]

unfortunately there are none, I checked, it's so weird

I have been working on the connectivity issue like 2 days

the servers seems up and running and working

[proven arrow]

If it was connection issue you would not see the 500 error

[torn sphinx]

So this would work
@proven arrow
INSERT OR IGNORE INTO (user_id, word) FROM worddata VALUES (?,?)

[thick moat]

But the server is up and running so that is out, I'm so confused now!

do you have time to come to vc

so I can share it and you can see, the code and everything works fine

[median swift]

How easy is the switch from psycopg2 to asyncpg?

[torn sphinx]

async def init():
    await bot.wait_until_ready()
    bot.db = await aiosqlite.connect("word.db")
    await bot.db.execute("CREATE TABLE IF NOT EXISTS wordata (user_id int, word text, PRIMARY KEY (user_id, word))")

@bot.event
async def on_ready():
    print("Hello")

@bot.command()
async def add(ctx, word):
    cursor = await bot.db.execute("INSERT OR IGNORE INTO (user_id, word) FROM worddata VALUES (?,?)",(ctx.author.id, word))

    if cursor.rowcount == 0:
        await ctx.send(f"`{word}`, Is already in the Word Database!")
        return

    await bot.db.commit()

@bot.command()
async def word(ctx, word):
    word_in_db = True
    async with bot.db.execute("SELECT word FROM wordata WHERE word = ?",(word)) as cursor:
        try:
            data = await cursor.fetchone()
            wdata = data[0]
        except:
            word_in_db = False
    if word_in_db == False:
        await ctx.send(f"That word is not in the Database! \nAdd it with, `w!add {word}`")
    elif word_in_db == True:
        await ctx.send(f"Word is in Database! \n{wdata}")
    else:
        await ctx.send("Error!")

Error

Traceback (most recent call last):
  File "/home/pi/.local/lib/python3.7/site-packages/discord/ext/commands/bot.py", line 902, in invoke
    await ctx.command.invoke(ctx)
  File "/home/pi/.local/lib/python3.7/site-packages/discord/ext/commands/core.py", line 864, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "/home/pi/.local/lib/python3.7/site-packages/discord/ext/commands/core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "(": syntax error

[median swift]

which line in your code is line 94?

[crude trench]

table ="""SELECT * from settbord WHERE ranks LIKE ?;"""
c.execute(table,string)
ans = c.fetchall()
for x in ans:
    print(x[0])```


`ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 6 supplied.`

i'm trying to select rows which have a certain string, it works as expected when i enter the string directly, like if i take the content of the "string"variable and paste it where i have the '?'. but I need there to be a variable in hte string that changes which is where my problem comes. 

the program thinks each character in the string is a value. how can i do this?

[proven arrow]

c.execute(table, (string,))

[crude trench]

oh wow, quite simple, thank you!

[icy terrace]

hey, i want to use a database for discord bot warning system

but i have no idea how to

[valid vault]

So each of my users has a dictionary assigned to them (among other data) in the form {item: amount_of_item} for a sort of inventory system. What would be the best way to implement this in a database?

Would it be best to have a table with each item having its own column? But there are 1000 possible items, so seems there could be a better way. Thoughts?

[ornate fulcrum]

Hey i got a question about mongo; I have the connection setupo inside a file and i get the errormsg each time i try to save stuff in the database
pymongo.errors.WriteConcernError: No write concern mode named 'majority\n found in replica set configuration
Which i believe is the /n fault. How to get rid off this. File makes the newline automatically.

[distant radish]

Greetings! I am currently doing a university senior project and my group members who are running mac are receiving this error when running my script:

mysql.connector.errors.DatabaseError: 2003 (HY000): Can't connect to MySQL server on 'localhost' (61)