#databases

1 messages · Page 194 of 1

fringe sundial
#

But not able to understand

#

what should be the arguments?

torn sphinx
#

I'd actually make a cache tbh to store prefixes

uneven sinew
#

yeah.. good luck teaching him how to use redis

fringe sundial
torn sphinx
torn sphinx
uneven sinew
torn sphinx
#

..?

uneven sinew
#

since it returns itself

fringe sundial
torn sphinx
#

I dont know

fringe sundial
#

Tf

#

Anyway I'll ask someone else

fringe sundial
uneven sinew
fringe sundial
torn sphinx
#

just looked at the source code

uneven sinew
#

you don't use the bot param though, is what he's asking afaik

fringe sundial
#

Is it correct?

torn sphinx
#

?

#

yeah?

fringe sundial
#

And i can select the prefix thing

#

And return prefix

#

Easy

#

Sqlite is very easy pesy

torn sphinx
fringe sundial
fringe sundial
torn sphinx
#

yes

#

you should create a custom cache, in the sense, a simple dict

#

which you should load with prefixes on startup

fringe sundial
#

I am using database

torn sphinx
# fringe sundial I am using database

you'll make a sql query every time a message is sent, instead selecting prefixes from a dictionary is better and if the prefix is not found in the dictionary, simply make a request ton your database

fringe sundial
torn sphinx
torn sphinx
fringe sundial
#

Idk how to

torn sphinx
fringe sundial
#

But there would be so many prefix to load

fringe sundial
torn sphinx
#

it's a startup thing

fringe sundial
torn sphinx
fringe sundial
#

I was thinking to use json.loads

torn sphinx
#

how would that work on a list of tuples?

#

pliz elaborate

fringe sundial
fringe sundial
torn sphinx
fringe sundial
#

It converts dictionary to json format

torn sphinx
#

.....

fringe sundial
#

Json.loads to load data(don't ask me difference between load and loads)

torn sphinx
fringe sundial
#

Anyway come to sql

torn sphinx
#

...

fringe sundial
torn sphinx
winged moth
torn sphinx
#

😭

fringe sundial
torn sphinx
#

pffttt

winged moth
#

what is the question?

torn sphinx
#

custom prefixes

#

with sqlite

fringe sundial
#

Me confused

torn sphinx
#

and I just said to make a custom cache, in the sense a simple dictionary that loads all the prefixes on startup to avoid making requests to the database every time

#

😔

fringe sundial
# 
import discord
from discord.ext import commands
import aiosqlite

bot = commands.Bot()

async def my_hook():
    bot.conn = await aiosqlite.connect("name.db")
    bot.cur = await bot.conn.cursor(){}
    await bot.cur.execute('CREATE TABLE IF NOT EXISTS pretable(prefix TEXT,guild_id INTEGER)')
    await bot.conn.commit()

bot.setup_hook = my_hook

Is it correct yet?

fringe sundial
#

Let me google with my small keyboard

torn sphinx
winged moth
# 
class Bot(commands.Bot):
    def __init__(...):
         prefixes = self.get_prefixes()
         ...
          super().__init__(..., command_prefix=prefixes)
    def get_prefixes(self):
        query to get prefix
torn sphinx
#

^

#

subclassing the Bot class is the only way of assigning a custom prefix

fringe sundial
torn sphinx
#

with sqlite

fringe sundial
#

Headache

torn sphinx
west hill
torn sphinx
#

it's still cleaner tbh

west hill
west hill
fringe sundial
torn sphinx
torn sphinx
fringe sundial
west hill
fringe sundial
#

What would I do if i got error huh

torn sphinx
west hill
#

eh nvm

torn sphinx
west hill
#

whatever you say u are helping anyway

torn sphinx
west hill
#

🚶

#

nah

torn sphinx
#

plz

#

😔

fringe sundial
torn sphinx
#

lmao

fringe sundial
#

Is this server a circus show

#

🎪

west hill
fringe sundial
fringe sundial
west hill
#

till someone in a good mood comes and helps you

fringe sundial
#

😂

#

You mean me in bad mood?

torn sphinx
fringe sundial
#

Why everything red here.

Screenshot_2022-06-28-14-11-29-75.jpg
torn sphinx
#

that's a really dumb question 😐 pass in self to the hook

fringe sundial
#

You mean bot setup hook?

torn sphinx
#

yes?

fringe sundial
# 
import discord
from discord.ext import commands
import aiosqlite

intents = discord.Intents.default()

class MyBot(commands.Bot):
  def __init__(self) -> None:
    super().__init__(command_prefix=self.get_prefix, intents=intents)
    self.prefixes = {}  

  async def setup_hook() -> None:
      self.conn = await aiosqlite.connect("name.db")
      self.cur = await bot.conn.cursor()
      await self.cur.execute('CREATE TABLE IF NOT EXISTS pretable(prefix TEXT, guild_id INTEGER)')
      await self.conn.commit()
      all_prefixes = await (await self.cur.execute("SELECT * FROM pretable")).fetchall()
      [self.prefixes.update({record[1]: record[0]}) for record in all_prefixes]
            


  def get_prefix(self, bot, message) ->  None:
    return self.prefixes.get(message.guild.id) or await (await self.cur.execute("SELECT prefix FROM pretable WHERE guild_id = ?"), (message.guild.id)).fetchone() or ">"

bot = MyBot
bot.set_hook = setup_hook

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

Like this?

west hill
#

rip

torn sphinx
#

...

fringe sundial
#

Rip me

torn sphinx
#

I'm really gonna cry, I'm out for now

west hill
# 
import discord
from discord.ext import commands
import aiosqlite

intents = discord.Intents.default()

class MyBot(commands.Bot):
  def __init__(self) -> None:
    super().__init__(command_prefix=self.get_prefix, intents=intents)
    self.prefixes = {}  

  async def setup_hook(self) -> None:
      self.conn = await aiosqlite.connect("name.db")
      self.cur = await bot.conn.cursor()
      await self.cur.execute('CREATE TABLE IF NOT EXISTS pretable(prefix TEXT, guild_id INTEGER)')
      await self.conn.commit()
      all_prefixes = await (await self.cur.execute("SELECT * FROM pretable")).fetchall()
      [self.prefixes.update({record[1]: record[0]}) for record in all_prefixes]
            


  def get_prefix(self, bot, message) ->  None:
    return self.prefixes.get(message.guild.id) or await (await self.cur.execute("SELECT prefix FROM pretable WHERE guild_id = ?"), (message.guild.id)).fetchone() or ">"

bot = MyBot()


@bot.event
async def on_ready():
    print("hello")
fringe sundial
#

Isn't it setup_hook?

#

The bot.setup_hook

torn sphinx
west hill
#

i just added the self over there no way i am reading all that

fringe sundial
torn sphinx
#

remove that line

#

and at least call the class

fringe sundial
#

Which line

torn sphinx
#

set_hook

west hill
#

bot = MyBot()
bot.setup_hook =

torn sphinx
#

nooooooooooooooooooooooooooooooo

#

the setup_hook is already in the subclass

west hill
#

i dont think u need setup_hook

fringe sundial
#

Oh u understand

west hill
#

yeah

fringe sundial
#

I understood

torn sphinx
west hill
#

just MyBot() is enough

fringe sundial
#

bot.set_hook = MyBot.setuo_hook

#

It it correct?

torn sphinx
#

I'm on the verge to cry

west hill
#

remove that

west hill
delicate fieldBOT
#

Hey @fringe sundial! I noticed you posted a seemingly valid Discord API token in your message and have removed your message. This means that your token has been compromised. Please change your token immediately at: https://discordapp.com/developers/applications/me

Feel free to re-post it with the token removed. If you believe this was a mistake, please let us know!

fringe sundial
#

A

#

Ah

west hill
fringe sundial
# 
import discord
from discord.ext import commands
import aiosqlite

intents = discord.Intents.default()

class MyBot(commands.Bot):
  def __init__(self) -> None:
    super().__init__(command_prefix=self.get_prefix, intents=intents)
    self.prefixes = {}  

  async def setup_hook(self) -> None:
      self.conn = await aiosqlite.connect("name.db")
      self.cur = await bot.conn.cursor()
      await self.cur.execute('CREATE TABLE IF NOT EXISTS pretable(prefix TEXT, guild_id INTEGER)')
      await self.conn.commit()
      all_prefixes = await (await self.cur.execute("SELECT * FROM pretable")).fetchall()
      [self.prefixes.update({record[1]: record[0]}) for record in all_prefixes]
            


  def get_prefix(self, bot, message) ->  None:
    return self.prefixes.get(message.guild.id) or await (await self.cur.execute("SELECT prefix FROM pretable WHERE guild_id = ?"), (message.guild.id)).fetchone() or ">"

bot = MyBot

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

Now?

torn sphinx
#

call the MyBot class

fringe sundial
#

Ok

west hill
#

f bro guy didnt even scroll and see

fringe sundial
#

Done

torn sphinx
west hill
#

lovely

#

lmao

lean olive
torn sphinx
#

fr

fringe sundial
# 
bot = MyBot()
#

Now?

torn sphinx
west hill
fringe sundial
#

Ye

west hill
#

🥲

fringe sundial
#

Should I test code now?

torn sphinx
fringe sundial
west hill
#

for a sec i thought the bug was real

fringe sundial
fringe sundial
#

Fli

torn sphinx
#

zig and sharko crab?

#

nvm

fringe sundial
#

My battery is 13%

west hill
#

async def

west hill
fringe sundial
#

Finnaly code ran

#

Let me test

#

Errors!

torn sphinx
fringe sundial
fringe sundial
torn sphinx
#

dunno worked for me

west hill
#

smh

fringe sundial
# 
import discord
from discord.ext import commands
import aiosqlite

intents = discord.Intents.default()

class MyBot(commands.Bot):
  def __init__(self) -> None:
    super().__init__(command_prefix=self.get_prefix, intents=intents)
    self.prefixes = {}  

  async def setup_hook(self) -> None:
      self.conn = await aiosqlite.connect("name.db")
      self.cur = await bot.conn.cursor()
      await self.cur.execute('CREATE TABLE IF NOT EXISTS pretable(prefix TEXT, guild_id INTEGER)')
      await self.conn.commit()
      all_prefixes = await (await self.cur.execute("SELECT * FROM pretable")).fetchall()
      [self.prefixes.update({record[1]: record[0]}) for record in all_prefixes]
            


  async def get_prefix(self, bot, message) ->  None:
    return self.prefixes.get(message.guild.id) or await (await self.cur.execute("SELECT prefix FROM pretable WHERE guild_id = ?"), (message.guild.id)).fetchone() or ">"

bot = MyBot()

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

@bot.command()
async def test(ctx):
    await ctx.send("eat poop")

Here is code

west hill
#

self.get_prefix(message) <-- message object from the guild here

torn sphinx
#

no

fringe sundial
#

Jmmmm

#

Me should listen music

torn sphinx
#

how would you access the Message object

#

¯_(ツ)_/¯

fringe sundial
#

Sad song ig

west hill
#

u need the guild id there somehow

torn sphinx
fringe sundial
#

Imma listen the last ride song

#

And do coding

#

SQL confusing

#

Ig pymongo more easy?

torn sphinx
#

mongo ew

fringe sundial
#

🥭

#

Mango

west hill
#

sql is basically english

fringe sundial
#

Mongo?

torn sphinx
#

mongo sucks

west hill
#

its as simple as it gets

fringe sundial
#

But very less tutorials on SQL in Hindi

torn sphinx
#

english-

west hill
#

(self, bot, message) instead try with (self, message)

torn sphinx
#

ew

#

but that could work

west hill
torn sphinx
#

Asher go to sleep

fringe sundial
#

Do you even know Hindi language 😂

west hill
west hill
fringe sundial
#

Really?

west hill
#

yes

fringe sundial
#

I want codewithharry to make tutorial on sql

torn sphinx
#

literally half the server is

fringe sundial
#

I never thought

#

I thought you from England

torn sphinx
#

I am

west hill
#

i wish

fringe sundial
#

I want to go to Australia

torn sphinx
#

nice

#

nice

fringe sundial
#

And become a doctor

torn sphinx
#

ok bye, have a safe trip

fringe sundial
#

And make my own hospital website

torn sphinx
#

ok good luck

fringe sundial
#

So learning coding

#

Me 13 yet bro

west hill
#

are u still getting error?

fringe sundial
#

Let me grow till 18

torn sphinx
fringe sundial
torn sphinx
#

😭

torn sphinx
west hill
torn sphinx
#

idk

fringe sundial
#

@west hill have you listen the news that sidhu moose wala died before 1 month

#

Me from his state

west hill
west hill
torn sphinx
#

ahem

#

!ot

delicate fieldBOT
fringe sundial
fringe sundial
west hill
#

quite far away from u

torn sphinx
west hill
#

for safety karnataka

fringe sundial
#

Oh cool

#

Anyway

west hill
fringe sundial
#

SQL

west hill
#

back to topic

#

try self, message

#

in get_prefix

fringe sundial
#

9(

torn sphinx
#

you guys arent even on topic, this is a databases channel not discord bots

fringe sundial
#

Ok

west hill
fringe sundial
#

Tf ma

torn sphinx
fringe sundial
#

I did wrong indentation in get_orefix 😂

torn sphinx
#

dude

#

I'm already annoyed

west hill
#

but that will give u indentation error not missing argument

fringe sundial
#

Still error

west hill
#

show code

fringe sundial
# 
import discord
from discord.ext import commands
import aiosqlite

intents = discord.Intents.default()

class MyBot(commands.Bot):
  def __init__(self) -> None:
    super().__init__(command_prefix=self.get_prefix, intents=intents)
    self.prefixes = {}  

  async def setup_hook(self) -> None:
      self.conn = await aiosqlite.connect("name.db")
      self.cur = await bot.conn.cursor()
      await self.cur.execute('CREATE TABLE IF NOT EXISTS pretable(prefix TEXT, guild_id INTEGER)')
      await self.conn.commit()
      all_prefixes = await (await self.cur.execute("SELECT * FROM pretable")).fetchall()
      [self.prefixes.update({record[1]: record[0]}) for record in all_prefixes]
            


  async def get_prefix(self, bot, message) ->  None:
      return self.prefixes.get(message.guild.id) or await (await self.cur.execute("SELECT prefix FROM pretable WHERE guild_id = ?"), (message.guild.id)).fetchone() or ">"

bot = MyBot()

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

@bot.command()
async def test(ctx):
    await ctx.send("eat poop")
#

Imagine mods come here and mute us

torn sphinx
west hill
# 
async def get_prefix(self, message) ->  None:
      return self.prefixes.get(message.guild.id) or await (await self.cur.execute("SELECT prefix FROM pretable WHERE guild_id = ?"), (message.guild.id)).fetchone() or ">"
west hill
torn sphinx
#

nvm, I'm out

torn sphinx
west hill
#

let him try atleast ¯_(ツ)_/¯

torn sphinx
#

ok sire

fringe sundial
west hill
#

whats cur?

fringe sundial
#

😐

#

Your code tho

fringe sundial
fringe sundial
west hill
#

¯_(ツ)_/¯

#

try with this

fringe sundial
west hill
#

¯_(ツ)_/¯

#

idk then

fringe sundial
#

Tf bro

#

I am out

#

Me quit SQL

slender atlas
#

What's wrong

fringe sundial
west hill
# 
import discord
from discord.ext import commands
import aiosqlite

intents = discord.Intents.default()

class MyBot(commands.Bot):
  def __init__(self) -> None:
    super().__init__(command_prefix=self.get_prefix, intents=intents)
    self.prefixes = {}  

  async def setup_hook(self) -> None:
      self.conn = await aiosqlite.connect("name.db")
      self.cur = await bot.conn.cursor()
      await self.cur.execute('CREATE TABLE IF NOT EXISTS pretable(prefix TEXT, guild_id INTEGER)')
      await self.conn.commit()
      all_prefixes = await (await self.cur.execute("SELECT * FROM pretable")).fetchall()
      [self.prefixes.update({record[1]: record[0]}) for record in all_prefixes]
            
  async def on_ready(self) -> None:
        print("Bot is online!")
        await self.setup_hook()

  async def get_prefix(self, message) ->  None:
      return self.prefixes.get(message.guild.id) or await (await self.cur.execute("SELECT prefix FROM pretable WHERE guild_id = ?"), (message.guild.id)).fetchone() or ">"

bot = MyBot()

@bot.command()
async def test(ctx):
    await ctx.send("eat poop")
#

this should work i forgot to run the setup hook

#

@fringe sundial

fringe sundial
west hill
#

thats a sqlite error

# 
return self.prefixes.get(message.guild.id) or await (await self.cur.execute("SELECT prefix FROM pretable WHERE guild_id = ?", (message.guild.id))).fetchone() or ">"
#

now try

fringe sundial
west hill
#

i havent used aiosqlit b4 so idk

# 
await (await self.cur.execute("SELECT prefix FROM pretable WHERE guild_id = ?", (message.guild.id))).fetchone()
#

just remove this line and try

fringe sundial
west hill
#

no i meant remove that line

# 
return self.prefixes.get(message.guild.id) or  ">"
#

just try with this

#

i am not too sure with the aiosqlite part u would have to wait for others to help

fringe sundial
#

But that will not return prefix?

west hill
#

it will

#

just try it

fringe sundial
#

It's giving error again

west hill
#

what error same?

fringe sundial
west hill
fringe sundial
west hill
#

cant be

fringe sundial
#

Ye

west hill
#

then u didnt remove the sql line?

fringe sundial
#

Anyway leave it

fringe sundial
west hill
#

not a problem with that the sql line was giving an error

#

idk how u got same error after removing the line

#

can u send the repl link?

fringe sundial
west hill
#

alr

#

dm drop it

fringe sundial
#

My mom calling me

#

Sorry

west hill
#

when u are free

fringe sundial
#

Or we make our own custom prefix thing? Together pls @west hill

west hill
#

dm drop the repl link when u are free i will see what the error is then👀

fringe sundial
#

Ok

torn sphinx
#

@fringe sundial ^

west hill
west hill
dull coyote
#

Hello guys,
I have two tables.
Picker and owner, and they inherit from User.

They have fields in common (the same as user) and at the same time, they have they own fields.
Like in the image:

As long as I know, this is an Is-A relationship
My question is, can I implement this with table inheritance in sqlalchemy? (https://docs.sqlalchemy.org/en/14/orm/inheritance.html))

unknown.png
fringe sundial
fading river
#

is this common practice in databases? found this in the lab examples for a Google Cloud course I'm taking.

"""
Takes an email address and reverses it (to be used as primary key)
"""
def reverse_email(email):
    return '_'.join(list(reversed(email.replace('@','_').
                        replace('.','_').
                        split('_'))))
#

there's a lot of fairly cursed Python in here so I wouldn't be surprised if it's unusual

storm mauve
#

I think that reversing URIs is not extremely uncommon, but I wouldn't call it particularly common either
that said, my only reference I can remember for that is that Java has imports like ```java
import com.google.gson.JsonObject;
import net.minecraft.data.HashCache;

paper flower
dull coyote
prisma jolt
#

Guys, does anybody know how I can do this with sqlalchemy? For me to query a table in sqldeveloper, I need to prefix its owner from the all_tables table. But in python with sqlalchemy either with the owner or without it, it returns the table or view doesn't exist. Any tips ?

prisma jolt
#

I was able to fix it by setting the schema name

sly pivot
#

can anyone see my normalsd table tell if anything wrong i am not sure what key shouldbe pk or fk

hard trench
#

hey guys - really need help with SQLAlchemy events, so I have this:

@event.listens_for(Emails, 'after_update')
def delete_retrieved_email(mapper, connection, target):
    # If email row has been retrieved, it has been saved in the client's local storage. We now delete it off the server.
    if target.retrieved:
        connection.execute(delete(Emails).where(Emails.id == target.id))

When I test this in the python console (update an Emails instance) - it works. When I do the same in a pytest test, it doesn't work! Why could this be? I have been stuck on this all day it's so frustrating!

hoary crypt
#

<@&831776746206265384> scam alert

robust fjord
hazy mantle
#

Quick question I’m not sure where to ask this. But is it possible to import prices from Best Buy into a excel sheet based on the SKU or model number? Without manually doing it

dim cedar
#

Hello! I'm quite new at using SQLAlchemy and the orm, and I'm trying to query for entries in my class model's "title" column, but the query is going to be trying to match for a user's input on the song title. So I'm concerned that just filtering where title equals the user's input isn't going to be enough

#

I found the like and ilike methods but I can't seem to get these working with filter(), and i'm not even 100% sure if they accomplish what I want. How can I query data that is most similar to a given string?

#

typing this out, i figure i can use the spotify api to search for the song and then use its official title if i need to. it'd be best if i can try to find matches in the local database though =]

fallow jacinth
#

Our Organization has ERP, We do collect all the data from the ERP database SQL. Since ERP Scope is small we not capturing all the data in the ERP system. There is a requirement for me to capture the kilometers where drivers are going for delivery purposes. based on the kilometer we need to analyze whether the Petrol allowance is reasonable or not. So I planning to create a simple APK so I can install it on the deriver's phone so whenever they go for a delivery, they need to give the kilometers.

Kindly Suggest to me how I can make a Simple form APK. Is it possible to do it Python ?

broken gazelle
#

im guessing im getting this error because my message_id column is of VARCHAR datatype and im trying to update int values to it, so ermm is there a way i can tell postgress the 991670121632374894 is supposed to be a string value and not an int? 😓

unknown.png
#

i tried wrapping it with (")'s but then for some odd reason i get this error column "991670121632374894" does not exist even tho 991670121632374894 is supposed to be a value/row...not a column

unknown.png
grim vault
sly pivot
#

CREATE DATABASE IF NOT EXISTS WORK;

#

not wroking

broken gazelle
dull coyote
#

Hello guys, can I make multiple request to different tables in the same database and the same endpoint?

E.g: first I make a GET request to a table.
If this data fits my condition, then I make a POST request to another table and if not, POST request to another table.
And so on

All in the same endpoint

civic cargo
#

GET and POST are HTTP verbs, which is most likely not a protocol you use to communicate with your database

#

are you asking if it's ok to send multiple queries to your database while responding to a single GET/POST request in your app?

#

if so then yes, that is normal - generally you try to reduce the amount of db queries needed to respond to the request, but it is normal to make more than 1

red oasis
#

error coming from line 157 and 165... how could i possibly do this? what im trying to do is to get the usermodel for 2 different lists

unknown.png
#

like i need 2 separated lists

coral briar
#

Hey can someone please help me in #help-bagel, it has to do with Flask and a Chat Apps formatting issues.

dull coyote
hybrid quartz
#

I'm receiving this error and very confused.. ever so simple. I thought this SQL would be correct? Working with MySQL.

Reference:
row is a list holding values of each row in the table being iterated via a for loop. (row is displayed on image below)
row[2] is the mutetype (value in this instance is text and type is TEXT)
row[1] is the discordid (value in this instance is 935665250030719028 and type is BIGINT)

cursor.execute(f"DELETE FROM muted WHERE discordid ={row[1]} AND temp = {True} AND mutetype = {row[2]}")

cursor.execute(f"DELETE FROM muted WHERE discordid ={row[1]} AND temp = {True} AND mutetype = {row[2]}")
_mysql_connector.MySQLInterfaceError: Unknown column 'text' in 'where clause'

'text' is not the column? 'text' is the value which belongs in the column of 'mutetype'

[Attachment of row in the table below]

unknown.png
torn sphinx
#

Any idea how I can efficiently store up to 300k entries over multiple weeks and update about 1mil times (every entry around 3 times if not more)?
currently I'm using simple text files to store it
right now it will be doing about 1-20 database updates a minute but once i rewrite it in rust (at least thats the plan) I want to increase it to possibly hundreds
the entries contain all the same data and I've been thinking about using mongodb for it since its free/online and till now is the most stable online database i've used

somber sorrel
#

I have a database of 500k+ strings of usernames in a CSV and I would like to search for any one of them as fast as possible.

What is the fastest way to search for a value in a large database? Should I use Python, C, excel, gsheets, ctrl-f in the CSV immediately? Would sorting the list make the search any faster?

keen minnow
broken gazelle
#

how can i insert a python list of strings into a text[] column in postgres (i use asyncpg)?

somber sorrel
# keen minnow can you give more context around the search?

Yep, the question is "how do I search for a name here faster"

An example of the data in my csv
H4NAJ7C8W, Sterling Fortune 27, 7800000
HRNAJUCIW, General eligun 60, 650462

Before I was using a vlookup or an index match in gsheets and the time to find a name is approx 10 seconds depending on the connection.

There are more than 500k entries, the data is sorted by the third column from top to bottom and one more time, I am only going to search for a row through the name (second row)

I'm looking for anything that could do this search in 1-2 seconds.

#

I guess this might not be the right place to ask this question as everyone here is specialized in python

keen minnow
somber sorrel
#

I'm thinking something along the lines of if I were to sort the data by the second row

keen minnow
grim vault
# hybrid quartz I'm receiving this error and very confused.. ever so simple. I thought this SQL ...

Don't use f-strings.

cursor.execute("DELETE FROM muted WHERE discordid = %s AND temp = %s AND mutetype = %s", (row[1], True, row[2]))

You problem is that {row[2]} is a text (and in this case also with the value text) which must be in single quotes in the SQL but your use of f-string doesn't have them. If you use placeholders (like above) the database module uses the correct form for the values depending on their datatype.
You tried to execute the statement:

DELETE FROM muted WHERE discordid = 9356... AND temp = True AND mutetype = text

but you need:

DELETE FROM muted WHERE discordid = 9356... AND temp = True AND mutetype = 'text'
lone wolf
#

Hello everyone , so um I have 3 .sql files .... and I'm supposed to retrieve data from them using Python
Now, I know python , and I have been learning SQL too but have never worked on .sql files before
Can anyone guide me about where am I supposed to do this work
I've done python programming in jupyter notebook , Kaggle Notebooks , Google Collab before
and have done SQL coding in Microsoft SQL Server Management Studio

I'm confused about which tool I am supposed to use for the task mentioned above

hybrid quartz
unkempt prism
ebon skiff
# 
Traceback (most recent call last):
  ...
    dbr = await interaction.client.database.execute(""DO
  File "C:\Users\eboss\Documents\GitHub\dpy-dispy\venv\lib\site-packages\asyncpg\connection.py", line 320, in execute
    _, status, _ = await self._execute(
  File "C:\Users\eboss\Documents\GitHub\dpy-dispy\venv\lib\site-packages\asyncpg\connection.py", line 1659, in _execute
    result, _ = await self.__execute(
  File "C:\Users\eboss\Documents\GitHub\dpy-dispy\venv\lib\site-packages\asyncpg\connection.py", line 1684, in __execute
    return await self._do_execute(
  File "C:\Users\eboss\Documents\GitHub\dpy-dispy\venv\lib\site-packages\asyncpg\connection.py", line 1731, in _do_execute
    result = await executor(stmt, None)
  File "asyncpg\protocol\protocol.pyx", line 183, in bind_execute
  File "asyncpg\protocol\prepared_stmt.pyx", line 142, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
asyncpg.exceptions._base.InterfaceError: the server expects 0 arguments for this query, 2 were passed
HINT:  Check the query against the passed list of arguments.  Note that parameters are supported only in SELECT, INSERT, UPDATE, DELETE, and VALUES statements, and will *not* work in statements  like CREATE VIEW or DECLARE CURSOR.
# 
await interaction.client.database.execute("""DO
            $do$
                BEGIN
                    IF NOT EXISTS (SELECT * FROM fnine_lib WHERE user_id=$1 AND url=$2) THEN
                        INSERT INTO fnine_lib (user_id, url) VALUES ($1, $2);
                    END IF;
                END;
            $do$;""", interaction.user.id, r.url)
#

It's saying it expects 0 args pithink
postgresql + asyncpg

tranquil vessel
#

I just started learning and want to play around with Flask, I want to store some scapped data in a db before I make any further steps. I was thinking of using MongoDB, are there any other recommendations?

sturdy birch
#

i got this error

ERROR 1524 (HY000): Plugin 'root' is not loaded

while running

sudo mysql -u root -p```

 command plz help me solving this
paper flower
fading patrol
paper flower
rain osprey
#

Hello, I have a JSON file of a 2500 English words, and I wanted to learn how I can insert them into a database (I'm using Django, very new to this)

tranquil zinc
#

hi

#

I am using access with python

#

this is my code

# 
Traceback (most recent call last):
  File "C:\Users\user123\PycharmProjects\review-reader\use_db.py", line 8, in <module>
    cursor.execute("""
pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in DROP TABLE or DROP INDEX. (-3556) (SQLExecDirectW)')```
#

and incase if u guys ask me to comment out drop table

#

heres the new error

tranquil zinc
vapid hawk
#

what is the best database engine for me if i have:

An ubuntu vps,
and I'm storing discord bot data, economy/items/so on.

rn i have sqlite and it works nicely, but I'm planning on scaling up soon, so i will need something that can also scale up, and i want it to have good python asyncio support,

does anyone know which dbsystem is going to be best fit for this?

torn sphinx
#

hello all

FROM python:3.9.5

RUN apt-get update && apt-get install -y  postgresql-client-11

for above dockerfile, docker build works
But for

FROM python:3.9.6

RUN apt-get update && apt-get install -y  postgresql-client-11

docker build throws the following error.

#5 2.088 Get:6 http://deb.debian.org/debian bullseye-updates/main amd64 Packages [2592 B]
#5 3.086 Fetched 8545 kB in 3s (3341 kB/s)
#5 3.086 Reading package lists...
#5 3.605 Reading package lists...
#5 4.113 Building dependency tree...
#5 4.251 Reading state information...
#5 4.356 E: Unable to locate package postgresql-client-11

Unable to locate pkg. Any idea?

fading patrol
# rain osprey Hello, I have a JSON file of a 2500 English words, and I wanted to learn how I c...

the details may depend on your models and stuff but maybe this will help: https://www.laurencegellert.com/2018/09/django-tricks-for-processing-and-storing-json/

Laurence Gellert's Blog | Re: Software Is My Life, So Make It A Good One
Django Tricks for Processing and Storing JSON | Laurence Gellert's ...

In this post I'll show a few tricks I use to make JSON fit into Django more seamlessly. The first is a lesson on coding that everybody should know. Parsing external JSON: Whenever you take in JSON from "strange computers" (which is basically any computer) it works most of the time. As C3p0 said: R2D2,

fading patrol
torn sphinx
#

just had a thought

#

can you use py dictionaries as a database

fading patrol
torn sphinx
# 
class userData(db.Model):
    DiscordID = db.Column(db.Integer, primary_key=True, nullable=False, unique=True)
    Web_ID = db.Column(db.String, nullable=False, unique=True)
    WebRank = db.Column(db.String(255), nullable=False)
    Points = db.Column(db.Integer, nullable=False)
    userPost = db.relationship("userPosts", backref="author", lazy=True)
class userPosts(db.Model):
    postid = db.Column(db.Integer, primary_key =True)
    postedWhen = db.Column(db.DateTime, nullable=False, default=datetime.datetime)
    postTitle = db.Column(db.String, nullable=False)
    postContent = db.Column(db.String, nullable=False)
    dID = db.Column(db.Integer, db.ForeignKey('userData.DiscordID'), nullable=False)
db.create_all()

Trying to create these tables gives me an error

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'user_posts.dID' could not find table 'userData' with which to generate a foreign key to target column 'DiscordID'

vocal fern
#

hey, when im executing a command in python, how can i include the column names in the shell

vapid hawk
torn pilot
# 
...
  else:
    username = input("Enter your username: ")
    password = input("Enter your password: ")
    
    c.execute("SELECT 2 FROM fork WHERE username = ? AND password = ? LIMIT 2;", (username, password,))
    if c.fetchone():
      play(username, ':-')
    else:
      print("Incorrect password or username")

New to sql. This is just a simple test with sqlite but how would I go about setting a variable in python with the prefix attribute in the database for that specific username without explicitly doing it the way shown above.

Where prefix in play() is explicitly put in as :-.

pure cypress
torn pilot
# 
def c_table(name):
  c.execute(f"""CREATE TABLE {tableName} (
            server_id INT,
            username VARCHAR(20),
            password VARCHAR(20),
            cmds INT,
            prefix VARCHAR(3),
            language VARCHAR(15),
            PRIMARY KEY(username)
           );
            """)
#

I updated the prefix by calling this function

def update_prefix(usr_name, prefix):
  with conn:
    c.execute(f"UPDATE fork SET prefix = ? WHERE username = ?;", (prefix, usr_name,))
torn pilot
pure cypress
# 
c.execute("select prefix from fork where username = ? and password = ?", (username, password))
prefix = c.fetchone()

if prefix is not None:
  play(username, prefix[0])

Does this work for you?

torn pilot
#

o lemme test it out

#

ayyy nice

#

thanks that works perfectly

pure cypress
#

You're welcome

frank summit
#

I am undergoing a class assessment which includes converting a relational database to dimensional model. As you know, dimensional model contains many tables. So, I need to find some public database with multiple tables. So far, I couldn't find one online. Help needed. Thanks

whole gate
torn pilot
#

hm I'll keep that in mind then

torn sphinx
#

can someone please explain what is wrong with this psql query

select 'some prefix'|| round(cast (float8(
    select SUM(transaction.amount) from transaction where type='deposit' and content_type_id='9'
) - (
    select SUM(transaction.amount) from transaction where type='withdraw'
)as numeric), 2)
rain osprey
torn sphinx
#

I been trying to sketch out why designing a curriculum for databases is hard and why it’s hard to learn it

#

So just by using select queries as an example, and listing out a couple of posible options in each clause we can see already we can form at least 96 possible types of select queries, unlike normal programming where I can devise a clean curriculum, I can not do that easily with sql

keen minnow
ocean grotto
#

Hello everybody. I'm trying to create a table "Genre" to which the objects of the table "Movie" belong, and I wanted one movie to have several genres, and those several genres to be contained in one column (like a list of genres), but this type of column does not exist in SQL, maybe I don't know something

crisp spade
#

is there any way to install mongodb on mac without brew?

grim vault
# 
Table Movie
  movie_id PK
  ...

Table Genre
  genre_id PK
  ...

Table Movie_Genre
  movie_id FK
  genre_id FK
  PK(movie_id, genre_id)
sly pivot
#

how to do i get multiple output out of my sql enquiry

#

SELECT supervisor_name ,
supervisor_id FROM branch_info WHERE branch_code =(SELECT branch_code FROM linker_table WHERE car_plate_nr =(SELECT car_plate_nr FROM car_detail WHERE car_type ="SUV"))

#

getting only one row of output

#

but there are more output

stone ocean
#

in mongodb how would i add another name in the characters object?

unknown.png
#

also how would i update the value if said name by +1

obtuse vortex
#

i am learning SQLAlchemy ORM to use with my flask application and was reading the documentation, but i did not find anything in the docs that teaches about Migrating the Models i have written into database tables. Is Migration something Flask is responsible for or SQLAlchemy ?

bleak needle
#

what would be the most optimal way to import a JSON file to a MongoDB database?

#

I've seen people doing it with MongoDB Compass, Atlas and a few more ways that I can't remember now, so is there any "best/fastest" option really? If so, which one?

warm yacht
#

is is matter if a column in a table gets a NULL or NOT NULL if we changed the default value in SQL?

native lodge
#

Hey there!
I don't know if that's the right place to ask for that but are files without any extension used as database files?
for example all the Login Data from Chrome is stored in "Login Data" (no extension)

pure cypress
#

They can be, but not necessarily.

#

Extensions are just part of the name. They don't affect the data. Like, I could take a text file and rename it to a .png extension and it will still contain the same data as it did when it was a .txt file.

unkempt prism
torn sphinx
#

I already learned it

#

But the way I learned it I had to keep going back and re reading

#

With programming you can roughly describe the knowledge obtained like a pyramid

#

But sql is different, it’s more like a network of facts

#

You have to put together

#

So somebody can’t learn the stuff 100% in a lecture

torn sphinx
#

One needs to learn relational algebra to help mental process what’s going on first

#

Which is what I did

keen minnow
# torn sphinx I’m a tutor bro, I’m saying there is no probably no efficient way of teaching it

That's great to be a tutor! But we can have a more interesting discussion if we don't rely on argument of authority. Let them stand on their own.

Learning the deeper side of SQL is tricky and probably not the most important topic because it varies from dialect to dialect and may miss on some more important topics like how to model the data, the purpose of ACID, the different types of DB, etc.

torn sphinx
#

And the one learns SQL

#

By breaking down tables and the possible quieres

#

But teaching somebody how to use those queries is not formulaic at all

torn sphinx
#

I’m not making an arguments other than the one right now

torn sphinx
#

Your free to think what you wish, but it’s not helpful to the people who are learning here

keen minnow
torn sphinx
keen minnow
#

The fact that you can combine the various parts of a SQL dialect in many possibilities does not render learning SQL impossible

torn sphinx
#

You can sometimes treat knowledge like math

keen minnow
torn sphinx
#

And form curriculums easily

#

There is a formulaic sets of steps to learn a given topic

#

Sql isn’t like that

#

And I illustrated that by sketching the number of possible select queries

#

At best I’m making an inductive argument

#

So your free to judge it as weak

keen minnow
#

if you try to learn SQL by all the possible combinations of queries, that would still be the wrong way to go about it.

torn sphinx
#

And can break down all the stuff step by step

#

I know exactly what problems to give etc

torn sphinx
keen minnow
torn sphinx
#

I know because I’m literally reading a book right now

#

And even with all the facts provided there is many different ways to organize the facts

#

But it doesn’t make sql easier to understand for other people

#

Like the prerequisite to learn sql is literall “be good at math”

#

That’s how I can describe it

#

And why I can do it

#

Because I’ve reasoned about many different things

#

It’s easy for me to know what to do next

keen minnow
torn sphinx
#

I’m not saying sql is easy for me, I’m just saying I know how to learn it

#

But I can’t teach that learning process

torn sphinx
torn sphinx
keen minnow
torn sphinx
keen minnow
#

If you can't have a normal discussion, I am not interested

torn sphinx
#

It’s my opinion;of you

lunar pier
#

Good day! I have little question about database structure.
For now, i have sqlite3 database to store 25 different storage sites and items on this sites. Current database designed in one table, using columns like: serial, name, quantity, site1, site2 .. site25. Site columns are integers with just a number of item on them.
Now i want to remake all this and make structure like:
table1: serial FK, name, quantity
table2: site_name FK
table3: serial FK, site_name FK, quantity_on_site
I have around 2k items(rows) and 25 sites. Will this be the right structure for database?

torn sphinx
#

ok i created a way to make it optimized

#

an optimized way to learn fundamentals of sql

bronze jetty
#

Is there a way to shift sqlite3 data to postgressql using Python easily?

meager atlas
#

hi

Hey, I'm trying to domarket_hash_name = "AK-47 | Phantom Disruptor (Factory New)" cursor.execute(f"""Update Set where market_hash_name = 'market_hash_name'""")

It just say near "Disruptor": syntax error

#

what can I do

#

guys should I run con.commit() every time after the every process, or should I run after all process done?

meager atlas
meager atlas
bronze jetty
#

Also the query is meant to be:

UPDATE <tablename> SET <column> = "whatever" WHERE <where stuff>

#

Example:

cursor.execute("UPDATE test SET market_hash_name = :market_hash where market_hash_name = 'something'", {"market_hash_name": market_hash)
bronze jetty
minor citrus
#

the code when i have the error :

unknown.png
#

Hey i have an error with my discord bot command to check an column in table.

The error when i use the command :

# 
Ignoring exception in command get:
Traceback (most recent call last):
  File "C:\Users\nstyb\AppData\Local\Programs\Python\Python310\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "D:\WORK\py\discordbot\main.py", line 27, in get
    rows = cursor.fetchAll()
AttributeError: 'MySQLCursorDict' object has no attribute 'fetchAll'

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

Traceback (most recent call last):
  File "C:\Users\nstyb\AppData\Local\Programs\Python\Python310\lib\site-packages\discord\ext\commands\bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\nstyb\AppData\Local\Programs\Python\Python310\lib\site-packages\discord\ext\commands\core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\nstyb\AppData\Local\Programs\Python\Python310\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: 'MySQLCursorDict' object has no attribute 'fetchAll'
#

Code :

@bot.command()
async def get(ctx):
    cursor.execute(f"SELECT hour from players_laders where discord = {ctx.author.id}")
    rows = cursor.fetchAll()

error in line : rows = cursor.fetchAll()

#

thanks for your help

grim vault
#

And please don't use f-strings.

# 
@bot.command()
async def get(ctx):
    cursor.execute("SELECT hour from players_laders where discord = %s", (ctx.author.id,))
    rows = cursor.fetchall()
sly pivot
#

anyone good at sql query

minor citrus
wicked flax
#

I have this little project I’m working on, dealing with multiple nested dictionaries. As a beginner, It struck me that in a way nested dictionaries are similar to tables and the relationships I develop between them, and the resulting dataframes are like queries. It got me to wondering if maybe what I’m doing would be more properly set up as an actual database rather than multiple dictionaries that are created on the fly.

#

My next thought was, and I haven’t researched this yet, but what kind of database capabilities does python have? Can it create its own tables, stored externally to the script? Or would I only be able to use some kind of 3rd party software to hold the data?

keen minnow
#

One of the foundations of Computer Science is the ability to process data, and that include being able to store and retrieve it. There are many many many many ways to go about it as there are many many many many different use cases, each one with a different set of trade offs

wicked flax
#

So in my particular case, my thought process is overkill. I'm dealing with relatively small datasets, probably no more than 1000 records. The most time consuming part is the "foundational data" is obtained from a 3rd party software. Once that's done, the processing can start. The results of processing would then be imported back into the software, or perhaps an intermediate visualization step to see the results of the processing.

#

I just like trying to do things "the right way" for personal reasons. That and I love databases.

wicked flax
keen minnow
keen minnow
wicked flax
# keen minnow It seems overkill for your case, but it's common in these cases to have a pipeli...

At the moment that's what I've done with the one "expensive" dictionary. I saved that as a text file and then included a boolean to indicate whether the script should go talk to the software, or go use a local file.

Come to think of it, at the moment, if the local file just had the same name as the model (3rd party) file, that could help a bit with the determination of whether or not to process.

But, at the moment I'm more skilled with SQL than I am with pandas so querying and linking dictionaries to produce new dictionaries is a bit cumbersome for me. But I digress.

I need to go google what's out there for python. I think wherever this script is used will have MS Access available so there's that, but I think things would be faster if I didn't have to go through another piece of software and just used local plain old text files, somehow.

keen minnow
wicked flax
keen minnow
obtuse vortex
hallow rover
#

is there an efficient way of selecting every column but excluding a specific one in postgresql?

tepid walrus
#

Well my best bet would be to make a temp table and then drop that column from the temp table

minor plover
#

I have a postgresql table that i'm trying to open in pandas with 23m rows, but, when I try to do so, it ends up running out of memory. However, I tried saving the table as a CSV file and opening the file in pandas and it worked perfectly, but what I'm noticing is that the CSV file is losing "metadata" of sorts in the table. In the table, I have a few JSON and array columns, but those get turned into strings and other undefined behavior when I import it into pandas from the CSV. What could I do to mitigate this? Is there another file format I could use?

fading patrol
minor plover
fading patrol
minor plover
fading patrol
minor plover
fading patrol
#

And you can do it in offset batches?

minor plover
#

i never thought about that

#

i must need some sleep

craggy ether
# keen minnow if you try to learn SQL by all the possible combinations of queries, that would ...

Just want to say i agree with this 100%....Why would you ever want to learn through every possible combination when the efficient way is to go through the parts of a query, what they do, and what to expect in the underlying tables. Also a teacher for SQL bootcamps and the number one thing people become confused on is when you throw 100 detail specific examples at them at once. Give them the basic foundation and a way to advance in knowledge and it takes care of itself.

#

In keeping with efficiency, does anyone know an efficient way to load 100k-800k rows in xlsx/csv workbooks into Teradata via python. Just curious on the most efficient module/method as there are a lot of options and idk whether to run a chunking process or insert rows in a loop

fading patrol
broken gazelle
#

erm, how can i check if an element {"ABC"} belongs to an array column (text[]) of a table named XYZ? i am using postgresql

minor plover
#

works most of the way, only thing is that each line is json formatted, but the file as a whole isn't

#

you need to add commas and [] to make it a valid json

#

but that's easy to automate

unkempt prism
unkempt prism
haughty tree
#

please help

So I made a discord bot, it works with sqlite3, uploaded it to heroku. When I download the repository, the database file remains unchanged, although I made changes in the bot. I made the bot output the database values - and they were changed, but when I downloaded them , there were no changes. How do I download the sqlite3 database file from heroku?

fading patrol
#

First, you don't want to keep your DB in your repo, that should be ignored

#

But I've never used sqlite3 on Heroku so I'm not sure if or where the file would be accessible for download.

little tendon
# 
if choose == "2":
    username1 = input("Username: ")
    password1 = input("Password: ")
    with open(username1, 'w') as f:
        f.write(password1)
        time.sleep(2)
        folder = mega.find('DATA')
        mega.upload(username1, folder[0])```
#

im using mega as a database and everytime the files uploads there's no text in the file while there's the text in the file in my pc

broken gazelle
#

hi! is there a way to check if a postgres array 'x' has an element 'y'?

knotty valley
#

hello. Is there an API that lets define tables like SQLModel? I'm asking this since SQLModel has many features that I don't want to use in my project. The only purpose that I consider useful is the creation of a db using table models which is more user friendly. Regarding data manipulation, I pretend to use asyncpg.

stray moss
#

postgres returns [0, 2, 133, 133, 51, 210, 141, 199] for a TIMESTAMP field, how would I decode it?

#

(ping on response)

narrow saffron
#

Hey guys, how do i alter a field in transact sql to be auto increment?

proven gyro
#

ohh wait u want the other way around

#

but it should still work imo

#

and can u show your select statement ? why is timestamp a list ?

stone ocean
#

in mongodb can i make characters something like

characters:
  "Michael":
    6
    4
    3
    7
  "Seraph":
    7
    2
    6
    7```
#

what i have right now

#

but i still want to retain the ability to index characters

orchid coral
#

Heya, I'm looking for some help with a Postgres query. I have a table that looks something like

id | title | alias_of
1  | 'foo' | 'foo'
1  | 'bar' | 'foo'
1  | 'bat' | 'foo'
2  | 'x'   | 'x'
2  | 'y'   | 'x'
...
```I'm trying to get, for a given input, the title with the minimum distance from that input (as calculated by pg_trgm's `<->` operator), for each group of 'alias_of'. The closest I've gotten myself is with
```postgres
SELECT DISTINCT ON (alias_of, min_dist)
*,
MIN(title <-> 'input here') OVER (PARTITION BY alias_of) AS min_dist
FROM tablename
ORDER BY min_dist, alias_of ASC;
```but it feels intuitively wrong to have both `alias_of` and `min_dist` as distinct parameters. I am quite unsure as to how I can make this work, so any help would be much appreciated ^^
orchid coral
#

I seem to have found a working solution with

WITH ranking AS
(
  SELECT *,
  title <-> 'input here' as dist,
  RANK() OVER (PARTITION BY alias_of ORDER BY title <-> 'input here' ASC) AS r
  FROM tablename
)
SELECT *
FROM ranking
WHERE r=1
ORDER BY dist ASC;
```though it seems rather roundabout...
maiden solar
#

hi I was wondering if someone could help me with my Peewee ORM's models and functions - I am writing a search application and I'm just having trouble to write it correctly. I keep getting mysterious error messages. I can share a whole "test file" with the relevant classes and functions or some excerpts where I think the problem might be but don't know how to fix it

delicate fieldBOT
maiden solar
#

yup

#

So this is the code. Currently when I try to run it it gives me the error apsw.SQLError: SQLError: sub-select returns 5 columns - expected 1
and I'm honestly confused about which part the code this even is, because it only gets thrown when I iterate over the results since everything is returned as iterable. Even debugging this whole thing seems a little hard to me.

#

welp maybe I'll go over to one of the help channels

dull coyote
#

Hello everyone, how do I make múltiple database queries in the same endpoint with FastAPI?
Async/await? Middleware? Normal way?

For example this is a POST endpoint:

  1. Query to DB if whatever id is correct.
  2. If its correct get xyz data from DB.
  3. If everything is OK and we have all the necesary parameters, finally add registry to DB.

Note that everything is in the same endpoint.
In this example we have 3 DB operations.

dull coyote
#

Thanks, I used to put only one query per endpoint.
I cannot test my multiple queries in one endpoint because I need the especifications of my project.
But I think in the normal way it may work :

obj = db.query().filter()
if obj != None:
   xyz_data = db.query().filter().all()
if everything is OK:
    db.create()
devout yacht
#

Hi everyone, i need a help with cursor, in python

#

how to make a cursor remember for every select query, lets take first time we have selected 100 rows
0-100
secondtime if i run query it should fetch from
101-200 rows

#

can it be possible?

fading patrol
devout yacht
#

postgresql

#

@fading patrol

fading patrol
devout yacht
#

ohh okay

#

i think we would have offset functionality in python as well

verbal jetty
#

hey guys. I need help. I'm using sqlite3 and I want to delete a table... can anyone me out with this ?

#

I want to delete a table from the database file

#

how can I do that ?

#

no no not table

#

I want to delete a row

#

really sorry

#

I want to delete a row from a table

#

I tried -> 'DELETE FROME <table_name> WHERE UID=<uid>'

#

yes I know

verbal jetty
devout yacht
#

@fading patrol @slow cove there is no equivalent function in python like offset or limit

verbal jetty
#

okay the columns are UID, Email, Passwords

#

just some general columns

#

a basic table

#

Anyone ??

#

please help me

fading patrol
#

If that doesn't solve your problem, show some code

devout yacht
#

no no i didnt mean that i tried searching i am sorry @fading patrol

median dome
#

hey, is it possible to split the models of sqlmodels in multiple files? rn im getting circular imports when trying it

fringe sundial
#

Guys can anyone help me making a custom prefix for my discord bot pls

fringe sundial
#

@winged moth

# 
async def setup_hook():
    connect = await aiosqlite.connect('prefixes.db')
    cursor = await connect.cursor()
#

I wrote this

#

Is it fine?

winged moth
#

make sure to close the connection before shutting the bot

stray moss
#

It’s a [u8] array

#

unsigned 8 byte integer array

#

How do I decode it to a time stamp I can understand

proven gyro
#

postges or something else?

fringe sundial
frozen python
stray moss
#

it's a [u8, 8] kind of array

#

8 members, all are type of unsigned integer 8 bits

proven gyro
#

how is the table initlized

stray moss
#

it's TIMESTAMP

#

DEFAULT current_timestamp

#

I'm trying to decode the byte array it's sending me

#

it looks like this: [0, 2, 133, 133, 51, 210, 141, 199]
and it's supposed to be this: 2022-06-28T18:45:00.002759

#

currently I'm trying to get it to unix timestamp

topaz glen
#

hey so i want to store user media information right, is it wise to provide a user one folder with a related parent folder for thier data storage?

root
|- user 1
|- user 2
|- user 3

etc
I wanted to user buckets but i suppose thats a bit out of my reach but i wanted to replicate somthing of that case. the folders and thier relationship with the user will be stored in a "indexing" database entirely and querying will be available through an api interface. Any thoughts?

stray moss
#

there are nothing in postgres's documentation saying how the bytes work

proven gyro
stray moss
#

thanks man

proven gyro
#

and this good ?

postgres=# create table garbage (
col1 timestamp );
stray moss
#

I figured it out

#

If I convert this [0, 2, 133, 133, 51, 210, 141, 199] to u64 with big endian

#

it will result in a timestamp

#

giving me how many microseconds it has passed since 2000 Jan 1st

proven gyro
#

why though ?

#

i can access it just fine

stray moss
#

?

#

I'm reading the bytes

#

because I don't know the type at runtime

amber mountain
#

Any idea how to do this properly? (Excuse me if wrong channel, just saw this related to DBs)

proven gyro
amber mountain
#

Oh sorry, forgot to delete that aswell. Already found an answer so I removed my wall of text

bold hawk
#

would anyone be able to help me out in trying to write parts of my notebook into a docx file, i need to somehow save dataframes and plots into one file

#

im trying to follow this

unknown.png
#

and it seems to process just a regular table from a df fine but doesnt work when I try to input dataframes that i've made with .agg()

craggy stag
#

I'm using pymongo but how exactly would you get the admin and local variable to not print when using list_database_names()?

#

I did that

#

I used a for loop and then a if loop inside saying if databases names in [admin, local] continue and then print

#

no

# 
for categories in database.list_database_names():
    if database.name ["admin", "local"]:
        continue
    print(categories)
#

still printed admin and local

#

'str' object has no attribute 'name' thats what it returns when using categories.names

#

That worked, tysm!!

maiden schooner
#

hay guys. what is the best database for a python bot (discord.py)

fading patrol
maiden schooner
fading patrol
maiden schooner
warped turtle
#

Is there an async database library?

#

or package?

#

(doesnt know what the imports are called)

fading patrol
fringe sundial
#

!pypi aiosqlite

delicate fieldBOT
fringe sundial
#

@winged moth

#

Can you help at sqlite

#

I am not able understand how can write the get prefix function

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

def get_prefix():
    pass

bot = commands.Bot(command_prefix='<', intents=discord.Intents.all())

async def connect():
    connect = await aiosqlite.connect('prefixes.db')
    cursor = await connect.cursor()
    await cursor.execute('CREATE TABLE IF NOT EXISTS prefixes(guild_id INTEGER,prefix TEXT)')
    await connect.commit()
    await connect.close()

@bot.event
async def on_ready():
    print("ready")
    await connect()

Here is my code

tiny eagle
#

I use a Dockerised Postgres db for my bot, and it often just raises asyncpg.exceptions.InvalidPasswordError: password authentication failed for user "postgres" randomly, after working normally for 3 days. I often end up needing to run docker volume prune -f and restart the container, and it'd go back to normal.
What could the cause of this be?

stone ocean
# 
characters:
  "Michael":
    6
    4
    3
    7
    "light"
  "Seraph":
    7
    2
    6
    7
    "dark"

in mongo db can i do something like this?

#

i want to be able to search for a name in characters and also be able to get some data from that character

cosmic summit
#

Hi. I have a simple cx_oracle question over in #🤡help-banana if anyone has a sec to help me out.

meager atlas
#

yo guys

lethal warren
#

how to fix that in java program sir

unknown.png
fair girder
#

what's the best solution when you want to select sum for total and for 30 first days in one select?

astral jacinth
#

hi, i dont know very well how to explain what i want to do, i'll give you an example and you tell me what is the correct way of asking help for this sql query
i have some table like this
id cod value
1 2 1.0
1 3 2.0
2 1 1.0
2 2 1.0
and i want an output like this, with the columns of the cod values above
id 1 2 3
1 0 1.0 2.0
2 1.0 1.0 0
how can i do this?

lost summit
#

Hello,

storm mauve
lost summit
#

Is there a way to merge rows so the output is like this? I use event sourcing and have 3 different events and i want to push them into the create ones
id name event reference_id object_reference_id object_id
1 test create 1 U00001 U00001
2 test2 correct 1 U00001 U00002

output
1 test2 create 1 U00001 U000001
so it merges updates into the create entry in database

astral jacinth
silent grotto
#

Can someone give me an idea to keep records of files with date.... I mean when I load file into memory, I want to put that file name and the loaded date and time

#

Not an online database.... A local one

restive turret
#

hello, when defining declarative tables with SQLAlchemy ORM, is there a built-in way of mapping each class attribute to a string?

#

I want to set up my tables in the modern declarative way, but also have a mapping of strings to the class attributes (column variables), so that I can programmatically refer to each attribute/column

#

I imagine I would have some kind of "imperative mapper" set up like this:

mapper_registry.map_imperatively(User, user_table, properties={
   'id': user_table.c.user_id,
   'name': user_table.c.user_name,
})
#

but I can't tell from the docs -- is it possible to have tables defined in the declarative way, and also have this mapping? also how would I even use this mapping?

I want the user to be able to use a string 'name' instead of a variable user_table.c.user_name when they add/insert data into that column

marsh kite
#

anyone know what the fastest database file type is that is able to be opened with a basic text editor?

zealous spire
marsh kite
zealous spire
marsh kite
zealous spire
delicate fieldBOT
#
csv

Source code: Lib/csv.py

The so-called CSV (Comma Separated Values) format is the most common import and export format for spreadsheets and databases. CSV format was used for many years prior to attempts to describe the format in a standardized way in RFC 4180. The lack of a well-defined standard means that subtle differences often exist in the data produced and consumed by different applications. These differences can make it annoying to process CSV files from multiple sources. Still, while the delimiters and quoting characters vary, the overall format is similar enough that it is possible to write a single module which can efficiently manipulate such data, hiding the details of reading and writing the data from the programmer.

flat pewter
#

Is it expected behavior in SQLAlchemy when I use a raw_connection on an engine that temp tables are still there when I con.close() and then open up a new connection? 🤔 I thought closing would already be enough to get rid of everything. It appears not? Do I need to also close and recreate the engine or did I go wrong somewhere? Thanks!

paper flower
stone ocean
#

hello in mongo db can i have something like

characters:
  "Earth Elemental": 7
  "Fire Elemental": 4
#

and then sort by the number for each character

keen minnow
stone ocean
#

how would i do that if characters is an array?

keen minnow
stone ocean
#

sorry, would you mind explaining what you mean?

keen minnow
stone ocean
keen minnow
paper flower
#

I don't use mongo but in this case it should be easy to sort by count/number

shadow fossil
#

hello, i am working with mysql 5.7 and i would like to insert data into my db. but i have an error probably cuz i dont write with the good syntax.could u please give an example of an "insert into values where" from Python please

#

because i found nothing on internet

#

this is my code :
command_best_time = ("INSERT INTO best_score "
"(Best_time) "
"VALUES (%(speedrun_total_time)s)"
"WHERE user_id = (%(id_user)s)")

            data_best_time = {'speedrun_total_time': total_time, 'id_user':file}
#

and it returns me that : SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (user_id = ('67967437944310345335035921252057299374076208405004'))' at line 1

snow drum
#

I just started backend developer, i learn database design but i only use ORM not native SQL. Do you think it is the suitable choice ?

fading patrol
past sundial
nimble monolith
#

I need to fetch few docs from s3 bucket whenever user id logged into their account. All user id are in dynamodb table. How to tag both ?

lament sphinx
#

anyone got recommendations for a database for distributed computing/3d rendering?

#

debating which one to choose

#

redis seems to tick a lot of boxes but seems more cumbersome to deploy on nodes than something like sqlite or sqlalchemy

torn sphinx
# 
CREATE TABLE `article_votes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `article` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `article_uid` (`article`,`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Can anyone tell me what this code means:

  UNIQUE KEY `article_uid` (`article`,`uid`)

Thanks

#

And what's the equivalent of that in Django?

keen minnow
keen minnow
pure cypress
torn sphinx
keen minnow
torn sphinx
#

Ok

keen minnow
torn sphinx
#

Right, in Django I could just set them as foreign keys

lilac bolt
#

hey guys, im a sorta beginner to SQLite and would like some help with the syntax

unknown.png
#

is there a way to get this value?

unknown.png
#

under shieldQuantity and row 1?

fading patrol
lilac bolt
#

OKOK

white salmon
#

is anyone there?

#

i just wanted to see that what happens if i delete the partition i use for linux ?

#

cuz i need space for my windows

keen minnow
#

Also that question would be more appropriate for #unix since it's unrelated to databases

white salmon
ashen mason
#

Hi there, I'm solving a delicate problem and gotta try to ask if someone has experience with this. I'm working in a company and previous developers messed up Django migrations and also everything is one single django app... I would love to refactor into multiple apps and to rework migrations from scratch, but I cannot lose data in production database. Anyone has any idea how to rework migrations and do not affect data in database?

unkempt prism
# ashen mason Hi there, I'm solving a delicate problem and gotta try to ask if someone has exp...

Its my understanding you can squash the migrations to make them easier to work with though apart from squashing you shouldn't be changing existing migrations. You will need to create migrations to go on the top.

and previous developers messed up Django migrations and also everything is one single django app.

I see this would just be a fresh migration after you've done the refactor that you want. Takes a bit of testing though with environments mirroring production though to make sure you've covered your bases. For something like this I'd take a backup of prod and restore it to another server so I can test and be sure.

ashen mason
# unkempt prism Its my understanding you can squash the migrations to make them easier to work w...

Thank you for the answer, well there is over 100models in a single app, that's why I want to split it... If you split it you divide migrations. It would Remove fields and recreate them in other migration tree. Also number of migrations should be 0192 but there is over 300migrations(Due to those duplicates. Dependencies don't add up. Few migrations deleted along the road because if you try to create new project, fields are missing if you fix all the issues in migration tree.) I'm kinda upset at the former devs due to this unsustainable code lol.

I've landed a question with a thought how to work this out on Stackoverflow, maybe someone who dealt with this will notice it. --> /questions/72920584/how-could-i-rework-django-migrations-without-affecting-database

The idea was to --fake zero --> delete migrations in project folder + in DB --> split models into separate apps --> makemigrations --> add flag: managed = False --> migrate

Already done a copy of DB and I'm preparing for an awful afternoon. 😀

knotty valley
#

hello. I'm using asyncpg. I pretend to create a database remotely, so first I connect to a db template (template1). Then, I send a create database (testdb) command. However, I need to connect to it in order to, for example, create tables, triggers... Do I need to close the first connection and then open a new one with the created database or is it possible to send (with the previous connection) a connect testdb and automatically connects to it?

unkempt prism
knotty valley
#

wow I didn't know that it doesn't allow to do that. thank you tourdownunder

unkempt prism
knotty valley
#

my initial idea was just opening a connection to do the whole "creation". but seems I can't ahahahaha. I'll adopt that solution and that's it

novel fjord
#

I am trying to make a moderation log for my bot and I am wondering on how I would auto_increment the case id per guild. For Example:
Guild 1 could have 39 Mod Cases and when a new mod log is entered I want the case id to be 40, not the amount of mod log entries there are for all the guilds in the database.

deep maple
#

Hi, do you know how many select where are possible per second with consumer hardware and postgresql database?

fading patrol
jade smelt
# 
pw = "my_pw"
username = "admin"
mongoClient = pymongo.MongoClient(f"mongodb://{username}:{pw}@ip:port/")

pymongo.errors.OperationFailure: Authentication failed., full error: {'ok': 0.0, 'errmsg': 'Authentication failed.', 'code': 18, 'codeName': 'AuthenticationFailed'}

Mongo-Shell (docker):

mongodb    | 2022-07-09T22:01:27.305+0000 I NETWORK  [listener] connection accepted from ipipip:1437 #7 (3 connections now open)
mongodb    | 2022-07-09T22:01:27.310+0000 I NETWORK  [conn7] received client metadata from ipipip:1437 conn7: { driver: { name: "PyMongo", version: "3.12.1" }, os: { type: "Windows", name: "Windows 10", architecture: "AMD64", version: "10.0.19041-SP0" }, platform: "CPython 3.8.5.final.0" }
mongodb    | 2022-07-09T22:01:27.384+0000 I ACCESS   [conn7] SASL SCRAM-SHA-256 authentication failed for admin on admin from client ipipip:1437 ; AuthenticationFailed: SCRAM authentication failed, storedKey mismatch
mongodb    | 2022-07-09T22:01:27.419+0000 I NETWORK  [conn7] end connection ipipip:1437 (2 connections now open)
mongodb    | 2022-07-09T22:01:28.778+0000 I NETWORK  [conn5] end connection ipipip:1409 (1 connection now open)

But when I login via a mongobrowser it's working fine
Anyone have any idea?

#

Please @ on response

chrome mango
#

ive got

                await cur.execute("INSERT INTO tickets (member_id, guild_id, opened, ticket_category, ticket_status) VALUES (%s, %s, %s, %s, 'online')", ctx.author.id, ctx.guild.id, get_now_time(), ticket_category.value)
                await cur.execute()

and its raising TypeError: Cursor.execute() takes from 2 to 3 positional arguments but 6 were given but im not sure why, as the statement looks the same as my other ones that are fine.

storm mauve
# chrome mango ive got ```py await cur.execute("INSERT INTO tickets (member_id,...

that was a few hours ago so probably solved by now, but either way:
you have to put the query parameters on a list or a tuple instead of just passing them as function parameters, so ```py
await cur.execute("INSERT INTO tickets (member_id, guild_id, opened, ticket_category, ticket_status) VALUES (%s, %s, %s, %s, 'online')", [ctx.author.id, ctx.guild.id, get_now_time(), ticket_category.value])

or

await cur.execute("INSERT INTO tickets (member_id, guild_id, opened, ticket_category, ticket_status) VALUES (%s, %s, %s, %s, 'online')", (ctx.author.id, ctx.guild.id, get_now_time(), ticket_category.value))

ember mural
#

my friend needs help building a website with node js does anyone mind helping

celest rapids
#

Can someone tell me which website to learn for SQL interviews

#

Practicing some Google and AWS questions can help me

#

Any ideas?

austere coyote
#

Do I need any python libraries for handling a database on my laptop? If there are can someone tell me some of them (thanks anyway)

dark pasture
#

So i want to make a discord bot with items that a server can create, And i use for i in blabla, so would it be more practical if i create a table for every guild or store every item in one table but with guild id so i can identify it?

fair girder
#

what is the smart way of getting the max days between each customer's purchases from a single table that has date and customer_id?

#

not max-min, but the longest between a purchase and the next

celest rapids
#

Thanks will try out

dire cedar
#

Hello everyone, i'm new on the channel and i need help for something ducky_hunt !! I would like install InfluxDB client on python. And i have no clue how to do it simply. Can someone please show me the way ? (I'm french sorry for the accent and syntax, lol) 🤠

torn sphinx
#

hey guys! im trying to search a database with criterial of two columns, having a tough time finding the right code. For example I need to show column A and how many times Colum B = the word successful

#

any help would be appreciated 😄

willow sequoia
torn sphinx
#

so if column B only shows Successful or Unsuccesful.. I want to only show successfull but filter it by Column A say Column A is a department

willow sequoia
#

you can use groupby clause

torn sphinx
#

ok i ran this and it worked, but i have 23 to find out if they are success or un, wondering if there a better way then runing 23 seperate codes

#

len(july_df[(july_df['Referral Source Type Name']=='PCP') & (july_df['successful']=='successful')])

#

for group by, would i just individually type all 23 'Referral Source Type Name'?

torn sphinx
#

How to write your text to the 2nd line of the file?

#

the two columns are Referral Source Type Name and successful, there are 23 referral types and "successful" and "unsuccessful" under the second column

#

only need to find successful total for each referral type (118k rows)

steel olive
#

Is there a way to use a database as file?

mental quiver
steel olive
#

I mean something like django

#

it has a file as database

#

it has sqlite.db file

mental quiver
#

That's with a lot of databases

icy oyster
steel olive
#

exactly

#

how can I do it in a normal python project?

icy oyster
#

import sqlite3

steel olive
icy oyster
#

and just use it

mental quiver
#

You could have a csv or JSON as a database if youd like as well but things like sqlite can store it in a .db file on drive

icy oyster
#

!d sqlite3

delicate fieldBOT
#
sqlite3

Source code: Lib/sqlite3/

SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.

The sqlite3 module was written by Gerhard Häring. It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249, and requires SQLite 3.7.15 or newer.

steel olive
#

ok thanks

#

I'll come back here if I have problems

#

thanks

mental quiver
#

Np

mental quiver
# steel olive thanks

I made a database manager too if you want to try it out helps me create my databases etc

versed inlet
#

Say I have tables T1 and T2. I want to join them so that I can only get the records from T1 which are unique and not found in T2. How can I do this?

low hatch
#

@versed inlet In Sql theres several ways.
If T2 cant have null values, id do something like
left join T1 on T1.id = T2.id
where T2.id is null
If T2 can have null values, id do
where T1.id not in (select distinct T2.id from T2)
Alternatively, you can outer join, but somehow I could never get that to work properly

versed inlet
#

the id in T1 and T2 cannot be null, but the row itself may not exist in T2. I'm guessing this works in that case as well

low hatch
#

correct, a left join will leave a null value, left/right joins are essentially xlookups if youre familiar with excel

#

Outer join is meant for the exact case you specified, but Im not quite sure on the correct syntax

versed inlet
#

alright, thank you very much! I'll look into left and outer joins

soft gorge
#

With SQLite is there a way to format my table to take any negative numbers and set them to zero on insert / update

hidden jetty
#

Heroku Postgres + SQLAlchemy
I have psycopg2.OperationalError: connection to server at "qwertyuiop0" (0.0.0.0), port 5432 failed: FATAL: password authentication failed for user "qwertyuiop1"
when invoking

    def add_devlog(self, channel_id, user_id):
        devlog = Devlog(channel_id=channel_id, user_id=user_id, archived=False)
        self.session.add(devlog)
        self.session.commit()
        return devlog

code for making session

    def __init__(self):
        raw_db_url = subprocess.run(
            ["heroku", "config:get", "DATABASE_URL", "--app", 'pgd-bot'],
            shell=True, capture_output=True).stdout
        # Convert binary string to a regular string & remove the newline character
        db_url = raw_db_url.decode("ascii").strip()
        # Convert "postgres://<db_address>"  --> "postgresql+psycopg2://<db_address>" needed for SQLAlchemy
        final_db_url = "postgresql+psycopg2://" + db_url.lstrip("postgres://")

        # Create SQLAlchemy engine
        self.engine = create_engine(final_db_url, connect_args={'sslmode': 'require'})
        session = sessionmaker(self.engine)
        self.session = session()

It's external device so I can't use simple os.environ['DATABASE_URL'] for getting database url for engine
final_db_url looks like postgresql+psycopg2://XXXXXXXXXXX:x6545x54x564x87645xx465x@xx1-23-456-789-012.eu-west-1.compute.amazonaws.com:5432/xxxxxxxxxxxxx

shy torrent
#

Error: FATAL: role "root" does not exist

#

CAN anyone help me this error ?

hidden jetty
#

@brazen flower ?

unknown.png
hidden jetty
#

But got this
FATAL: database "dd9nm8qjb3a3t8" does not exist
But

unknown.png
hidden jetty
shy torrent
#

its hardly 1hr m started with sql

#

my sql

#

m using pop sql

hidden jetty
shy torrent
#

mysql

shy torrent
hidden jetty
shy torrent
hidden jetty
#

ha-ha we have same errors xD

shy torrent
#

now what should i do ?

hidden jetty
#

🥲

shy torrent
hidden jetty
shy torrent
#

🥲

pliant ember
#

my values in pandas are type series,

#

even after I force them to be integers

#

means I cannot do logs with them even when using np.log which should work on series/arrays

frozen python
celest fractal
#

sql question that I feel like I should know but I'm struggling. I'm using a mysql 5.7 and I've an employee id and when the query is displayed I need to put dashes every three numbers. ie 123456789 needs to print as 123-456-789. I know I can truncate and divide by etc and concat it back together but I was wondering if theirs an int parse to string function.

grim isle
#

Oracle and MS-sql might have some formatting built in functions for MySQL I guess it’s substring and concat

celest fractal
torn sphinx
#

I want to make it so that when a channel is created, 0 (the default value in my column), rises by 1, im not sure how to increase the default value

carmine quiver
#

Is it possible to make an internal database with a python file? (.py) like if I wanted to create a data sheet for companys contact info

lofty barn
#

How do I get the current prefix of a guild from a database (postgresql)

async def get_prefix(bot, message):
    prefix = bot.db.field('SELECT prefix FROM guilds WHERE "guild_id" = ?', message.guild.id)
    if len(prefix) == 0:
        await bot.db.execute('INSERT INTO guilds("guild_id",prefix) VALUES ($1, $2)', message.guild.id, DEFAULT_PREFIX)
        prefix = DEFAULT_PREFIX
    else:
        prefix = prefix[0].get("prefix")
    return commands.when_mentioned_or(prefix)(bot, message)```

That's what I have, but when I do `bot.db.field`, the error message I get is `'Pool' object has no attribute 'field'` . I'm not sure what that means. When I do `await bot.db.fetch` instead of `bot.db.field`, I get a LOT of red lines
fading patrol
#

You might want to consider a config file (ini, toml, whatever) but if literally all your doing is putting an address in a constant in a .py file, there is nothing wrong with that.

#

If you have a lots of addresses, maybe put them in a CSV for a JSON file if they're not going to change. If you're tracking lots of addresses programaticaly, you probably want SQlite

carmine quiver
mellow trench
#

I'm testing cockroachdb, I installed it and I'm copying data into it... I'm using executemany() and it seems to be going at 250 rows/s. Apparently each inserted row is treated as a separate statement

#

is there a fast way to import data?

mellow trench
#

building a CSV file is very inconvenient

fading siren
#

Hello, someone who uses Panda to generate csv, do you know how I can do this? , split the value of a column into several rows with the same value of the remainder

Screenshot_9.png
uneven sinew
#

what would be the ideal way to access a database from multiple files? I don't want to create a new connection for each request to the API
EDIT: figured it out, same way as JS, one file

green whale
#

hello guys
i dont know python but have to do assessment
Download a Comma Separated Values (csv) file titled shoppingtransactions.csv. This file contains over 7,000 transactions with each row representing a comma separated list of items purchased together in one transaction. Here are some samples rows:
• soup,light cream,shallot
• frozen vegetables,spaghetti,green tea
• french fries
• eggs,pet food
• cookies
• turkey,burgers,mineral water,eggs,cooking oil
• spaghetti,champagne,cookies
• mineral water,salmon
• mineral water
Implement an application in Python that reads in shoppingtransactions.csv and allows users to interact with the application with the following three commands:

  1. sup item[,item]
  2. con item[,item] --> item[,item]
  3. exit
    Users will be prompted the menu above until the user selects Option 3. Command 1 calculates and prints the support of an item or a set of two items. For example, users may select Commend 1, then type:
    sup Burger
    Or
    sup parmesan cheese,spaghetti
    Command 2 calculates and prints the confidence of an item or two items provided that another item or the other two items were bought. For example, users may select Commend 2, then type:
    con cookies --> chocolate
    Or
    con frozen vegetables --> sparkling water, ham
    The only libraries that you can import are numpy and csv. Save your Python code in a file called shopping.py.

can u guys help me with this
its due date is near

bitter lion
#

For avoiding SQL injection, it is safe by just removing the special characters from the user's input?

fading patrol
median dome
#

hey, can someone help me with this sqlmodel/sqlalchemy error i have in #help-carrot ?

bitter lion
primal notch
#

A postgres question
I have a table of users and a table of their answers to some questions. I need to join those like that:

first_name | last_name | email | ... | answer_text_1 | answer_timestamp_1 | ... | answer_text_10 | answer_timestamp_10 |```
So I have their info and their answers to each question in one view. There is an arbitrary amount of questions, could be from 0 to like 100. 

How should I go about that? Right now I build the SQL query with a cycle in my code, iterating over all questions, but there sure should be a way to do that with just SQL, right?
#

So I would need to dynamically add columns to the main SELECT clause and join the answers table for each question. Seems pretty tough for me to solve on my own

hidden hamlet
#

anyone know a good tutorial of some sorts for a leveling database for discord bots?

jovial night
#

do you guys know some discord servers that have discussions/help about SQLAlchemy?

torn sphinx
#

cursor.rowcount and cursor.lastrowid , can someone explain guys

opaque wave
#

is it bad practice to re-alias a sum with an existing alias in sql?

e.g. I have a chain of left joins, in the first one I get sum(a.revenue) as total, can I do in the next one sum(sum(b.revenue) + ISNULL(a.revenue,0)) as total instead of adding them in the end?

fading patrol
torn sphinx
fading patrol
#

If you have three rows and their IDs are 2, 4 and 6 then the row count is 3 and the last I'd is 6. If that still isn't clear, read up on the basics of SQL

hearty plover
#

can anyone help me with some database to JSON code ?

wicked flax
#

OK, using python to talk to a Microsoft SQL database. I installed pyodbc and the ODBC driver. I've made the connection, pulled data, it's working. But I got this warning and I'm not sure what to do about it:

UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy

So I found this answer to what to do about the warning and, as a beginner in python, I'm a bit confused on how to proceed.

https://stackoverflow.com/questions/71082494/getting-a-warning-when-using-a-pyodbc-connection-object-with-pandas

I'm confused about the relationship between SQLAlchemy and Microsoft's SQL Server. For the moment, the only thing I'll be doing is SELECT queries to pull data into Excel. The most I'll ever be dealing with is < 1000 rows and maybe 300 columns. Is this a warning I can safely ignore, or do I need to start using SQLAlchemy? And is that instead of or in addition to pyodbc?

I'm just confused... 🙂

Stack Overflow
Getting a warning when using a pyodbc Connection object with pandas

I am trying to make sense of the following error that I started getting when I setup my python code to run on a VM server, which has 3.9.5 installed instead of 3.8.5 on my desktop. Not sure that m...

pure apex
#

Hi I am trying to connect to Microsoft SQL, however I am getting the following error. Can't connect to MySQL server on '10.0.99.208:3306' (111)

cunning lotus
#

Hello is someone on?

#

Does someone have experience with Cassandra db?

candid swallow
#

how fast are SQL queries compared to some containers we normally use

unkempt prism
mental sage
# 
                "  <div class=\"thumbnail\"></div>\n" }```

is there a way to store a doc in this format? i am trying to store this in couchbase
opaque wave
#

why is sql so garbage

#

how am I supposed to understand what's wrong if the issue just says that An error occurred while executing batch. Error message is: Value to add was out of range. Parameter name: value

dark mica
#

Reposting it here cuz I didn't get any response in python general

Hi, I'm using tortoise-orm (pgsql) to connect to database. I'm using it with quart. Whenever I make a query I get this weird error message. But after making the query 7-10 times pgsql successfully executes my query.

OperationalError prepared statement "__asyncpg_stmt_a__" already exists HINT: NOTE: pgbouncer with pool_mode set to "transaction" or "statement" does not support prepared statements properly. You have two options: * if you are using pgbouncer for connection pooling to a single server, switch to the connection pool functionality provided by asyncpg, it is a much better option for this purpose; * if you have no option of avoiding the use of pgbouncer, then you can set statement_cache_size to 0 when creating the asyncpg connection object
fading patrol
analog totem
#

im tryna make a basic login system which uses json data to check if the user is there or not.

import json
def jsonlogin():
    usernameinput = input("Enter a username: ")
    passwordinput = input("Enter a password: ")
    with open("userdata.json","r") as f:
        users = json.load(f)
        users[str(username)] = password
    if password and username in users:
        print("Succesfully logged in \n")    
    else:
        print("Invalid Username / password \n")
jsonlogin()```
Right now its always saying login success unless there is nothing inputted.
How can I fix that?
Here is a sample of what the data looks like in userdata.json
```json
        {"id":"9","username":"cogborne8","password":"VStasVgDd","email":"nswalteridge8@dmoz.org","access_level":"3"},
stable bay
#

Hey y'all, can a table's PK be referenced as a FK in two different tables?

#

Here I have the zoo_id being used as a FK in both the enclosure and individual_animal tables

unknown.png
keen minnow
#

(it looks wrong)

analog totem
#

and im not sure how

keen minnow
analog totem
#

if passwordinput is the same as password in json file then print success

keen minnow
#

which does not match your intent

analog totem
#

oh...

#

ok...

keen minnow
#

But it does mean you need to find the user where the username matches and then compare their password

analog totem
#

so how would i compare the input

#

and see if it matches anything

torn sphinx
# 
@bot.command()
@commands.has_permissions(administrator = True)
async def newcategory(ctx, acctype):
 c.execute("""CREATE TABLE ? (
         email text,
         password text,

            )""", (acctype,))
 await ctx.send("Successfully created new account type!")
 c.execute("""insert into ?  values("anything", 'rawr', 10)""", (acctype,))```
    c.execute("""CREATE TABLE ? (
sqlite3.OperationalError: near "?": syntax error
keen minnow
analog totem
keen minnow
# analog totem

write your line as multiple statements. Keep It Simple and Stupid

#

also you are using user as different variables, which will mess up things

analog totem
#

ok

analog totem
keen minnow
#

(I would expect not to work)

analog totem
#

it says every time user not found

#

idk why tho

keen minnow
#

Print the first few lines of data

analog totem
#

of the json file?

keen minnow
#

of the variable data

analog totem
#

if i just print data it prints the wole of userdata.json

keen minnow
#

cool

#

so

#

it's a map that has an entry "userInfo" which itself contains an array of user data

#

thus obviously, data[usertofind] wouldn't find anything

analog totem
#

so data[username]?

keen minnow
#

why?

analog totem
#

because we're tryna search in the usernames

torn sphinx
#

Does anyone know how I do it so when I run the ban command (or kick, whatever), the user can't ban the bot from the server?

keen minnow
#

How does that map to your data?

analog totem
#

i mean.. im not sure

analog totem
keen minnow
#

Look at the keys, the values so they make sense to you and you can understand how to get what you want out of it

analog totem
#

i'm confused with what you mean by map and explore data

keen minnow
#

I mean to think about the structure of the information in data

analog totem
#

i mean data is just a variable...

torn sphinx
#

sorry

analog totem
#

which shows call of userdata.json

torn sphinx
#

wrong chat

keen minnow
# analog totem i mean `data` is just a variable...

Right and you would look for users very differently depending how it store values:

data = [
   {name: user1, password: user2},
    ...
]
is very different from 
data = {
   users = [user1, user2, etc.],
   passwords = [password1, password2, etc.]
}
from other ways to organize it
#

and the way you currently have it in data is different from the two ways I wrote above

analog totem
#

yes

#

i mean its almost the same as the first one

keen minnow
#

so if you just type data[username], it will means different things

#

and you would get different results

analog totem
#

wait but in that case if i did data[name] would it output user1?

keen minnow
#

no

#

because the keys of your data aren't user names

analog totem
keen minnow
analog totem
#

nvm

keen minnow
#

That can give you some clues

velvet lotus
#

for firebase security rules, is it possible to only allow a read if the user knows the id?
so for example, it only allows it to read the specific reference for the key that the user has?

lilac ruin
#

anyone know how can i put python code in php i want to calculate with python but i want to fetch data from phpmyadmin

unknown.png
dark mica
#

I tried using only asyncpg and statement_cache_size is only accepted by asyncpg and not tortoise orm