databases

[alpine moon]

what is the difference?

[paper vapor]

Primary key is for one column
Composite will take more column and see if both values make a unique row

Depends on use case
What do you wanna achieve

[alpine moon]

like there are tables like answer, answer_vote (that stores the votes for the answer) and i want that everyone can upvote only once

thank you so much

it works

<3

no homo

[paper vapor]

Account_id and answe_id composite primary key, dont make them unique individually and it will work

Cool

[alpine moon]

yes i did PRIMARY KEY (account_id, answer_id)

[exotic sand]

If I have a dataset my_database(name, amt_spent), what is the most efficient way to write a query to determine how many people have spent under 10 dollars and how many people have spent >= 10 dollars?

I can write a query to do so but it requires two sub-queries which feels messy. Must be a more compact way to do so

[harsh pulsar]

UNION seems apt, no?

(assuming this is sql)

[exotic sand]

I'm not sure how to use UNION to reduce the number of subqueries I have to write

And yes, postgresql to be specific

[harsh pulsar]

i'm saying that UNION seems like the right tool for the job, and writing two subqueries doesn't seem that bad

the alternative is SUM(CASE WHEN amt_spent >= 10 THEN 1 ELSE 0 END) which is a lot worse imo, and probably a lot less efficient

[exotic sand]

That gives me something to think about. Sounds like you might be right, two subqueries isn't too bad. Performance is good so who cares. Thanks

[tacit nimbus]

ok

[grim vault]

The fastest in my test (sqlite, table with ~165k entries, ssd, no index on column) was:

select
  count(*) filter (where amt_spent < 10),
  count(*) filter (where amt_spent >= 10)
from my_database

[harsh pulsar]

TIL about filter

nice, postgres supports it too

that's really useful

[pallid plover]

im trying to add a mongo database

im not familiar with it

so what can i do

[proven arrow]

Get familiar with it, use something else, or get someone else to do it for you.

[pallid plover]

the last option seems tempting

but no

i want to be taught

[arctic granite]

I'm trying to answer this SQL question I'm not too good at SQL though

Would I use a JOIN statement?

[torn sphinx]

what do you use to take something out an array?

opposite of insert into

[arctic granite]

@torn sphinx use drop

SELECT Consultant.LastName, AVG(Client.Balance), COUNT(Client.ConsltNum)
RIGHT JOIN Consultant 
WHERE Consultant.ConsltNum = Client.ConsltNum

Is this wrong?

[mystic shale]

what SQL are you using

I would use GROUP BY

[paper vapor]

Where is from and main table

Select …. From table right join 2ndtable on condition match
Check w3school for syntax

[nimble magnet]

Hi, how can I reorder the columns returned in a SQL query?
Right now the results return 4 columns:

Rentals, City, Country, Amount

I'd like it to return in this order:

City, Country, Rentals, Amount

Is there something I can add to my query to achieve this?

SELECT COUNT(rental.rental_id) AS "Total Rentals",
           city.city, country.country, SUM(amount) AS "Total Amount"
           
FROM rental 
INNER JOIN customer ON rental.customer_id = customer.customer_id
INNER JOIN payment ON rental.rental_id = payment.rental_id
INNER JOIN address ON customer.address_id = address.address_id
INNER JOIN city ON address.city_id = city.city_id
INNER JOIN country ON city.country_id = country.country_id
GROUP BY city, country 
ORDER BY `Total Rentals` DESC 
LIMIT 10;

Oh, I figured it out - just reorder my select statement.

[torn sphinx]

Hey anyone has experience with parquet?

[primal notch]

Is it bad practice to use parent id as primary key in one-to-one relationships? I'm using postgres

[harsh pulsar]

you should ask your entire question. don't "ask to ask", forcing someone to interview you in order to help.

[harsh pulsar]

if it truly is one-to-one, i don't think there's anything wrong with it. are you talking about a situation like this?

user: id, username, email_address
moderator: user_id, level

where every moderator is a user, but not all users are moderators

[primal notch]

Kinda. There are nodes that will hold data, and data is a separate entity that should only be present in one node

Just wanted to be sure, thanks

[harsh pulsar]

yeah that seems reasonable too, maybe there's some "web scale" reason why it's not a good idea

[arctic granite]

Why do I keep getting syntax errors - Jet SQL

[harsh pulsar]

what is the syntax error?

oh

you wrote TABELE, not TABLE. at the top

but you should always post the actual error

[arctic granite]

Oh

XD

[grim vault]

The date in the insert is also not quoted.

[arctic granite]

it just says Syntax error

in field defintion

Syntax error in field definition - Jet SQL

CREATE TABLE Sept21(
OrderNum INT, 
TaskID VARCHAR(5), 
Description VARCHAR(50), 
ScheduledDate DATE, 
QuotedPrice DECIMAL(15, 2)
);
INSERT INTO Sept21(ScheduledDate)
VALUES("9/21/2018");

[harsh pulsar]

@arctic granite usually you need single quotes for strings in sql, that might be the problem. VALUES('9/21/2018')

[arctic granite]

@harsh pulsar I tried both right now Microsoft Access just doesn't like how I am trying to create the table.

[harsh pulsar]

unfortunately i don't know the specific of microsoft access, hopefully you figure it out

[calm cedar]

i made a discord bot and the data is stored in json but i wanna switch to Postgres

how do i quickly convert my code

[snow niche]

Is heroku really great in hosting a Postgres DB?

[fading patrol]

Depends on your project and budget. It's the best free option I've seen.

[snow niche]

For a discord bot, is it good?

[fading patrol]

Sure, it's fast and easy and free to setup so just give it a try

[torn sphinx]

wasn't a question question, more of like general experience with it and if it is good for data storage of around 1gb

[harsh pulsar]

that's still a question! and yes, it's fine for data storage of 1gb, although it depends on what the data is and what you need to do with the data

[torn sphinx]

numerical and some string data storage

archivingand storage mainly, read

[harsh pulsar]

yeah, it's fine for that. good compression, fast (parallelizable) and "lossless" reads. much much better than csv

[torn sphinx]

attempted to writie on it with pandas, but seems there is no appending mode, is that something normal for parquet? is it a common practice to split the files and then merge them all together?

[harsh pulsar]

parquet is column-oriented, so it's not really good for appending workflows. however, pandas supports reading from multiple parquet files at once, so you can just put a new file in the same directory. so parquet is not that good (inefficient) for lots of small appends

[torn sphinx]

ow okay so i guess just create multiple 100mb parquet files (size is just estimating) and then just read all

btw create 100mb parquet is like daily archivation

archiving*

[harsh pulsar]

yep that's perfectly fine. it'd be silly to make a bunch of files with like 10 rows each. but a 100 mb daily archive is very reasonable

[torn sphinx]

Anyone know a good database for my program I need licenses that expire

[arctic granite]

Is there a way to use inserts from other tables to make a new one?

Like using insert select

😭 Been at this for 10 hours

[torn sphinx]

is the correct datatype for a guild whitelist bigint[]?

[thorny anchor]

if you're storing the ids? yeah

[torn sphinx]

alr

so i have a whitelist command, adding users works fine but viewing the whitelist doesnt.

        whitelist = await self.client.db.fetch('SELECT whitelist FROM guild_data WHERE "guild_id" = $1', ctx.guild.id)
        print(whitelist)
        embed = discord.Embed(description="\n")
        embed.set_author(name='Whitelisted Users', icon_url=self.client.user.avatar_url)
        for i in whitelist:
          member = self.client.get_user(i)
          embed.description += f"{member} | {member.mention} [`{member.id}`]\n"```
whitelist is being printed out as `[<Record whitelist=[885172387002351617]>]` and with this code returns `TypeError: unhashable type: 'list'`

i tried a different code using my events cog
```py
        wl = []
        whitelist = await self.client.db.fetch('SELECT whitelist FROM guild_data WHERE "guild_id" = $1', ctx.guild.id)
        for data in whitelist:
          wl.append(data['whitelist'])
        print(wl)```
this code returns the same error but printing wl returns `[[885172387002351617]]`

both of theses "solutions" dont work

[fathom star]

afaik you don't put quotes around the guild_id part

for me it works without using quotes

also you're supposed to be passing in ? for variables inside the query, or else it tries to find a literal $1

[torn sphinx]

i am passing in variables

ctx.guild.id

[nimble magnet]

Hello, I'm doing a query to find all the actors who appeared in films together in the Sakila database. I'm getting duplicate/inverse results however.
What should I look at to mitigate this?

JULIA MCQUEEN - HENRY BERRY
HENRY BERRY - JULIA MCQUEEN 

SELECT concat(a1.first_name," ", a1.last_name) AS "Actor 1 Name", fa1.actor_id AS "Actor 1 ID", concat(a2.first_name," ", a2.last_name) AS "Actor 2 Name", fa2.actor_id AS "Actor 2 ID", COUNT(film.film_id) AS "Films Together"

FROM film

INNER JOIN film_actor fa1 ON film.film_id = fa1.film_id
INNER JOIN actor a1 ON fa1.actor_id = a1.actor_id
INNER JOIN film_actor fa2 ON film.film_id = fa2.film_id
INNER JOIN actor a2 ON fa2.actor_id = a2.actor_id

WHERE 
fa1.actor_id != fa2.actor_id 
AND fa1.film_id AND fa2.film_id = film.film_id

GROUP BY
fa1.actor_id, fa2.actor_id  
ORDER BY `Films Together`  DESC;

Thank you for the help! 🙂

[fading patrol]

I think this should get you there: https://stackoverflow.com/questions/1330692/distinct-pair-of-values-sql

[nimble magnet]

Thank you! I'll give this a read now.

[arctic granite]

Is there anther way to type this statement?

SELECT *                                                                                    
FROM Tasks
ORDER BY Category, Price;

[obsidian lichen]

hi! I am seeking an advice on starting with DBs for my project. is this a right place to ask those kinda questions?

[arctic granite]

@obsidian lichen yes

[obsidian lichen]

cool, I am kinda new with python. I have started a project where i would scrape data from web n convert it to dict. I am wondering what's an ideal db to use to store those dicts?

i can explain my project idea if that helps you understand better

[arctic granite]

@obsidian lichen im not really an expert either im big time noob using this chat foe help on something i have been stuck on for li 9 hours

What is wrong with my INSERT statement

INSERT INTO Sept21(OrderNum, TaskID, ScheduledDate, QuotedPrice)
FROM OrderLine
WHERE ScheduledDate = '9/212018';

[obsidian lichen]

aren't you missing keyword VALUES after the tablename?

[arctic granite]

Im struggling with this so badly.

INSERT INTO Sept21 (OrderNum, TaskID)
SELECT OrderNum, TaskID
FROM OrderLine
WHERE ScheduledDate = '9/21/2018';

its a Data Type mismatch in criteria expression.

[fading patrol]

A database might be overkill if dumping to JSON is sufficient.

import json 
with open("file.json", "wb") as f:          f.write(json.dumps(dict).encode("utf-8")) 

That third line needs to be indented

[arctic granite]

@fading patrol Can a Where statment be used on an Insert Into select SQL statement?

[torn sphinx]

!paste

[delicate field]

Pasting large amounts of code

If your code is too long to fit in a codeblock in discord, you can paste your code here:
https://paste.pythondiscord.com/

After pasting your code, save it by clicking the floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[fading patrol]

IDK Google it https://stackoverflow.com/questions/485039/mysql-insert-query-doesnt-work-with-where-clause

[grim vault]

Use None or don't include it in the insert list? But what would be the purpose of an empty role_id?

[lofty fractal]

Are there any good abstraction layers for python that abstract away Postgres queries into functions such as db.get("KEY", "COLUMN") instead of db.one("SELECT key FROM column")? I know redis does this well but I'm using Postgres with other stuff and I want a Python program to interface into it.

I just absolutely hate dealing with queries in every shape and form, they make no sense to me whatsoever.

[grim vault]

        cursor = await self.bot.db2.execute("INSERT OR IGNORE INTO mute (guild_id) VALUES (?)", (guild_id,))```
will use the default for role_id which is NULL if not otherwise defined at the create table.

But, again, what would the purpose of such an entry be? Mute the whole guild?

A insert won't overwrite the current entry.

DELETE or UPDATE?

This would delete all rows with that guild_id.

If there are more column and you just want the role_id set you need an update.

await self.bot.db.execute("UPDATE guildData SET role_id = NULL WHERE guild_id = ?", (guild_id,))```

Btw, you changed tablenames.

Be sure to use the table you want.

[grim vault]

UPDATE only works on existing rows. If there is no entry nothing gets updated.

What database (and db module) are you using?

you can try:

...
        cursor = await self.bot.db.execute("UPDATE guildData SET channel_id = ? WHERE guild_id = ?", (channel_id, guild_id))
        if cursor.rowcount == 0:
            cursor = await self.bot.db.execute("INSERT INTO guildData (channel_id, guild_id) VALUES(?, ?)", (channel_id, guild_id))```

or:

            sql_stmt = (
                "INSERT INTO guildData(channel_id, guild_id) VALUES(?, ?)"
                " ON CONFLICT(guild_id)"
                " DO UPDATE SET channel_id = excluded.channel_id"
            )
            cursor = await self.bot.db.execute(sql_stmt, (channel_id, guild_id))

That's the UPSERT clause if the guild_id is the primary key or unique.

[grim vault]

The default is only used if you don't specify the column on insert.
so
... insert into level_channel(guild_id) values(?) ...
will use the default for channel_id, but
... insert into level_channel(guild_id, channel_id) values(?,?) ...
will use the supplied value.

You define the default value at table creation and it is used only on insert like I described above.

[arctic granite]

What I'd another way that I can get the same results as:

Select *
FROM Tasks
ORDER BY Category, Price;

Using SQL

[gusty apex]

hello y'all! I just started in the python's world. do you guys usually use ORMs or rather write down your SQL queries?

[grim vault]

What do you mean, another way? That's as simple as it gets.

[arctic granite]

@grim vault I was asked to do this statement another way but in the end I guess the alternative way to do it was to enter each element in the select statement individually 🤷‍♂️

[arctic granite]

@grim vault Could I ask you another question which just a yeas or no answer

SELECT WorkOrders.OrderNum, WorkOrders.OrderDate, WorkOrders.ClientNum, Client.ClientName
FROM WorkOrders
INNER JOIN Client
ON WorkOrders.ClientNum = Client.ClientNum
WHERE WorkOrders.OrderDate = '9-10-2018’;

In theory this should work right?

[grim vault]

Yes, depends on how the db handles dates I guess.

[arctic granite]

@grim vault MS Access won't accept it, I guess it just on tagt side being annoying

[merry kite]

I'll delete it from here, and ask in #algos-and-data-structs.

[grim vault]

The ending apostrophe looks wrong ’ instead of '

[arctic granite]

@grim vault OH yea

[delicate field]

You are not allowed to use that command here. Please use the #bot-commands channel instead.

[torn sphinx]

hey anyone did some personal testing on pyarrow vs fastparquet engine for parquet? (maybe @harsh pulsar )

[slate relic]

This is more of a general question, but how would one take a large collection of photos and automatically rename them based on a list of plaintext values?

[harsh pulsar]

i recall a few years ago i switched to fastparquet because there was some bug or unsupported feature in pyarrow, but i don't remember what it was. i don't remember which one was faster, but personally i'd go with pyarrow since it actually uses bindings to the apache arrow library

[torn sphinx]

ola! I am having trouble with flask-sqlalchemy, Here are the errors:

error

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "profile_image" of relation "profile" does not exist
LINE 1: INSERT INTO profile (profile_image, display_name, descriptio...
^

[SQL: INSERT INTO profile (profile_image, display_name, description, user_id) VALUES (%(profile_image)s, %(display_name)s, %(description)s, %(user_id)s) RETURNING profile.id]
[parameters: {'profile_image': 'https://res.cloudinary.com/', 'display_name': 'texo', 'description': None, 'user_id': UUID('79ee0e7f-da94-47b5-b1f9-dcbc08725ca4')}]
(Background on this error at: https://sqlalche.me/e/14/f405)

models.py

class Users(db.Model):
    id = db.Column('id', UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    username = db.Column('username', db.String(20), nullable=False, unique=True)
    email = db.Column('email', db.Text, nullable=False, unique=True)
    password = db.Column('password', db.Text, nullable=False)

    profile = db.relationship('Profile', backref='user')

    def __repr__(self):
        return '<User %r>' % self.username


class Profile(db.Model):
    id = db.Column('id', db.Integer, primary_key=True)
    image = db.Column('profile_image', db.Text, default='')
    display_name = db.Column('display_name', db.String(50))

    description = db.Column(db.String(200))

    user_id = db.Column(UUID(as_uuid=True), db.ForeignKey('users.id'))

    def __repr__(self):
        return '<User %r>' % self.id

routes.py

        user = Users(
            username=args['0'],
            email=args['1'],
            password=bcrypt.hashpw(str.encode(args['2']), bcrypt.gensalt(12))
        )
        profile = Profile(display_name=args['0'], image='https://res.cloudinary.com/', user=user)

        db.session.add(user)
        db.session.add(profile)
        db.session.commit()

args is essentialy 0 for username, 1 for email, and 3 for password

[obsidian current]

what's the best way to handle user roles in mysql?
should I create a table users with name, password and admin columns?

[wild pelican]

cursor = await db.execute('SELECT * FROM bans')``` How could I only select a certain column from a table? Let's say that column name is `x`.

This is a SQLite database.

[harsh pulsar]

select x, y, z from bans

[wild pelican]

So SELECT x FROM bans would just select the x column?

[harsh pulsar]

yes

https://sqlbolt.com/lesson/select_queries_introduction

[wild pelican]

@tasks.loop(seconds = 60)
async def bans():
    await client.wait_until_ready()
    guild = client.get_guild(764574130863079424)
    db = await aiosqlite.connect('database.db')
    cursor = await db.execute('SELECT time_expired FROM bans')
    rows = await cursor.fetchall()
    stored_timestamp = cursor
    stored_date = DT.datetime.fromtimestamp(stored_timestamp)
    if stored_date <= DT.datetime.now():
        oof
    await db.close()
bans.start()``` One more thing- This is kinda database, kinda not... but how could I loop through all the `x` columns of the database so it checks all the stored_date's if it's past now?

Or select only the expired bans- which I'm not sure how.

[wild pelican]

@ me if you have a response, preferably on the second thing since that'll be the most efficient.

[chrome gulch]

How do i check if theres a document or not in mongodb

[chrome gulch]

Im using motor

[torn sphinx]

How would I insert data into a colum with spaces on sqlite3???

[arctic granite]

What is wrong with this SQL statement?

CREATE TABLE Horse(
   ID    SMALLINT UNSIGNED AUTO_INCREMENT,
   RegisteredName    VARCHAR(15) NOT NULL,
   Breed    VARCHAR(20) CHECK (Egyptian Arab, Holsteiner, Quarter Horse, Paint, Saddlebred),
   Height   decimal(3,1) CHECK (Height>=10.0) AND (Height<=20.0),
   BirthDate   DATE CHECK (DATE>='1-1-2015')
);

Its having issues with my sql CHECK (Height>=10.0) AND (Height<=20.0) statement am I doing it wrong or do I have to do it another way?

[wild pelican]

    db = await aiosqlite.connect('database.db')
    cursor = await db.execute('SELECT time_expired FROM bans')
    rows = await cursor.fetchall()
    stored_timestamp = idk
    stored_date = DT.datetime.fromtimestamp(stored_timestamp)
    if stored_date <= DT.datetime.now():
        oof
    await db.close()``` How can I make the database to go through all the data and see if the datetime passes the current time?

[mossy shale]

Nested for loop: for i in row: for j in i:

[wild pelican]

Nested?

[mossy shale]

Then you would set your if condition in the nested for loop fetchall returns a list of tuples you have to iterate through that to fine the stored_date then execute your if

Does that help?

[wild pelican]

Uhhh- can you explain a bit further? I've never really looped a database before so I'm not sure how I can go abouts doing it :P

[mossy shale]

https://pynative.com/python-cursor-fetchall-fetchmany-fetchone-to-read-rows-from-table/

That explains it pretty well, fetchall is returning a list with tuples [(column1),(column2),(column3)]. You have to iterate through that list to get to stored_data which is one of the tuples in the list. Hope this helps to clear it up

[wild pelican]

Could I ask what j is a variable for in my instance?

[mossy shale]

j would be the data in the tue that it's currently iterating through so in [(column1),(column2),(column3)] it would be column1,column2, or coulmn3 data

[wild pelican]

Hhmmm, my cursor is currently the row I want cursor = await db.execute('SELECT time_expired FROM bans') Should I use cursor?

[mossy shale]

Do you know what index your stored_date is at in fetchall?

If you went to that link I sent earlier scroll down to the fetchall it has an example of how to loop through it and grab the appropriate columns

[wild pelican]

@tasks.loop(seconds = 5)
async def bans():
    await client.wait_until_ready()
    guild = client.get_guild(764574130863079424)
    db = await aiosqlite.connect('database.db')
    cursor = await db.execute('SELECT time_expired FROM bans')
    rows = await cursor.fetchall()
    for row in rows:
        print(row[0])
        #stored_timestamp = cursor
        #stored_date = DT.datetime.fromtimestamp(stored_timestamp)
#        if stored_date <= DT.datetime.now():
        await asyncio.sleep(1)
    await db.close()
bans.start()``` I might be back for a little bit more help, but I think we're good so far 😂

[chrome gulch]

Anyone?

[torn sphinx]

find_one returns None if the documents isn't found

[torn sphinx]

Around 2-3x faster pyarrow

[chrome gulch]

Thanks

[violet bobcat]

hi i need help

so i have this code in sql and i cant really get it to work
this is the code, it dont save the data in it its just blank, this is the code :

myconn = sql.connect('school_program.db')
        myc = myconn.cursor()

        myc.execute("INSERT INTO students VALUES (:username, :password, :iidentity, :grade)",
                    {
                        'username': username.get(),
                        'password': password.get(),
                        'iidentity': iidentity.get(),
                        'grade': grade.get()
                    })


        myconn.commit()
        myconn.close()

this is what it saves :

[('', '', '', '')]

mention me if there is any answers

oh wait

nvm

i found the problem

sorry

[harsh pulsar]

were those .get() methods returning empty strings?

[violet bobcat]

no it was another code on top of this code that was doing that, i didnt think of it even :

        username.delete(0, END)
        password.delete(0, END)
        iidentity.delete(0, END)
        grade.delete(0, END)

[teal sparrow]

In Django, can I create a model with another model?

So if I have a model like books, can I do something such that the author model is automatically populated with the author I gave in the books field?

[fading patrol]

Haven't done with Django myself, but I think the answer is yes. https://stackoverflow.com/questions/48262544/how-to-specify-parent-child-relationship-within-one-model

[teal sparrow]

Thank you. This would really help.

[torn sphinx]

value = f"""{cursor.execute("SELECT cash FROM users WHERE id = {}".format(ctx.author.id)).fetchone()[0]}"""```

writes an error, I can't understand why, help

[fading patrol]

Not sure if this is the only problem, but that initial { doesn't look like it should be there

Before cursor

Also I don't think .format works with f-strings like that

What does the error message say?

[harsh pulsar]

yes, you shouldn't mix f"" and .format. also, don't use f-strings for putting data into sql queries anyway

#databases message

@torn sphinx ☝️

[grim vault]

And if the user is not found you'll get TypeError: 'NoneType' object is not subscriptable

so better do it like:

row = cursor.execute("SELECT cash FROM users WHERE id = ?", (ctx.author.id,)).fetchone()
value = 0 if row is None else row[0]

[arctic granite]

What is wrong with this SQL statement?

CREATE TABLE Horse(
   ID    SMALLINT UNSIGNED AUTO_INCREMENT,
   RegisteredName    VARCHAR(15) NOT NULL,
   Breed    VARCHAR(20) CHECK (Egyptian Arab, Holsteiner, Quarter Horse, Paint, Saddlebred),
   Height   decimal(3,1) CHECK (Height>=10.0) AND (Height<=20.0),
   BirthDate   DATE CHECK (DATE>='1-1-2015')
);

Its having issues with my

CHECK (Height>=10.0) AND (Height<=20.0)

statement am I doing it wrong or do I have to do it another way?

[torn sphinx]

how do i insert a value into a biginit[] array, using asyncpg

[lunar reef]

Try sql CHECK (Height BETWEEN 10.0 AND 20.0)

[blissful tulip]

how i can send all invites in one embed?

code:

[arctic granite]

@lunar reef ok will do

[blissful tulip]

dou, fix it, is there a way to remove the ( and "?

how can I add a field for each invitation?

thx

[torn sphinx]

how do i insert a value into a biginit[] array, using asyncpg

[harsh pulsar]

you get an error because that's not valid syntax 🙂 https://sqlite.org/lang_delete.html

[blissful tulip]

how i can delete (' ') in the list?

code:

[fathom star]

well the default row factory for sqlite is kinda garbage, you should switch to sqlite3.Row

then printing will be much easier

but if you have to do it this way you'll have to either do a list comp or append each element in each tuple to a list

[blissful tulip]

ok, I'll try this, thx

[arctic granite]

How do I set the default value for State to TX?

CREATE TABLE Student (
ID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(30) NOT NULL,
Street VARCHAR(50) NOT NULL,
City VARCHAR(20) NOT NULL,
State CHAR(2) NOT NULL,
Zip MEDIUMINT UNSIGNED NOT NULL,
Phone CHAR(10) NOT NULL, 
Email CHAR(30) UNIQUE
);

[steel rover]

so im using aiosqlite, and i get this error:

AttributeError: 'Result' object has no attribute 'execute'```
The code:
```py
an = aiosqlite.connect(db_path)

a = an.cursor()

a.execute("""CREATE TABLE IF NOT EXISTS afk (
            user_id INTEGER,
            message TEXT)""")```

[wild pelican]

@tasks.loop(seconds = 5)
async def bans():
    await client.wait_until_ready()
    guild = client.get_guild(764574130863079424)
    db = await aiosqlite.connect('database.db')
    cursor = await db.execute('SELECT time_expired FROM bans')
    a = await cursor.fetchall()
    for row in a:
        await asyncio.sleep(1)
        #print(row[0])
        stored_timestamp = row[0]
        if row[0] <= DT.datetime.now().timestamp():
            muted = discord.utils.get(guild.guild.roles, name="Muted")
    await db.close()``` This one is confusing my brain. So in the `for row in a` how can I make it determine what user_id it is currently on?

[wild pelican]

It needs to be an.execute rather than a.execute

[ancient granite]

anyone can help me with ER Diagram?

[arctic granite]

@ancient granite What's the issue?

[royal siren]

heyo i need help with SQLAlchemy

i get this error when i use the Session class from sqlalchemy.orm module

with Session(engine) as session:
        session: Session = session
        data = session.query(model).filter_by(id=primary_key).scalar()
``` this is my code

its only coming in my linter tho my code runs fine

[simple grove]

it must actually implement them, then, right?

sounds like their type hints are incomplete

maybe you just need to annotate with a more derived subtype of Session

[royal siren]

i checked out the module source code and i can see an __enter__ and __exit__ function implemented

here

[simple grove]

maybe there's another Session class imported?

can you hover over Session in your code to see?

[royal siren]

it seems it's seeing the sqlalchemy session class

there arent any other Session definitions either

[simple grove]

how odd

[royal siren]

pylance wack lmao

[simple grove]

seems like it... ignore, I guess 😅

[royal siren]

yea ig

[simple grove]

#type: ignore I mean

[royal siren]

oh

oh ye

[simple grove]

I've had similar experience with overzealous linters in VSCode

[royal siren]

i've always been curious about the #type: flag in vscode, what other options does it have

[simple grove]

that's a good question..

[simple grove]

# type: ignore[attr-defined] - it can take an error ID looks like

[royal siren]

i see

what errors the error code for the error im getting it doesnt show when i hover over

[simple grove]

check the problems view?

[royal siren]

omg here comes discord's slow ass image upload

[simple grove]

you tried to send?

it still didn't come lol

[torn sphinx]

How would I ignore the error
TypeError: 'NoneType' object is not subscriptable

mongodb ^^

    @commands.Cog.listener()
    async def on_message(self,message):
        if message.author.bot:
            return
        for x in message.mentions:
            print(f'{x.id} has been mentioned')
            uid = await self.coll.find_one({"user_id": str(x.id)}) #getting the user ID if in db then getting reaction
            reaction1 = uid["reaction"]
            await message.add_reaction(reaction1)

This is my code
and I get that error if the user mentioned is not in the db

[harsh pulsar]

Change your code to check if the response is None, before trying to use it

[torn sphinx]

yeah I managed to fix it^^ Thanks though

how do i insert a value into a biginit[] array, using asyncpg

[frozen light]

anyone here??

[fading patrol]

No

[frozen light]

what is mysql

[fading patrol]

It's one of the popular forms of SQL database. Postgres is a fairly similar alternative. If you've never worked with SQL databases at all, SQLite is yet another alternative which is a little more limited but great for small projects

[merry kite]

Could someone tell me the difference between using bigquery.Client() vs. apache_beam.io.ReadFromBigQuery within the data pipeline, in terms of connectivity/performance/usage?

[twin badger]

Hello

Can anyone show me how to access all data from Microsoft Academic

[fading patrol]

https://docs.microsoft.com/en-us/academic-services/graph/resources-faq#how-to-get-the-latest-dataset

[harsh pulsar]

I don't have any first-hand experience, but I would guess that the Beam interface is more limited. Performance is probably not going to be different

Beam is good because you don't have to think about the specific details of the backend

It's the same interface for everything

So you can reuse your knowledge of the library to read from other sources

But ultimately it's the same backend doing the same data processing

The python library you use is not going to be a bottleneck

[merry kite]

Ah, I see. Were you able to find any helpful docs?

[grizzled bobcat]

Has anyone gotten psycopg2 to work with 3.10? Can't seem to get it working. Tried some recommendations on stack overflow and some google searching but none have worked and most seem to be from a few years ago. Wondering if its a version problem

django.core.exceptions.ImproperlyConfigured: Error loading psycopg2 module: DLL load failed while importing _psycopg: The specified module could not be found.

[brazen charm]

You should really use psycopg3

[torn sphinx]

how do i insert a value into a biginit[] array, using asyncpg

[brazen charm]

Litterally the same you
Would any other type

Although generally you dont need an array type as much as you tgink you do

Normally you have subtables for that with relational dbs

[torn sphinx]

well its for a whitelist cmd and an array worked perfect with mongodb, not sure if it carries over with sql

await self.client.db.execute('INSERT INTO guild_data("guild_id", whitelist) VALUES ($1, $2)', ctx.guild.id, [user.id])

this code above makes a new row with a guild id and the user

[grizzled bobcat]

Thank you, totally didn't realize there was a v3

[woven sun]

Hi, I am trying to remove the SQL injection problem in my INSERT query.
I tried this, but it doesn't seem to work:
result2 = cursor.execute("INSERT INTO Loan_History (BookID) VALUES (?)", (book_id_entry))

[harsh pulsar]

Normally you would structure this table differently, to have 1 row per whitelist entry. Mongo teaches bad habits...

As for the original question, https://stackoverflow.com/a/29320442

[harsh pulsar]

The parameters should be a sequence, a list or tuple. Maybe you meant (book_id_entry,)? (book_id_entry) is just book_id_entry

[unkempt prism]

Is it that there is no comma in the values list so its not a tuple and is just a value surrounded by parentheses.

result2 = cursor.execute("INSERT INTO Loan_History (BookID) VALUES (?)", (book_id_entry,))

[swift flax]

def show_table(args):
query = "SELECT * FROM {} ".format(args)
my_cursor.execute(query)
res = my_cursor.fetchall() # storing the output of fetchall() in the res
for row in res: # printing all the records in res
print(row)

can anyone tell me the error in this

i am getting the below error

File "C:\Users\Yash\AppData\Local\Programs\Python\Python38\lib\site-packages\mysql\connector\connection_cext.py", line 518, in cmd_query
raise errors.get_mysql_exception(exc.errno, msg=exc.msg,
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(add_bike=None, show_table=['Rent_hours'], view_tables=None)' at line 1

Please help

[proven arrow]

@swift flax Your sql syntax is wrong.

Try to understand the query you want to write before trying to do it through code.

[uncut widget]

I cant work out what is wrong

Ik it is the wrong channel but i cant see any other cahnnel tht would be applicatble

[swift flax]

i simply want to print all the records , can you tell the mistake

[proven arrow]

@uncut widget Card-Score should be card_score

[uncut widget]

ok

thx

I will do

[torn sphinx]

ah i see, something like

guild_id user.id
guild_id user2.id

either way thank you for the overflow

[neon bronze]

how i can delete a value from a table with a condition?

[fading patrol]

You can delete the whole record like DELETE  FROM  table_name  WHERE  condition; but be careful and test your condition with SELECT first

There is no "delete a value" AFAIK, but you can modify a value to null if you want

You didn't say what db you are using etc but this is basic SQL

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @odd lintel until <t:1635032944:f> (9 minutes and 58 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

[wild pelican]

cursor = await db.execute('DELETE FROM warns WHERE time_expired=?', (row[0], ))``` How can I make it just delete just the `time_expired` data and not the whole row/column?

[torn sphinx]

Isn't there update command + set

[fading patrol]

I think you want to UPDATE warns SET time_expired = NULL WHERE time_expired ='?' or something probably

[grim zephyr]

i am facing a problem while storing a list in mongo db

like when i give the command
1st time - py ["this"]
2nd time - py ["this", "is"]
but in the third time mongo db return boths as same element and appends "me"- py ["this, is", "me"]

[noble trout]

Hey, how do I open my .sql file in Postgres if anyone knows ?

[earnest cradle]

since hadoop is still slowly dies, which local database should i use for data governance datawarehouse? in order to keep it fashionable

[earnest cradle]

i think you shouldnt do it. use datagrip to connect to postgre. i make all interactions with this databases using jdbc connector with sqlalchemy and datagrip

[long dome]

result = await conn.fetch("SELECT material,quantity,price,tier FROM materiallistings ORDER BY price ASC"
How can I make it so I only get distinct materials
and always the lowest price
1 result for each different material

[glass glen]

Do a groupby on material and min on price?

[fading patrol]

Is this what you mean? https://kb.objectrocket.com/postgresql/how-to-run-an-sql-file-in-postgres-846

You can also do it with various GUIs like the VS Code extension, DBeaver, PGadmin, and so on

[noble trout]

Yesss

Thank you very much

[torn sphinx]

does anyone know how i fix the syntax ive tried alot of stuff and i cant fix this stupid error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') AND `type`='tool'' at line 1 php "SELECT * FROM `catalog_items` WHERE `id` IN (".implode(',',array_map('intval',$invItems)).") AND `type`='$type'";

[fading patrol]

What is all that (".implode ... )).") business? I don't know MariaDB too well but that doesn't look anything like valid SQL AFAIK

[grim vault]

That's PHP for ",".join(map(intval, invItems))

[uncut widget]

How do you do loops, for instyance I want to code blackjack adn IA mdoing endless if statements is there a better way of doing this

[copper dragon]

I'm using SQLalchemy to work with MySQL, and getting this error sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1317, 'foreign key constraints are not allowed)
It's only happening when I add bidirectional relationship, simplified version of my code:

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    links = relationship("Link", back_populates="owner")

class Info(Base):
    __tablename__ = "links"
    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    owner_id = Column(Integer, ForeignKey('users.id')) # Error only when adding this line
    owner = relationship("User", back_populates="links")

I'll try figuring out it myself till than, ping me if someone can help.

[serene shoal]

How do i update values in a sqlite3 db
i want to search for where username == X
and then update the time to the current time

[fading patrol]

Wrong channel, check #❓｜how-to-get-help

[fading patrol]

I believe INSERT INTO Date (LastModifiedTime) VALUES(CURRENT_TIMESTAMP) WHERE username='X'; should work, assuming LastModifiedTime is a column that exists in the current table.

[serene shoal]

thanks

[earnest kayak]

Anyone have any good tutorials for a beginner in databases

[fading patrol]

It's a broad topic so it might help if you know more specifically what you want to learn. This guy has a couple of nice ones first to learn what SQL is (https://datagy.io/sql-beginners-tutorial/) and then to implement a basic database with it using Python/SQLite3 (https://towardsdatascience.com/python-sqlite-tutorial-the-ultimate-guide-fdcb8d7a4f30)

[earnest kayak]

thanks

[serene shoal]

  File "C:/Users/XXXXX/Desktop/testing db.py", line 26, in <module>
    cursor.execute("""
sqlite3.OperationalError: near "WHERE": syntax error```

[fading patrol]

What is the exact code you're running?

[serene shoal]

import time
from datetime import datetime
with sqlite3.connect("some.db") as db:
    cursor = db.cursor()


cursor.execute("""
CREATE TABLE IF NOT EXISTS some(town, username, time);""")
db.commit()

timeNow = datetime.now()

town = ['town1', 'town2', 'town3', 'town4', 'town5']
name = ['name1', 'name2', 'name3', 'name4', 'name5']

town3 = 'what'

for i in range(5):
    insertData = """INSERT INTO some(town, username, time) VALUES(?,?,?)"""
    cursor.execute(insertData, [town[i], name[i], timeNow])
    db.commit()



cursor.execute("""
INSERT INTO some(town) VALUES(town3) WHERE (username = name3);""")
db.commit()

cursor.execute("SELECT * FROM some")
rows = cursor.fetchall()
for row in rows:
    print(row)```

this is just a testing thing im doign so i can find out how to do it before i put it in my main thing

[earnest kayak]

Can I have two on_message functions in a single program?

[fading patrol]

I'm not sure if you can execute a query direclty like that isntead of declaring a string first... if that's not the issue, open a help channel #❓｜how-to-get-help

[serene shoal]

how do i declare it a string first

[fading patrol]

Are you asking about #discord-bots ?

[fading patrol]

you did it with insertData

[serene shoal]

ohhhh

ok

ill try that

[earnest kayak]

yes

[serene shoal]

Which one is a binding parameter

[fading patrol]

No idea what that means sorry

[serene shoal]

oh it just said that in an error message cursor.execute(insertData, [town, town3, name3])
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

[storm mauve]

I don't think that you can use WHERE in an INSERT INTO statement?
You probably meant UPDATE, but I'm missing a bit on terms of context

[serene shoal]

import time
from datetime import datetime
with sqlite3.connect("some.db") as db:
    cursor = db.cursor()


cursor.execute("""
CREATE TABLE IF NOT EXISTS some(town, username, time);""")
db.commit()

timeNow = datetime.now()

town = ['town1', 'town2', 'town3', 'town4', 'town5']
name = ['name1', 'name2', 'name3', 'name4', 'name5']

town3 = 'what'
name3 = 'help'
for i in range(5):
    insertData = """INSERT INTO some(town, username, time) VALUES(?,?,?)"""
    cursor.execute(insertData, [town[i], name[i], timeNow])
    db.commit()
cursor.execute("SELECT * FROM some")
rows = cursor.fetchall()
for row in rows:
    print(row)



insertData = """INSERT INTO some(town) VALUES(town3) WHERE username = name3 VALUES(?,?,?)"""
cursor.execute(insertData, [town, town3, name3])
db.commit()

cursor.execute("SELECT * FROM some")
rows = cursor.fetchall()
for row in rows:
    print(row)

context

[fading patrol]

good point

[serene shoal]

ive sorta messed around with the area but im not sure what im doing lol

[storm mauve]

your database probably contains a hella lot of repeated entries now? but that probably doesn't matters much

[serene shoal]

I think so

[storm mauve]

hmm... ok, so what exactly did you want to do?

[serene shoal]

Im just trying to find out how to update values in a db

[storm mauve]

sql update statement

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

query = """
UPDATE some
SET town = ?, time = ?
WHERE username = ?"""
cur.execute(query, [town, time, username])
# or (I think)
query = """
UPDATE some
SET town = :town, time = :time
WHERE username = :username"""
cur.execute(query, {"town": town, "time": time, "username": username})

let me test to make sure

!e ```py
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS foo (name TEXT, age INT)")
cur.execute("INSERT INTO foo VALUES (?, ?)", ("bar", 13))
cur.execute("UPDATE foo SET age = :age WHERE name = :username", {"username": "bar", "age": 18})
cur.execute("SELECT * FROM foo")
for row in cur:
print(row)

[delicate field]

@storm mauve :white_check_mark: Your eval job has completed with return code 0.

('bar', 18)

[storm mauve]

I'm slightly surprised that it worked first try

so yeah, you can use either ? + tuple|list or :key + dictionary

[serene shoal]

ok thanks

[storm mauve]

I'm saving this as an example for sqlite3 though lol

[serene shoal]

it doesnt seem to work

[serene shoal]

lol

Does the query need to be in order of the order that they were created in @storm mauve

[storm mauve]

you are trying to update where username = 'help'?

[serene shoal]

im just testing atm

[serene shoal]

yes

[storm mauve]

I mean, there's no entry with username = 'help' in the database it seems?

[serene shoal]

OHHH

im being stupid

Thanks i got it working now lol

[neon bronze]

There is a way to check how much time a query takes to execute using sqlite3?

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @torn sphinx until <t:1635120984:f> (9 minutes and 58 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

[fading patrol]

Try this: https://www.sqlite.org/eqp.html

I know in Postgres, this includes the time, hopefully SQLite does the same

But looks like maybe not

[calm cedar]

hello i coded my bot with a json data base because i thought it was going to be personal use but it's not anymore so we are going to need a db instead

i followed this tutorial

https://www.youtube.com/watch?v=4EIy0bw7s-s

a but disappointed it only sets it up

and doesn't use it yet

anyway

how do i even use it

this is my build.sql file

CREATE TABLE IF NOT EXISTS exp (
    UserID integer PRIMARY KEY,
    XP integer DEFAULT 0,
    Level integer DEFAULT 0,
    XPLock integer DEFAULT CURRENT_TIMESTAMP

):```

but in my json db things were like this

{
"server id": {
  "prefix": "_"
  "user_id": {
    "messages": 0
    "level": 0
    "exp": 0
    "last_message": 0
    "url": "https://example.com"
  }
}

}```

i dont even know where to startr

nvm im using asql

still would like some help on how to remake my db

[torn sphinx]

How do you add to an array in mongodb

[real ginkgo]

?

[torn sphinx]

Mongodb is a database platform hoe do you add to an array in the database

[mossy socket]

in sqlite, is there a way to test a column value exists in another table, when that column value is NULL? that is, is x IN y possible where x is NULL, and NULL exists in y?

it seems the only way to do this is to only include non-NULL values in y, and then have a separate OR x IS NULL clause after, but this seems clunky since I think it means I'll have to dynamically change the query depending if there is or is not a NULL value in y.

ah

came up with this, does this seem proper? sql x_col IN y OR ( EXISTS (SELECT * FROM y WHERE y_col IS NULL) AND x_col IS NULL )

[simple grove]

not that I know of

you mean like an outer join?

[mossy socket]

hmm, I guess it would be intersection of nulls?

[simple grove]

select * from x
full outer join y on y.id = x.id
where x.id is NULL and y.id is NULL

something like that?

[mossy socket]

no

[simple grove]

oh that's not valid anyways

[mossy socket]

my specific situation is that I have a table with a parent_id column, which might be null

I want to "filter" a table, so that only rows that match one of my desired parent_ids are selected

[simple grove]

ok

[mossy socket]

which include NULL, items without a parent

[mossy socket]

I came up with this here

[simple grove]

so you just want a left join it sounfs like

select * from parent
left join child on child.parent_id = parent.id
where parent.id in (5,6,7) or parent.id is NULL

?

there, I think I got that last constraint in there

[mossy socket]

well, the where condition is the main thing

the issue is that (5,6,7) are items in a table

[simple grove]

so you don't know which values

without looking up from somewhere else

[mossy socket]

the values are passed in to the query, yeah

right now I populate a temp table

and then just do where item.parent_id in temp.filter_parent

except that doesn't work will nulls so I added that whole extra clause

[simple grove]

the values are passed in by a temp table?

[mossy socket]

right now, yeah

[simple grove]

what about
where (item.parent_id in (select temp.id from temp.filter_parent) or item.parent_id is NULL)

[mossy socket]

nope, because the user may or may not want to include items without a parent

that query always includes root items

[simple grove]

ohhh

i got it now

yeah that's gonna be messy

[mossy socket]

here's the select I have right (the relevant part anyway) ```sql
SELECT id, parent_id
FROM timeline, timeline_tag
WHERE id = timeline_id
AND (
NOT EXISTS (SELECT * FROM temp.filter_parent)
OR (
timeline.parent_id IN temp.filter_parent
OR (
EXISTS (SELECT * FROM temp.filter_parent WHERE parent_id IS NULL)
AND timeline.parent_id IS NULL
)
)
)

[simple grove]

yep

[mossy socket]

and yeah it's not pretty but it seems to work fine

I was mostly now wondering if there are any problems with it I'm not seeing

[simple grove]

something like that, you could change some of it tp joins but it's going to have the same structure basically

yea, I can't think of anythimg right off.. unless you do COALESCE(col, -1) as like a replacement for NULL

[mossy socket]

ok, I'll use it for the time being then. if I have issues I'll probably return here later lol

thanks for the input

[simple grove]

i don't know if a union would be simpler, probably not

you're welcome

[oak oyster]

how would I find a certain value in mongo db? cause I tried but TypeError: 'Collection' object is not iterable raised

[burnt turret]

show your query

[oak oyster]

bal = economy.find({"_id":12345})

for a in bal:
  print(a)

that?

[burnt turret]

can you show where you define economy as well?

[oak oyster]

db = cluster["discord"]

economy = db["economy"]

bal = economy.find({"_id":12345})

for a in bal:
  print(a)

[foggy lava]

hi

[burnt turret]

🤔 can you show the full traceback as well? that query looks fine

[foggy lava]

what is databases

🤔

[burnt turret]

A database is an organized collection of structured information, or data, typically stored electronically in a computer system.

you can google "what is a database" to get more info

[foggy lava]

ok

thy

[oak oyster]

Ignoring exception in on_command_error
Traceback (most recent call last):
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/disnake/ext/commands/core.py", line 171, in wrapped
    ret = await coro(*args, **kwargs)
  File "main.py", line 33, in balance
    if ctx.author.id not in economy["_id"]:
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymongo/collection.py", line 3478, in __next__
    raise TypeError("'Collection' object is not iterable")
TypeError: 'Collection' object is not iterable

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

Traceback (most recent call last):
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/disnake/client.py", line 483, in _run_event
    await coro(*args, **kwargs)
  File "/home/runner/bonbons/cogs/events.py", line 63, in on_command_error
    raise error
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/disnake/ext/commands/bot_base.py", line 547, in invoke
    await ctx.command.invoke(ctx)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/disnake/ext/commands/core.py", line 901, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/disnake/ext/commands/core.py", line 180, in wrapped
    raise CommandInvokeError(exc) from exc
disnake.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: 'Collection' object is not iterable

hm

[burnt turret]

the error is happening at if ctx.author.id not in economy["_id"]:

File "main.py", line 33, in balance

do you see the issue?

[oak oyster]

hm

[burnt turret]

economy is defined as economy = db["economy"]

so it is a collection

you need to run a query first (probably to retrieve all user IDs), and then do if ctx.author.id in that result

[oak oyster]

hmm

[dire elbow]

can someone give me some use cases of materialized views?

[torn sphinx]

how do i get my conection string in mongodb?

connection string*

[harsh pulsar]

https://docs.mongodb.com/manual/reference/connection-string/

[torn sphinx]

thanks a lot but i found

[harsh pulsar]

nesting or isn't necessary, a or (b or c) is the same as a or b or c. you can simplify the query for reading purposes by flattening out one layer of nesting and indentation.

SELECT id, parent_id
FROM timeline_tag, timeline_tag
WHERE
  timeline.id = timeline_tag.timeline_id
  AND (
    NOT EXISTS (SELECT * FROM temp.filter_parent)
    OR timeline.parent_id IN temp.filter_parent
    OR (
      EXISTS (SELECT * FROM temp.filter_parent WHERE parent_id IS NULL)
      AND timeline.parent_id IS NULL
    )
  )

i don't quite understand what the NOT EXISTS (SELECT * FROM temp.filter_parent) is supposed to achieve. isn't this going to be "all or nothing" over the entire query, because it's not correlated at all? that is, the inner query doesn't refer to the outer query, so it's either 1 for every row (if there is data in that table) or 0 for every row (if there is no data in that table).

i also tend to prefer JOIN syntax instead of putting the join condition in WHERE, because it removes yet another layer of grouping in WHERE

SELECT id, parent_id
FROM timeline_tag
INNER JOIN timeline_tag ON timeline.id = timeline_tag.timeline_id
WHERE
  NOT EXISTS (SELECT * FROM temp.filter_parent)
  OR timeline.parent_id IN temp.filter_parent
  OR (
    EXISTS (SELECT * FROM temp.filter_parent WHERE parent_id IS NULL)
    AND timeline.parent_id IS NULL
  )

[oak oyster]

thanks it works now :))

[serene shoal]

Code which sets up the database

import sqlite3
import requests
import time
from datetime import datetime

timeNow = datetime.now()
print(datetime.now())

with sqlite3.connect("Towns.db") as db:
    cursor = db.cursor()

#create the tables
cursor.execute("""
CREATE TABLE IF NOT EXISTS Towns(town, username, time);""")
db.commit()

#inputting all town data
response = requests.get("http://earthmc-api.herokuapp.com/towns/")
response.raise_for_status()
data = response.json()
towns = list(data)
name = [m["name"] for m in towns]
town = [m["mayor"] for m in towns]
ListLength = len(name)

#inputting data into database
for i in range(ListLength):
    insertData = """INSERT INTO Towns(town, username, time) VALUES(?,?,?)"""
    cursor.execute(insertData, [town[i], name[i], timeNow])
    db.commit()

print("Task Completed")

Code which is meant to search for the username and output the time + town that correspond with the username

import sqlite3
with sqlite3.connect("Towns.db") as db:
    cursor = db.cursor()

print("Are you looking for a player or towns last logon time?")
Choice = str(input(""))
if Choice == "player":
    print("Your choice is player")
    print("What is the players name?")
    PlayerInput = str(input(""))
    
    with sqlite3.connect("Towns.db") as db:
        cursor = db.cursor()

    find_user = ("SELECT * FROM Towns WHERE username = ?", (PlayerInput,))
    #cursor.execute(find_user,[(PlayerInput)])
    Results = cursor.fetchall()
    print(Results)```

Output:
Are you looking for a player or towns last logon time?
player
Your choice is player
What is the players name?
sab2003
**[] **
sab2003 is in the database because if u run this code:

with sqlite3.connect("Towns.db") as db:
    cursor = db.cursor()

#Show Database
cursor.execute("SELECT * FROM Towns")
rows = cursor.fetchall()
for row in rows:
    print(row)```

It prints all the data in the DB
and on line 289 this exists:
```('sab2003', 'Moria', '2021-10-25 14:35:06.572479')```

(Player name, Town, Time) 
Is anyone able to help me with why it doesnt output the data in the most recent black box thing i sent? ^

[harsh pulsar]

@serene shoal

1. you don't need to write = str(input("")), input() already returns a string, so you can just write = input(""). however, you might want to check that the user input contains no extra leading or trailing whitespace, = input("").strip()

2. with does not automatically close a sqlite connection, it starts a transaction. this is a common misunderstanding but it is documented. you might want with closing(sqlite3.connect("Towns.db")), https://docs.python.org/3/library/contextlib.html#contextlib.closing

3. even if you were closing the connection with with, the connection would then be closed when you tried to run the query! you would have to run the query and fetch the data inside the with block

4. cursor.execute is commented out, and this find_user = doesn't actually do anything. also, in sqlite3, you can just call execute directly on the connection, you don't need to explicitly create a cursor

5. at the top of your code, you create a cursor and don't use it for anything. don't do this. in general, don't use a cursor for more than one query. cursors should be considered "single use"

6. this doesn't affect the code as such, but in python we normally use lowercase variable names. TitleCase is for classes.

you might want to re-write your code this way, which should eliminate these various possible sources of error:

import sqlite3
from contextlib import closing

print("Are you looking for the last logon time of a 'player' or 'town'?")
choice = input("").strip()
if choice == "player":
    print("Your choice is 'player'.")
    print("What is the player's name?")
    username = input("").strip()
    with closing(sqlite3.connect("Towns.db")) as db:
        cursor = db.execute("SELECT * FROM Towns WHERE username = ?", (username,))
        results = cursor.fetchall()
        print(results)

[serene shoal]

Thank you

[serene shoal]

What is contextlib?

[harsh pulsar]

i linked to it in my message. it's part of the python standard library

[serene shoal]

Ah

[spring mica]

have any of you have worked with an API that connects to an Arangodb database hosted in a external server. Any tips on achieving this?

[floral lark]

I have two tables , (one parent table, one child table)
How can i insert a row into both tables at once ?

I'm using postgresql with asyncpg

[torn sphinx]

alright

so this is only pseudo-relative to this topic

but Im working on a database client and im using the __setitem__ method to set keys to values

but If I put a dictionary inside of a value, it wont allow me to set a value inside of that dictionary like it would with typical python behavior

[cosmic ingot]

can anyone help me?

[fading patrol]

You want to insert the same row in both tables? This sounds like a terrible idea which defeats the whole purpose of SQL, but if you really must you could use a trigger I guess

Why not just use a foreign key to link back to the parent or vice versa?

[floral lark]

not same row

Different data but for one primary key which is used as foeign key for the other table

[calm cedar]

anyone know how to use asqlite

[fading patrol]

Ah, that makes more sense. I'm not familiar with asyncpg but can't you just do the two updates in sequence?

[fading patrol]

Don't ask to ask, just ask

[calm cedar]

async def main():
    async with asqlite.connect('./data/db/users.db') as conn:
        async with conn.cursor() as cursor:
            # Create table
            await cursor.execute('''CREATE TABLE users
                                    (guild_id text, user_id text, last integer DEFAULT 0, experience integer DEFAULT 0, level integer DEFAULT 0, messages integer DEFAULT 0, url text DEFAULT "https://cdn.glitch.com/dff50ce1-3805-4fdb-a7a5-8cabd5e53756%2Fblankrank.png?v=1628032652421")''')

            # make new user
            async def new_user(guild_id, user_id):
                await cursor.execute("INSERT INTO users VALUES ('%s', '%s'", (guild_id, user_id))

            # Save (commit) the changes
            async def save():
                await conn.commit()

asyncio.run(main())``` ok so this is my code

should i put this in my bot.py

[floral lark]

i can do that but i wanted to know if there's a way to do it at once

[calm cedar]

or a db.py

and import it into bot.py

also can i even use functions inside of main()?

[fading patrol]

If nobody here knows try #discord-bots

[harsh pulsar]

note: you are missing a ) at the end of your insert query, and you are not supposed to put quotes around the %s placeholders

[calm cedar]

await cursor.execute("INSERT INTO users VALUES (%s, %s)", (guild_id, user_id)) like this?

[harsh pulsar]

👍

[velvet ridge]

How do I create a key/database in redis.

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @torn sphinx until <t:1635190828:f> (9 minutes and 59 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

[mossy socket]

You're right about the ors and using JOIN instead. I needed a LEFT JOIN for my query to work properly.

The purpose of NOT EXISTS (SELECT * FROM temp.filter_parent) is to ignore that where clause if the temp.filter_parent table is empty. That is, if the table is empty, do not filter rows by parent_id. But, if it isn't empty, only allow rows that have a matching parent_id. I do this to avoid having to dynamically build a query depending on which filtering I'm doing, my full query has two other filtering clauses with similar short-circuiting.

this is the full query I have now, if you're curious. it includes some tag filtering as well. https://paste.pythondiscord.com/ecupuvoyil.sql

[harsh pulsar]

you might want to put a comment about this NOT EXISTS business in the query. it's a good trick, but not obvious unless you already know the idiom

[calm cedar]

why do i get this error when trying to use the db in an event or command

    raise ValueError("no active connection")
ValueError: no active connection

async def new_guild(guild_id):
    async with aiosqlite.connect("data.db") as db:
        await db.execute("INSERT INTO settings VALUES ("+str(100000001)+", '_')")
        await db.commit()
        await db.close()```

see if i called this on it's own it would be fine

but this

@client.event
async def on_guild_join(guild):
    await new_guild(guild.id)``` gives the error

same thing if i just pasted the code into the event

[mossy socket]

don't do db.close(), the database will be closed when you exit the with block

basically you code is closing the database connection twice: once with await db.close(), and then again when you exit the with block

[calm cedar]

thx it worked

how do i fetch only 1 column

for example

i want the coloumn that has guild id xxxxxxx

[mossy socket]

you mean only 1 row?

[calm cedar]

yes

but how do i tell it which row

[mossy socket]

you can have a WHERE clause ```sql
SELECT * FROM my_table WHERE guild_id = 1234567

also, I'd recommend using parameterized queries ```py
await db.execute("SELECT * FROM my_table WHERE guild_id = ?", (guild_id,))

[calm cedar]

so does that return the value or do i gotta do something else

[mossy socket]

ah

yeah, you get a cursor back

cursor = await db.execute("SELECT col1, col2, col3 FROM my_table WHERE guild_id = ?", (guild_id,))
row = await cursor.fetchone()
if row:
    col1, col2, col3 = row
    # do things with col1, col2, col3 here
else:
    # you didn't find any rows with the right guild_id

[calm cedar]

cool

[fallow forge]

do i jus ask a quetion abt DB in here?

so I want to find the region with the highest GDP, but I dont know what to put in the WHERE, and I cant use MAX

[glossy crow]

Hello, I am starting with SQL and I have a question. If I already have Microsoft SQL Server installed, do I need to install MySQL Server again? Or for learning purposes, just installing MySQL workbench is enough? thank you (sorry for my bad english)

[fading patrol]

You can just use MySQL Workbench as a client and connect to Microsoft SQL Server, that's fine. You don't need to have MySQL running as a server if you don't want to

[glossy crow]

Thank you

[calm cedar]

what happens if you try and input data that already exists

ehh ill just check it first

[calm cedar]

like this but can i use 2 filters?

[fading patrol]

You can add identical rows to a database if there are no uniqueness constraints. The key will be different

[calm cedar]

grab if user_id and guild_id are both in there

[fading patrol]

Yes, I think you just need AND

[calm cedar]

ok

[calm cedar]

can i add a number to the value of a column?

like if i have it written down as level 3

and they are now level 4

instead of having to find the value i could just do UPDATE users SET exp = +1 for example

[mossy socket]

yes, you can do UPDATE users SET exp = exp + 1 to increment EXP by 1 in every row

[velvet ridge]

How do I create a key to Redis?

[torn sphinx]

How to upgrade pymongo code to motar

[livid crypt]

Hi my graph is not plotting the x pointspoints correctly, my code:
import pandas as pd#import pandas package to read data more easily
import matplotlib.pyplot as plt#imported pyplot to plot graphs
import datetime as dt#date time to read first column of csv file
import numpy as np
from dateutil.parser import parse

d_parser=lambda x:pd.datetime.strptime(x,'%d/%m/%y%H')#
df = pd.read_csv('LAC.csv', parse_dates=['Date'], date_parser=d_parser)
df2 = pd.read_csv('LIT.csv', parse_dates=['Date'], date_parser=d_parser)

#startdate='20/10/2013
#end = dt.datetime.now() #the end date is the present date

y1=df['Close']#refering to the close column in the csv file
y2=df2['Close']
x1=df['Date']
x2=df2['Date']

plt.xlabel('date')
plt.ylabel('price')

fig,ax1=plt.subplots()
ax2=ax1.twinx()
curve1=ax1.plot(x1,y1,label='close1', color='r')
curve2=ax2.plot(x2,y2,label='close2', color='b')

plt.plot()
plt.show()

[grim vault]

You sure the date format is '%d/%m/%y%H'? That would mean 01/01/2021 is Jan 1st, 2020 at 21:00

[livid crypt]

ohh i see i fixed it thankyou

[livid crypt]

Hi I'm also not sure about this loop as its not plotting anything although its from the csv database:
import pandas as pd#import pandas package to read data more easily
import matplotlib.pyplot as plt#imported pyplot to plot graphs
import datetime as dt#date time to read first column of csv file
import numpy as np

df = pd.read_csv('LAC.csv')
df2 = pd.read_csv('LIT.csv')
df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%Y')
df2['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%Y')
#startdate1='20/10/2013'
#pd.date_range(start="",end="2020-02-02").to_pydatetime().tolist()
#start = df.loc['2011-10-06']
#start='06/10/2011'#picked the same start date for both of the graphs in order to plot them on the same graph
end = dt.datetime.now() #the end date is the present date

y1=np.array(df['Close'])#refering to the close column in the csv file
y2=np.array(df2['Close'])
x1=np.array(df['Date'])
x2=np.array(df2['Date'])

t0=[]
for i in range(len(x1)):
for j in range(len(x2)):
t=x1[j]-x2[i]
while j>i:
x2[j]+=1
if j==len(x2):
x1[i]+=1
t0.append(t)

dcf=[]
def DCF(x1,x2,t0):
d=((x1-np.mean(x1))*(t0-np.mean(x2)))/(np.std(x1)*np.std(x2))
dcf.append(d)
return d

plt.plot(t0,dcf , ls='-', lw='1', color='red', marker='.')
plt.title('DCF vs Lag')
plt.xlabel('time lag')
plt.ylabel('DCF')
plt.show()

[grim vault]

You may want to put this into the help-channel you have. I'm not that experienced with all that plot and array things.

Don't forget to put your code between the ``` (backticks).

[livid crypt]

i see np will do thanks

[grim vault]

I don't see you call the DCF() function but using the dcf list, maybe it's still empty?

[harsh pulsar]

@livid crypt you can ask in #data-science-and-ml too

[nimble bear]

Whats the easiest local python database

[fading patrol]

SQLite if you need a full blown db. Pickle, JSON, etc. If you don't

Well, not that SQLlite is a full blown db really, but it's close

If you really, really need a db then Maria or Postgres

[signal hazel]

I am trying to get this song meta data

now from audio media it connects to song meta data

hello anyone

[fading patrol]

What's the question?

[signal hazel]

I am trying to get all song metadata

but I am not able to figure erd diagram

@fading patrol

[fading patrol]

Select * from song; ?

Too blurry to read but there's a lot more than just 2-3 entities there

[signal hazel]

Yeah

[fading patrol]

So what relationship are you having trouble with?

[signal hazel]

I am trying to get the metadata

but this is not working

[fading patrol]

!paste

[delicate field]

Pasting large amounts of code

If your code is too long to fit in a codeblock in discord, you can paste your code here:
https://paste.pythondiscord.com/

After pasting your code, save it by clicking the floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[fading patrol]

^^^ do that in a help channel and hopefully someone else can help you

[calm cedar]

how can i sort by experience out of everyone with x guild_id and then find their ranking so the highest exp is 0 then 1 then 2

can this be done without getting everyone from the same guild then looping through counting?

i just want to grab x user in x guild's exp ranking

[fading patrol]

What application is that image from? Are you asking how to build the query in SQL?

[calm cedar]

the image is a sqlite viewer

but i do it in py

[harsh pulsar]

do you want their ranking within the guild, or across all guilds?

https://www.db-fiddle.com/f/xf9h2wbybpnXNCQoQpgMhJ/0 @calm cedar

[tulip sonnet]

How to remove this panel that interferes with viewing the code?

In vs code

[calm cedar]

within

[fading patrol]

I think that's only supposed to appear when you hover, so if it's stuck, restart. You can probably disable it all together but ask in #editors-ides if you need help finding that

[calm cedar]

close but not what i mean

so for example

it sorts to one guild

then sorts by exp

i would then say ask for 867655715585130507's rank

it would return 2

or do i need to loop through everyone in the guild and count till i find 867655715585130507

[grim vault]

You can select from an select you know:

select * from (
  select *, dense_rank() over (order by experience) as user_rank
    from users
   where guild_id = 554362814794563586
)
where user_id = 867655715585130507
;

[calm cedar]

ok but does that tell me what position it is on the list

[grim vault]

Try and see

[calm cedar]

ok

what is dense_rank

nvm

[harsh pulsar]

you don't need a nested query for this

https://www.db-fiddle.com/f/xf9h2wbybpnXNCQoQpgMhJ/3

@calm cedar @grim vault

[grim vault]

Ok, I don't have much experience with window functions.

[calm cedar]

returns #1 everytime 😬

[harsh pulsar]

i might be wrong 🤷‍♂️

actually yes you're probably right @grim vault , it seems to be applying the where before computing the window function stuff

[harsh pulsar]

https://www.db-fiddle.com/f/xf9h2wbybpnXNCQoQpgMhJ/5

you probably have enough to take it from here

the sqlite docs explain everything, but admittedly aren't easy to search

[calm cedar]

yeah i know what to do now

[sacred finch]

Hello! I am using sqlalchemy and AsyncEngine to make queries and I am trying to move code from sync to async. The old code has

# query is of type: <class 'sqlalchemy.sql.selectable.Select'>
query = session.query(SomeClass)
# engine is of type <class 'qlalchemy.engine.base.Engine'>
df_graph = pd.read_sql(query.statement, engine)

I usually do something like this when working in async

engine: AsyncEngine
async with engine.connect() as conn:
  await conn.execute(select(SomeClass)) # Sometimes I use conn.stream but yes

I looked into the docs for a bit but couldnt find something for getting the actual sql query, any help?

Tag me if you reply :)

[sacred finch]

I am looking more into the code and am not sure if im looking at the wrong file cause it looks like the async session has a lot of stuff missing including this...

[mint sparrow]

silly Q i'm sure but I'm pulling a list from a DB and for some reason the first item always is {}. For example I get:
{"Sam's Hardware"}, 'True Dirt', "Mike's Basement"
Even when I change the order I grab them the first one still is {} ie {"Mike's Basement"} I use json. Just wondering if someone knew what's up.

It might be happening when I'm appending them to a list.... still not sure why it's doing it though.

[torn sphinx]

may need to check the output from the query before you append and determine where it's happening, what's your append to the list look like?

[mint sparrow]

for team in bookD['teams']: if bookD['teams'].get(team) == True: teams.append(team) print('teams: ',teams)
output:
teams: ["Sam's Hardware", 'True Dirt', "Mike's Basement"]

But when I print teams:
{Sam's Hardware} True Dirt {Mike's Basement}

sorry that code paste does, not look good.

I can str the results but then I'll get "" within [] where really I'd prefer it just prints the 3 names.

[torn sphinx]

what does bookD['teams'].keys() get you?

[mint sparrow]

keys : dict_keys(["Sam's Hardware", 'True Dirt', "Kevin's Pumps", "Mike's Basement"])

Kevin's Pumps is false so it shouldn't grab when I want only true's

[torn sphinx]

got it, so you have a dictionary of teams with a boolean

[mint sparrow]

yes

incase the file looks like this
"teams": { "Sam's Hardware": true, "True Dirt": true, "Kevin's Pumps": false, "Mike's Basement": true }

[torn sphinx]

and you're getting brackets around the first team name in your list when you print?

is teams set to an empty list before you start that loop teams = []

[mint sparrow]

quick test by adding more teams, every other

[torn sphinx]

I'm confused...you say you when you print teams, but you're output there looks good...what do you mean when you print teams

are you reading in from JSON or querying an actual DB? Are you querying with raw SQL, an ORM?

[mint sparrow]

reading from JSON and putting the true's into a local list. Then putting that list into a list box.

on screen visually.

[torn sphinx]

so it's the visual piece that's showing it

Because it seems like at the end of that loop, your local teams list looks good, right?

[mint sparrow]

it shows the same as the print via console yes.
on the console teams pint out well:
teams: ["Sam's Hardware", 'Bruces Bumbells', 'True Dirt', "Mike's Basement"]

(adding and removing teams while testing)

[torn sphinx]

what's the code that displays this on screen?

[torn sphinx]

I mean the code that generates that? Is this in a templating language, javascript, python to the terminal?

[mint sparrow]

player_teams_info['text'] = teams

I use tkinter in python. I haven't added space formatting or anything just wanted the output to pop up first. Might be something I come back to in the morning. This was one of those.... I'll just add this into it before I make dinner type of things.

[torn sphinx]

Okay. Short answer, I don't think it's the JSON. After your loop, your list looks good so it's somewhere between that and displaying it

[mint sparrow]

good to know.

I'll try adding some formatting steps between them in the morning.

BTW freaken love python, without schooling, prob my fav language to pick up and learn.

[torn sphinx]

either a debugger or loads of print statements will help you here. if it's a list all the way down and then displays wrong, it's something with tkinter

👍 it's a great language for sure

[gentle marlin]

Can someone explain JOIN to me

Like why is there 3 different types of JOINS

[harsh pulsar]

#data-science-and-ml message does this help?

[solar umbra]

Can someone help me how do I delete all rows in my sqlite database without deleting my database?

[fading patrol]

TRUNCATE that biz https://www.techonthenet.com/sqlite/truncate.php

[fading patrol]

https://m.imgur.com/t/sql/Mrjtlyu

[solar umbra]

thanks for help

[mint sparrow]

I separated the items out into the label and it cleaned up the brackets.
("\n".join(teams))
Thanks for the help. 😄

[signal hazel]

what's the alternative for union function

hello anyone

[earnest atlas]

Hello,
I am using the SQLITE3 library in python. I found a problem that I could never quite solve, I hope you guys can help me :)
So, every time I start/restart the Python program, the database completely clears itself. Does anyone have a similar experience? Maybe even a solution?
If I made something unclear in my problem, you guys are free to ask me.
Thank you,
Luna.

[fading moss]

Hi so I have gthis cmd

    cur.execute(f'''INSERT INTO banlist (user_id) VALUES ({user}) (reason) VALUES ({reason}) (moderator) VALUES ({ctx.message.author})''')    
``` or more line but it **always** gives me a syntax error
Traceback (most recent call last):
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "main.py", line 107, in ban
    cur.execute(f'''INSERT INTO banlist (user_id) VALUES ({user}) (reason) VALUES ({reason}) (moderator) VALUES ({ctx.message.author})''')
sqlite3.OperationalError: near "(": syntax error

[harsh pulsar]

!paste post your code using our paste site. read below for instructions 👇

[delicate field]

Pasting large amounts of code

If your code is too long to fit in a codeblock in discord, you can paste your code here:
https://paste.pythondiscord.com/

After pasting your code, save it by clicking the floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[harsh pulsar]

don't use f-strings for sql

[fading moss]

ok but how do i solfe error

[harsh pulsar]

#community-meta message

read that, let me know if you have any questions

[fading moss]

ill do that tmrw

i gtg

thanks tho

[earnest atlas]

conn = sqlite3.connect("server.db", check_same_thread=False)
curs = conn.cursor()

try: curs.execute("""CREATE TABLE IF NOT EXISTS users (username text, password text)""")
except sqlite3.OperationalError: pass

@harsh pulsar this is the code that I use to connect to the DB and create a table. The problem is that I do not know how to only create a table if it doesn't exist. Because of that, my table always gets cleared.
The solution I tried above does not work :/.

[calm cedar]

hmmm that's weird

let me check how i do mine

await db.execute('''CREATE TABLE IF NOT EXISTS users(guild_id integer, user_id integer, experience integer DEFAULT 0, level integer DEFAULT 0, last integer, messages integer DEFAULT 0, url text DEFAULT "https://cdn.glitch.com/dff50ce1-3805-4fdb-a7a5-8cabd5e53756%2Fblankrank.pngv=1628032652421")''')```

btw ik this is dumb but ive only every selected 1 row at a time now im trying to make a lb and i already know how to do that and stuff but what do i do with the top 10 after i do cursor.fetchall()

[harsh pulsar]

if i recall, you might need to use an explicit transaction using with db:, or use db.commit() after creating a table, otherwise it will only live in memory and never be written to disk. you can configure this by changing the isolation_level parameter when connecting to the database. see https://docs.python.org/3/library/sqlite3.html#sqlite3-controlling-transactions

[calm cedar]

like how can i make them into variables

if i do col1, col2, col3... will each row be assigned

[harsh pulsar]

fetchall() returns a list of tuples. each tuple represents a row. what do you want to do with those rows?

[calm cedar]

then i'd do col1[2] to get the exp of #1

[calm cedar]

well since im actually converting this bot from json to db

the lb embed is already made

so i guess just replace the variables

[harsh pulsar]

there's no general answer here

[calm cedar]

embed.add_field(name=f"4. {p4[0]}", value=f"Total Experience: {p4[1]}\nLevel: {p4[2]}\nTotal Messages: {p4[3]}", inline=False)

[harsh pulsar]

a list of tuples is a good sensible "default" for storing rows returned from a database

what is p4?

[calm cedar]

place4

so for example i could just

assign p1-10 to the tuple

and it's already setup

but yesterday i tried using a tuple as is and not break every part down to it's own variable and it gave me an error

[torn sphinx]

I know nothing about db but i want to learn how do i even begin and i want to use mongodb

[harsh pulsar]

my advice: don't start with mongodb. start with sqlite

[harsh pulsar]

why do you want to assign each column to a variable?

if you want to do something row 4 of the results, what's wrong with rows = curs.fetchall() ; do_something(rows[3])?

[calm cedar]

well i tried that with something else and it gave me an error

[torn sphinx]

Ok so how do i even start i know 0 about dbs

[harsh pulsar]

https://sqlbolt.com and check the pinned messages

[calm cedar]

well i will be doing it

this video helped me a lot

https://www.youtube.com/watch?v=girsuXz0yA8

[harsh pulsar]

try it. if you get an error, show your code and the full error output

[calm cedar]

and it's short

[torn sphinx]

Thx

[torn sphinx]

Thx

[calm cedar]

@harsh pulsar so for examople

this is the output because my test server only has 2 users

how would i get the exp for the 2nd user

can't do

row[1][1]

otherwise it will say

TypeError: list indices must be integer s or slices, not tuple

[earnest atlas]

Thanks

[earnest atlas]

Thanks too

I am going to try it later on, thanks for the help :)

Have a great night guys!

[calm cedar]

oh i didn't even see you didn't commit

yeah you have to commit or else it wont be saved

[earnest atlas]

Ok.

[calm cedar]

this goes for anything you to do the db

other than select

ect

[harsh pulsar]

!e ```python
rows = [(867655715585130507, 1102, 4), (428733263251505152, 177, 1)]
print(rows[1][1])

[delicate field]

@harsh pulsar :white_check_mark: Your eval job has completed with return code 0.

177

[harsh pulsar]

@earnest atlas @calm cedar you can open and commit a transaction nicely using with

db = sqlite3.connect(...)

with db:
    db.execute('create table ...')

[calm cedar]

ok i know that it closes it

but it also commits?

i could have sworn there are times it didn't save till i saod commit

[calm cedar]

bruh so why doesn't that work for me

[harsh pulsar]

it does not close the db in sqlite3. it might in other libraries. this is a common misconception (including by me at one point) and it's imo kind of confusing

if you want to close the db use contextlib.closing

import sqlite3
from contextlib import closing

with closing(sqlite3.connect('...')) as db:
    with db:
        ...

so to answer your question, it only commits, it does not close

[harsh pulsar]

because something is different in your code compared to mine 😉 i noticed you wrote row and not rows - perhaps row is only a single tuple, not a list of tuples?

[earnest atlas]

I got it now, thanks guys!

[edgy bloom]

Hi everyone, excuse me for this ask but i´m really struggled in this issue

work on a Glue Job ETL with pyspark

I need to loop a column that has xml texts, very veryyy long texts, to struct them un another dataframe

i used collect to encapsule the rows and iterate, but this action took more than 4 min to execute just for one register

other option was to convert to pandas and iterate but, the dataset has 500k registers and it causes problems. Can someone has any advice for this problem?

obs. the library to struct xml texts is xmltree

[torn sphinx]

has anyone created their own server? im looking to build my own i am a music producer and would like to access my project files across devices while on the go. And would like to gain experience in this new field , thanks!

[harsh pulsar]

you might not want to create your own file server from scratch. have you looked into syncthing or seafile?

[torn sphinx]

interesting, why is that? and I havent! Thanks for the suggestion. I havent heard of those either hahah soo womp

[harsh pulsar]

it might just be more work. you can set up a webdav server with nginx pretty easily (no database required).

but in general you wouldn't want or need a database for a file server

[torn sphinx]

interesting so to access my files across devices i can create a file server for my specific needs?

[torn sphinx]

so out of of seafile, nginx, and syncthing which would you recommend? I liked the sound of nginx after I gave all three a google

[harsh pulsar]

nginx is the most minimal - it's a general purpose http server that happens to support webdav

in all 3 cases, you will need a domain name and an ssl cert for that domain name (e.g. via letsencrypt), which will require some setup

i haven't tried self-hosting seafile or syncthing, both are reasonably popular and probably have easy-to-deploy docker container versions

however this is no longer about databases

[torn sphinx]

i hear ya, okay thanks ill try the web dev thread then?

[harsh pulsar]

sure

[calm cedar]

no i just forgot the s on discord. the issue was i did for i in rows not i in range of rows

oof

[harsh pulsar]

!e ```python
rows = [(867655715585130507, 1102, 4), (428733263251505152, 177, 1)]
for row in rows:
print(row[1])

[delicate field]

@harsh pulsar :white_check_mark: Your eval job has completed with return code 0.

001 | 1102
002 | 177

[harsh pulsar]

you usually don't need to loop over range(len(thing)))

[calm cedar]

oh

[harsh pulsar]

!e ```python
rows = [(867655715585130507, 1102, 4), (428733263251505152, 177, 1)]
for i, row in enumerate(rows):
print(f'Row number {i}, column 0: {row[0]}, column 1: {row[1]}')

[delicate field]

@harsh pulsar :white_check_mark: Your eval job has completed with return code 0.

001 | Row number 0, column 0: 867655715585130507, column 1: 1102
002 | Row number 1, column 0: 428733263251505152, column 1: 177

[harsh pulsar]

!e last one: ```python
rows = [(867655715585130507, 1102, 4), (428733263251505152, 177, 1)]
for row_number, (user_id, x, y) in enumerate(rows):
print(row_number, user_id, x, y)

[delicate field]

@harsh pulsar :white_check_mark: Your eval job has completed with return code 0.

001 | 0 867655715585130507 1102 4
002 | 1 428733263251505152 177 1

[fading patrol]

@torn sphinx, this is just a private thing to to sync your own devices? If so the domain and certificate may not be necessary. You could use no-ip if you need remote access, and if this is just for use on a home network, you don't even need that. I haven't used seafile or syncthing my self but if all you want to do is access files across devices, that's exactly what they are meant for

Totally off topic here though, both for this server and this channel!

[harsh pulsar]

true, you could do something as simple as set up a raspberry pi at home w/ a ssd connected + dynamic dns

[calm cedar]

await db.execute(f"UPDATE roles SET role_id = {role.id} WHERE guild_id = {ctx.guild.id}")```

d: roles.role_id

im not trying to insert a new row

im updating

i dont have this with other updates

wait

[harsh pulsar]

why are you still using f-strings in sql

did you read the link i sent earlier?

[calm cedar]

no

what link

[harsh pulsar]

#community-meta message

[calm cedar]

also i use them because usign ? randomly stops working for me

[harsh pulsar]

i thought i sent that to you this morning or yesterday

or maybe it was someone else... my apologies if so

[calm cedar]

nah wasn't me

ive ever been in that channel

[harsh pulsar]

i copied and pasted the link here. either it wasn't you or you just missed it, well read it now!

[calm cedar]

some of my commands work

[harsh pulsar]

"unique constraint failed" means that roles.role_id has a UNIQUE constraint and you tried to insert a duplicate value for that column

[calm cedar]

but sometimes it's like "wong object type" or something more technical

when tho it's an integer

[harsh pulsar]

i find that hard to believe, check to make sure you didn't accidentally pass something else

[calm cedar]

and ive even used int(guild_id) JUST TO MAKE SURE

[harsh pulsar]

if you can reproduce the error, we'd be happy to help here

[calm cedar]

but once i use f strings the very same variable WORKS

[harsh pulsar]

i guarantee it's not a bug in sqlite3's handling of ?

[calm cedar]

@harsh pulsar ok so that one worked but not this one

ed type

cursor = await db.execute(f"SELECT user_id, experience, level, messages FROM users WHERE guild_id = ? ORDER BY experience DESC LIMIT 10", (ctx.guild.id))```

[harsh pulsar]

1. remove the f just to be safe
2. (ctx.guild.id) is just ctx.guild.id - a length-1 tuple is spelled (ctx.guild.id,)

pretty annoying but that's just the way python decided to do things

you can use a list if you really hate the syntax, [ctx.guild.id]

[calm cedar]

wait wait

so the fix is adding a ,

?

[harsh pulsar]

yes

[calm cedar]

bruh

[harsh pulsar]

() is used for grouping, in a way the , is really what makes it a tuple

[calm cedar]

thanks it worked now

[harsh pulsar]

same reason you can write return x, y instead of return (x, y)

[calm cedar]

and for my issue of discord.ext.commands.errors.CommandInvokeError: Command raised an exception: IntegrityError: UNIQUE constraint faile d: roles.role_id idk why it was saying that but i found you can do INSERT OR REPLACE so i was able to merge all my code into 1 execute

wait 1 more thing if i have 2 variables do i just add a comma to the last one

[harsh pulsar]

you only need the trailing comma if there is only one element

if there is more than one, you don't need the trailing comma

[calm cedar]

ok

[harsh pulsar]

(x, y, z) and (x, y, z,) are both valid

but (x) is a special case, it's not a tuple, it's just grouping, as in (a + b + c)

once in a while you might leave a , at the end of a line and then be very annoyed when you get strange errors later in your code

welcome to python

[wraith mist]

hey guys, do you recommend any sqlite3 UI?

[harsh pulsar]

https://sqlitebrowser.org/

[grizzled kraken]

unresolved attribute "Column" in class "SQLAlchemy"

can someone tell me what i'm doing wrong? i followed the same thing in a tutorial and this happens

[velvet ridge]

Code+traceback would be quite nice

[grizzled kraken]

alright theres no traceback, its just code

from flask_sqlalchemy import SQLAlchemy
from flask_login import UserMixin

app = Flask(__name__)
db = SQLAlchemy(app)
app.config['SQLALCHEMY_DATABASE_URL'] = 'sqlite:///database.db'
app.config['SECRET_KEY'] = 'QWERTYUIOPASDFGHJKLZXCVBNM'


class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), nullable=False)
    password = db.Column(db.String(80), nullable=False)

@app.route('/')
@app.route('/homepage')
def home_page():
    return render_template('home.html')

pycharm highlighted .Column and .Integer as unresolved attribute "Column" in class "SQLAlchemy"

when i look inside my db. i added stuff inside it does not show anything @velvet ridge

[velvet ridge]

There is nothing called Column in the module I guess

Maybe try column

[grizzled kraken]

didnt work, should i try this instead?

add method

i never encountered these errors before

[velvet ridge]

Did you try lower case?

[grizzled kraken]

yep still has that yellow tint

[velvet ridge]

It's still upper case?

[grizzled kraken]

i tried with column still has that error thingy

typed the wrong thing there but same error

if i press add method, what does it add? how does it automatically know what method to add

[velvet ridge]

Ermm dunno, I never used that

[grim vault]

It's just the IDE, the code should work.

[grizzled kraken]

alright hold up, im gonna add something to it

[grim vault]

But you need to do the db = ... after you set the config.

[grizzled kraken]

db.session.commit()```
this should work right?

yep done that

[grim vault]

https://flask-sqlalchemy.palletsprojects.com/en/2.x/

[velvet ridge]

I guess it's just a warning, is it

Try using the code anyways

[grizzled kraken]

alright i db.create_all() now

is this right?

its all zeros for some reason

[wraith mist]

Is anyone experienced with bulk inserts on peewee?

[grizzled kraken]

yep i added something and nothing shows up to my db

[wraith mist]

you use peewee?

[grizzled kraken]

uh no srry

[wraith mist]

oh okok

[grim vault]

take a look at C:\database.db

[grizzled kraken]

what do you mean? like with db browser?

[grim vault]

I mean your database could be in the root directory of your drive.

[grizzled kraken]

oh its in my directory, im making a flask app

[calm cedar]

when sorting a db can i make it stop when an int is too big?

for example

i have 20 rows with an id

their ids are unique but not 1-20

i sort ASC

but i want it to stop when an id is = or > than 20

so maybe it returns

1,3,6,7,8,12,16,19

oh wait

nvm

im dumb

[mint dust]

def next_id(self) -> int:
    # INSERT
    query = "INSERT OR IGNORE INTO cur_id (id) VALUES (?)"
    values = tuple([FIRST_ID])
    self.cursor.execute(query, values)

    # UPDATE
    query = "UPDATE cur_id SET id = id + 1 RETURNING id"
    result = self.cursor.execute(query).fetchone()

    if result[0] > LAST_ID:
        query = "UPDATE cur_id SET id = ? RETURNING id"
        values = tuple([FIRST_ID])
        result = self.cursor.execute(query, values).fetchone()
    
    self.conn.commit()       # <----- ERROR
    return result[0]

I'm getting an error on the commit, no idea why
sqlite3.OperationalError: cannot commit transaction - SQL statements in progress

[quartz stump]

My code uses db.command function (MongoDB and PyMongo) but mongomock raises an exception when use (Not Implemented Error). How to mock db.command?

[torn sphinx]

pymongo.errors.ServerSelectionTimeoutError: localhost:27017: [WinError 10061] No connection could be made because the target machine actively refused it

anyone encountered this error? ive searched the internet and docs and nothing helps me. most people are connecting to their db running off their pc using ports. im using it from mongo mongodb+srv://user:[e-mail]/

[crisp meadow]

Hey, guys could anyone please suggest good github related with pandas to practice for beginner?

[still pine]

Is it normal for mysql workbench to freeze whenever I try to stop an instance?

[sharp dragon]

Hello everyone, Im storing an excel file in a filstream(varbinary(MAX)) column in database, how do i now read from the varbinary field and convert it back into an excel document? Any help appreciated
Thank you

[runic shoal]

When SQL outputs data it says it like this [(100.0,)]. How can I make it only say the number without the [()]?

[grizzled kraken]

Unresolved attribute reference 'Column' for class 'SQLAlchemy'

can someone help me?

[edgy bloom]

Hi guys. Here is the Glue ETL job code that is causing headaches to me

That line takes almost 4 minutes to execute for just one register

this job tries to struct XML texts

these texts are stored un pyspark dataframe column. Very long texts

and i need to loop that column to pass texts to extract functions. Tried with collect() and ToLocalIterator() and now with ToPandas()

have the same result. Really need your help guys :c

[faint blade]

Can't you loop through it without converting it?

This may be more about #data-science-and-ml

[pure iron]

guys, i made code with cooldown, but bot doesnt change anything in db
here code:

@commands.command(
        name="daily",
        aliases=["Daily"]

    )
    async def user_daily(self, ctx):
        place_datetime = datetime.datetime.now()
        local_collection = collection['coins']
        data = local_collection.find_one({'id': ctx.author.id})

        if not data:
            data = {}
        data['datetime'] = data.get('datetime', place_datetime - datetime.timedelta(days=1, seconds=60))
        if (place_datetime - data['datetime']).days < 1:
            await ctx.send(
                f"You can only collect the reward once every 1 day! Come via `{int((place_datetime - data['datetime']).seconds / 3600)}` часов")
        else:
            local_collection.update_one({'id': ctx.author.id},
                                        {'$inc': {'balance': 50}, '$set': {'datetime': place_datetime}})
            await ctx.send(f"You took 50 coins! Come back in a day and pick up more!")

[hybrid dust]

I need to find a set of possible superkeys when given a relation and a set of attributes, does anyone have any direction? everything I look up only tells me how to find the relation given the keys

[quaint river]

hey all, I'm working on a Flask/Sqlalchemy guide. I have 2 classes defined in my models.py, that I can query like this: User.query.all() and Post.query.all() and that works fine, but then I have a self-referential association table that also lives in models.py but is not actually a class, it was created using followers = db.Table("followers", ...) , but if I try to query followers I get NameError: name 'followers' is not defined. How could I inspect/query the followers association table?

[fading patrol]

https://stackoverflow.com/questions/3618690/how-to-query-a-table-in-sqlalchemy/34757231

[quaint river]

@fading patrol thanks for the link, I tried this, but still got errors: ```>>> from sqlalchemy import create_engine

from sqlalchemy.orm import sessionmaker
engine = create_engine(app.config["SQLALCHEMY_DATABASE_URI"])
Session = sessionmaker(bind=engine)
Session = Session()

Session.query(followers)
Traceback (most recent call last):
File "<console>", line 1, in <module>
NameError: name 'followers' is not defined

Session.query(Base.metadata.tables['followers']).all()
Traceback (most recent call last):
File "<console>", line 1, in <module>
NameError: name 'Base' is not defined ```

not sure what I'm missing

[fading patrol]

Base is from the example so you don't want to use that. And it looks like you didn't declare or import followers into that interpreter session.

Otherwise I think Session.query(followers) is probably on the right track

[quaint river]

@fading patrol ok I see, I thought Base was generic to sqlalchemy. In theory followers is already imported/declared since I'm working directly with flask shell, but I also tried re-importing with from app import models (followers is declared in models.py) and I'm still getting the NameError on followers

by contrast, Session.query(User).all() and Session.query(Post).all() work perfectly

@fading patrol adding from app.models import followers made it work! 😄

thanks man!

[grave eagle]

small question, what's better?
A single but huge database
or small databases with 3/4 tables each?

[fading patrol]

One database, unless the tables are completely unrelated or you have some other very good reason to handle separately

[thorn canyon]

table tags:

| ID | NAME      | CONTENT        |
+----+-----------+----------------+
| 1  | email     | bruh email     |
| 2  | gmail     | bruh gmail     |
| 3  | morth     | bruh morth     |
| 4  | gmail.com | bruh gmail.com |
| 5  | nomail    | bruh nomail    |

SELECT     tags.name
FROM       tags
WHERE      tags.name SMTH 'fmail'
LIMIT 3;```
I want this SMTH to be as LIKE operator but to it return:
```py
[ ['email'], ['gmail'], ['gmail.com'] ]```
Is there any way to do that?

[foggy mountain]

**i am trying to make a afk cog ** with very limited knowledge of python

the screenshot shows what i have in mongodb

what i want to do is:
on_message
if someone mention you
bot check if mention.id in member (on database)
check AFK (on database)
send message: user is afk with {reason}

if member texts
bot check if author.id in member (on database)
set AFK to false (on database)

can anyone help please?
how do i read database, any docs would help too.

[bitter bone]

Say, I have three models - thread, configuration and server. Now the configuration can be targeted either to a particular thread or all threads of a server. The configuration.id can either be a server ID or a thread ID, now I want to make this id a foreign key relation, so the foreign key relationships can either be a relation with server.id or thread.id. How will I go about doing this? A "union" foreign key relationship

Moved to: #help-kiwi message

[fading patrol]

Don't think so... Can you state directly what you are trying to accomplish (instead of how you want to accomplish it)?

[thorn canyon]

bot's command:
user executes it like ?tag search <tag_name>
And it shows the list with tags with similar names

getting it from db

[fading patrol]

LIKE '*mail*'? Or use a chain of LIKE with OR if you want to be more precise

[thorn canyon]

but name can be different

[fading patrol]

Maybe try in #discord-bots

[thorn canyon]

    def get_tag(self, guild_id: int, name):
        def disambiguate(rows):
            if rows is None or len(rows) == 0:
                return 'Did not found anything.'

            return '\n'.join(ro[0] for ro in rows)

        t = self.bot.database.fetchall('SELECT user_id, name, descr FROM tags WHERE guild_id = %s AND name = %s',
                                       (guild_id, name))
        if len(t) == 0:
            query = """SELECT     tags.name
                       FROM       tags
                       WHERE      tags.guild_id=%s AND tags.name LIKE %s;
                    """
            r = self.bot.database.fetchall(query, (guild_id, name))
            return disambiguate(r)

        return t[0]```

this is my current code @fading patrol

that always returns 'Did not found anything.' when name not exists

[fading patrol]

I'm not familiar with bot, maybe try #discord-bots . But clearly your query isn't returning results.

[thorn canyon]

it is not related to bots but ok bruh...

[foggy mountain]

Tried before coming here
Chat go brrrrr no one replied T_T

[thorn canyon]

no one helps bruv

[thorn canyon]

pls?

[cerulean gate]

I am having a hard time updating a document in mongodb with pymongo.

my collection looks sometihing like so:

{"_id": objectid,
"thing": "sdad",
"otherThing": [{"name": "john", "amount": 15}, {"name": "bill", "amount": 37} ]
}

I am trying to set a new value for the amount field, but only for the one that name is bill.

[obsidian vector]

Hey, anyone can help in dumping data to a mssql DB using pyodbc, actually file is in xlsx format and has approx 250MB file size, I tried to dump it but it is taking 45 or more minutes., Same file when dumped with SSIS package it takes around 5 min. So, is there any way I can optimize my dumping as well

[fair ridge]

how do I connect to localhost mariadb on windows? (my code doesn't work)

    conn = mariadb.connect(
    user="root",
    password="password",
    host="127.0.0.1", #also tried localhost here
    port=3306) #also tried nothing here

[earnest atlas]

I'm not sure. But I think the only thing you need to set, except for user and password, is host="localhost".

[fair ridge]

i tried that

doesn't work

[fair ridge]

does windows have a socket file?

when i do that, i get 10061

[fast burrow]

weird question but I have a table that carries the username and id of users. Is it more traditional to call the table user or users

[sturdy vine]

users cause its plural so its good grammar

[fast burrow]

thanks

[tight topaz]

Assuming an RDMBS schema (django ORM specifically), what would be your approach to saving multiple resolutions of some asset - for example - a video that in 4 different resolutions (4 different files), plus 4 accompanying images in the same size for each video?

These video+image pairs are logically the content of one of my models in the system. One option is to just have a model like VideoImagePair (i.e. a single resolution) and have a foreign key to my main model. Another is to have a model like ImageVideoBundle that actually has all the resolutions together.

[solemn bramble]

why do i keep getting this?>

[fading patrol]

I've been getting contradictory answers about this, but the one thing everyone seems to agree on is to be consistent across tables

[fast burrow]

yea thanks

[glacial wren]

hmm yes

database database database stuff

[steel lagoon]

How do you add database to your discord bot?

[gilded hedge]

hello

anyone knows python pandas?

from urllib.parse import urlencode

url_data_gov_hk_get = "https://api.data.gov.hk/v1/historical-archive/get-file"
url_covid_csv = "http://www.chp.gov.hk/files/misc/enhanced_sur_covid_19_eng.csv"
time = "20200801-1204"
url_covid = url_data_gov_hk_get + "?" + urlencode({"url": url_covid_csv, "time": time})

print(url_covid)

import pandas as pd

df_covid = pd.read_csv(url_covid)
print(type(df_covid))
df_covid
url_building_csv = "http://www.chp.gov.hk/files/misc/building_list_eng.csv"
time = "20200801-1203"    ```
```py
url_building = (
    url_data_gov_hk_get + "?" + urlencode({"url": url_building_csv, "time": time})
)
df_building = pd.read_csv(url_building)
df_building```

[tardy zodiac]

I know this isn't specifically Python related but is there any good guides/books on creating better databases? Covered the basics in one of my Comp Sci module but obviously making them safe/efficient for actual large scale projects was never really covered and I'd like to get better

[solid pewter]

for discord.py storage when you're just storing keys and values, would SQL or NoSQL databases be better

ok scratch that question

new question: what databases would be best for discord.py? like specific examples

[brazen charm]

postgresql with asyncpg generally is the go to db + driver combo

[solid pewter]

I need some help getting started with asyncpg

for a discord bot

I'm using discord.ext, and I already know how to do the SELECT * FROM table stuff

but idk really how to incorporate it into my code

[stray ingot]

Hiya, can we query if a point is contained in a given polygon using geointersect operator in MongoDB?

as in to return the documents which are contained in a given polygon, the location for each document is represented as a single point.

[scarlet latch]

I'm trying to write a program that fetches and stores crypto price changes in the last 24 hours in my local machine and deletes them after 24h. which DB management system/library/package is best for this?

it's gonna be storing over 600 coin pair data and the fetching interval will be 15 seconds

[fading patrol]

I'm not certain but SQLite is probably sufficient, and if so, that's going to be simplest to configure.

[scarlet latch]

thank you

[honest cedar]

i got a posgresql DB on my pc, how can I transfer it to my raspberry pi?

[high geyser]

If this is timeseries data, then you could try postgres with a timescale extension:
https://github.com/timescale/timescaledb

although regular SQL databases are sufficient for your use case!

[torn sphinx]

im having problem at mongodb, anyone can help in #help-cherries 🙏

[high geyser]

If you're new to databases and stuff, try mongodb for your bot!

[scarlet latch]

Thank you. I looked at SQLite and it looks like it's sufficient.

going to learn the entire thing soon

[high geyser]

yep, sqlite might be sufficient, but since you're ingesting data rather fast, it might be worth looking into timescale + postgres later.
or you could try timeseries specific databases, too!

[scarlet latch]

right! if SQLite proves to be slow at the task I'll probably have to go for postgress.

[fallow bluff]

i have these two, was wonderin how to set the database remotely?

yes i have a vps

[limpid salmon]

hey, i have 2 tables with some of the following fields:
Results table:
id home_team_results away_team_results results_date
History table:
id h_results_id (FK for results id) home_team_history away_team_history match_date
I want to find out the results id of the enteries that have the same home team, away team, and date in both tables.

Iv tried doing the following, however the issue with this is that results_id variable only gives the last value assigned to it after the for loop is done, therefore i cant use the results_id in my insert query because it just gives the last id.

    c.execute("SELECT h.home_team_history, h.away_team_history, h.match_date, r.id, r.home_team_results, r.away_team_results, r.results_date FROM history h LEFT OUTER JOIN results r ON h.h_results_id = r.id WHERE r.id IS NULL")
    history_data = c.fetchall()
    
    c.execute("SELECT id, home_team_results, away_team_results, results_date FROM results")
    history_results = c.fetchall()
    
    results_id = 0
    for j in history_data:
        for k in history_results:
            history_results_team = (k[1], k[2], k[3])
            history_matches_team = (j[0], j[1], j[2])
            print(history_results_team, history_matches_team)
            if history_results_team == history_matches_team:
                results_id = k[0]
                print("RESULT FOUNDDD")
    print(results_id)

[fading patrol]

Dump on the PC, transfer the file, import on the Pi, should be pretty simple. https://www.postgresql.org/docs/9.1/backup-dump.html

[halcyon stream]

Hey I am trying to connect to SQL Server Express database, and I am flummoxed by the fact that my script seems to ignore the UID/PWD being specified and keeps trying to connect to the database as my current logged in system user.

conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};'
                        'SERVER=(local)\SQLEXPRESS;'
                        'DATABASE=tmp_db;'
                        'UID=tmp_admin;'
                        'PWD=tmp_pwd;'
                        'Trusted_Connection=yes;')

oops, well it looks like Trusted_Connection forces the database connection to be through my logged in user account. Removing that at least lets me try to login with another account, even though it is unsuccesful.