#databases

1 messages Β· Page 190 of 1

viscid adder
#

ok

#

i'll try

paper flower
#

Maybe that lib is somewhat outdated?

viscid adder
#

maybe

paper flower
#

Looks like the case since it was updated ~5 years ago

viscid adder
#

ah

paper flower
#

I'd look for another firebase wrapper

viscid adder
#

ok i'll look at it

#

when u find it

paper flower
#

I mean, you should do that πŸ˜…

viscid adder
#

yes

#

what's im doing

#

for 2 days

#

okay not wait i'll show you my output :

unknown.png
burnt turret
#

!pypi firebase-admin i've used this in the past

delicate fieldBOT
burnt turret
#

although i think that will run all operations as an admin which may not be what you want

viscid adder
#

thanks guys !!!

#

i'll check it out

#

WORKING thanks !!!!!

paper flower
#

You have to install postgres DB API driver and change your connection string

#

psycopg2 for sync and asyncpg for async

bright inlet
# 
Traceback (most recent call last):
  File "test.py", line 1, in <module>
    import pymango, secrets
  File "/home/anandh/Documents/Projects/Python/arc-discord-bot/venv/lib64/python3.8/site-packages/pymango/__init__.py", line 4, in <module>
    from resources import Charge as Charges
ModuleNotFoundError: No module named 'resources'```
got this error while on linux (opensuse tumbleweed)
bright inlet
paper flower
#

You should add your username, password and db to that connection string

#

postgresql+psycopg2://username:password@host/database

#

Don't think so, what's SQLAlchemy here?

#

You have to change SQLALCHEMY_DATABASE_URI

#

Change it to new uri

paper flower
#

Yeah, but you have to add your username, password and database

burnt turret
#

!pypi pymongo

delicate fieldBOT
bright inlet
#

thanks

paper flower
#

Hm, what do you mean by "mixed up"?

#

3, 5.. are ids, right?

#

How did you get that data?

#

You didn't order them

#

By default postgres returns rows in whatever order they're on disk

#

If you want to order them by id then add order_by

#

Also i would personally recommend to use sqlalchemy directly in your code

#

You're using flask_sqlalchemy

#

Modern sqlalchemy has different api:

slides = session.scalars(select(Slide).filter(Slide.keynote == keynote))
#

No, i don't think so, it looks like flask_sqlalchemy has a bit different api, also it's using now deprecated Query object

#

At least Select is preferred in 2.x version, which is now in development but preview is available in 1.4

#

You're most likely using 1.4, you can check that in your dependencies

#

order_by(Slide.id)

#

Share code?

#

Are you sure? This should order slides correctly

#

Share your slide model

compact marlin
#

Thanks!

paper flower
#

You didn't inherit it from model

#

@clever gate

formal narwhal
#

What is the meaning of "NOT NULL" and "DEFAULT NULL" ?

paper flower
# 
class Keynote(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    # user_id instead of user
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
#

@clever gate

jade wing
#

why is your password column of such a long string?
that usually is a sign of people storing clear text passwords in the database, which isn't good security practices for you users

austere portal
#

How do I set a max value for Integer column in sqlalchemy?

paper flower
austere portal
paper flower
austere portal
#

thanks

past sundial
#

whats a very easy to use kv store that has a python client and an easy to use docker image?

#

redis would be perfect but my data is too large to store in memory

#

(i know that redis can persist to disk but from what i know it still needs to have the entire data in memory)

#

python client isnt even a must

tired canyon
#

Can someone DM me to jump in a quick voice chat, just need some help understanding some documentation

jade wing
shrewd bough
#

Sorry bout music idk. Need help on this end now

jade wing
# shrewd bough

a vertical tiktok in the wrong orientation is probably not a good way to describe your problem for us

opal moon
#

hello so guys

tired canyon
#

Can someone DM me to jump in a quick voice chat, just need some help understanding some documentation

vast epoch
formal narwhal
shrewd bough
#

Hmm

trail patio
#

Hi, one question, I'm using sqlite3 and when I want to insert something into my db it gives me this error back:
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 18 supplied.
Does anyone know why? I'm trying to save one string of numbers into there

earnest lily
#

regarding sqlite3, is there any way to monitor or β€œrefresh” table data (not an update query, data is added to the table externally) as opposed to creating a for loop, making a new connection/cursor to requery everytime? Seems like when you make the connection it takes the current data/state of the table and isn’t a dynamic connection

keen minnow
# earnest lily regarding sqlite3, is there any way to monitor or β€œrefresh” table data (not an u...

it depends.
It can be done as a job to check any recent updates, as part of the app logic to send updates to a message or event broker, or by using CDC tools like https://debezium.io/.
Personally, I am not too fond of tools like debezium since they work at a much lower level than the app level and potentially create tight coupling.

Note: my bad, I missed the sqlite3 part. No idea about it, but the same ideas remain πŸ˜‰

bold acorn
# 
gdi1['Mean_Yrs_Schooling_Male']= gdi1['Mean_Yrs_Schooling_Male'].astype(float)

could not convert string to float: '..' any ideas ?

molten crystal
#

Anyone know why this is created a corrupted table/db file? No error messages are popping up.

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

intents = discord.Intents()
bot = commands.Bot(command_prefix = "?", case_insensitive = True, intents = intents.all())
bot.remove_command('help')


    
    


@bot.event
async def on_ready():
  print('Logged in as {0.user}'.format(bot))



async def load_extentions():
    for filename in os.listdir('./cogs'):
        if filename.endswith('.py'):
            await bot.load_extension(f'cogs.{filename[:-3]}')

async def main ():
    async with aiosqlite.connect('memes.db') as db: 
        async with bot:
            bot.db = db
            cur = await bot.db.cursor()
            await cur.execute('''
            CREATE TABLE IF NOT EXISTS memes (
            messageid text PRIMARY KEY,
            userid text NOT NULL,
            username text NOT NULL,
            timestamp text NOT NULL
            )
            ''')
            await bot.db.commit()    
            await load_extentions()
            await bot.start(os.environ['botsecret'])
asyncio.run(main())
round valley
#

Anyone know if it's possible to write to a SQL server via Pandas to_sql() function while defining primary keys/indexes? Or do you have to go down to the SQLAlchemy level to define that?

paper flower
round valley
#

It both does and it doesn't. If the table doesn't exist, it will generate it using the dtypes assigned to your columns. But there doesn't seem to be a way to specify a SQL-level index or keys. I know Pandas uses SQLAlchemy under the hood, just looks like it doesn't expose that additional functionality from SQLAlchemy 😦

delicate fieldBOT
#

Hey @copper quest!

You either uploaded a .txt file or entered a message that was too long. Please use our paste bin instead.

copper quest
#

Hello guys i have a question for a personnal project (very dirty right now). I 'am trying to insert a whole data frame into an accdb database. The goal is to take an excel file full of data and insert it directly inside a table of the db. The goal would be to be able each month to run the code and have the new simulation data added to the current table. Right now i'am trying to insert the first excel file but iam running into some errors hat i'am not able to fix at the moment could you please help me or advice me on what is going on ?The error is : line 48, in <module>
cursor.executemany( '''INSERT INTO Seriatimo (
pyodbc.DataError: ('22003', '[22003] [Microsoft][ODBC Microsoft Access Driver]Numeric value out of range (34) (SQLExecDirectW)') . I do somethimes run into some syntax issues also. Thanks a lot for your help reallly appreciated https://paste.pythondiscord.com/zukohesana

unkempt prism
brave bridge
#

Any idea why this works correctly? I need to find all rows where at least one column is 'waldo', without using OR and IN.

CREATE TABLE foo (a1 text, a2 text, a3 text);
...
SELECT * FROM foo
    WHERE NOT (a1 <> 'waldo' AND a2 <> 'waldo' AND a3 <> 'waldo');

https://www.db-fiddle.com/f/oL4TCq4vJBkorJuw9AKQdq/0
Specifically, shouldn't (a1 <> 'waldo' AND a2 <> 'waldo' AND a3 <> 'waldo') equal NULL if the first one or two columns are NULL?

#

Ohh wait I think I get it. If any of the conditions is False, then the parenthesized condition is false because NULL and FALSE is FALSE. And Trues don't add anything

#

well as always, typing the question out answers it

jade wing
plucky anvil
#

hi I am working on a project that downloads a CSV from a website and imports to database [my sql] please help me

jade wing
shrewd bough
kindred shard
#

hello! do you know how to do something like this
self.bot.pg_con.execute("DELETE FROM kennel WHERE pet = $1 AND userid = $2", pet, ix)

with postgres?

#

i just wanted to use a parameter instead a concrete value

#

like instead "where id=10" comparing it with a variable like " where id = client_id"

burnt turret
kindred shard
#

@burnt turret

unknown.png
burnt turret
#

what library are you using?

kindred shard
#

psycopg2

burnt turret
#

ah, i think psycopg has a different notation for parameter substitution. let me check

#

this is what asyncpg uses though

kindred shard
#

thank you. @burnt turret I tried different ways to struture the query but still not working.
wouldn't be better if i just import the other library?

burnt turret
#

can you show what your query looks like now?

#

i can try helping you fix it

kindred shard
burnt turret
#

remove the parentheses around the %s, and the second argument has to be an iterable

#

so cursor.execute("DELETE FROM ...", [id_cursor])

#

refer the examples in the page i linked above

kindred shard
#

omg, it worked

#

thank you so much @burnt turret

#

i struggled so hard with just a simple query

#

not a friendly syntax, but guess it's all lack of experience. 1st time managing dbs.

burnt turret
#

you'll get it πŸ˜„

mellow trench
#

Is there any way to write a BLOB or raw string to a file using psql (PostgreSQL client)?

sudden gust
#

how i can add many values in the (members ids)?

unknown.png
mellow trench
#

Use an association table:

CREATE TABLE member_roles(
    user_id INTEGER NOT NULL,
    role_id INTEGER NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES members(user_id),
    FOREIGN KEY (role_id) REFERENCES role_data(role_id)
);
sudden gust
mellow trench
sudden gust
mellow trench
#

no, no "roles" column in "members" and no "member ids" column in "role data"

#

you just join when you want the related data

sudden gust
#

oh

#

im new to databases

#

:/

digital bronze
#

Not sure if this is the right place to ask but best I got. Using MySQL, is there a good way to ensure concurrent INSERTs with AUTO_INCREMENT will work on a specific table and that they're as performant as possible? This specific table doesn't rely on the AUTO_INCREMENT values being consecutive but others in the DB do.

mental basin
#

quick question, if I have the following columns: Name, Amt in Table1 and Query, FiltAmt in Table2 and I want to sum(Amt) in table1 / sum(FiltAmt) in table2. What would the optimal sql query be like?

I tried some thing like Select sum(Amt)/(select sum(FiltAmt) from table2) from table1; which works but not sure if this optimal

harsh pulsar
#

that said, i don't think it's a good idea to ever rely on the insertion order with auto increment

harsh pulsar
mental basin
#

let's say I am working with 200GB of data

harsh pulsar
mental basin
#

makes sense

digital bronze
# harsh pulsar i think in general mysql itself can guarantee the safety of auto increment withi...

Alright, I'm trying to modernize a, uh, like six year old codebase and the community around it has relied on values being incremented consecutively, I don't want to have weird edge cases breaking that but part of my modernization is one table to track changes that I want concurrent inserts to, since it's polled then deleted every minute I'm using auto increments to for an ordering but I'm considering something like a NOW() timestamp instead, all I need to be able to do is easily identify which have been fetched with a WHERE clause rather than specify like a hundred thousand specific rows to delete.

harsh pulsar
# mental basin makes sense

in general i dont think theres a better way to compute this other than two SELECTs. i wouldn't worry about it. but always use EXPLAIN when in doubt, for performance

harsh pulsar
#

actually im not even 100% confident that auto increment is fully thread safe

#

you'll have to check the mysql docs carefully

digital bronze
#

I read through the page on InnoDB auto increment locking, depending on the mode you set there's various reasons for it to have a increment lock on the table

#

There's one mode that doesn't guarantee values will be consecutive and one that does

#

One I forget

harsh pulsar
#

what if instead of concurrently writing to the database, you pull requests into batches and then do a single write every minute, or something like that?

digital bronze
#

I can't really, I'm basically logging changes to specific tables in triggers so they can be tracked and forwarded as subscriptions to users

#

Since the codebase is a six year old mess I can't just slap batching and stuff everywhere so the simplest solution is tracking before and after values through triggers then computing stuff later on the API side

paper flower
digital bronze
#

Maybe, but that'd be hard to do in a trigger

#

I might just use milliseconds since epoch

paper flower
#

Yep, in a trigger that would be hard to do

digital bronze
#

I don't need guaranteed unique values, just need to know if something came before or after a given value

paper flower
#

Multiple writes can occure in single millisecond though

digital bronze
#

Yeah, that's fine

paper flower
#

Why not use a timestamp then?

digital bronze
#

Milliseconds or timestamp, I'm not sure which I'd end up choosing

paper flower
#

Timestamp should be fine, it stores milliseconds too

digital bronze
#

As long as there's not 2+ writes to the same row in one millisecond (which, uh, given networking and stuff I strongly doubt will happen), then I don't really care

paper flower
#

It would definitely happen depending on your orm

digital bronze
#

PHP's mysqli πŸ˜›

#

(I did not choose it)

digital bronze
#

My guess is with the way things work there's like a one in ten trillion chance that a two queries on the same model run in the same millisecond

paper flower
#

Depends on traffic, but yes

#

Postgresql for example has a select for update construct that would allow you to lock specific rows until you commit

#

πŸ€”

ruby breach
#

Made a type-checked async ORM wrapped around asyncpg. The part I like most about this is that the types of model fields always match, so you can have an ORM and not loose mypy/pyright support.

Examples under /examples and in the wiki, and I appreciate any feedback!

Project Repo: https://github.com/trigondev/apgorm

I also have a project that actually uses apgorm, https://github.com/TrigonDev/Starboard/tree/main/starboard/database

class User(apgorm.Model):
    name = types.VarChar(32).field()
    nick = types.VarChar(32).nullablefield()
    primary_key = (name,)

user = await User.fetch(name="CircuitSacul")
reveal_type(user.name)  # str
reveal_type(user.nick)  # str | None
burnt turret
#

pretty neat

#

instantiating User does not seem to have type-checking though (its __init__ is just (**vals: Any) so i can do User(name=1)), is that intentional?

shy night
# 
raise ConfigurationError(
pymongo.errors.ConfigurationError: The "dnspython" module must be installed to use mongodb+srv:// URIs. To fix this error install pymongo with the srv extra:

how to install this ?

obsidian thunder
#

how to make good data base

paper flower
grizzled wadi
crystal vapor
torn sphinx
#

I wanna make a backend database for a job search engine. What would be better for it rdms or nosql

exotic mirage
#

Cant we have column of list in aiosqlite?

ruby breach
ruby breach
#

somewhat

#

it's not perfect

#

the part that's really typehinted is the types of the model fields

paper flower
burnt turret
paper flower
#

^

paper flower
paper flower
exotic mirage
#

per guild

torn sphinx
ruby breach
#

@paper flower @burnt turret yeah I might try dataclass transform. I don't want to use a plugin because then you (1) need the plugin installed and (2) only support mypy (not pyright, for example)

paper flower
exotic mirage
#

example?

paper flower
# 
class Guild:
    id: int

class Channel:
    id: int
    guild_id: int # < Points to guilds.id
timber sonnet
#

hey guys, if there's no unique value in a table, then is there no primary key then?

#

ie. area_id can be used a couple of times so that means that it's not a unique key right?

vast epoch
high agate
#

Does anyone know why my SQLite database loses its data after 12 hours?

#

or 24

#

it keeps losing data

burnt turret
# high agate or 24

That isn't really a known issue, can you give some more context? For example, are you running this on heroku or something like that?

high agate
burnt turret
#

You can try using something like their postgres add-on

high agate
burnt turret
#

It'd depend on what you're doing, but the big providers like GCP/Azure etc will all have some free tiers and all

high agate
burnt turret
#

I'm pretty sure there's a message pinned in #discord-bots with a list of cloud providers

high agate
burnt turret
#

i'm sure they have some tutorials on it

paper bluff
torn sphinx
#

So i'm wanting to put info into an embed, but i want to get the information from the databse about that specifc trade, i tried this but it didnt work:
py trade = mycursor.fetch(f"SELECT trade FROM t_info WHERE channelID = {message.channel.id}")

keen marsh
#

add single quotes

torn sphinx
#

ok

next sparrow
jade wing
keen marsh
high agate
#

I'm going to probably look for a host that supports SQL as my whole discord bot is using that already

ebon skiff
jade wing
high agate
torn sphinx
next sparrow
delicate fieldBOT
#

@next sparrow :x: Your eval job has completed with return code 1.

001 | Traceback (most recent call last):
002 |   File "<string>", line 1, in <module>
003 | AttributeError: 'str' object has no attribute 'hellobello'
keen marsh
delicate fieldBOT
#

@keen marsh :white_check_mark: Your eval job has completed with return code 0.

['__new__', '__repr__', '__hash__', '__str__', '__getattribute__', '__lt__', '__le__', '__eq__', '__ne__', '__gt__', '__ge__', '__iter__', '__mod__', '__rmod__', '__len__', '__getitem__', '__add__', '__mul__', '__rmul__', '__contains__', 'encode', 'replace', 'split', 'rsplit', 'join', 'capitalize', 'casefold', 'title', 'center', 'count', 'expandtabs', 'find', 'partition', 'index', 'ljust', 'lower', 'lstrip', 'rfind', 'rindex', 'rjust', 'rstrip', 'rpartition', 'splitlines', 'strip', 'swapcase', 'translate', 'upper', 'startswith', 'endswith', 'removeprefix', 'removesuffix', 'isascii', 'islower', 'isupper', 'istitle', 'isspace', 'isdecimal', 'isdigit', 'isnumeric', 'isalpha', 'isalnum', 'isidentifier', 'isprintable', 'zfill', 'format', 'format_map', '__format__', 'maketrans', '__sizeof__', '__getnewargs__', '__doc__', '__setattr__', '__delattr__', '__init__', '__reduce_ex__', '__reduce__', '__subclasshook__', '__init_subclass__', '__dir__', '__class__']
keen marsh
torn sphinx
#

uhh

jade wing
keen marsh
torn sphinx
keen marsh
torn sphinx
#

yeah

#

I tried this:
trade = mycursor.execute(f"FETCH trade FROM t_info WHERE channelID = '{message.channel.id}'")

keen marsh
torn sphinx
#

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 'FETCH trade FROM t_info WHERE channelID = '925825930847477790'' at line 1

keen marsh
torn sphinx
#

ok

#

hmm it goes through but the embed just contains None

jade wing
torn sphinx
#

oh okay

grim vault
#

mysql uses %s as placeholder not ?

jade wing
#

ah, my bad if it's mysql, i probably didn't read carefully enough

torn sphinx
#

no worries

grim vault
#

And You need to fetch the data after execute with fetchone() or fetchall() depending what you want.

torn sphinx
grim vault
# 
mycursor.execute('SELECT trade FROM t_info WHERE channelID = %s', (message.channel.id,))
trade = mycursor.fetchone()
keen marsh
grim vault
#

Yes, but it's better do not build up a bad habit.

torn sphinx
#

it works but it just returns as None

grim vault
#

None means no data found.

torn sphinx
#

there is data in my database

mental basin
jade wing
keen marsh
# torn sphinx there is data in my database

you could also try:

mycursor.execute('SELECT trade FROM t_info WHERE channelID = %s' %message.channel.id)

and if that still doesn't work just go back to the f-string:

mycursor.execute(f'SELECT trade FROM t_info WHERE channelID = '{message.channel.id}')

torn sphinx
#

okay

keen marsh
# jade wing i agree with <@227812854022144003> never the less, better to learn to do it the ...

Ultimately, the same way in which somebody would have to modify his code to successfully perform a SQL injection with an f-string would be the same way that it would have to be done using your "safer" method. You are still substituting the same part of the string with the same variable.

And for the record, the "right way to do it" is the way that works... it's six of one and half a dozen of the other.

jade wing
# keen marsh Ultimately, the same way in which somebody would have to modify his code to succ...

i don't know how it works with that driver and in python, i must confess that i come from other languages that do not do it on the client side at all but does it using prepared statements and placeholders for proper bind variables in the database, in those cases it's not done on the client side at all and that is a big difference from doing it as you say it's being done in the driver

jade wing
keen marsh
jade wing
paper flower
potent sentinel
#

Hey guys, I have a very beginner type question for yall:

#

I am working on a small project that involves scraping web data and putting it into a shared excel document. Since I was learning how to use sqlite at the time, I put it into an sqlite database, along with some other dated info I wanted to match it up with

#

My end goal is to have it running on the cloud instead of on a schedule on my own pc, and to have it uploading to some sort of database or excel document

#

but it seems like all my options are complete overkill. Like setting up an azure database seems way too complicated at this time right?

#

I just feel like there have to be easier ways of doing this but I don't know where to look or who to ask

jade wing
# potent sentinel but it seems like all my options are complete overkill. Like setting up an azure...

you can still use sqlite in many cloud environment as long as they offer persistent storage
on others like Heroku you can't, they will wipe the storage quite regularly and the data will be gone, there you would have to use there there database add-on which is a postgres database that they host and run
so, as you see it can very vastly from hosting company to hosting company and even among different product offerings from the same hosting company

potent sentinel
#

So something like azure would be able to hold my sqlite file in the same directory as a script?

jade wing
keen marsh
round valley
#

Data type mapping between Pandas and SQLAlchemy sure is... something. Having to write some pretty hefty manual datatype remapping functionality to allow for proper indexing and DB optimization.

exotic mirage
paper flower
exotic mirage
paper flower
exotic mirage
exotic mirage
paper flower
#

You have to use foreign keys to "link" them

#

What you need is called one to many relationship

exotic mirage
# 
      await cursor.execute('CREATE TABLE IF NOT EXISTS fc (id INTEGER PRIMARY KEY AUTOINCREMENT, guild INTEGER, channels INTEGER, FOREIGN KEY (channels) REFERENCES lists (id))')
        await cursor.execute('CREATE TABLE IF NOT EXISTS channels (id INTEGER PRIMARY KEY AUTOINCREMENT, channel INTEGER)')
exotic mirage
paper flower
#

Or just select all channels where guild_id equals guild id you want

jade wing
paper flower
#

@exotic mirage Your tables should look something like this:

create table guild (
    id bigint primary key
);

create table channel(
    id bigint primary key,
    guild_id bigint not null references guild(id)
);
jade wing
slender crypt
#

Hi mates, Anyone can help me better understand Error (Condition) Handlers in MySQL ?
I don't get why Handlers are bound to Stored Procedures and can not be defined elsewhere

exotic mirage
#

oh thanks

torn sphinx
#

I am familiar with SQL databases. I still have a verz basic question: What is the difference between NoSQL and SQL. Should I try NoSQL in my next project?

torn sphinx
#

Ok, Thanks 😊

odd canyon
#

hey guys do u think there could be any fix for sqlite3 module in python not finding a table that is already there

#

sqlite3.OperationalError: no such table: stud

spiral mason
#

2 why are you using that gui interface

#

3 If you are using that app which i think it is then you must save the chnages first in that application before running the python program it wont commit itself.

odd canyon
#

i did all that

#

i use gui to show that table exists

#

obviously i use scriptural version

#

and i commited it db.close()

#

db = the var with refering database

grim vault
# odd canyon

Switch to the Database Structure tab and check the create table statement, maybe there is a space at the end?

unknown.png
jade wing
rapid ginkgo
#

How to make a list for member ID? Like in the photo, but instead of Object -> member ID?

unknown.png
#

My code

unknown.png
odd canyon
odd canyon
#

i tried everything still aint working

#

thanks for the help everyone

#

πŸ‘

grim vault
#

You can NOT use parameter binding for column / table names. In this case you'll need the f-string.

weak breach
jade wing
#

also, in this example the f-string would be safe as the variable isn't coming from any kind of user input πŸ‘

crystal kindle
#

i am having this issue

#

i want to remove these brackets and coma

paper flower
#

@crystal kindle

>>> import itertools

>>> result = [(10,), (45,)]
>>> list(itertools.chain.from_iterable(result))
[10, 45]
graceful widget
#

hii im new to db
and im trying to use aiosqlite for my discord bot

paper flower
graceful widget
#

im reading this docs btw

paper flower
#

I didn't use railway

#

sqlite database is simply a file, it's created on the same host where your bot is

graceful widget
#

im hosting on replit (pls dont judge)

paper flower
#

They wipe storage quite frequently

graceful widget
#

huh

#

u mean replit db?

#

or what

paper flower
#

You won't be able to use sqlite there, they would just delete the file

graceful widget
#

this is what i found

#

@sly garden

sly garden
#

"the best to use" isn't really a good query lol

torn sphinx
#

they ask you to use replit-db which is as bad as json, even worse.

graceful widget
#

so what do i use

#

cuz im not very familiar with db

#

i dont want to pick a gg db

torn sphinx
#

try learning raw sql first, all the sql databases work same ( upto 90% ), just the way of connection and query execution differs

then you can use any online hosted database, which wont get deleted because of your replit

graceful widget
#

so i learn sql?

torn sphinx
torn sphinx
graceful widget
#

alright

#

will codecademy do good tho

#

cuz i used to learn stuff there

torn sphinx
#

never tried it, so wont make a comment on it

graceful widget
#

ok

kindred shard
#

hello!
I'm having an issue, where i believe it's happening due the cursor is blocked by the previous query, so when i run something else it returns an error.
Any suggestions to fix it?

# 
def select_element(conn):
    cursor = conn.cursor()
    select = "SELECT * FROM production_orders"
    cursor.execute(select)
    return cursor.fetchone()


def delete_order(conn, id_order):
    cursor= conn.cursor()
    cursor.execute("DELETE FROM production_orders WHERE client_id = %s", [id_order])
    conn.commit()
    cursor.close()
#

it looks like when i execute delete_order after select_element gives an error

torn sphinx
#

what's the error though?

kindred shard
#

i think i figured out the problem.
I had to save cursor.fetchone() value on a variable, then close the cursor, and return the variable at the end of select

#

@torn sphinx thank you

ionic latch
# 
SELECT LastName, FirstName, COUNT(*) AS "OrderCount"  
FROM [Order]
WHERE "OrderCount" > 15
INNER JOIN Customer ON [Order].CustomerId = Customer.Id
GROUP BY CustomerId
ORDER BY "OrderCount" DESC, LastName, FirstName```
#

what is wrong here

gloomy echo
#

this might be a noob question, but when should i use databases?

ionic latch
# gloomy echo this might be a noob question, but when should i use databases?

You need it when your trying to link different types of information together. Say for example apple, when you buy something you send them your information. They then store these information and link them together in a system. So when they search you up they can find your address, and how many apple product you have bought etc.

#

A libary can have such a database

gloomy echo
#

wouldnt it be better if its just stored into memory?

ionic latch
#

That would be too big and complicated

#

Every customer is given a Id which is unique, which makes it easlier to find customer fast

#

In the past when computers weren't as developed as now, they used to write it down on paper

gloomy echo
#

damn

ionic latch
#

If you learn Sql which is basically how you make a database, you learn how to make database, insert data into it, remove data, find data just by simple lines

gloomy echo
#

is sqlite ok?

#

since its part of the python standard library

ionic latch
#

Yes you need to do 3 things when using it. First you import sqlite, then you make a destination for your database, and make a cursor

gloomy echo
#

cool, ill look more into it, thank you

ionic latch
#

Of course, i'm as new as you. If there is anything more dm me

icy oyster
# 
    cur.execute('''CREATE TABLE Genie
sqlite3.OperationalError: near "Long": syntax error
#

new to SQL know nothing tbh

tawdry halo
#

Is it possible to store data in sqlite and make sure its not erased when the program close? I've run into this error a lot

icy oyster
tawdry halo
#

i set the function to store data in the database then when i relaunch the app all the login details are gone and i have to start over

icy oyster
jade wing
tawdry halo
#

locally

#

how would i do this?

jade wing
tawdry halo
#

lemme show you

jade wing
delicate fieldBOT
#

Here's how to format Python code on Discord:

```py
print('Hello world!')
```

These are backticks, not quotes. Check this out if you can't find the backtick key.

jade wing
#

!paste

delicate fieldBOT
#

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.

torn sphinx
#

I'm tryin to design database for educational purpose and i don't know if i'm doing good : does this design make sense ?

unknown.png
#

I tried to say a book avec 1 publishing company and a company can have books ?

jade wing
# tawdry halo

that is definitely not python
is it javascript running in the browser or running under node.js, or what kind of environment is this?

proven arrow
jade wing
torn sphinx
torn sphinx
jade wing
torn sphinx
#

@jade wing

unknown.png
#

should i set every category (romance , action) or just name is enough ?

sinful saffron
#

!paste

delicate fieldBOT
#

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.

sinful saffron
#

and send the url

#

or do this

tropic plank
# 
async def on_message(message):
    if message.author.id == client.user.id: 
        return
    author = message.author
    guild = message.guild
    async with client.db.cursor() as cursor:
       await cursor.execute("SELECT xp FROM levels WHERE user = ? AND guild = ?", (author.id, guild.id,))
       xp = await cursor.fetchone()
       await cursor.execute("SELECT xp FROM levels WHERE user = ? AND guild = ?", (author.id, guild.id,))
       level = await cursor.fetchone()

    if not xp or not level:
        await cursor.execute("INSERT INTO levels (level, xp, user, guild) VALUES (?, ?, ?, ?)", (0, 0, author.id, guild.id,))
        await client.commit()

        try: 
            xp = xp[0]
            level = level[0]
        except TypeError:
            xp = 0
            level = 0
            
        if level < 5:
            xp = random.randint(1, 3)
            await cursor.execute("UPDATE levels SET xp = ? WHERE user = ? AND guild = ?", (xp, author.id, guild.id,))
        else:
            rand = random.randint(1, (level//4))
            if rand == 1:
              xp = random.randint(1, 3)
              await cursor.execute("UPDATE levels SET xp = ? WHERE user = ? AND guild = ?", (xp, author.id, guild.id,))
        if xp >= 100:
            level += 1
            await cursor.execute("UPDATE levels SET level = ? WHERE user = ? AND guild = ?", (level, author.id, guild.id,))
            await cursor.execute("UPDATE levels SET xp = ? WHERE user = ? AND guild = ?", (0, author.id, guild.id,))
            await message.channel.send(f"{author.mention} has leveled up to level **{level}**!")
        await client.commit()```
sinful saffron
#

the py

#

needs to be in the same line as ```

tropic plank
# 
async def on_message(message):
    if message.author.id == client.user.id: 
        return
    author = message.author
    guild = message.guild
    async with client.db.cursor() as cursor:
       await cursor.execute("SELECT xp FROM levels WHERE user = ? AND guild = ?", (author.id, guild.id,))
       xp = await cursor.fetchone()
       await cursor.execute("SELECT xp FROM levels WHERE user = ? AND guild = ?", (author.id, guild.id,))
       level = await cursor.fetchone()

    if not xp or not level:
        await cursor.execute("INSERT INTO levels (level, xp, user, guild) VALUES (?, ?, ?, ?)", (0, 0, author.id, guild.id,))
        await client.commit()

        try: 
            xp = xp[0]
            level = level[0]
        except TypeError:
            xp = 0
            level = 0
            
        if level < 5:
            xp = random.randint(1, 3)
            await cursor.execute("UPDATE levels SET xp = ? WHERE user = ? AND guild = ?", (xp, author.id, guild.id,))
        else:
            rand = random.randint(1, (level//4))
            if rand == 1:
              xp = random.randint(1, 3)
              await cursor.execute("UPDATE levels SET xp = ? WHERE user = ? AND guild = ?", (xp, author.id, guild.id,))
        if xp >= 100:
            level += 1
            await cursor.execute("UPDATE levels SET level = ? WHERE user = ? AND guild = ?", (level, author.id, guild.id,))
            await cursor.execute("UPDATE levels SET xp = ? WHERE user = ? AND guild = ?", (0, author.id, guild.id,))
            await message.channel.send(f"{author.mention} has leveled up to level **{level}**!")
        await client.commit()```
#

i did

# 
@client.event
async def on_message(message):
    if message.author.id == client.user.id: 
        return
    author = message.author
    guild = message.guild
    async with client.db.cursor() as cursor:
       await cursor.execute("SELECT xp FROM levels WHERE user = ? AND guild = ?", (author.id, guild.id,))
       xp = await cursor.fetchone()
       await cursor.execute("SELECT xp FROM levels WHERE user = ? AND guild = ?", (author.id, guild.id,))
       level = await cursor.fetchone()

    if not xp or not level:
        await cursor.execute("INSERT INTO levels (level, xp, user, guild) VALUES (?, ?, ?, ?)", (0, 0, author.id, guild.id,))
        await client.commit()

        try: 
            xp = xp[0]
            level = level[0]
        except TypeError:
            xp = 0
            level = 0
            
        if level < 5:
            xp = random.randint(1, 3)
            await cursor.execute("UPDATE levels SET xp = ? WHERE user = ? AND guild = ?", (xp, author.id, guild.id,))
        else:
            rand = random.randint(1, (level//4))
            if rand == 1:
              xp = random.randint(1, 3)
              await cursor.execute("UPDATE levels SET xp = ? WHERE user = ? AND guild = ?", (xp, author.id, guild.id,))
        if xp >= 100:
            level += 1
            await cursor.execute("UPDATE levels SET level = ? WHERE user = ? AND guild = ?", (level, author.id, guild.id,))
            await cursor.execute("UPDATE levels SET xp = ? WHERE user = ? AND guild = ?", (0, author.id, guild.id,))
            await message.channel.send(f"{author.mention} has leveled up to level **{level}**!")
        await client.commit()```
#

there we gp

sinful saffron
#

there you go

tropic plank
#

now what

sinful saffron
#

the error

tropic plank
#

ok

#

Hi

#

Hi

#

lvl

#

lvl

#

Hi

#

Sup

sinful saffron
#

wrong server

tropic plank
#

Sup sup

#

Lmao

#

code is too long @sinful saffron

sinful saffron
#

!paste

delicate fieldBOT
#

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.

tropic plank
sinful saffron
#

which line gives the error

#

and where do you connect to your db

tropic plank
#

aiosqlite

#

Current error shows line 38

sinful saffron
#

are you sure you don't close your db in any moment?

tropic plank
#

Can u explain more to me

sinful saffron
#

closing the connection to your db

tropic plank
#

Idk if I’m closing it I don’t think I am

sinful saffron
#

well idk, i can't see that part of the code shrug

tribal barn
#

Can someone help me with a SQL question

solid flint
#

What do you guys think of this data structure?

/Sites
-----------------------------------------------

Sites posts
- id
- user_id
- site_category_id
- site_brand_color_id
- is_visible
- published_at

Sites Post Info
- id
- site_post_id
- name
- slug
- logo 
- version


Sites pages
- id
- site_post_id
- site_page_category_id
- versoin
This will need to get the 'site' brand color as well, and should be able to be filtered by that.

Sites pages content (1-4 variations - shown only one record)
- id
- site_page_id
- image
- is_mobile
- is_dark
jade wing
torn sphinx
#

what do you think about this

unknown.png
#

@jade wing

wraith tiger
tribal barn
jade wing
# torn sphinx what do you think about this

it's really shaping up πŸ‘
table names can actually be plural or singular, either is fine if you think one or the other makes more sense to you, either way you choose just keep it consistent for all tables
columns are usally in singular. so a column with a foreign key would typically be named like book_id instead of books_id even if it points to a table named in plural like books
join tables are often named like the two tables it connects but with an underscore between them

torn sphinx
#

ok do you think i should populate categories with name of category like "Romance" ?

jade wing
# tribal barn Do u know SQL

i know some sql, but not as good as some of the professional database administrators i know

if you have a question, ask it in the channel, i bet there is people in here that is better then me at sql

tribal barn
#

Where’s the SQL channel

lavish trellis
#

There is no SQL channel it falls under this channel.

tribal barn
#

Can someone help me with a join in

lavish trellis
#

Also it is better to ask a question then to ask to ask a question.

tribal barn
#

Okay

#

Here’s my code

torn sphinx
jade wing
tribal barn
# 
WITH (
  SELECT BUYDATE, NAME, SUM(ORDERSIZE) AS NEW_COLUMN
  FROM shopper_data sd
  WHERE BUYDATE = CURRDATE() - 7
  GROUP BY BUYDATE, NAME
  ORDER BY SUM(ORDERSIZE) desc
) AS totalordersize

SELECT tos.*, RANK() OVER (PARTITION BY NAME, ORDER BY NEW_COLUMN desc) AS RANK
FROM totalordersize tos
#

I can’t figure out my error I use vertica database

torn sphinx
jade wing
jade wing
tribal barn
#

Anything

#

Rank()

jade wing
torn sphinx
#

i want my bot to import info in db

torn sphinx
#

How can i manage my info using dbs
When i generate key using command that key goes to database

#

into collum unused keys

orchid shadow
#

i can seem to print things from my db

digital bronze
#

How would you lock a row to prevent reads in MySQL?

#

SELECT FOR UPDATE doesn't seem to do it

torn sphinx
#

Hey anyone know any cloud providers where I can store any sort of database? I kinda need one in the terrabytes (5-10) and easily upscale to 20TB+

#

Preferably NOSQL

keen minnow
jade wing
jade wing
# digital bronze How would you lock a row to prevent reads in MySQL?

first, what kind of mysql storage engine are you using for that table?
i'm guessing InnoDB
SELECT ... FOR UPDATE will block any other locking select such as SELECT ... FOR UPDATE or SELECT ... FOR SHARE but not plane SELECT ... statements without locking requests
however, you can change the isolation level set either as the default or at least for all the involved sessions to a strict level such as SERIALIZABLE and disable autocommit which will treat all select statements as if they had FOR SHARE added to them, but doing so will negatively impact the performance of your database
if you are in control of the other select statements that you want to be blocked by your SELECT ... FOR UPDATE you could change just those other select statements and add FOR SHARE only to them, then your SELECT ... FOR UPDATE will block those
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html
together with https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

torn sphinx
#

hi can anyone help me setup influx database? i dont know how to download it

jade wing
torn sphinx
digital bronze
jade wing
torn sphinx
#

or Windows Binaries

jade wing
jade wing
jade wing
digital bronze
torn sphinx
#

i guess thats not good

unknown.png
jade wing
# torn sphinx thats what it should do?

the powershell line just downloads the zip file for you and extracts it to the folder that is specified on the command line, there's nothing more to it really

jade wing
jade wing
# torn sphinx i guess thats not good

you would need to run powershell as an administrator and run at least the last command with those privileges to be able to expand the archive to that directory

torn sphinx
#

i have this only

unknown.png
torn sphinx
torn sphinx
jade wing
torn sphinx
#

ok i need to use powershell

jade wing
jade wing
# torn sphinx ok i need to use powershell

yes, i wrote that much, and again, you need to run powershell as an administrative user, or else you will not have permissions to move things stored in that systems directory

torn sphinx
#

and how do i make this db run?

#

i need to run this .exe file?

#

okay i guess its working since i can go to http://localhost:8086/

jade wing
torn sphinx
#

okay but i need to have this powershell running to not close the db yes?

torn sphinx
ionic latch
# 
SELECT ProductName, Quantity, LastName, FirstName, OrderNumber
From Product, OrderItem, Customer, [Order]
WHERE Product.Id = OrderItem.ProductId
AND OrderItem.OrderId = [Order].Id
AND [Order].CustomerId = Customer.Id
ORDER BY ProductName, Quantity, LastName, FirstName, OrderNumber ASC;```
#

I'm trying to enter a WHERE Quantity > 100

#

but when i put it in there it gets me an error

paper flower
torn sphinx
#

Hello what would be the sql query to delete rows from table where (column1, column2) in [(1,3),(2,5),(6,3)]

paper flower
torn sphinx
#

Let's see if I can fit in with what I have right now

torn sphinx
ionic latch
#

I want to put in sql WHERE Quantity > 100

paper flower
#

You can't have multiple where statements, you can use logical operators like AND instead

jade wing
# torn sphinx okay but i need to have this powershell running to not close the db yes?

yes, unfortunately you will have to keep it running in powershell as they have not bothered to give influxdb any native windows service support, but...

there is ways to run it as a windows service anyways, you will just have to do it by hand or use third-party tools: https://github.com/influxdata/docs.influxdata.com-ARCHIVE/issues/2164
if you want to set it up your self by hand microsoft has general instructions on how to do it that you would have to tailor to influxdb your self: https://docs.microsoft.com/en-US/troubleshoot/windows-client/deployment/create-user-defined-service
or using the third-party open-source tool NSSM: https://nssm.cc/download
and some instructions on how to use it: https://web.archive.org/web/20211125170627/http://richardn.ca/2019/01/04/installing-influxdb-on-windows/#2runningasaservice

ionic latch
paper flower
ionic latch
#

yes but everything else is ordered correct

#

i did order it ASC;

paper flower
#

asc is only applied to one column

#

also it's asc by default

ionic latch
#

But shouldnt i do it?

jade wing
tribal barn
#

Hey anyone on that can help m

#

With coding

#

Hey guys I've been stuck at this error for so long rn and can't figure it out it's a relation DNE.

β€˜β€™β€™
With
INFO AS (SELECT to_char(WeekDate, YYYY-WI) as Week_Period, ordType, Count(Distinct shoppers) as names,
Sum(total)Ordertotal
From Order_data ord
Join name_reference nr using (shoppers)
Group by week_Period, ordType

Select week_period, ordType, names, ordSize rank() over (partition by ordTypr) as rank
From INFO

β€˜β€™β€™

keen minnow
high agate
#

How do i change a value every minute?

#

On sqlite dn

#

db

paper flower
high agate
#

please

paper flower
#

Don't forget to start your task

high agate
#

Hey, anyone see the error?

unknown.png
#

Line 20 is the cursor.execute(''' BTW

paper flower
#

group is a reserved keyword

dim lantern
#

Ty

modest shore
#

is there any way i could host my discord bot on my vps but use the db on my pc im using postgres

lilac bolt
#

Hi guys, I'm getting this error sqlite3.InterfaceError: Error binding parameter 2 - probably unsupported type.

#

I believe this is the line thats causing this issue python self.cur.execute("INSERT INTO accounts VALUES (?,?,?)", (str(discordid), float(200.0), username))

#

And this is the table ```python
self.cur.execute('''CREATE TABLE IF NOT EXISTS accounts (discordid integer, money real, username text)''')

paper flower
#

Otherwise it would change frequently and bot would stop functioning

paper flower
#

Also you should use biginteger instead of integer, discord ids are 64 bit wide, integer is only 32 bit

lilac bolt
#

Oh okokok

#

I changed that but I'm still getting this issue discord.errors.ApplicationCommandInvokeError: Application Command raised an exception: InterfaceError: Error binding parameter 2 - probably unsupported type.

paper flower
#

You changed what?

lilac bolt
#

I removed the converting and I changed it to biginteger

# 
        self.cur.execute("INSERT INTO accounts VALUES (?,?,?)", (discordid, float(200.0), username))
# 
        self.cur.execute('''CREATE TABLE IF NOT EXISTS accounts (discordid biginteger, money real, username text)''')
#

^its what it looks like now

paper flower
#

Maybe it's an issue with your float value, since it reports an error with argument number 2

lilac bolt
#

I think it has something to do with the float

#

Yeahhhh

#

But I'm not sure

#

I think the conversion from float to real isnt working

paper flower
#

Is that the full error?

lilac bolt
# 
    self.cur.execute("INSERT INTO accounts VALUES (?,?,?)", (discordid, float(200.0), username))
sqlite3.InterfaceError: Error binding parameter 2 - probably unsupported type.
#

This isnt the full error msg but I think this is where the issue lies

paper flower
#

Hm, i'm not sure what's the issue here, did you delete your table before creating it again?

#

@lilac bolt

lilac bolt
#

ohhh

#

No but I have some data in this database

#

It's in a different table though

paper flower
#

Well, you should change your tables manually then

#

There are migration tools for database schema like alembic but it's usually used together with sqlalchemy

lilac bolt
#

It's okay I'll just delete it

#

lololxdx

#

Im still getting the same issue

paper flower
#

Check values of discordid and username πŸ€”

lilac bolt
#

Okok

#

Ohh maybe I need to convert discorid into int

#

But that has never been a problem before

paper flower
lilac bolt
#

Earlier the way I was doing was I was inputting the values directly into it without the ???s

#

It should be int

#

Ok converting it did nothoing

paper flower
#

Just check types of all arguments please

lilac bolt
#

Also looking at the documentation i dont think bigint is a thing

#

I cant find anything on it

#

biginteger

#

Okok

#

oh.

lilac bolt
#

i needed to convert username to string

#

wait thats so dumb

#

username already was a stirng

#

okok

#

tyty for the help

paper flower
#

type(username)

lilac bolt
#

ohh........

unknown.png
#

OK TY

#

Also I cant find anything about bigint

#

And also it was working before when I was just using normal ints

#

So I'm a bit confused

paper flower
#

😐

modest shore
fading patrol
timber magnet
#

I want to learn database but I don't know where to start what do you recommend

jade wing
lilac bolt
#

Ah thank you!!! @jade wing

jade wing
# timber magnet I want to learn database but I don't know where to start what do you recommend

you probably want to start with a relational database and learn SQL and the basics of the normalization levels
one of the simpler databases are sqlite, which is an embeded database and doesn't need any separate server software running
other well known and widely used databases are mysql and mariadb (that is a fork of mysql) and postgres (which is highly capable), and then you have commercial behemoths like Oracle (which now owns mysql as well)
mainly on the windows side of things you also have microsoft mssql server
all of them use a flavor of SQL

vale stream
#

Is there any good resource for python data science?

jade wing
vale stream
#

Oops sorry πŸ˜‚

nova forge
#

Is it a good idea to have autocommit=True while using aiomysql?

#

Idk why but after sometime of using pool object it is not working after some time!

tired canyon
#

Not sure if this is the right topic to find help with openpyxl

#

I have a program reading data from one row and using it as a variable in subroutine, i can print the output in terminal

#

I'm looking to have it write the data into the adjacent column then move onto the next one

#

simplified:
Taking data from cell 1B βœ…
Running data through function βœ…
Outputting result βœ…
Write result into cell 1C❌
Return to step 1, starting at 2Bβœ…

#

Trying a new method now or I'd post code, but if anyone has any advise would appreciate it. If this is the wrong channel let me know, I used the help channels but know one knew!

tired canyon
#

^^Update to above^^

# 
column_name = 'username'
column_name2 = 'hashtags'

for column_cell in sheet.iter_cols(1, sheet.max_column):
    if column_cell[0].value == column_name:
        B = 0
        for data in column_cell[1:]:
            htag = data.value
            h = Hashtag.from_name(l.context, htag)
            if column_cell[0].value == column_name2:
                C = 0
                for cell in column_cell[1:]:
                    cell.value = h.mediacount
                    book.save

I've written this code out, however, it runs indefinitely without any errors, but also without any changes to the sheet. I am unable to see where it's going wrong as there are no errors.

pastel wren
#

is this a proper implementation of parameterized queries?

      s="INSERT INTO deck_storage (id,card,amount,deck_name,game,email) VALUES(%s, %s, %s, %s, %s, %s)"
      args=hold,cards,camt,deckN,game,userN                 
      db.execute(s,args)
jade wing
ashen elbow
#

can I get some help understanding databases just a little bit more to get me pointed in the right direction for my project, I am brand new her and am not voice certified yet so any help will do

fading patrol
ashen elbow
pale cairn
#

Is this correct?

torn sphinx
#

hm is using f-strings for replacing table names and parameters names bad? (sqlite)

# 
table_name = "uwu"
Cursor.execute(f"CREATE TABLE {table_name} ...")
fading patrol
torn sphinx
#

thanks though

jade wing
# tired canyon ```py column_name = 'username' column_name2 = 'hashtags' for column_cell in she...

i might be a bit late the pick this one up...
handling excel files in anything else then excel aren't idle even though things like google docs and openoffice has come a long way
it would be much easier to handle something like a csv file instead
if you must process an excel file you can, but the documentation for openpyxl has warnings such as:

openpyxl does currently not read all possible items in an Excel file so images and charts will be lost from existing files if they are opened and saved with the same name.

why are you processing the sheet column by column when you want to do operations per row?
you probably only want to check the column header names once and not for each iteration, so we'll start with that
you must call save with a filename as an argument and you defensively don't want to be saving the excel file for each iteration, that should be done at the end outside of the loop
instead try something along the lines of:

from openpyxl import load_workbook

filename = 'test.xlsx'

...

book = load_workbook(filename)
sheet = book['Sheet1']

# verify the names of the column headers on the first row for the first two columns of the sheet
if 1 and sheet['A1'].value == 'username' and sheet['B1'].value == 'hashtags':
    # process the sheet row by row starting from the second row
    # to make range() in python incluseive we need to add 1 to the index of the last row
    # otherwise we will not process the last row of the sheet
    for row_num in range(2, sheet.max_row + 1):
        # fetch the value from column B of the current row and use it as input to the function
        h = Hashtag.from_name(l.context, sheet[f'B{row_num}'].value)
        # set the value for column C of the current row
        sheet[f'C{row_num}'].value = h.mediacount

book.save(filename)

if there is someone else in here that knows the openpyxl library better then me they could probably write this in a better way as i have never worked with this library before now

#

... in the code is just where i had the code for Hashtag.from_name() and the l.context stuff to make it compatible with your code

jade wing
# torn sphinx ```py table_name = "uwu" Cursor.execute(f"CREATE TABLE {table_name} ...") ```

there is no placeholders for anything else then values as far as i know, so you would need to do something like that to be able to have other dynamic parts in a sql statement
if you have to do that for your design to work you should go to great lengths to avoid using variables that come from any kind of external input
as a last resource if that isn't an option either you should take extreme care to sanitize the input data and be strict with what characters or strings you allow to go through
when sanitizing data it's best to build a filter with what you allow rather then what you don't want to allow

torn sphinx
grizzled compass
#

I'm trying to connect to my DB with

from mysql.connector import connect, Error
try:
    with connect(
        host="mywebsite.nl",
        user="myusername",
        password="password",
    ) as connection:
        print(connection)
except Error as e:
    print(e)

And i'm getting this

2003 (HY000): Can't connect to MySQL server on 'mywebsite.nl:3306' (10060)

Does anyone know why and how i can fix this?

jade wing
# pale cairn Is this correct?

no, not really, and i apologize in advance if i'm going to sound kind of harsh now, but...
there are so many nuances to this all of this to be able to encompass it all in a few bullet points like that
you are trying to touch upon so many subjects at once such as security, privacy, data access and access rights, backups and recovery, data integrity/consistency and data redundancy (and not in a high availability sense but rather along the lines of normalization)
each of the different subjects could probably fill several books on there own and will be different from product to product
there are even completely different categories of databases and you can't just do sweeping generalizations and say that they are inherently secure, private, consistent, has no data redundancy and "We don't need to create regular backups the database management software(DBMS) does this for us" (which is not even generally true, actually none of it really is depending on the context)

jade wing
# grizzled compass I'm trying to connect to my DB with ```py from mysql.connector import connect, E...

your code can't reach the database
without more information it's impossible to say why
here is a few things to check:
is that the right address for the database and not just the site?
is the database running?
is the database configured accept external tcp connections?
do you have the right username and password for the database?
is there a firewall of any kind blocking the traffic between where your code is running and where the database server is running?

grizzled compass
#

Let me check those things πŸ™‚

#

Thanksss tho!

digital bronze
#

Is aiomysql the best option for async MySQL?

burnt turret
#

The library was unmaintained up until a while ago but it seems like it's back now

#

I think I'd seen of another library called asyncmy but I've never used it

digital bronze
#

Alright, thanks!

torn sphinx
#

Hi, I'm trying to learn asyncpg and I wonder what's the difference if I acquire a pool and then execute a query like py pool = await asyncpg.create_pool(...) async with pool.acquire() as conn: conn.execute(...) or just use pool's execute method like ```py
await pool.execute(...)

molten gazelle
#

Which would be better to store product data SQL or NOSQL. The data that will be stored is product generic info and and below that product types approximately 40 types with approximately 25 attributes. More attributes and tables can be connected to the product types

#

@ me when replying please

paper flower
#

If you have some unstructured fields you can still use json columns for that

paper flower
#

@molten gazelle

molten gazelle
#

Let me tell you more about what I will be doing

#

I have product attributes such as weight, packaging dimensions, etc.
Below that, there're types of products such as t-shirts, pants, etc.
Under each type there will be items.
For each item, there will be 25+ attributes

#

@paper flower

paper flower
#

What kind of attributes?

molten gazelle
#

color, size, texture...

paper flower
#

They could be expressed in sql too πŸ€”

molten gazelle
#

Yeah, I am trying to see what would be better

#

Most of the data is going to be pulled from some APIs and then needs to be compared and validated

paper flower
molten gazelle
#

Why?

paper flower
#

How would it help with validation?

molten gazelle
#

I did not say it would lol

#

I am wanting to know which way to go

paper flower
#

@molten gazelle IMO you can't go wrong with SQL, i didn't use NoSQL databases really and i wouldn't put any valuable/important data into them

#

At least when it comes to document db's like mongo

molten gazelle
#

I understand that you can't go wrong, but is this the efficient/ right way to do it?

#

It is giving me headache lol

paper flower
#

Data that you described could be modelled using sql, it's not hard to do.
If i understand correctly you would have Products and ProductTypes with multiple items each with unique attributes

#

I'm not quite sure why you need ProductType though

molten gazelle
paper flower
#

So essentially just products and their variations? e.g. different color, size, etc

molten gazelle
#

Well yes. If for example, it is a knife then you have the handle which has the dimensions and material, as well as the blade's dimension, style count, type of steel and etc

paper flower
#

You would probably want add some functionality that would be easier with relational databases (e.g. favorites, brands, sellers)

#

Also products with different properties are usually just are different products joined into some kind of group

#

since they would have different SKUs

molten gazelle
#

So why not NOSQL like DynamoDB?

#

I can store those attributes in a json under attriubtes in dynamo

paper flower
molten gazelle
#

Ok I see

#

This cannot be achieved with dynamo?

paper flower
#

I think nosql databases have references, so they would be kind of like joins in sql

molten gazelle
#

I am also planning to connect sales and returns to each product

paper flower
#

I think that's just not the best use for nosql

molten gazelle
#

So SQL is a better option

#

I agree

paper flower
#

to me it seems like your data is relational

#

But if you have a hammer everything looks like a nail

#

I only briefly used nosql, so i'm not the best person to ask

molten gazelle
#

I see

#

In your opinion how big of a project is this

#

Is it really complicated or not

paper flower
#

Well, it depends on how many features you need/want

#

I only know that you definitely can use sql for that πŸ˜…

molten gazelle
#

I understand

#

Well thank you for your opinion and advice

paper flower
#

There are definitely some use cases for nosql databases in case your data can fit into different documents with not so much relationships between them, but i think that's no the case for a marketplace type of project

#

Maybe you would have some data that could be stored in nosql type of database? It's not like you should just one or the other

#

But it wouldn't store say user data/profiles in nosql (just my opinion)

mental cosmos
#

hi

torn sphinx
#

scammer

mental cosmos
#

Bro wtf

#

Man I am not a scammer bruh @torn sphinx

#

I'm new to the community and this is how I'm treated

#

Sad

#

Just sad

torn sphinx
#

i saw ur stolen pfp

#

with your new

#

user

solar atlas
#

hi
i need i lil bit of creativity for a database manager
does anybody have any idea why a login and register interface would be useful on a db manager at all?

scarlet glade
#

hi, i want to ask if anyone have any references/idea on how to connect a webcam into a database. the problem is i want my webcam to scan my face and directly insert the saved photo into database. tq.

light blade
light blade
paper flower
timber sonnet
#

hey guys, is it not possible to link a key to a table that has multiple primary keys?

paper flower
timber sonnet
#

Since I tried linking it to a table like below through:
CREATE TABLE table (
id INTEGER,
studentname TEXT,
subjects TEXT,
PRIMARY KEY (id, studentname)

CREATE TABLE table2 (
student_name TEXT PRIMARY KEY
score INTEGER)

#

wait hang on, lemme fix it up

paper flower
#

Table2 could reference table

#

Oh, no

#

Table could reference table2

#

table.studentname could be a foreign key to Table2.student_name

timber sonnet
paper flower
#

One of the columns of composite primary keys could reference other tables

#

But to reference a composite primary key you need multiple columns

#

I hope that makes sense

timber sonnet
# 
CREATE TABLE areas (
    sa2_main16 INTEGER,
    sa2_name16 TEXT,
    sa3_code16 INTEGER,
    sa3_name16 TEXT,
    sa4_code16 INTEGER,
    sa4_name16 TEXT,
    PRIMARY KEY (sa2_main16, sa2_name16)

CREATE TABLE drinkingfountains (
    objectid INTEGER PRIMARY KEY, 
    site_name VARCHAR(80), 
    suburb VARCHAR(80) REFERENCES sa2(sa2_name16))
paper flower
#

You can't reference just one of the columns of a composite PK since it wouldn't identify row uniquely

timber sonnet
#

It's like something like this where sa2 is the subject of larger sa's

paper flower
timber sonnet
#

And suburbs = sa2 area names

timber sonnet
paper flower
#

As i just said to reference a table you need it's primary key

#

Since it's a composite PK you need to reference both columns

timber sonnet
#

ohh

#

I think I understand

paper flower
#

In your example you might have multiple rows with the same sa2_name16

timber sonnet
#

Not really, imagine it to be a guide to the locations

#

like city names

paper flower
#

Then you don't need composite primary key?

timber sonnet
#

where they only appeaer once

#

I don't? But each values represent the corresponding PKs in the row

paper flower
#

I still don't understand why you need a composite primary key here

#

What's sa2_main16?

timber sonnet
#

It's just an id number given to each area

worn flame
#

Seems like a common error.

sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30.00

My db gets pinged a few times to fetch a row, nothing serious. Currently on a freetier aws server.

Any idea how to approach this? Is it the code? The server? The db? I really don't think I have to extend this since it will be pinged more on a real server and this situation might be a nice simulation for a stressed case situation

paper flower
timber sonnet
#

and sa2_name16 is the area name

paper flower
#

Like autoincrementing integers, uuids, etc

worn flame
paper flower
pure cypress
timber sonnet
#

Yeah, so sa2_main16 is the id of the city, and sa2_name16 is the name that correlates to the city id

paper flower
#

You don't need a composite pk here

worn flame
# paper flower How do you manage your connections?

import os
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Set Database connection:

SQLALCHEMY_DATABASE_URI = f"postgresql://{os.environ['POSTGRES_USER']}:{os.environ['POSTGRES_PASSWORD']}@{os.environ['POSTGRES_HOST']}:{os.environ['POSTGRES_PORT']}/{os.environ['POSTGRES_DB']}"
engine = sqlalchemy.create_engine(
SQLALCHEMY_DATABASE_URI,
)

Set Database session settings:

SessionLocal = sessionmaker(
autocommit=False,
autoflush=False,
bind=engine
)
Base = declarative_base()

paper flower
worn flame
pure cypress
worn flame
# paper flower ok, but how do you create your sessions?

from sqlalchemy import Column, DateTime, Integer, String, ForeignKey
from sqlalchemy.orm import relationship

from ..configs.database import Base
from datetime import datetime

class hspDB(Base):

__tablename__ = "hsp”

…

In router:

hspDB.metadata.create_all(bind=engine)

Set DB dependency

def get_db():
db = SessionLocal()
try:
return db
finally:
db.close()

paper flower
#

Also just use with

# 
def get_db():
    with SessionLocal() as session:
        yield session
#

This worked for me in production πŸ™‚

worn flame
paper flower
#

You should read on context managers πŸ€”

pure cypress
#

I recall doing something very similar for shelve

paper flower
#

But basically fastapi would execute your code until yield and then code after yield when your endpoint is done

paper flower
pure cypress
#

Ah okay, not familiar with fastapi

paper flower
#

But it works the same way

pure cypress
#

Didn't realise this was even for fastapi

paper flower
#

Contextmanagers are used to do some setup/cleanup without you having to remember to call .close or similar method

#

sqlalchemy sessionmaker is already a context manager but fastapi requires you to have a function to work with DI, so you end up with something like this:

def get_db():
    with SessionLocal() as session:
        yield session
timber sonnet
worn flame
worn flame
# paper flower ```py @contextmanager def my_context_manager(): print("Hello") yield 42 ...

I understand now, today i learned what are generators within python. Thanks Doc!

Briefly: https://stackoverflow.com/questions/64763770/why-we-use-yield-to-get-sessionlocal-in-fastapi-with-sqlalchemy#:~:text=It's a great question%2C the,connection for all your app

In-depth: https://stackoverflow.com/a/231855/3802177

Stack Overflow
What does the "yield" keyword do?

What is the use of the yield keyword in Python? What does it do?
For example, I'm trying to understand this code1:
def _get_child_candidates(self, distance, min_dist, max_dist):
if self._leftch...

Stack Overflow
Why we use yield to get Sessionlocal in Fastapi with sqlalchemy?

def get_db():
db = SessionLocal()
try:
return db
finally:
db.close()

I got this code snipped to get Sessionlocal in fastapi with Sqlalchemy. Well, when I used return in...

torn sphinx
#

Hello I'm reading an sql table and storing the content into a pandas df. I excluded PK column from the dataframe establishing a condition in order to update PK key values given a tuple of tuples containing PK values. Now I would like to update every single column for those primary keys that match the condition:

update_query = f"""
  UPDATE {self.schema}.{self.table_name}
  SET 
     /* Grab values from df */
  WHERE (user_id, author_id)
  IN ((1,5),(6,8));
"""
kindred shard
#

hello, how do i print the number of rows returned by a select query?

brave bridge
#

So I've been reading up on relational theory a bit and I think I don't really get what a primary key is. Is it just a key (candidate key) which was chosen as the 'cool key' by convention?

proven arrow
tulip fox
#

How it is possible to fasten AES-256 to sqlite3?

brave bridge
#

Every key uniquely identifies a tuple, as I understand

proven arrow
#

Primary key cannot be null

tulip fox
proven arrow
kindred shard
#

does it necessary give an error when selecting a value from an empty table, or it returns "None"?

proven arrow
timber sonnet
#

Hey guys, if the area_id are the PK for each of the table, what should be the foreign keys then? I also have another table that has another area_id but it's for a much larger area, and another table that has an area_name of a smaller area than those of neighbourhoods and businessstats. I'm really confused as I thought that area_name could be a foreign keys but errors keep popping up

Screen_Shot_2022-05-23_at_7.54.39_pm.png Screen_Shot_2022-05-23_at_7.54.36_pm.png
kindred shard
#

the select is locked in the loop, and it's selecting values from due_date, but not every day is including , but not all days are included, so in those days it should return None.
@proven arrow

#

thank you!

brave bridge
proven arrow
proven arrow
tulip fox
fading patrol
tulip fox
timber sonnet
paper flower
brave bridge
proven arrow
#

Unique columns can still have null values.

#

Therefore in some cases you can’t identify. And so when it comes to referential integrity you can’t reference such rows.

#

Maybe your question is better asked what is difference between primary key and unique key. Should be plenty of answers online with more detailed explanation.

mellow trench
fading moon
#

Hello everyone

#

Please help

timber sonnet
paper flower
fading moon
#

I want to connect to a database which is not in the same network as my computer

#

But the problem is the database server is not reachable through the internet

#

How can I do it please ?

mellow trench
#

You can't

#

either it needs to be made reachable through the internet, or you need to connect to it locally

untold thorn
#

how to return largest values from orders if there is two same value of orders then return orders with smallest id and largest orders? - MySql
table: STORE | Columns:
ID, ORDERS
1 , 991
2 , 32
3 , 383
4 , 991

fading moon
#

Any hint how i can make it reachable through the internet ?

mellow trench
#

I don't know, why isn't it reachable? Firewall? NAT?

fading moon
#

It is located in another network. And right now I cannot put myself in the network

mellow trench
#

I don't know what that means

#

internet is made of interconnected networks

untold thorn
rare kelp
#

does this work?

#

cursor.execute(f"INSERT INTO Accounts (Username, Password) VALUES ({USER},{PASS}")

jade wing
torn sphinx
#

Whenever i try this, i get the error:

  File "C:\Users\user\AppData\Local\Programs\Python\Python310\lib\site-packages\discord\client.py", line 382, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\user\Desktop\Discord Bot\Ticket System\main.py", line 939, in on_message
    mycursor.execute(f"UPDATE t_info SET user_added = 'Yes' WHERE channelID = '{str(message.channel.id)}'")
  File "C:\Users\user\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\cursor_cext.py", line 243, in execute
    self._cnx.handle_unread_result()
  File "C:\Users\user\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 794, in handle_unread_result
    raise errors.InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found```

```mycursor.execute(f"UPDATE t_info SET user_added = 'Yes' WHERE channelID = '{message.channel.id}'")```
jade wing
# torn sphinx Whenever i try this, i get the error: ```Traceback (most recent call last): Fi...

i think it's because you are reusing the same mycursor that you have used for a previous query without reading all the results from that query before you try to use it again, mycursor.reset() should fix that for you
instead try (bonus: this is a better way to write queries then with f-strings):

mycursor.reset()
mycursor.execute('UPDATE t_info SET user_added = %s WHERE channelID = %s', 'yes', message.channel.id)
fading patrol
still orchid
#

discord.errors.ExtensionFailed: Extension 'cogs.economy' raised an error: ImportError: cannot import name 'cursor' from 'psycopg2' (C:\Users\ToxicPenguin\AppData\Local\Programs\Python\Python39\lib\site-packages\psycopg2\__init__.py)
how can I fix this error ? I am using a sqllite3 database

silk elk
#

Hi, I'm using async-sqlalchemy and I can't get the delete statement to work correctly, any idea why?

async def delete_user(self, model_name, user_id) -> None:
        async with self.session() as session:
            query = delete(self.models[model_name]).where(
                self.models[model_name].user_id == user_id
            )
            await session.execute(query)
            await session.commit()
still orchid
#

discord.errors.ExtensionFailed: Extension 'cogs.economy' raised an error: ImportError: cannot import name 'cursor' from 'psycopg2' (C:\Users\ToxicPenguin\AppData\Local\Programs\Python\Python39\lib\site-packages\psycopg2\__init__.py)
how can I fix this error ? I am using a sqllite3 database.
And I don't need to send a link to the python documentation. I don't understand this stupid documentation

proven arrow
still orchid
proven arrow
#

I don’t understand what you mean. Use the correct library for your database, and that should solve your problem.

jade wing
# still orchid I'm not experiencing it!!!

as @proven arrow already said, psycopg2 is for a database known as Postgres or PostgreSQL
if you want to use sqlite3 (only one "l" in there) you should use that library instead of psycopg2

paper flower
silk elk
#

Executes correctly

#

But entry on db remains as if nothing had happened

paper flower
#

Try using select instead of delete to see what you're retrieving from db πŸ€”

silk elk
# 
async def fetch_user(self, model_name, user_id):
        async with self.session() as session:
            query = select(self.models[model_name]).where(
                self.models[model_name].user_id == user_id
            )
            result = await session.execute(query)
            scalars = result.scalars()
            ...
silk elk
#

This is how the setup looks

DATABASE_URL = "sqlite+aiosqlite:///bot.db"
engine = create_async_engine(DATABASE_URL, future=True)  # echo=True
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)


async def db_init():
    async with engine.begin() as conn:
        # await conn.run_sync(Base.metadata.drop_all)
        await conn.run_sync(Base.metadata.create_all)
paper flower
silk elk
#

I'm stating to think this might noy have anything to with the db handler itself

paper flower
#

What's self.session btw?

silk elk
silk elk
#

I made a class to handle db functionality

paper flower
#

so just
self.session = async_session?

silk elk
#

Is passed as an argument to initialice it

#

But yes

paper flower
#

Is it an AsyncSession or a sessionmkaer?

silk elk
#

Is a sessionmaker that uses an async engine

paper flower
#

Hm, delete should work

#

You can try using async with self.session.begin() as session:

#

which would automatically commit your transaction πŸ€”

#

Also i think delete should return amount of affected rows, you could also check that

#

@silk elk so?

silk elk
paper flower
icy oyster
#

where can I learn the basics of SQL? I wanna write some data (strings, integers, lists) and read (strings, integers, lists) in SQL, nothing advanced tbh

help is much appreciated

mellow trench
#

Is there a specific database system you are going to use? MySQL, PostgreSQL, Microsoft SQL Server, etc?

abstract pivot
tardy badge
#

Is this correct? tia.

Select all the unique values from the Album column in the Artists table.

SELECT DISTINCT Album FROM Artists;
past spindle
#

Display full names of publishers with whom the author Alfred Aho did not publish his book(s). Your
query must use EXISTS (/NOT EXISTS) clause.
select publisherfullname from publisher join Published_By pu on publisher.PUBLISHERID= pu.PUBLISHERID join written_by wr on pu.bookdescID=wr.bookdescID join author on wr.authorID=author.authorID where not exists(select authorID from author where firstname='ALFRED' and lastname='AHO');

unknown.png
#

is this correct

jade wing
dreamy scaffold
fading patrol
#

Any DBeaver experts here? I somehow removed the blue "Read Only" lock icon from my toolbar. Right click and "Restore Hidden Toolbar Entries' does nothing. I can still toggle with the Database menu but that's annoying 😭

icy oyster
#

is this ok to learn basic SQL?

#

https://www.youtube.com/watch?v=7S_tz1z_5bA

YouTube
Programming with Mosh
MySQL Tutorial for Beginners [Full Course]

MySQL tutorial for beginners - Learn MySQL, the world's most popular open source database.
πŸ”₯ Want to master MySQL? Get the full MySQL course: http://bit.ly/2uAoPM3
πŸ‘ Subscribe for more SQL tutorials like this: https://goo.gl/6PYaGF

⭐️Want to learn more from me? Check out these links:

Courses: https://codewithmosh.com
Twitter: https://twitter....

β–Ά Play video
paper flower
icy oyster
jade wing
fading patrol
#

Also, I like interactive tutorials for the basics, and they're are plenty of good ones for SQL... Datacamp, Dataquest, sqlzoo, whatever you pick is not important just keep practicing

wraith seal
#

Hi, I am beginning to learn SQL as well. Should I go with PostgreSQL or MySQL, I am not really sure which one would be useful for data science and PM roles?

#

Secondly, what's the best software to use for that?

#

What exactly are the differences between the two, except the 'case' difference? I am completely new to this.

jade wing
wraith seal
#

And also which software/platform to use for this (online/offline)?

frigid ruin
# 
PUT test_index
{
  "mappings": {
    "dynamic": "runtime"
  }
}

what will this do in elasticsearch?

jade wing
fair cloud
#

yall my brain is mush. I can't figure out how to write this stupid SQL query.
I have a large table, the 3 relevant columns are (FOO, BAR, BAZ). There is a 1 to many relationship between FOO and BAR. And every BAR has an attribute BAZ that is either YES or NO.
Example:
FOO | BAR | BAZ
fooA | barA1 | NO
fooB | barB1 | YES
fooB | barB2 | YES
fooC | barC1 | NO
fooC | barC2 | YES
...

I would like to find all FOO who have at least on BAR with BAZ equal to YES and bar with BAZ equal to NO
That is in this example only fooC would fullfill that

jade wing
jade wing
slender atlas
#

I would use SELECT DISTINCT with EXISTS probably

#

And ANDing

slender atlas
jade wing
#

i think one would need to do a self join in this case to solve it if it's just one table

fair cloud
jade wing
fair cloud
#

yeah, brain is mush, but I managed to get there in the end haha

#

took me 20 min for a simple query like that tho haha

jade wing
fair cloud
#

cool

jade wing
# fair cloud cool

just an example of the self join I was talking about
one can use other join syntax as well

dull radish
#

hey everyone , i recently started web dev and received few MONGODB files from my professor, he wants me to figure something out, i have very less knowledge about MONGODB , the problem is i am unable to find the main DB files in 1000s of files its just lost, can you tell me the what path it could be in most prolly !! HELP !!! dm me or @ me here pleaseeeeeeeeeeee !

kindred pagoda
#

I am using mongodb and when I started to load a list of all users this list stopped and no one else is loaded and the bot is not responding to commands either

109.png
#

Simply put, the bot stopped uploading new users to the database

torn sphinx
odd raven
# 
cursor.execute("create table anime(id integer primary key, name varchar(1000));")
cursor.execute(f"insert into anime(id, name) values(%s,'%s')"%(2,"testing"))

it creates but doesnt inserts mysql

#

is it something that i am doing wrong

odd raven
#

i didnt commit

#

it works

jade wing
golden moat
#

Hey! No idea if this is the right channel to ask. Basically I am attempting to setup some custom middleware that will allow my Django app to run even if my Azure Redis instance is down. Currently if anything is amiss (URL, port, etc.) rather than just falling back on not caching it errors the entire app. Any ideas? Thanks!

#

If this is totally the wrong spot to post this I’m sorry

fading patrol
golden moat
#

Oh it’s more of a dummy proofing situation I’m working on, (URL put in wrong by another dev) but basically if Redis is down then the django app itself errors out on a ConnectionError exception. I’m just trying to have it skip using Redis in the settings.py CACHE dict and use the dummy one (so it won’t cache at all)

clear stirrup
#

maybe creating a custom cache by inheriting from RedisCache and overriding some of the methods in it?

#

get, set, delete...

jade wing
violet token
#

So i made a library which basically acts as a perm number counter, now when i install this library from github and run it, instead of it using the database in the library it creates its own in the folder that im using the library in, how can i fix this

jade wing
violet token
#

aiosqlite

#

but same thing

jade wing
# violet token but same thing

basically, yes
you should be able to fix your problem by in your library doing something like:

import os
from pathlib import Path

def db_file(filename):
    return Path(os.path.dirname(os.path.abspath(__file__))) / filename
```and then call the function when you open the database like `sqlite3.connect(db_file('my_lib.db'))` or whatever your sqlite database file is named
#

with that code paths should also be handled independent of os

jade wing
# violet token yes correct

note that the code in the bigger code block should go within your library, not the script that you are calling your library from

violet token
#

here is my file tree

unknown.png
#

Code,

#

is there something that im doing wrong

unborn pilot
#

hey is anyone here?

hot trellis
#

just gonna leave this here :)

unknown.png
torn sphinx
#

Hello, how can i make an object inside of an object in TinyDB if anyone has an idea?

icy oyster
#

can you INSERT NULL in your database?

#

like if you have nothing to insert it will give an error

#

to avoid that you have to INSERT NULL right?

ruby breach
#

you should be able to do ```sql
INSERT INTO my_table DEFAULT VALUES

icy oyster
#

I've an SQL

#

but

ruby breach
#

there's more than one kind of sql db

icy oyster
#

I see...

#

what I mean is

ruby breach
#

postgres, mysql, sqlite

icy oyster
#

sqlite

#

in my case

ruby breach
icy oyster
#

but like

# 
cur.execute(f'''INSERT INTO genie VALUES ("{date}", "{user_id}", {trade_id}, "{pair}", "{timeframe}", "{side}", {order_price}, {tp_min}, "{tps}", {sl}, {rr}, "NULL")''')

I've this. do you see the NULL

#

that will be NULL text

#

or NULL

#

like nothing

ruby breach
#

just put null

icy oyster
#

cause I need it to be nothing

#

a blank space

ruby breach
#

actually

#

no just don't

icy oyster
#

cause if I dont give a value

#

it returns an error

ruby breach
#

yeah you need to specify the columns you want to insert

# 
INSERT INTO my_table (column_1, column_2) VALUES (value_1, value_2)
icy oyster
#

aaaaah

ruby breach
#

also

icy oyster
#

amazing

#

πŸ‘