databases

[cunning scarab]

found a solution yet?

[sand imp]

Nope

[molten sable]

Can someone please help me with the Linux Command to install MySQL Python Connector install code ??

like what is the code ?? can't find it on the net......

[frozen grotto]

what is that ? What are you trying to do exactly ?

[exotic stream]

https://dev.mysql.com/doc/connector-python/en/connector-python-installation-binary.html

[halcyon dew]

pretty sure there is a package for it on pypi somewhere irc

[molten sable]

mysql-python connector, on linux subsystem. nvm found it

[sand imp]

db.students.find({ }, {'full name': '$name.first ' ,'$name.last',
'year': {$year: '$bdate'}, 'age': { $subtract: [new Date(), '$bdate']},
'number phone': {$'Phones.0'},
'#courses': {$size: "$courses' })

I tried to put "," but still this error keep going on,any idea how to fix it?

[fading patrol]

Not sure what library this is and what syntax it uses but did you try a comma after the courses line?

[sand imp]

Thats a mongodb on json file , and yes i did

[sharp rover]

Try replacing the " with a ' .... so {$size: '$courses' }

[sand imp]

Still sayin "," expected on {$'Phones.0'} {$size: '$courses' })
and on '$name.last' it saying ":" expected
thats the code so far:

 'year': {$year: '$bdate'}, 'age': { $subtract: [new Date(), '$bdate']}, 
 'number phone': {$'Phones.0'},
 '#courses': {$size: '$courses' })```

[sinful yacht]

how to save data

if user is exist or not

[cunning river]

Hi

I'm having a hard time understanding this

and this

[white elm]

is there any possible way of inserting a list into a row?

so for example, i have made an python tkinter app with accounts and every single account can add friends, how can i possibly store like a list into the users record in sql with all their friends

[storm mauve]

some databases do support it, but usually for that case you'll just have another table and store each relationship as a row

[white elm]

oh

so like making a table for each user or something?

[storm mauve]

no, not for each user

# USERS TABLE
ID INT PRIMARY KEY | NAME STRING
0 bob
1 foo
2 bar

# FRIENDS TABLE
ID_A INT | ID_B INT
0 1  # bob and foo are friends
0 2  # bob and bar are friends

[white elm]

oh okay

but how can i set a name to the that to represent the friend list database for each registered user if you get me

so like the table is called "friend_list"

[storm mauve]

take a look at the answers for https://dba.stackexchange.com/questions/10199/how-should-i-design-a-relationship-table-for-friendship & perhaps related questions

[white elm]

and it inserts all friends to a specific user

oh okay thanks

[drifting pendant]

so I am using the i2c bus for inputs on my pi4, and was wondering how i could wait for an input to resume the next chunk of code

[sweet remnant]

can anyone help me with a nested loop, trying to get data from my db but it just grabs the last item in the second for loop and updates the same last item for everything

[fresh sapphire]

Is there any documentation on building a SQLAlchemy/Flask-SQLAlchemy relationship between two models representing data from two different databases (no FK constraint)?

[paper flower]

https://docs.sqlalchemy.org/en/14/orm/persistence_techniques.html#session-partitioning
Not sure how relationships would work but I think if you use selectin loading it should? 🤔

[tropic kayak]

Anyone know why I’m recieving this error?
Code:```py
async def on_guild_join(self, guild: discord.Guild):
print(f"I was invited to {guild.name} - ({guild.id}). It has been added to the database.")
db = self.cluster["Aziel"]
collection = db["Main"]

    data = {"_id": guild.id, "prefix": "!"}
    collection.insert_one(data)

**Error:** https://paste.pythondiscord.com/siqovomodo
*For content, I’m using mongodb.*

[paper flower]

You can also ask question on github, Mike usually responds pretty quickly

[fresh sapphire]

Good question. I understand FKs can't map across databases, but I thought SQLAlchemy could provide this in memory at the application layer. I came across something promising using db.session but haven't found time to test (or translate to Flask-SQLAlchemy). May put up the question on Ovefflow or GHub to give it time to breathe before I can get back to it.

[crimson tangle]

i have hosted my mongo on aws but i want to login it on compass how can i do that ?

[bleak bough]

for ab_group in (1, 2):
  await c.execute(
    """SELECT COUNT(main) as Count 
    FROM guild 
    WHERE guild.ab_group = ? 
    LEFT JOIN main ON guild.id = main.guild""",
    (ab_group,),
  )```
how would i do both at once and return it as a list of two items ![7739monkathink](https://cdn.discordapp.com/emojis/960314142030442566.webp?size=128 "7739monkathink")

[paper flower]

WHERE guild.ab_group in (1, 2) but you should check if where ... in is available in your db

[bleak bough]

wouldn't that just return one result?

[paper flower]

If there's only one guild that satisfies that condition - yes

Also you're selecting count, so I'm not quite sure what you want to do

You can use group by if you want to select amount of main rows for each guild

[quartz lynx]

How do I enumerate rows with the same primary key?

This is in Postgresql or its Python API.

[fading patrol]

Primary keys have to be unique, unless you're talking about keys that are the same across tables?

If you're asking across tables, this could just be a basic nested query

[mystic eagle]

anyone ever worked with dokku and django and utilized a database?

[open gust]

hello, can someone help?
I'm trying to query the average count
here is my code

class AveragePolicyPerUser(ListAPIView):
    permission_classes = [IsAuthenticated]
    serializer_class = ProductInsureeSerializer

    def get(self, request):
        today = datetime.now()
        start_date = request.data.get('start_date', '2021-01-01')
        end_date = request.data.get('end_date', today)

        query = ProductInsuree.objects.all().filter(created_at__range=[start_date, end_date])
        
        if query.exists():
            average_user = query.aggregate(Avg('application_number')).distinct().count()

        context = {
            "query": query,
            "average_user": average_user,
        }
        return Response(context, status=200)```

this is the error I'm getting

[quartz lynx]

Sorry, I meant a foreign key that is the primary key in the other table.

Basically I have a table containing bids on different auctions. There is a column called auction_Nr which is a unique key given to each auction lot. If two rows share the same auction_Nr they're from the same lot. Each lot also has a bid_time_UNIX, which is the time at which the bid was placed in UNIX Epoch time.

What I would like to do is create some sort of function, that groups bids from the same auction (same auction_Nr) together and then gives them each a number starting from 1 based on when the bid was made relative to the other bids (meaning 1 has the lowest bid_time_UNIX) and inserts this value in a new column called bid_Nr.

[grim vault]

aggregate() is a terminal clause for a QuerySet that, when invoked, returns a dictionary of name-value pairs.
query.aggregate(Avg('application_number')) will return a dictionary like {'application_number__avg': 123.45}
You can't distinct() or count() a dictionary.

Also, an average is just one value, you don't need to count it.

[fading patrol]

Seems like a relatively simple join query could do what you need but without sample data I'm not exactly sure what it would look like

[stone mauve]

Very basic question:
When trying to access an element of a numpy array by index, using arr[i] can return an incorrect data type. What method or function am I supposed to use to access the item? take and choose seem to return new arrays instead of a lone item.

[harsh pulsar]

note that in general you're more likely to get numpy answers in #data-science-and-ml or maybe a help channel. "databases" usually refers to data storage systems like mysql.

as for your actual question: what do you mean by "incorrect"? are you talking about how you sometimes get a np.float64 instead of a float when you access values from a numpy array? what problem are you trying to solve here?

[finite tiger]

Is it possible to make a PayPal API so when ever someone donates it will edit a table with PostgreSQL asynpg?

[fading patrol]

My guess would be yes, have you checked their documentation?

[finite tiger]

couldn't find anything.

[torn sphinx]

How to define one element from sqlite3 database and use it?

I have in database: channel, message, test. And I would to print channel. How to do it?(this is example)

@topaz vector

[harsh pulsar]

what do you mean by "define one element"? also it's not good to ping people who aren't already helping you.

if you are asking about how to get the value of one column from one table, know that sqlite returns rows as tuples. so if you call fetchone, you will always get a tuple. you can use [] to extract values from the tuple, just like you would do with a list

[torn sphinx]

cursor.fetchone()["channel"]```?

[harsh pulsar]

right. sqlite 3 returns rows as tuples, not as dicts.

cursor = conn.execute('select channel from my_table')
row = cursor.fetchone()
if row is not None:
    channel = row[0]

you have to select elements by position, not by column name

you can however easily get the column names from a cursor, if you want them:

colnames = [desc[0] for desc in cursor.description]

[storm mauve]

well, there is that:

If returning a tuple doesn’t suffice and you want name-based access to columns, you should consider setting row_factory to the highly optimized sqlite3.Row type. Row provides both index-based and case-insensitive name-based access to columns with almost no memory overhead.
https://docs.python.org/3/library/sqlite3.html#row-objects

[harsh pulsar]

i was just about to point that out 🙂

[torn sphinx]

If I have name, test, message. To print test i use cursor.fetchone()[2]?

[harsh pulsar]

yes, but read what etrotta just posted. it might be nicer to use

[vivid latch]

I currently have code set up to look at names (first and last) but I'm not fully sure how I would drop the first name and just have the last name in a list. Any tips?

with open("nameList.txt") as f:
    global lns
    lines = f.readlines()
    lns = [line.strip() for line in lines]
print(lns)

Printing that code brings up a list like ["John Doe", "Mary Brown"] and so on

I'm hoping to cut off from the left of the space, including the sapce but I'm not the brightest when it comes to python lol

To clarify, I'm hoping for the list to look like ["Doe", "Brown", etc] in the end

[harsh pulsar]

!e ```python
name = "Janet Elanor Roe Jr."
print(name.split(" "))
print(name.split(" ", maxsplit=1))

[delicate field]

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

001 | ['Janet', 'Elanor', 'Roe', 'Jr.']
002 | ['Janet', 'Elanor Roe Jr.']

[vivid latch]

Thank you so much!

Would that work with lists with a for x in list?

Scratch that, figured it out :)

[rapid ginkgo]

How to find the number of duplicate names?

And then display all the names in the format:

Name1: 2 (repeated twice)
Name2: 1 (repeat once)

etc.

[harsh pulsar]

maybe count(*) with group by?

[rapid ginkgo]

Yes, I did. Can you please tell me how to get the sum of the values of the entire column?

[fading patrol]

I think you just need sum()

[hallow rover]

is there a way with postgresql to have it automatically delete a row x minutes after its been inserted? that way i wont have to worry about it in my code

[pastel wren]

is it a good practice to encrypt a person's email in a db rather than keep it in plain text?

[maiden widget]

why u wanna do it?

[rustic elbow]

Curently i'm updating my postgres Field like this:

db.drop_all()
db.create_all()
db.session.commit()

But the problem is, it delete my old data from DB, is there anyway to update field without drop all the table in flask_sqlalchemy?

[paper flower]

Use alembic

[rustic elbow]

I just readed it, there’s no 1 line command for updating the table field?

[paper flower]

No, you have to write or generate migrations and then apply them

Autogeneration is not perfect so you should pay attention to generated files and adjust them when needed

[jolly fern]

Hello, im currently using InfluxDB which i have no experience with, and im trying to visualize real-time data im receiving via Python. If anyone has had the same problem and could help me out id aprecciate it.

So my current problem is writing data into a bucket in influxDB and adding .time, im using data point(i think its called) and ive used datetime format and timstamp. Both dont work as Influx doesnt seem to be detecting it.

p = Point(dataid).field("data",num2).time(int(datetime.now().timestamp()*1000))
write_api.write(bucket=bucket, record=p)

[quartz lynx]

Here 🙂

[fringe tiger]

can someone explain to me what the purpose of ODBC is (Microsoft Open Database Connectivity)?
ping for reply

[fringe tiger]

I got it

[uncut moss]

hey guys what is the equivalent to SQL:l tinyint in Django models? I want a field where the range is between 1 to 11 (1-10) and nothing else

i have this thus far, would this do it?

rating = models.PositiveSmallIntegerField(validators=[MinValueValidator(1), MaxValueValidator(10)])

[fading patrol]

So you need to GROUP BY auction number, INCREMENT a new value and INSERT in the original table... I'm not going to try to write out the exact query but if you share your best attempt maybe someone can help you where you get stuck

[quartz lynx]

Thanks! Will try omce I am done with HW.

[deep lintel]

guys
i really need help
with sql developer
Status : Failure -Test failed: IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=2Ec31F7YSR+QDW/Z8Zoz0g==)
what's wrong with this error
&& how can i get the pass and user because i forgot it

[stable sandal]

hey, im trying to import a row into BigQuery,
here's error msg:
TypeError: rows argument should be a sequence of dicts or tuples
from line:

client.insert_rows_json(table_id, gamble.entry_to_insert('Heads'))

    def entry_to_insert(self, ID):
        entry = {
            u'Date':datetime_today.date,
            u'Time':datetime_today.time,
            u'ID':1,
            u'Result':ID,
            u'Money':gamble.gamble_value
            }
        return entry

any solution will be great 🙂 (please ping if so)

[brazen charm]

Youre only returning a single dict

The method wants a list of dicts or a tuple of dicts

If you only have one row to insert you can probably just get ways with wrapping your entry to insert method result with []

[quiet nebula]

i keep this error with aiosqlite lib ValueError: no active connection when i do stuff like await conn.backup(second_Con) or await conn.execute()

im using a local .sqlite3 db

[kindred blade]

is there anyway to work with sql for free?

[exotic stream]

what do you mean Caliush?

[kindred blade]

well, when I google stuff like mysql, it always seems that there is something to pay to use it

I only got to work with sql through highschool oracle stuff, not on my own on a separate database

[exotic stream]

yeah, there are tons of community edition database clients you can play with to create a database to play around with. I mean python has sqllite built in, postgresql is open source so you could try that if you need something a bit better than sqllite.

[kindred blade]

just searched for mysql community edition, is that good?

[exotic stream]

if your poking around to try and learn sql concepts its more than good enough.

[kindred nova]

@exotic stream

it gives syntax error

nvm

[queen jetty]

I have error, in mysql 'Lost connection to MySQL server during query' ,why?

using pythonanywhere server

[dense imp]

ImportError: cannot import name 'create_engine' from 'sqlalchemy' (unknown location)

anyone know how fix it?

[paper flower]

Did you create a file named sqlalchemy? 🤨

[torn sphinx]

I need some advice. Regarding PostgreSQL and TimescaleDB connection pooling in a microservice architecture,
where about 20 microservices are using asyncio and asyncpg,
I am wondering which is the best option for connection pooling:

1. Only use pgbouncer on the database side.
2. Only use asyncpg's connection pool on the microservice side.
3. Use both pgbouncer on the database side AND asyncpg's connection pool on the microservice side.

[torn sphinx]

There are some interesting sources on the net (e.g. https://www.pgbouncer.org/faq.html , https://www.pgbouncer.org/features.html , https://news.ycombinator.com/item?id=23498508 ) but none has really been conclusive for me so far, and also some of them like this ycombinator source might contain outdated info.

ddorian43

asyncpg is not scalable. It can only do "session pooling" because it needs advisory_locks, listen/notify, which will end up needing a lot of Postgresql connections.

Hacker News · Jun 12, 2020, 12:25 PM

[kindred nova]

I've created a table with sqlite but it don't store anything

[civic cargo]

The asyncpg docs contain a section on usage with pgbouncer, in case you haven't seen it: https://magicstack.github.io/asyncpg/current/faq.html#why-am-i-getting-prepared-statement-errors
Apart from that, I'm not sure. I spent a lot of time working a lot on an application with many microservices depending on Timescale for persistence, and we pooled connections client-side using asyncpg. It worked for us (worker services usually only need 1 connection, APIs have pools of configurable size), but I cannot compare it to using pgbouncer since I don't have experience with it.

[torn sphinx]

Thank you so much for sharing this insight. Am I correct in assuming that in your scenario you then also used very low max sizes for the asyncpg connection pools, like max number of connections 3 or even as low as 1?

[civic cargo]

Yes, each service would receive those values as config via environment variables

For workers, it was usually min=1 and max=1

i.e. always keep 1 connection open

[torn sphinx]

We have a TimescaleDB database server that already has pgbouncer in place, with pgbouncer configured to use Transaction pooling.

But I think additionally using asyncpg connection pool (i.e. option 3. from my post above) with 1 connection would not hurt. We might try if my colleagues agree.

[civic cargo]

According to asyncpg docs, you won't be able to use prepared statements via pgbouncer in transaction mode

[torn sphinx]

True. We currently use statement_cache_size=0 so basically the second option mentioned in https://magicstack.github.io/asyncpg/current/faq.html#why-am-i-getting-prepared-statement-errors

[torn sphinx]

Did you in your scenario need/use prepared statements with TimescaleDB to improve performance?

[civic cargo]

Yes, asyncpg uses them automatically

If I'm not mistaken, that's what makes it so performant

though in general, it's difficult to predict what will work best for your application - it's best to profile performance in various setups, and choose based on measurable metrics like response time of your API

[kindred nova]

I've created a table with sqlite but it don't store anything

[torn sphinx]

Yes, indeed. I am coming from Scylla and other database systems to TimescaleDB and I am investigating quite a bit today. Thank you, I already got two steps further thanks to you and also found more sources to look at etc. such as https://docs.timescale.com/mst/latest/connection-pools/

[civic cargo]

👍

[fringe sundial]

.

[celest zodiac]

@fringe sundial for multiple users, your second table would have two colums: the user ID, and the item (reference to the first table)

[fringe sundial]

I mean that like one is user id and other column is item but there are multiple item how would I store that?

[celest zodiac]

here's a more detailed example, coming up

[fringe sundial]

Like the amount of item user have

[celest zodiac]

Items table
1. Sword
2. Gun
3. Gumball machine

User items table
UID: 1 Item: Gumball machine (3)
UID: 2 Item: Sword (1)
UID: 2 Item: Gun (2)
UID: 3 Item: Gun (2)
UID: 3 Item: Gumball machine (3)

You can then query for all the items owned by a particular user, and just get a simple count of that query to get how many

[fringe sundial]

Where would I store the item quantity?

Oh

And if user have one or more items?

[celest zodiac]

@fringe sundial You could either just store multiple rows with the same item, or have a 3rd column indicating quantity, but the first option means each item could be customized (e.g., with power levels, bonuses, etc.)

The above example shows users with multiple items

[fringe sundial]

I am really confused

You not able to understand what i trying to say

[celest zodiac]

You don't need to hard-code item quantity, you can infer it from how many instances of it are in the items table

[fringe sundial]

Like

User: 5378484 item: sword(1) catgirl(5)

How will I know user have catgirl?

I mean do i need to make seprate column for every items?
Like

User | sword | gun | podbdj

[celest zodiac]

The user item table would look like this:

User: 5378484 item: sword(1)
User: 5378484 item: catgirl(5)

Each row in the user item table has a column for the user ID, and a column for an item.
This way you can insert multiple rows per user

And no, you don't need per-columns for items

[fringe sundial]

You saying that i add 1 or more records of user for items?

[celest zodiac]

You just need one column that references the ID number of the item in the first table

[celest zodiac]

correct

[fringe sundial]

How would I fetch them?

[celest zodiac]

a SELECT with a JOIN

[fringe sundial]

The query would fetch all the user records

can i have one example pls?

[celest zodiac]

you'd use WHERE to filter by user

[fringe sundial]

Where will filter the user id not the items?

[celest zodiac]

select * from user_items where user_id=32767

[fringe sundial]

it will select all items user have?

[celest zodiac]

correct

[fringe sundial]

So i can sort that and get the items in tuple?

[celest zodiac]

correct

[fringe sundial]

Oh k ty thanks

[celest zodiac]

and you can perform a join on the items table so that the item data is included

[fringe sundial]

Whats join?

I not able to find docs on join

[celest zodiac]

it's a way to have a query return records from multiple tables when they share references. In the above example we have the column that references the items table, so a join would bring in all the data from each row in items where the item number matches the column in user_items

https://www.sqlitetutorial.net/sqlite-join/

good explanation of how joins and relations work in tables

[fringe sundial]

Do i need to use INNNER JOIN?

[celest zodiac]

LEFT JOIN, I believe, with user_items as the "left" table and items as the "right" table

[fringe sundial]

😵‍💫

[celest zodiac]

it's useful to set up an example with a small data set, which you can play with manually

[fringe sundial]

Very very very confusing

[celest zodiac]

I encourage you to tinker with it apart from the bot you're trying to build so you get an idea of how things work

[fringe sundial]

self.named_tuple = namedtuple("user", ["id", "item",])

I am using this to return data in my other dbs
How would I use in items one?

[woven dragon]

Hi guys - which is most preferred library for MySql connection for python?

i see this sql alchemy somewhere and it makes me think if it's more for DS based stuff? idk...

[frozen grotto]

sqlalchemy is an ORM that replaces SQL with their own logic to talk to the database, so does a lot more than just connecting
mysql.connector is the base one afaik https://pynative.com/python-mysql-database-connection/

[woven dragon]

so, im reviewing someone else's code and i'm also reading that ORMs are not great for joins and complex queries and since i know that it's only mysql we're dealing with, makes no sense to use it right over a regular mysql connector, right?

[frozen grotto]

exactly
ORM is for people who don't like SQL but you still end up learning a whole language basically

[woven dragon]

got it! thanks!!

[fringe sundial]

Guys how to fix this error?

    async def read(self,id: int):
        _data = await self.cur.execute("SELECT * FROM items WHERE id = ?", (id,))
        data = await _data.fetchall()
        print(data)
        if not data:
            return False
        else:
            return self.named_tuple(data[0],data[1])

[paper flower]

Check what you actually receive in your data 😉

[lean plover]

Anyone know how to us the nolock option for sqlite db in sqlalchemy? can I just put iton the uri string?

[icy raven]

Hey everyone I am trying to get live Air quality data state wise for India can someone suggest some good API for the same ?

[brittle bolt]

Mongodb keeps throwing network connection errors. I'm running a community edition (v5.0.12), locally (on localhost 27017) on my machine (ubuntu 20.04) so there shouldn't be any network issues. The errors occur sporadically during long running operations such as creating indexes or aggregations. There is no information as to what might be causing the errors, I have tried everything to find a solution, but I'm at my wits end. I have been using Mongodb for years and I have never come across anything like this before. I have tried running the command with Python, (pymongo), PHP and even directly in the shell and it has not bearing. The machine has completely reformated and Ubuntu re-installed, the data has been re-imported, no change. Even installed the Ubuntu server edition, and that didn't solve the issue. Anyone have any idea how I can solve this issue?

[tall mica]

does anyone know of a vscode extension that will allow me to look at my azure sql database?

[fringe sundial]

Its retrieving
[(12345,"saddle")]

I want to fetch all items user have

    async def read(self,id: int):
        _data = await self.cur.execute("SELECT * FROM items WHERE id = ?", (id,))
        data = await _data.fetchall()
        print(data)
        if not data:
            return False
        else:
            return self.named_tuple(data[0],data[1])

This is my code

[fringe sundial]

@paper flower

[grim vault]

If you only expect one row just use fetchone() not fetchall(). As you can see, fetchall() returns a list of tuples.
fetchall():
-> [(12345,"saddle")]
-> data[0] == (12345,"saddle")
-> data[0][0] == 12345; data[0][1] == "saddle"

fetchone():
-> (12345,"saddle")
-> data[0] == 12345; data[1] == "saddle"

[fringe sundial]

I want to fetch all rows in which it's user id

[grim vault]

Ok, so you do need fetchall() but what do you expect from return self.named_tuple(data[0],data[1])?

This will only use the first two entries of the list (if there are more than one). And produce an error if there is only one.

[fringe sundial]

I want it return ("user id here","items list")

[grim vault]

In your code id is the user id and data is the list of all user items. I guess the table has two columns?

[delicate field]

Hey @fringe sundial!

It looks like you tried to attach file type(s) that we do not allow (.db). We currently allow the following file types: .gif, .jpg, .jpeg, .mov, .mp4, .mpg, .png, .mp3, .wav, .ogg, .webm, .webp, .flac, .m4a, .csv, .json.

Feel free to ask in #community-meta if you think this is a mistake.

[fringe sundial]

Ye

[molten sable]

I want to execute line 13 and line 14 together, how to do ??

pls help

[kindred nova]

if message.author.id == NP: or if message.author.id in NP:

which one

Np = cursor.fetchall()

[molten sable]

@kindred nova mycursor.execute line 13 and line 14, I want to execute both the select commands (MySQL python)

[tight junco]

guys, when i call this function

    async def add_bp(self, user_id: int, amount: int) -> None:
        self.player_db.update_one(
            {"_id": user_id}, [{"$inc": {"inventory": {"bp": amount}}}]
        )```
i get this error
`Unrecognized pipeline stage name: '$inc'`

can anyone tell why?

it's mongodb and im using motor

[regal glade]

hi, i was gonna replace my db from aiosqlite to prostgres, cause therell be multi threading, problem is prostgres doesnt say theyre compatible with debian based OS, cause ima be hosting my bot on a pi

[fading patrol]

Yes you can run Postgres on a pi, just a simple apt install https://pimylifeup.com/raspberry-pi-postgresql/

[regal glade]

oh lit thanks

[lime hearth]

yo

any sql guy up?

[burnt wraith]

just ask your question n someone will answer

nobody’s going to respond to “hello” or “i have a question” typically

[covert gust]

hello guyss

could someone help with this error

I appreciate your help guys, I am new in python trying to learn from youtube

[frozen grotto]

have you tried to follow the suggestion that sqlalchemy gives ?

[brittle bolt]

To answer my own question and in the hopes of potentially helping others, I found the root cause of the problem and the solution. The problem was caused by an OS limit on the number of open files being set too low, the value for ulimit -n must be set to 64000. This is documented here: https://www.mongodb.com/docs/manual/reference/ulimit/
And there is also a warning message in the mongod.log file when mongod instance is started. However, the problem doesn't reveal itself on start-up so it is difficult to attribute that warning as the source of the problem. To further the confusion, the error I was facing was referring to a failed network connections. That failed network connection only occurred because the mongod instance crashed because it hit file limit.
I guess the lesson from all this is, after installing, don't just start the application, check the logs and resolve any warnings that appear.

[compact warren]

What is the best way to use a orm session from sqlalchemy over multiple threads?

should i use scoped_session

always create one or use a globale one?

im currently using db_session = sessionmaker(bind=engine)() but after some commits its breaking

[paper flower]

Why not create a session just when you need it?

[prime sedge]

guys with sqlite is it better to keep a cursor open for as long as the script is running or open/close whenever I need to do something with the database?

[storm mauve]

open/close when you need to do something

[fringe sundial]

@celest zodiac can you help me?
I have got the user items from database
How can I display them?

[unique fossil]

hey i have made a database with mongo db and i want to add username and password according to the input form things

how do i save the uesrname and the password that the user entered

[fading patrol]

Not sure if you are looking to insert the data in your DB or actually set the username and password on the DB itself. It also depends what language/library you're using. (Pymongo?) In any case, it's all well documented and easy to find online.

[compact warren]

i have decided to use scoped_session its basically the same thing and thread safe

[muted spade]

Hii

I would like a bit of help please!

A company system has a number of application programs; each of them is designed to manipulate data files. These application programs have been written at the request of the users in the organization. New applications are added to the system as the need arises. The system just described is called the file-based system.

does this mean that the application programs are like text files, and spreadsheets or does it mean that application programs are functions that a user in an organisation wants, for example, being able to check bank balance? Thank you

or maybe its both. "i need a spreadsheet that is able to check bank balance" ? :)

[fading patrol]

I think it's closer to the second option. An application program is a program, not a file

But I'm guessing this is a class exercise? If so better to ask a teacher or fellow student

[muted spade]

Thanks, I was reading a book on databases!

[muted spade]

yeah that's what i thought but then the definition of an application program on google is a spreadsheet, textfile, presentations etc

[gleaming lotus]

Can i get stuff from a google form or sheets to my python program and push it to a google slideshow? is that possible?

[quaint scaffold]

sure you can! Google has documentation on how to interact with each of those services using Python.

• for forms: https://developers.google.com/forms/api/quickstart/python
• for sheets: https://developers.google.com/sheets/api/quickstart/python
• for slides: https://developers.google.com/slides/api/quickstart/python

[gleaming lotus]

thanks!

[quaint scaffold]

don't thank me yet 😦 I'm not feeling well today so I decided to skip food and just type out a quick example for you and after many problems and some googling I still don't have the "security" part setup right. this is always the hardest part. good luck!

[raven spire]

I'm trying to use postgres on pterodactyl but when trying to connect to database I get this error

2022-09-20 01:22:24.728 UTC [22] FATAL: password authentication failed for user "pterodactyl"
2022-09-20 01:22:24.728 UTC [22] DETAIL: Role "pterodactyl" does not exist.

Any idea

[raven shore]

Hello, has anyone ever dealt with this Booby table exercise?
https://www.proveyourworth.net/level3/start

[fading patrol]

The error message is straightforward so it all depends on your setup but the user does not exist where you're trying to log in

[raven spire]

see, i get why the error is happening but I'm kinda of confused on how to fix it

I connected to the docker container of the postgres db, and the create command

just isnt found at all

[fading patrol]

Make sure Postgres is installed in the container. What commands are you trying to run exactly?

[raven spire]

see, so the way im doing it, is pterodactyl has a thing calls eggs, which i guess is installed in it's own container

and one of the eggs they have is postgres

https://github.com/parkervcp/eggs/tree/master/database/sql/postgres

sudo docker exec -it containerhere CREATE ROLE userhere LOGIN PASSWORD 'passhere'; /bin/bash

i been googling for past few days, and been trying different commands regarding this without /bin/bash with bash but nothing seems to work which is due to my lack of knowledge with

containers / docker itself

[gleaming lotus]

You don't have to do that much, I can try and figure it out on my own

[fathom star]

What's the deal with SQLAlchemy's internal typehints? Is it just not typed strongly? For example, I have:

def create_user(username: str, password: str) -> User:
    with Session(engine) as session:
        user = User(username=username, password=password)
        session.add(user)
        session.commit()

and User just has *args, **kwargs so I have no information about it's type. Having that intellisense would be helpful

And with select:

def get_all_users() -> list[User]:
    with Session(engine) as session:
        stmt = select(User)

stmt is just Any so I have no idea what I'm doing with it

[paper flower]

I think it depends on your ide but it generally needs some help with any queries you do

For example in select you can select text or cast field to another type, you get the idea, it's not that easy to type hint

[fathom star]

🤔 Hm alright. So I've got to do that manually?

[paper flower]

But orm models should work fine:

user = User(id=user_event.id)
reveal_type(user) # note: Revealed type is "db.models.User"

[paper flower]

for selects, yes

for session.get too

No excuse for that though ^

Seems like Session.get is type hinted correctly in stubs package though https://github.com/sqlalchemy/sqlalchemy2-stubs/blob/main/sqlalchemy-stubs/ext/asyncio/session.pyi#L126

[delicate field]

sqlalchemy-stubs/ext/asyncio/session.pyi line 126

) -> Optional[_T]: ...```

[fathom star]

I don't think I have this installed, unless it comes as part of the sqlalchemy package

[paper flower]

It's not and it seems to have it's own problems 😅

[fathom star]

Ugh. I've been going back and forth between using Nodejs and Python for my backend. Python seems to have better request validation with FastAPI (I really need request validation), but Node seems to have bettern ORM support. (I'm really familiar with Prisma and like it, too). Downside is that Node's request validation isn't the best, and SQLalchemy just isn't clicking for me.
So I'm not really sure on what to do, to be honest

[willow moon]

Has anyone here worked with Oracle APEX? I'm trying to create a Master-Detail page for an application. When I try to select the Master table, it comes up with an error.

What does that mean? Does it have to do with constraints on the table?

[paper flower]

You could try something like nestjs for node, what's wrong with sqlalchemy though besides type hints? 🤔

[west wharf]

hello, anyone working at IT company using NoSQL database? i want to ask for school task

[cedar tiger]

I have a list of IDs in a text file and I wanna check the details of each ID loaded via psql.

I can extract and output SQL from psql and \copy it to a CSV or TXT file but I don't know how to load the txt file into the psql select statement

Not as an import but as to select * from table where index = (list of ids from txt file);

Something like that

[paper flower]

You want to select some rows by list of ids?

you can use in operator if that's the case:

select * from table
where id in (1, 2, 3, 4);

[cedar tiger]

Ah yes thanks. Now how do I load the content from a text file inbetwen the brackets there?

The text file contains 4400 ids that I wanna search

So assuming the text file looks like

1
2
3
4
5
6

How do I load those up to the sql statement?

[paper flower]

That's up to you really 😉

You can parse that file as a list on numbers, as of putting that into sql query - not sure, I mostly use sqlalchemy to build queries

[cedar tiger]

I decided to just edit the text file and include select * from table where id in ( and place the ending ); after that.

Then load it with psql -f file.txt

[unkempt prism]

Next time you can try to upload it to a temp table using \copy and then just use the temp table as part of your query.

something like the following in a sql file called by psql -f like you did.

CREATE TEMP TABLE temp_interesting_ids (
id int
);

\COPY temp_interesting_ids(id) FROM ./ids.txt DELIMITER ',' CSV;

select * from table where id in ( select id from temp_interesting_ids);

[spark pike]

I want to have as an output for each id: the best price for each outcome, the title connected to the outcome and to exclude all keys "h2hlay".
So as an example for the first id i want the output:
title: Marathon Bet, name: Bayern Leverkusen, price: 8.2
title: Marathon Bet, name: Bayern Munich, price: 1,38
title: Pinnacle, name: Draw, price: 6.45
http://json-parser.com/84ad99cf

[true island]

Next time you can try to upload it to a temp table using \copy and then just use the temp table as part of your query.

something like the following called by psql like you did.

CREATE TEMP TABLE temp_interesting_ids (
id int
);

\COPY temp_interesting_ids(id) FROM ./ids.txt DELIMITER ',' CSV;

select * from table where id in ( select id from temp_interesting_ids);

[sharp socket]

With SQLAlchemy, I have a basic table with one-to-many relationship relationship('VideoClip', backref='origin'). However I would only like to populate with those records that have a column that's deleted=False. Is that possible?

[torn sphinx]

Hello, I need help with request status code 429 in aws(amazon web services) / cloudfront
I tried to print the headers, and if randomize sleep it's will end up with block, I want to know the specific amount of how long time I have to sleep..
Output:

{'Server': 'CloudFront', 'Date': 'Tue, 27 Sep 2022 13:48:12 GMT', 'Content-Length': '188', 'Connection': 'keep-alive', 'Content-Type': 'text/plain', 'X-Cache': 'Error from cloudfront', 'Via': '1.1 397f210a9eb9ec34ba3f1f814bc1a7a2.cloudfront.net (CloudFront)', 'X-Amz-Cf-Pop': 'AMS1-P2', 'X-Amz-Cf-Id': '06LZjQ7t9v6RbIkiAjlaEoY1c2klERyzQWnxAuPFe1OWF38ria5pYA=='}

--ping me when you replay.

[topaz walrus]

Hi, sqlalchemy question here - I'm wondering if anyone knows how to catch an asyncpg error that's wrapped by an sqlalchemy error? eg.

sqlalchemy.exc.IntegrityError: (sqlalchemy.dialects.postgresql.asyncpg.IntegrityError) <class 'asyncpg.exceptions.NotNullViolationError'>: null value in column "shop_id" of relation "shops_users" violates not-null constraint

This falls through to the exc.SQLAlchemyError

try:
  // sql query
except asyncpg.NotNullViolationError:
    raise CustomError("can't be null blah blah")
except exc.SQLAlchemyError:
    raise

[high bone]

I have a weird need. I start with a Postgresql database dump that I need to load into a sqlite database. This answer - https://stackoverflow.com/questions/6148421/how-to-convert-a-postgres-database-to-sqlite - seems to imply that you can just edit the dump to get a sqlite-parseable text, and load that in. But it seems that this approach does not work for me, so the dump might not be in the right format. Anyone has any ideas or tools to achieve this conversion?

[dense barn]

How would i fetch the most repeated row from a table in postgres?

[solid swift]

Hello, I'm about to put variables in mysql but how do i do that?

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='devicedb',
                                         user='root',
                                         password='')

    if connection.is_connected():
        db_Info = connection.get_server_info()
        print("Connected to MySQL Server version ", db_Info)
        cursor = connection.cursor()
        cursor.execute("select database();")
        record = cursor.fetchone()
        print("You're connected to database: ", record)

except Error as e:
    print("Error while connecting to MySQL", e)

finally:
    if connection.is_connected():
        cursor.close()
        connection.close()

How and where do i Insert the Variables?

[fading patrol]

This should get you most of the way there: https://stackoverflow.com/questions/8129441/how-to-count-duplicate-rows

[dense barn]

yup, thanks

[fading patrol]

This may help: https://pynative.com/python-mysql-insert-data-into-database-table/

[lean plover]

I am trying to use the nolock option when connecting to an sqlite db with flask-sqlalchemy. I tried to do this by adding this to the app config: py config_dict['SQLALCHEMY_ENGINE_OPTIONS'] = {'nolock':True} ...but I am getting this error: ```
Invalid argument(s) 'nolock' sent to create_engine(), using configuration SQLiteDialect_pysqlite/NullPool/Engine. Please check that the keyword arguments are appropriate for this combination of components.

Which is odd because nolock is an option used in the sqlite3 documentation for how to pass variables to the sqlite connection

[opaque wave]

hey people, I have a query in bigquery that essentially boils down to aggregating metric A grouped by x, y, z
I want to get the aggregated metric A grouped by x and grouped by x, y as well
what is the best approach? temp tables and join?

[lunar pier]

Using sqlite3 with bot for ~30 users, how bad or good is this way to handle connections?

[brazen charm]

looks mostly fine, you probably want to make the commit() be part of an else block if we're being nit picky. Because you're essentially doing a useless op, as everything will have been reverted by the rollback

[slender atlas]

A discord.py bot? You should use an asynchronous library, because discord.py is

[brazen charm]

for 30 users, you can largely get away with it

worst case you make it an actor

Only thing I would say though, is only make the DB manager once, and keep it in your Database class

and just re-use it, rather than opening and closing each time

[stoic gulch]

does anyone know the best way to download SQL/MySQL?

[brazen charm]

Any particular reason why you want MySQL?

[stoic gulch]

I want to make a database

[brazen charm]

Use Postgres over MySQL

you will have a better time

https://www.postgresql.org/download/

[stoic gulch]

It's mainly to hold financial data

thnks!

[stoic gulch]

wait so postgressql is more effective than mysql?

[stoic gulch]

not that im against your help

but why is it a better time?

[brazen charm]

Generally more effective, more advanced, allows you to do more, better tooling, and isn't managed by Oracle with shitty licenses

A long with specifically in python, better drivers like AsyncPG and psycopg

[stoic gulch]

nice thanks again

[lunar pier]

No, it's aiogram. I tried to use asyncsqlite, but it's not needed in my case with 30 users :)

[lunar pier]

Thanks! Did it this way :)

[quasi quest]

this should work

@bot.command()
async def remove_bday(ctx, member:nextcord.Member):
    member = ctx.author
    async with aiosqlite.connect("bday.db") as db:
        async with db.cursor() as cursor:
            data = await cursor.fetchall()                                   
            await cursor.execute("DELETE FROM bd WHERE id = ?", (member.id,))
            
            await db.commit()

[lunar pier]

I made this abomination while trying to understand aiosqlite

[brazen charm]

Personally Im not a huge fan of using aiosqlite if your at a level where you need it to be async to avoid problems

at that point I just say use postgres, it's just easier with asyncpg

[lunar pier]

Only reason - i don't want to change all of my code, i had just 4 functions for db before, now i have a little bit more knowledge about OOP, so making simple class is enough :)

Yeah, it was huge mistake to use sqlite for this project, but anywa, there's only around 2000 rows and ~25 columns in main table :))

[quasi quest]

ye, I usually use sqlalchemy

[lunar pier]

sqlalchemy too hard for me rn, tried several times, still doesn't understand how to work with it.

[quasi quest]

the delete function doesn't seem to work...it deletes the first row in the table

[quasi quest]

its better than sqlite

[lunar pier]

sqlalchemy is ORM, not database as i remember

[quasi quest]

yep

[lunar pier]

you mean better than python sqlite3 library?

[quasi quest]

ye, the library

sqlalchemy and sqlite3 is decent

[lunar pier]

oh, of course, sqlalchemy is one of those things that i will definately use for my future projects if i'm going to make them on SQL databases :)

[quasi quest]

cool

[lunar pier]

But i somehow managed to setup mongo in docker on my server, so... idk

[grim vault]

It should delete all rows with that id. And what is the .fetchall() for? You don't select anything before that command.

[quasi quest]

yes, the data is irrelevant for now but as you said it should delete the row with the discord id

unfortunately it deletes the first row

[quasi quest]

nvm I got it myself

I mixed up the guild and member ids

[paper flower]

You don't have to use orm component if you don't want to

Also mongo is a trap :3

[lunar pier]

Why? Mongo did something hard to understand with their security, but mongo can hold lists and looks like json, i think this hard times configuring and understanding security worth it

Ye, we have redis also, but it's still a bit of challenge to understand, how to make redis save to disk after every query and why

[fading patrol]

Not speaking for doctor, but while it seems easy to dump unstructured data into Mongo, you may regret it later unless you really and truly have no need for structured tables... Mongo can be a trap in that sense

[brazen charm]

Mongo is great, for quick and dirty prototyping

but outside of that, I find it's a total footgun because it doesn't have a basic schema

and oml if you have ever had the pain of a little piece of code inserting a number a string vs a int like everything else... the pain is very real

because instead of erroring like basically any SQL db, or wide column, it'll just accept it

[minor ruin]

You don’t. Redis is designed for caching even if certain discord servers refuse to believe it.

[torn falcon]

So I have setup a few rudimentary "databases" in a project. Essentially my application creates CSV files, and I collect those and look at them via pandas. I think my ideal database is more "relational" like SQL. I like pandas because I can also look at things in real time and filter it like that, but do people do the same with SQL? Live, interpreted? Is converting my frameworks over to SQL something I should consider?

[fading patrol]

If this works well for you, don't complicate things for no reason. What problem would you hope to solve by using a SQL db?

[torn falcon]

I see your point. There's a few reasons. 1) I wonder which method is better to learn/become familiar with, especially when it comes to parsing these databases in a script or live command line way. 2) There are some drawbacks to my method, each row has redundant information in it because I'm logging a bunch to a CSV file instead of relating data to arrays of rows.
So yeah CSVs work for me right now, but maybe SQL a thing that is more productive in the long term

[fading patrol]

In that case I would experiment a bit with SQLite in place of csv files. If you're able to benefit from that you might consider moving on to Postgres before you go all in.

[pure frost]

hey, anyone got an idea how asyncpg's record_class works exactly?
I made a class derived from it, but the values I got back from fetch still seem to be pure Record's

[quasi mountain]

guys

[shut trellis]

Hi i'm getting this error Could not process parameters: str(Valley), it must be of type list, tuple or dictfor the following line mycursor.execute("UPDATE customers SET address = %s WHERE address = %s", ("Valley", "Canyon 123"))
How to fix it pls ?

[delicate field]

Hey @worthy stone!

It looks like you tried to attach a Python file - please use a code-pasting service such as https://paste.pythondiscord.com

[pure frost]

What library are you using?

[shut trellis]

mysql connector

[tawny dew]

~~Hello! I'm attempting to make a relation between two models using tortoise-orm.

I have a User model:

class User(models.Model):
    class Meta:
        table = "users"

    class PydanticMeta:
        computed = []
        exclude = ["two_factor"]

    id = fields.IntField(pk=True)
    username = fields.CharField(max_length=64, unique=True, null=False)
    email = fields.CharField(max_length=256, unique=True, null=False)
    password = fields.TextField(null=False)
    two_factor = fields.TextField(default=None, null=True)
    verified = fields.BooleanField(default=False)
    avatar_hash = fields.TextField(default=None, null=True)
    banner_hash = fields.TextField(default=None, null=True)
    created_at = fields.DatetimeField(auto_now_add=True)
    pronouns: fields.ReverseRelation["Pronouns"]

    def __str__(self):
        return self.username

and a Pronoun model

class Pronouns(models.Model):
    class Meta:
        table = "user_pronouns"

    class PydanticMeta:
        computed = []
        exclude = ["user"]

    value = fields.CharField(max_length=32)
    preferred = fields.BooleanField(default=False)
    user: fields.ForeignKeyRelation[User] = fields.ForeignKeyField("models.User", related_name="pronouns", to_field="id")```

I've also used the below code to generate the proper Pydantic models that include the relations
```Python
Tortoise.init_models(["api.entities.users"], "models")
UserIn = pydantic_model_creator(User, name="UserIn", exclude_readonly=True)
UserOut = pydantic_model_creator(User)

However, whenever I run user = await User.get().prefetch_related() and then return await UserOut.from_orm(user) I get a NoValuesFetched Exception like so: NoValuesFetched: No values were fetched for this relation, first use .fetch_related()

Both tables have values. I appreciate any help I can get! First time using an ORM despite programming in python for 5 years lol~~

[tawny dew]

Nevermind, should have been using from_tortoise_orm.
However, if someone does have proper tips for Relations with tortoise that would be appreciated, something isn't quite clicking for me.

[shell gale]

hello, can i ask how to check first data if exist before inserting ?

[exotic stream]

What do you mean? your question is kind of unclear

[fading patrol]

Just do an appropriate SELECT query.

[fossil vale]

Does anyone know about elastic search?

I need some technical help for the project.

[brazen charm]

what are your question?

[fossil vale]

We have records taken every 30 seconds. Over the past few years we have a quite a few built up. Creating a windowing search on those records based on some interval of time.
For Example:
1.User inputs 15 minute increment between records based on current time: Time now: 8:53 (next 15 would be 9:08, 9:23, etc) within 1 hour.
2.User wants records between two dates (could be as big as 3 years of records at a 15 minute increment).
3.The records are not guaranteed to be exactly at those times but more like a maybe... we need to return the closest record to each 15 minute window of time.
4.There are several other query additions to filter these records by but the main issue is the problem above.
I have already created something that meets this goal but it likely can be refined and more performant. Also the way the UI queries and applies the incremented records can likely be improved

tell me if you have any idea for a solution?

[brazen charm]

What sort of data is this?

[finite tiger]

Can I use PostgreSQL with Heroku? Not the Heroku PostgreSQL though

[fossil vale]

document

[brazen charm]

I mean is it things like Numerical data which you're just performing aggregations on, or are you actually using the FTS

[fossil vale]

Just a json object. everything has a timestamp on it though

[brazen charm]

Do you have an example object I can see 😅

[fossil vale]

{ timestamp: 1234567890 }

The part that matters in creating this query is the unix timestamp

All the other fields are just application specific data

[torn sphinx]

guys

how can i view my table in sql

i have made this code where it accepts data from user and ended with commit code

i tried using select* from details

details is my table name

but its showing empty set

@brazen charm can u help me out bro pls

i posted in questions but nobody replied

:/

my aim was to accept data from user using a normal input box in python and save it to a database named bank which contains a table named details with 2 records , name and pin

and the pin must be 4 digits

thats it

[brazen charm]

My point was more the fact that your documents size and type of data can make a world of difference and affect whether Elasticsearch is actually a smart choice or not 😅

If your application is only aggregating and filtering and doing these windows than Elasticsearch is simply trying to hammer a nail with a sledge hammer, you would be better off with Clickhouse which will make you app faster and easier to perform these windows.

If you're performing additional text searches on the data then it's fine, but outside of doing something like a histogram aggregation with a fixed interval, it probably can't be improved hugely if you're already doing so. -> Extending this I can't remember whether you can convert to a date on the fly, but I guess it doesn't matter a whole lot.

[brazen charm]

The database in general isn't going to lie to you.

So you either have the following situation:

• Your app isn't saving the data correctly.
• You're looking at the wrong table.
• You're looking at the wrong DB.

[torn sphinx]

yeah nvm i fixed it

i forgot to execute

i forgot to put con.execute()

also

now i have to make the programn menu driven

set 2 IF statements , one for new users which i have completed and one for old users where my programn matches the Pin entered by the user to all the pins existing in the database

if true then it shows a welcome statement

i guess this stuff is too easy for u guys im sorry if im annoying but i need to make this project for credits lol

[fossil vale]

Not migrating to another DB solution

There are other things going on. The ask is only for this windowing search

[torn sphinx]

wdym?

where can i ask database related questions then bro

ik its too annoying but can someone pls help me out fr i really need grades and i cant find shit online

[brazen charm]

they're not talking to you dw

you're in the right place

[torn sphinx]

oh ok

my bad bro

[brazen charm]

In that case use a histogram aggregation, which should do what you want?

[cloud path]

How can i sort by array size in mongodb?

[fossil vale]

What about the way the UI queries and applies the incremented records?

[brazen charm]

You should be able to use the $size operator on the array as a field and then sort by that field.

[brazen charm]

Can't really provide any help on that because I don't know what you're currently doing to say if it's any good or not

[torn sphinx]

man can anybody help out a beginner 😢

is there any way i can save stuff permanently in a list?

like how exactly can i use membership operators to match records in a table in sql

[fossil vale]

I really appreciate your help. Thanks.

[fading patrol]

If I understand the question, you just need a basic SELECT ... WHERE ... query. If you're still stuck, show us some code and what the problem is

[nova path]

Hi!

I have a problem with Python.

I'm taking columns from an .xlsx table and creating other tables using the data from these columns. But how can I make each cell fit the text?

The tables have some incorrect spaces

[lean plover]

I am trying to use sqlite database on a mounted CIFS share which apparently requires using the nolock sqlite option. How can implement that option when using flask-sqlalchemy (or just sqlalchemy) ?

If I append ?nolock=1 to the uri, I get (sqlite3.OperationalError) unable to open database file.

[fading patrol]

An example might help but if it's a simple question of a string having an incorrect number of spaces I would use re.replace or something to fix it

[rustic elbow]

Hi i'm trying to use alembic
when i'm running alembic revision --autogenerate -m "Test"
it's saying that

    from models import Base
ModuleNotFoundError: No module named 'models'```
How do i fix it?

[torn sphinx]

I want to make a data breach search engine for my Email service, i want to store 20 billion records in a SQL server which will be indexed with a email the user will give when using the email service, and if the email is in the records it will return something.

But i do not know how i would store the records in the SQL database, i have 0 knowledge with SQL.

[brazen charm]

I probably wouldn't use an SQL server for this

This is probably more suited to either a search engine I.e QuickWit or Elasticsearch. Or a NOSQL system like Cassandra/Scylla.

it'll make your life scaling and dealing with resource management considerably easier

[harsh pulsar]

postgresql and sqlite do have "full-text search", but it won't be as configurable as elasticesearch or something else purpose-made for searching text

that said, for a prototype or proof of concept, postgres might be good enough

[keen minnow]

Do they really need a full-text search? Since it's for data breach existence, they could get away with a simple kv approach.
Regardless, any SQL db should able to handle that, be it mysql or postgres

There is also a question of whether or not they want to store the emails or their hashes, in case themselves get breached

[tame quarry]

Create an empty __init__.py file in the alembic folder

[zenith flame]

Any King of SQL query ?

[slim trench]

Hi, I got this error when I try to run the project.

configuration file:

[postgresql]
host=localhost
dbname=sloby
user=sloby
password=sloby
should_initialize_database=True

connection:

  self.conn = connect(
                host=conf['host'],
                dbname=conf['dbname'],
                user=conf['user'],
                password=conf['password'],
                )

[slim trench]

Any tips? Thanks

[fading patrol]

It's telling you the password is wrong. Did you set it yourself on the Postgres side?

[slim trench]

what do you mean by "postgres side"?

[fading patrol]

You are apparently running a Postgres DB server on your local machine. You need to create the user and set the password. If you didn't do that, this is the cause of the error

https://stackoverflow.com/questions/12720967/how-can-i-change-a-postgresql-user-password

[slim trench]

I created an account before, so I could run it with a remote server, but I just changed it to localhost, and now should I do this thing again?

[fading patrol]

Yes, every instance of Postgres will have it's own local user accounts,.so if you changed server you need to create the user there

[slim trench]

and should I do it via the postgre terminal?

[fading patrol]

Yes, and if that fails edit the conf file per the link above

[slim trench]

I already created the config file, so in the SQL shell, I just have to do the same things with the same information(what I wrote into the config file)?
So I don't have to do the project creation thing again?

[fading patrol]

The config file you shared is telling your script what details to use to access the server. That's fine as long as your server is actually configured that way.

You said you just switched server to localhost, you haven't said that you created a user or set a password on that local instance of Postgres... I'm assuming this is the problem

[slim trench]

Thanks, yes I just switched, and now I have to create a new user for the localhost right?

[fading patrol]

Correct

[slim trench]

Thanks, I've tried it, and I getting almost the same error in the shell, can the username and the password be the same?

@fading patrol or is this the case when I should try to password changing thing?

[slim trench]

When I used elephantsql I didn't have to do this because the elephantsql created an instance for me, right?

[fading patrol]

Yes, you clearly need to change the password

[slim trench]

why should I change the password, because I never created one(in the localhost)?

[fading patrol]

Then you clearly need to create the user.

[slim trench]

yes but I can't, I got the error, do you have any tips? I had the same problem before.

[fading patrol]

The stack exchange link I shared earlier addresses this

You need to edit the .conf file for Postgres itself (not the one your Python script is using

[slim trench]

So in the sql shell,(I think)?

[fading patrol]

No, I thought you said you can log in there. Read the link please

[slim trench]

will do! I just don't understand why should I change something, that I never used before.

[wraith adder]

I don't know if there are SQL pros here, in case there are:
if a query starts with SELECT , it's always read , right? (the database can't be changed with a query that starts with SELECT )

[storm mauve]

not necessarily
SELECT INTO is a thing

[wraith adder]

ok, is there any other query that starts with select and has the capability to edit the database ?

[storm mauve]

if you're thinking about something safety/security related, make as few assumptions as possible, and use as many existing features to control permissions as possible instead of trying to add your own layers

[storm mauve]

select * from idk; drop table idk;

if possible, restrict what the query can do based on strict permission systems provided by the database, do not try to do it yourself based on the query's content

[wraith adder]

aow, aight aight , thankss

[silk lantern]

At #GoogleCloudNext let’s modernize, build, model, analyze, operate, secure, collaborate, and
innovate—together. Join experts, October 11-13, to learn and grow together to meet tomorrow head on.

Register today, https://goo.gle/3xnxi2q

[potent nacelle]

How big is an sqlite database going to be in MB if I have maybe like 50 things with 50 values each if that makes any sense

[storm mauve]

1 table with 50 rows x 50 columns? depends on what are these 'things'

if these '50 values' are all integers or booleans, almost definitely under a 1MB
if each of these '50 values' are BLOBs of huge images, each of which take multiple MBs on their own, quite a lot

[potent nacelle]

Just strings and integers

Thank you for answering the question, I just didn't know whether or not I should be worried about the file size

[upper temple]

How do I get a variable from mongodb without knowing the text?

[fading patrol]

The question isn't so clear to me. Is it that you want to search all values across all documents and keys for a certain string, or what? And you need to do this with mongodb itself or pymongo or what?

[soft gorge]

Hello does anyone know why when I try to use mysql.connector I am not able to use the cursor.executescript() method?

[nova forge]

Is it good to use pool object throughout program cycle?

Or should we renew it?

Am using aiomysql

[paper flower]

You should only make one pool, it pools mysql connections so they could be reused

You generally need only one

[nova forge]

Umm yeah am using the same pool for the whole program

[fringe sundial]

Guys do delete query in aiosqlite deletes all same records? Or it delete only 1 of them?

[ionic pecan]

Depends on whether you specified a WHERE condition or not

And what you put into the WHERE condition

[young vigil]

Intending to make a project using an sqlite3 database of IMDb titles, stored in imdb.db
My main.py script is within the same folder as imdb.db
Trying to use Cinemagoer for glue/abstraction between the database and the things I'll write myself.
I'm stuck at the part where I open the db connection in my main.py script (using relative path) - the Cinemagoer example page use postgresql instead of sqlite3, and I can't find any Sqlite3 examples by searching either.
Valid Sqlite3 URL forms:
sqlite:///:memory: (or, sqlite://) - I can't use this one due to large db size.
sqlite:///relative/path/to/file.db - This is the one I'd like to use.
sqlite:////absolute/path/to/file.db - I'd rather not hardcode an absolute path.
main.py:

from imdb import Cinemagoer

ia = Cinemagoer('s3', 'sqlite:///imdb.db')

results = ia.search_movie('the matrix')
for result in results:
    print(result.movieID, result)

Results:
(.venv) C:\Users\Mope\Dev2022\1>c:/Users/Mope/Dev2022/1/.venv/Scripts/python.exe c:/Users/Mope/Dev2022/1/main.py
Traceback (most recent call last):
File "c:\Users\Mope\Dev2022\1\main.py", line 5, in <module>
results = ia.search_movie('the matrix')
File "c:\Users\Mope\Dev2022\1.venv\lib\site-packages\imdb_init_.py", line 444, in search_movie
res = self.search_movie(title, results)
File "c:\Users\Mope\Dev2022\1.venv\lib\site-packages\imdb\parser\s3_init.py", line 238, in _search_movie
tb = self.T['title_basics']
KeyError: 'title_basics'

Edit: Python formatting got weird

[tight junco]

how do i erase all my data stored in my mongodb

im using motor if that helps

[slim trench]

I checked this link, and it sounds like I can rename it after the user creation, another way I cannot access it??

[slim trench]

I'm still suffering with that someone have any tips?

in pg admin

okay so it looks like I have to change the password(pg_hba conf file)

[fading patrol]

This is the equivalent of the config file you showed earlier... Changing this will only change how you are trying to connect, not the actual username and password your database is looking for

[fading patrol]

Exactly

[slim trench]

but where can I find it? it seems like the path has changed.

[slim trench]

Yes, but the question is if I switched and didn't create a new user, then why should I change it? there was a super user from the past? Or what do you think about that?

[fading patrol]

Did you ever create this user on your local instance of Postgres, and if so did you set the correct password? If so then you shouldn't be getting the error you're getting. If fixing the correct .conf file doesn't solve the issue I'm out of ideas.

[slim trench]

I had a few projects before and I used local instances but it should be a different database with different users right? I didn't try the conf file things because I couldn't find it. Or just one local user for every local instance?

[fading patrol]

You can only have one local instance of Postgres unless you're using docker or VMs or something, but you can use multiple users if you want, your choice

[slim trench]

So for example, if I have a local user then I can use it with every database?

and do you have any idea where can I find that file? the path has changed.(what I saw in the StackOverflow)

[fading patrol]

A Postgres user on your local instance of Postgres can access any database it has permission to access

[fading patrol]

What OS?

[slim trench]

windows

[fading patrol]

I've never used pg installed on windows, see if this helps: https://www.postgresql.org/docs/current/libpq-pgpass.html

[slim trench]

I'm not sure this is what I need...

[jade current]

not used this framework so curious by the snippet, is this not open to sql injection?

[slim trench]

now I created a new user, and getting this error when I am trying to create a new instance. do you have any tips?

[slim trench]

@fading patrol thanks now everything is working. 😄

[waxen finch]

their snippet shows the query is supplied as an argument for sqlite to run, so SQL injection would require the program to be formatting user input in whatever query strings are provided
in other words, by itself it wont do harm but you can create vulnerabilities by misusing the class

[young vigil]

I've now also tried with absolute path, with no luck.
If anyone has ideas, please ping. In the meantime I'm turning off the PC for a moment to try and clear my head.

[rugged swift]

Hi, I am trying to connect mysql with python on jupyter notebook using sql.connector (which i have to use), however i am getting an error. I will share the ss of my code as well as the error, it would be great if someone could help me rectify the error

[grim vault]

I tried it out and it worked for me. You sure your imdb database is correct?

$ sqlite3 imdb.db
SQLite version 3.39.3 2022-09-05 11:02:23
Enter ".help" for usage hints.
sqlite> select name from sqlite_master where type = 'table';
name_basics
title_akas
title_basics
title_crew
title_episode
title_principals
title_ratings
sqlite>```

$ ll
total 11362889
-rw-r--r-- 1 Gazelle 197121 11635597312 Okt  1 23:08 imdb.db
-rw-r--r-- 1 Gazelle 197121         177 Okt  1 23:14 main.py
$ cat main.py
from imdb import Cinemagoer

ia = Cinemagoer('s3', 'sqlite:///imdb.db')

results = ia.search_movie('the matrix')
for result in results:
    print(result.movieID, result)
$ python main.py
13714430 The Matrix
11089880 The Matrix
9851526 The Matrix
9642498 The Matrix
6464836 The Matrix
133093 The Matrix
820928 The Meatrix
10838180 The Matrix 4
242653 The Matrix 3
234215 The Matrix 2
117021 The Matrix 2
6016218 The Mutrix
4730778 The Metrix
7247178 The Matricks
370942 The Matrices
21158648 The Matrix
21051632 The Matrix
14788064 The Matrix
13809214 The Matrix
13218800 The Matrix
$ ```

[young vigil]

Thank for checking. I can open the database just find and run queries using a GUI program (db browser for sqlite), but when I try your steps in a terminal to see if the database is ok, I get an error.
First I make sure that I'm in the correct directory where the imdb.db is, then I try running your command '''sqlite3 imdb.db'''

os.listdir()
['imdb.db', 'movies.db']

sqlite3 imdb.db
File "<stdin>", line 1
sqlite3 imdb.db
^^^^
SyntaxError: invalid syntax

I am creating the imdb database again, in case there is something wrong with it (though creating it initially gave no errors, and I can use it with with a GUI program) - but it will take some time.

[grim vault]

I used the shell command sqlite3 not the python module.

[young vigil]

cmd or powershell?

or Linux, probably.

[grim vault]

You can check with the db browser, just switch to the datavase tab or SQL and execute ir.

I'm using a the git bash on windows.

That's how my imdb.db looks like.

[young vigil]

This is mine, quite different....

[grim vault]

Ok, so your problem is the different table names, that's why it fails.

[young vigil]

So yeah, my db is borked then. Thanks for helping sort it out.

I'm going to let this script create a new database and see how that turns out.

[grim vault]

The table names are based on the names of the downloaded files, so you'll need to check that too.

[young vigil]

I've left them with the default names for now, don't remember what I did the first time, but think it was default then too.

[grim vault]

Mine look like:

Gazelle@DOSE MINGW32 /d/Downloads/imdb/2022-10-01
$ ll -h
total 1,2G
-rw-r--r-- 1 Gazelle 197121 224M Okt  1 19:56 name.basics.tsv.gz
-rw-r--r-- 1 Gazelle 197121 271M Okt  1 19:58 title.akas.tsv.gz
-rw-r--r-- 1 Gazelle 197121 155M Okt  1 19:57 title.basics.tsv.gz
-rw-r--r-- 1 Gazelle 197121  60M Okt  1 19:56 title.crew.tsv.gz
-rw-r--r-- 1 Gazelle 197121  37M Okt  1 19:55 title.episode.tsv.gz
-rw-r--r-- 1 Gazelle 197121 394M Okt  1 19:58 title.principals.tsv.gz
-rw-r--r-- 1 Gazelle 197121 6,1M Okt  1 19:55 title.ratings.tsv.gz```

[young vigil]

Yeah, same on my end. Hopefully that means it will work this time. :)

[grim vault]

It took a while on my PC to build the DB >1 hour (on an NVMe)

[young vigil]

When I tried this a couple years back (when it was imdb2py instead of cinemagoer) it took 4+ hours. I have a faster PC now (m.2 drive), so hopefully it isn't as slow.

[grim vault]

I used s32cinemagoer.py 'D:\Downloads\imdb\2022-10-01' 'sqlite:///imdb.db'

[young vigil]

For the record, I ran (.venv) C:\Users\Mope\Dev2022\1>C:\Users\Mope\Dev2022\1\s32cinemagoer.py downloads\ sqlite:///imdb.db
It's been running for 20-30 minutes with no errors so far, but no progress bar either. I'll probably go visit my brother for an hour or so while it runs.

It just finished with the name.basics.tsv.gz now, and moved on to title.akas.tsv.gz

[tulip fox]

how to create column in the db? 🤔

(in an already existing table)

sqlite3

[grim vault]

To add a column in sqlite3 you would do soemthing like: ALTER TABLE tablename ADD mynewcolname INTEGER;
See https://www.sqlite.org/lang_altertable.html for a complete description.

[tulip fox]

Thanks!

[young vigil]

The database has been re-made, and now it halfway works. Script and output:

Database structure:

Hmm, I'm missing one of the tables and one of the indices, compared to Berndulas' db structure. Dang it.

Why the hell did it skip the title.crew.tsv file for me? And why did it not throw errors because of it? Bah.

Third times the charm? Running the script that compiles the files into an sqlite db again now, but with --verbose flag, which gives me a progress bar and hopefully some more information.

[half sky]

Hi, I'm working on a chat system (in game, visual novel) and I want to know how to organize the structure (storing data, accessing it, modifying it, etc.).
I have a program, for example Viber, in which there are several chats, in which there are many messages of different types (pictures, audio, text, text in the center, status, choices).

Here is a small part of the code I am using:

time = 0
class Chat:
    def __init__(self, name, img = None):
        self.name = name
        self.img = img
        self.msgList = []

    def msg(self, who, icon, what):
        self.msgList.append({
            "type": "msg",
            "time": time,
            "icon" : icon,
            "who": who,
            "what": what})

    #def imgs...
    #def choices..
    #def text...
    #def statu...

viber = Chat("Viber")
viber.msg("Someone", "Icon1", "Help me pls :D")

print(viber.msgList)```
Output:  ```python
[{'type': 'msg', 'time': 0, 'icon': 'Icon1', 'who': 'Someone', 'what': 'Help me pls :D'}]```
Output example of a usialy variable:```python
{ChatAppName : {ChatName : [{'type': 'msg', 'icon': 'icon', 'who': 'He', 'what': 'Apple'}]},
                            {'type': 'msg', 'icon': 'Icon2', 'who': 'Me', 'what': 'Orange'},
                            {'type': 'msg', 'icon': 'icon3', 'who': 'She', 'what': 'WTF?'}}```
And so I was wondering what other ways there are. Or is it ok to store a lot of data in a dictionary?

[grim vault]

Did you unpack it? The script needs the names to end in .tsv.gz

[young vigil]

No, didn't unpack any of them. Running with --verbose now, and it has done title.crew.tsv this time, so hopefully it will work.

[blissful kite]

Hey I would like to improve my program and wanted to ask if there is an efficient way in python to determine if a file was created by a user or created by for example the operating system/software to ignore in the next step...?

[young vigil]

@grim vault The script finished running and added all 7 .gz files to the sqlite db this time, and now I don't get any errors when running the example query. Many thanks for the help.

[potent nacelle]

In sqlite how would I insert a value into a variable name database? So like

table = input("Which table to gather data from?")
cursor.execute("INSERT INTO table VALUES (data)")

[waxen finch]

do you mean how would you pass some input as one of the values for an insert query? or do you mean the table name changes depending on something else?

[potent nacelle]

I mean whichever table is edited changes depending on user input

[waxen finch]

sqlite doesnt allow parameterizing the table name so your program will have to change the query string to whatever table it wants to insert to - in other words, string formatting

e.g. py allowed_tables = {'abc', 'def', 'ghi'} if table in allowed_tables: cursor.execute(f'INSERT INTO {table} VALUES (...)')

of course you need to make sure you dont allow arbitrary text to go there

[potent nacelle]

Ah I see

Thanks for helping me

[dusky steppe]

So it looks like Tortoise ORM doesn't have one-to-many relations built-in, which surprised me. Is there a way to recreate this, or do I need to migrate to another ORM to have access to this?

[wise goblet]

One to Many relationship is having Foreign Key from second table to first one the rest is syntax sugar. Never used Tortoise ORM, but it should be really having Foreign Keys for tables if it is claiming to be ORM for SQL databases

i recommend SQLAlchemy though, as most popularily used and quite advanced in introduced functionality

[dusky steppe]

Yeah i was looking at sqlalchemy recently

[wise goblet]

Usually Python devs in commercial environment are asked to know SQLAlchemy or Django ORM for SQL dbs. Never heard about anything else asked in job positions. So that alone is argument to choose it, because higher chances other devs will be supporting it (because they learned it too for the sake of having known matching ecosystem necessary for jobs)

[dusky steppe]

Good to know, thanks!

I've heard it has a steep learning curve, which is why I went with tortoise orm originally

If you don't think it's too bad, I'll def switch pretty soon then

[wise goblet]

Ergh. It has its quirks. I would have recommended Django ORM for starting people. It has the best level of enjoyment, easy to use, and everything is out of box for it

U can use Django ORM as standalone stuff in other Frameworks too, but better going for Django in this case

It has its own certain limitations when u try to write complex queries though, but in general it has its own stable philosophy/interface regarding that, that with some transformation will fit well for complex stuff too

[stable ibex]

Hello, I'm having problem with an assignment I'm working on.

This is the code that I wrote.

FROM CUSTOMER
GROUP BY REP_ID
WHERE BALANCE = (SELECT SUM(BALANCE)
                        FROM CUSTOMER
                        );

And these are the instructions.

List the REP_ID and the corresponding sum of the balances, as BALANCE, of all customers for each sales rep. Order and group the results by sales rep ID.

I'm note sure if what I am doing is right.

ERROR 1064 (42000) at line 1: 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 'WHERE BALANCE = (SELECT SUM(BALANCE) FROM CUSTOMER ' at line 4

[dusky steppe]

Oh nice, looks like that's async as well

[stable ibex]

SELECT REP_ID, BALANCE
FROM CUSTOMER;

[hasty quest]

hi i need help

meta= MetaData(engine, schema=Table_PlanMuestreo[6])
meta.reflect(engine,schema=Table_PlanMuestreo[6])
pdsql=pd.io.sql.SQLDatabase(engine, meta= meta)
pdsql.to_sql(Tabla,'IDENTIFICACION',if_exists='replace')
Tabla.to_sql('IDENTIFICACION',engine,schema=Table_PlanMuestreo[6],if_exists='replace')```

i get this

    pdsql=pd.io.sql.SQLDatabase(engine, meta= meta)

TypeError: __init__() got an unexpected keyword argument 'meta'```

[lethal warren]

Hi all.

Which way should I go to cross information between two scraped websites data (nested dicts)?
I'm a beginner on programming and have researched a lot, but I can't seem to find a way to corelate both data structures.

[polar eagle]

Maiby it's the gap between meta= meta

[wise goblet]

Don't delete. U a preventing other passive readers from leaving bits or adding important bits

Python is a multi universal tool to do anything
https://github.com/vinta/awesome-python from all fields for which it has libraries essentially. Including dealing with databases at the level of writing SQL ORM code in python syntax.

Raw SQL is still needed being learnt to understand how to write ORM code though

SQL databases and their language are just more advanced alternative to store data in persistent way.
The best one available for generic case, because SQL databases keep data integrity (making sure it has always correct data structure and links between each other)
Plus providing language features which make possible to store the most minimum amount of data repetition. All necessary data can be extracted out of it with sufficiently complex SQL request

There are always alternative to store data just in files in some filesystem or S3 bucket
Or key value dB like Redis
Or JSON storing dB like MongoDB
Or Apache Cassandra
But they have their own specific performance/usage cases when they should be preferred.
They all have disadvantages of not keeping data structure integrity like SQL DBs too

Ensuring that for any random data object like user, is always present related data to profile and etc, queried without any errors

We use Python specific libraries to communicate with databases purely because of easier development and maintaince of result. Less code is written. We have full advantages of dealing with databases table structures as a code in git version controlled way. And well, code is perfectly bridged with our backend applications as the main obviously reason, the rest of stuff is just nice additions

Essentially we often have SQL database just for the sake of having centralised network accessable storage of data when it is present. It makes possible easily deleting and replacing other applications during their upgrades. (Including just destroying servers in the process if necessary, instead of trying to alter their state) (or when we need to scale their hardware resources for example)
Treating most of infrastructure as replaceable cattle.

It is all kind of evolving from Monolith into microservices oriented infrastructure architecture. And event driven programming and applications are also related words. Which are part of code architecture.
All this stuff creates room for performance upgrades to handle millions of users traffic.

[fleet ibex]

SQLite3 ..
So I create my own timestamps for various reasons in my program and I just log them also in sqlite along with sqlite's created at self timestamp.

So question is where can I look to find out why the sqlite self created timestamp is way off.
Time stamps here are mine / sql-created
10/03/2022 05:25:53 2022-10-03 11:25:53

[waxen finch]

https://sqlite.org/lang_datefunc.html

[format] 3. YYYY-MM-DD HH:MM:SS

The date and time functions use UTC or "zulu" time internally ...

so your program is inserting datetimes with an assumed offset of -0600, while sqlite is using +0000

[fleet ibex]

apprently so.. I cant find any reason it's doing it.. the system time is correct using my timestamp.. I cant find anywhere SQLite is pulling the difference from.. nor do I really know where to look past the clock in OS..

[fading patrol]

As was just stated, SQLite uses UTC by default instead of local time, so that's where it's pulling the difference from unless you're not actually on -6h UTC?

You probably should leave it that way but can change it if you want to https://stackoverflow.com/questions/381371/sqlite-current-timestamp-is-in-gmt-not-the-timezone-of-the-machine

[fleet ibex]

otay.. I read up on it now that i knew where to look.. as I expect this program to run im different time zones.. Ill leave it alone and do conversions when the sql timestamps are needed, and contiue to refer to my own as local time.

thx

[valid mortar]

Noob question.
I am studying python and this topic fits my interest. Can someone help me why the code is not returning an output? I am running it in vscode juypter notebook. Thank you so much

https://www.kaggle.com/code/jsaguiar/dataset-update-with-opendota-api/notebook

[quartz island]

Hello. I'm trying to learn MySQL. Im a noob at RDMBS. For some reason I just cannot get the mysql server to start on localhost. While installing MySQL, I opted for manually starting the server instead of auto-start at boot up. And the only way I know to start the server is through services.msc. But when I try to manually start the MySQL server, it doesn't allow me to. Like the Start and other buttons are greyed out. Also when I start the MySQL CLI, It closes right after entering the correct password. Will someone please help me out. Thanks

[small nest]

i want to put values from Tkinter ENTRY using e.get()
but when i do
c.execute("INSERT INTO student VALUES ('{one}','{two}',{three},'{four}')".
format(one=a.get, two=b.get(), three=c.get(), four=d.get()))")
terminal writes
Entry object has no attribute 'execute'

entry is from tkinter i think

help

[lime hearth]

Hi

i need help with sql database

im trying to return whole database from azure

@app.get("/posts")
def root():
cursor.execute("""select * from [dbo].[Post]""")
posts = cursor.fetchall()
return(posts)

im using fastapi

it does return all data in in ide

but when i try in browser using the link or in postman using get request

it gives internal server error

ValueError: [TypeError('cannot convert dictionary update sequence element #0 to a sequence'), TypeError('vars() argument must have dict attribute')]

[fluid lava]

Hey All, I want to get your thoughts on these questions. What would you select and how do you think about that? Also, what triggers an index search in a query statement, is it only the "select " keyword or does the "where" keyword factor in?

[paper flower]

Everything can trigger index scan - ordering, filtering and if you db can select result just from that index it would

For example if you have posts and post(title) is indexed then if you select just title if would likely use an index-only scan

[torn sphinx]

Isn't SQL easy?

[dull dust]

I need help with an SQLAlchemy database problem. The details are in #help-mango, please help me!

[halcyon palm]

are there any ppl who still uses mlabs over mongodb atlas?

[ashen mason]

Hi there, any recomendations for Python ORM libraries to use for pgsql and Mongo as a side? Basically I want something which has defined fields and basic SQL statements, so I don't need to manage everything by myself. Also if any of those could handle joins as Django ORM does it would be great.

[rough orchid]

how can I use $nin in pymongo.
if I want to find all documents except one with some specific _id, how do I write that?

[tulip briar]

I might be asking in the wrong place but does anyone know of any good guides whether its video or text on web scraping particularly scraping past results of sporting events and sorting into a easy to read format?

[fading patrol]

If you're going to use Django use Django. Otherwise SQL Alchemy seems to be the obvious choice.

[harsh pulsar]

and if you don't have an obvious unambiguous use case for mongo, don't use mongo

[fading patrol]

#web-development seems to be where most scraping questions go but there are just so many free tutorials I would search around. I've definitely seen some for sports data specifically. For basic stuff, lxml is the easiest link to use. For the really tricky stuff you may need Selenium. In between the two, scrapy is popular but I haven't used it

[wraith adder]

Hi sql pros , I need some help with converting (POSTGRE) a Character Varying column to int ( just in the query)
here is mi situation:
inside the auth_link table there is a column called utime , that has the unix time , but in string (character varying) , well I need to see all the tuples that have that column with a value lower than 1664833846
here is what I tried so far :
select * from auth_link where cast(utime as bigint) >0
but it does not work , it just returns this
ERROR: invalid input syntax for type bigint: "utime1" SQL state: 22P02

[grim vault]

You most likely have an entry with the text utime1 in the column utime which can not be cast to an bigint.

This should work:

SELECT * FROM auth_link
 WHERE utime SIMILAR TO '[0123456789]+'
   AND CAST(utime AS bigint) < 1664833846;

[wraith adder]

yeah , that's it, thanksss

[grim vault]

And to find the error lines:

SELECT * FROM auth_link
 WHERE utime NOT SIMILAR TO '[0123456789]+';

[wraith adder]

I only have 7 tuples lol ,thanx though :)

[ashen mason]

I'm going as much lightweight as possible. I'm thinking about testing Peewee or ORM and if neither will perform well I will go with SQLAlchemy

[harsh pulsar]

"lightweight" in what sense? sqlalchemy is maybe more complicated, but is it "heavier" at runtime than peewee or pony? i'm not sure about that.

[harsh pulsar]

"lightweight" in what sense? sqlalchemy is maybe more complicated, but is it "heavier" at runtime than peewee or pony? i'm not sure about that.

[ashen mason]

Yes I know SQLAlchemy is the lightest over there, but might require extensive time studying and typing the code. That's what I'm talking about, also would need big amounts of raw sql.

[fading patrol]

If you think you need any raw SQL with SQL Alchemy, you're doing it wrong

[cosmic sand]

if I want to organize my database based on stocks and their individual attributes. I should create the Database first then seperate by their own table?

[tulip briar]

Thank you so much for the help ill go have a look

[crisp ravine]

can you open using sqlite browser? is the data available?

[ashen mason]

Can SQLAlchemy handle joins properly?

[fading patrol]

Yes, is there some specific situation you have that isn't addressed by the docs? The basics of joins are covered here: https://docs.sqlalchemy.org/en/14/orm/queryguide.html?highlight=join#joins

[ashen mason]

I can't think of situation right now, I'm just precautious of the growth.

[void compass]

I think tortoise tries to be really close to django

[ashen mason]

Gonna check that out also, thank you.

[livid coyote]

Is this a safe alternative to passing db.Model as the base class when using flask-sqlalchemy?

from typing import TYPE_CHECKING, Type
if TYPE_CHECKING:
    from sqlalchemy.orm.decl_api import _DeclarativeBase

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import declarative_base

db = SQLAlchemy()
BaseModel: Type[_DeclarativeBase] = declarative_base(metadata=db.metadata)

class SomeModelClass(BaseModel):
    ...

After digging around in the source code for both packages, it seems that this is basically what is happening when inheriting from the Model from the SQLAlchemy instance, but I want to make sure this isn't just nonsense lol.

[wraith adder]

I have made a chatting app in django (with log in and sign up) , and I have configured the database (created tables through the pgadmin4 gui) my sign-up function is something like this :

@csrf_exempt
def signUp(request):
    
    IP = request.META.get('REMOTE_ADDR')
    jned = json.loads(request.body)
    email = jned['email']
    password = jned['password']
    user_id = tokenGen()
    user_token = tokenGen()
    now = int(time.time())
    create_user =  f'''insert into auth_user(idx,email,passwordx,ip,utime,tokenx) 
                                    values ('{user_id}','{email}','{password}','{IP}','{now}','{user_token}')'''
    try: 
        connection = psycopg2.connect(user="postgres",
                                password="0000",
                                host="127.0.0.1",
                                port="5432",
                                database="project1")
    except:
        print('error in connection ')
    cursor = connection.cursor()
    cursor2 = connection.cursor()
    cursor2.execute(f"SELECT * FROM auth_user WHERE email = '{email}'")
    if cursor2.rowcount >0 :
        print('email already exists')
        return HttpResponse(json.dumps({'status':'email already exists' , 'done':False}))
    else:
    values('{jned['email']}','{ip}','{hashit(jned['password'])}','{token}',{int(time.time())})")
    cursor.execute(create_user)
    connection.commit()
    return HttpResponse(json.dumps({'status':'never used this website huh?' , 'done':True, 'token':user_token}))```

everything works perfectly fine. but I think that I am connecting to the database in a wrong way, all the tutorials on youtube setup the database in settings.py , taking a completely different path (they use models and django.db). so , the question is : can I push this to production or should I write all the database models even though I don't need them?

a friend told me to use this query instead , he said that it limits sql injections , is he right?

create_user =  '''insert into auth_user(idx,email,passwordx,ip,utime,tokenx) 
                                    values(%s,%s,%s,%s,%s,%s,)''', (user_id,email,password,IP,now,user_token)

he also told me that I should never use fstrings

[torn sphinx]

Command raised an exception: AmbiguousColumnError: column reference "vanity_list" is ambiguous

anyone have any idea wtf this means?

it was saying that too many columns have that name when i check stack overflow, but that is the only column with that name>???

[fluid lava]

Thanks @paper flower

[fading patrol]

If this doesn't solve it for you, show the command that is triggering the error. https://towardsdatascience.com/how-to-solve-the-ambiguous-name-column-error-in-sql-d4c256f3d14c

[velvet knot]

How do you insert inputs into a database?

[torn sphinx]

what kind of db

[empty granite]

How to solve it

I'm a newbie..can anyone help?

[velvet knot]

SQLITE3

[slim trail]

hello
I have question (mysql)

|ID|Data | Username|
| 1 | 2010-11-02 | Hill
| 2| 2010-09-02 | Benny
| 3 | 2010-12-02 | Joe
| 4| 2010-01-14 | Adam

I would like to have the data sorted by date to get something like this

|ID|Data | Username|
| 1 | 2010-01-14 | Adam
| 2| 2010-09-02 | Benny
| 3 | 2010-11-02 | Hill
| 4| 2010-12-02 | Joe
I tried everything but no idea how to do it
the idea is to have columns such as date and username placed in consecutive IDs
I tried sth like this

UPDATE
    tab AS dest,
    (
       SELECT * from tab ORDER BY Data ASC
    ) AS `src`
SET
    dest.Username =  src.Username,
    WHERE dest.D = src.D;
;

[dim siren]

I'm working on a project rn and one of it's features is allowing users to upload their own profile pictures.. I know I shouldnt store images in a database, and a lot of the suggestions I've read mention storing the images within the file structure of the project and storing the path to the image as a string in the database

im just thinking like.... isnt it a big no no to store user information within the project's file structure?

If anyone with experience could let me know about possible workarounds I'd greatly appreciate it

[paper flower]

If you're ok with using single server/node then it's ok to store images on it

Otherwise you can use S3 compatible storage and store your images there

You should only store relative url in your db in both cases, e.g. /avatars/<some-user-id>.png

[dim siren]

yeahhhh I asked a friend about it and he recommended using Azure blob storage to do the same thing

I think I got it now, thank u homie

[slim trail]

sorry, anyone knows Mysql?

I have question related to mysql

|IDX_cnt |Date | Username|
| 1 | 2010-11-02 | Hill
| 2 | 2010-09-02 | Benny
| 3 | 2010-12-02 | Joe
| 4 | 2010-01-14 | Adam

I would like to have the data sorted by date - I want to assign the oldest date a value equal to 1 in the IDX_cnt column, the next date a value equal to 2, and so on up to the youngest date

|IDX_cnt |Date | Username|
| 1 | 2010-01-14 | Adam
| 2 | 2010-09-02 | Benny
| 3 | 2010-11-02 | Hill
| 4 | 2010-12-02 | Joe
I tried everything but no idea how to do it

UPDATE
    tab AS dest,
    (
       SELECT * from tab ORDER BY Date ASC
    ) AS `src`
SET
    dest.Username =  src.Username,
    WHERE dest.D = src.D;
;

anyone?

[celest fractal]

Row_number() Over (partition by unique key order by date)

[wraith adder]

hi guys , I need some help with sql (postgre) , I need to update the email of the user, so what I have in input is an access token and the future email.
this query is not working, can someone help me?

update user_table join user_details on user_table.id = user_details.idx 
set user_table.email = 'email-new'
where user_table.email = 'email-old'
and user_details.token = 'access-token'```\

the problem seems to be the join that is not supposed to be there

I could not find anything online

I searched for 15 minutes

I am hoping that someone replies during the night :)

[fading patrol]

"not working" how? Do you get an error message or what? We can't see your schemas, are the table names.and.cokumn names correct?

[grim vault]

PostgreSQL is using FROM to join tables in an UPDATE command (https://www.postgresql.org/docs/current/sql-update.html):

update user_table
   set user_table.email = 'email-new'
  from user_details
 where user_table.email = 'email-old'
   and user_table.id = user_details.idx
   and user_details.token = 'access-token'

it could also be done with an sub-select:

update user_table
   set user_table.email = 'email-new'
 where user_table.email = 'email-old'
   and exists (select 1 from user_details
                where user_details.idx = user_table.id
                  and user_details.token = 'access-token')

Also, for me it looks like the email-old is not really needed, you can just set the new value.

And if the access token is unique to the user, you could also do:

update user_table
   set user_table.email = 'email-new'
 where user_table.email = 'email-old'
   and user_table.id = (select user_details.idx from user_details
                         where user_details.token = 'access-token')

[granite saddle]

Basic question from a guy new to databases and python:
How would I sort a Mongo's database by existing entry's values within the same field?
In this case, after sorting in descending order
Cup Cake would be at #4
Donut at line #5
Muffin at line #6
and Bar Chocolate at line #7
And how'd I exclude entries from the sorting?

[fading patrol]

What have you tried? If this link doesn't help, show your code. https://stackoverflow.com/questions/8109122/how-to-sort-mongodb-with-pymongo

[wraith adder]

hey , thank you so much ,I will try and let you know 🙂

[wraith adder]

ngl , you look like the guy from lord of rings

[wraith adder]

after a few tweaks I was able to make it work

tx :)

[stoic relic]

hi, i have a postgresql server, and i am trying to code some python on different machine why do i have to install postgresql on the machine i deal with python in order to install psycopg2? Do i have to?

[stoic relic]

this is the error i get.

Collecting psycopg2
  Using cached psycopg2-2.9.4.tar.gz (384 kB)
  Preparing metadata (setup.py) ... error
  error: subprocess-exited-with-error
  
  × python setup.py egg_info did not run successfully.
  │ exit code: 1
  ╰─> [25 lines of output]
      /home/yusuf/Desktop/ecommerce/venv/lib/python3.10/site-packages/setuptools/config/setupcfg.py:508: SetuptoolsDeprecationWarning: The license_file parameter is deprecated, use license_files instead.
        warnings.warn(msg, warning_class)
      running egg_info
      creating /tmp/pip-pip-egg-info-71uow94x/psycopg2.egg-info
      writing /tmp/pip-pip-egg-info-71uow94x/psycopg2.egg-info/PKG-INFO
      writing dependency_links to /tmp/pip-pip-egg-info-71uow94x/psycopg2.egg-info/dependency_links.txt
      writing top-level names to /tmp/pip-pip-egg-info-71uow94x/psycopg2.egg-info/top_level.txt
      writing manifest file '/tmp/pip-pip-egg-info-71uow94x/psycopg2.egg-info/SOURCES.txt'
      
      Error: pg_config executable not found.
      
      pg_config is required to build psycopg2 from source.  Please add the directory
      containing pg_config to the $PATH or specify the full executable path with the
      option:
      
          python setup.py build_ext --pg-config /path/to/pg_config build ...
      
      or with the pg_config option in 'setup.cfg'.
      
      If you prefer to avoid building psycopg2 from source, please install the PyPI
      'psycopg2-binary' package instead.
      
      For further information please check the 'doc/src/install.rst' file (also at
      <https://www.psycopg.org/docs/install.html>).
      
      [end of output]
  
  note: This error originates from a subprocess, and is likely not a problem with pip.
error: metadata-generation-failed

× Encountered error while generating package metadata.
╰─> See above for output.

note: This is an issue with the package mentioned above, not pip.
hint: See above for details.```

i made research people said ' you need to install postgresql'

[fading patrol]

Makes sense, did you do that?

[stoic relic]

i dont want to because i have different machine where postgresql is installed why do i have to install on my own machine?

[fading patrol]

You don't need a pg server on this machine, but the client is obviously required for psycopg2

[stoic relic]

so each client will become a pg server? 😄

[fading patrol]

No, you don't need to setup the server

Just to have the client

[stoic relic]

i did not get the idea behind this, to have psycopg2 working on a machine there has to be a pg server in unconfigured state.

working but not serving

[fading patrol]

There has to be the pg client software. There might be a separate installer if you really need one (and depending on your OS) but I guess the basic installer may include both

[stoic relic]

thank you for illuminating 🙂 i will try the pg client thing.

[stoic relic]

i installed the binary as suggested by a friend and viola, it solved the problem, i did not get it how it is solved 🙂 but it is working as expected. no need to install anything else.

pip install psycopg2-binary

[warped turtle]

How can I pass a dictionary as the variables for a sqlite query?

await cur.execute("INSERT INTO playerprofile VALUES (:bmid,:steamid,:playername,:rusthours,:aimtrain,:steamurl,:avatar,:relatedplayers,:updatedat,:names,:kills_week,:kills_day,:deaths_day,:deaths_week,:gamebanned)",({playerinfo}))

where playerinfo is a dictionary

[harsh pulsar]

await cur.execute(query, playerinfo)

don't overthink it

the params are either a sequence (list or tuple) or a mapping (dict)

there's no special syntax required

[warped turtle]

it's okay I got the response I needed from a different channel

[cloud path]

Hi,
I am making a server for a game where you upload levels and user data. What is a good database that i can use?

[cloud path]

anyone?

[unkempt prism]

you can always use supabase. The Realtime might come in handy for you down the track too.

[keen minnow]

any database would do.
I would suggest to start with the simplest and most common SQL based ones such as mysql or postgres

[granite saddle]

Apologies for the late reply, but I will try that!
Thanks

[tacit narwhal]

someone goto #help-donut and help me there

the message's tooo big

[fossil ingot]

Can someone please recommend me any cloud database services with an API that only rate limits per IP/User but not the overall app/project/API key
I expect to have many users and it will overrun the API if it just overall limits the number of calls but not upon per user/IP

(Exclude Dropbox)

[keen minnow]

Is the implication that your users connect directly to your database?

[brave bridge]

yeah that's a bad idea

[thick parrot]

I need help with sqlite3

[fading patrol]

#❓｜how-to-get-help

[thick parrot]

yea nvm I don't need it anymore

[young vigil]

How far do you guys generally normalize your databases?
My use case is a database of movies, and I am considering for some things to have multiple values per field - e.g. Genres (Animation, Comedy, Crime, etc..) which will probably be 20-30 different genres in total - and Keywords (twist ending, car chase, heist, dystopia, time travel, etc..) which might be in the thousands total.

[fading patrol]

I was taught that the correct way to handle these cases would be as many to many relationships. So you want a movies-genres table and a movies-keywords table. I'm not sure there's ever any reason to do it differently.

[young vigil]

That is probably correct, but I am struggling to visualize it.

[fading patrol]

https://blog.devart.com/wp-content/uploads/2010/06/Junction-Table-example.png

So movies-genres would be a junction table like that, where each row has: 1) a unique ID 2) a movie ID (foreign key from movie table) and 3) a genre ID (foreign key from genre table)

If movie 1 has three genres, you would have three rows -- one for each genre -- that includes 1 as the value of movie_id

This way you can easily join all the genres associated with a given movie or all the movies associated with a genre

[young vigil]

Ok, I think I understand now.
So if I have 5 movies with 100 keywords for each of them, then the Keywords table alone would be 500 lines - just for those 5 movies.

[fading patrol]

Yes, even if that sounds like a lot

It doesn't really matter in a modern DB, you can have millions of rows

[young vigil]

Yeah, I guess the performance doing it this makes up for the increase in space, even if I'm using something simple as sqlite for this little pet project.

[fading patrol]

And it should also just be simpler and cleaner to manage. Not intuitive if you don't know the concept, but very easy when you do

[young vigil]

Yeah, I was leaning towards having one row for each movie with all the genres for it one cell, all keywords for it in one cell etc, mainly because I really do not looking forward to headaches regarding joining tables - but I guess it's all a part of the process, so I should learn it anyways.

[young vigil]

I've been trying to make a mock db now, but don't quite get the Junction ID part. Is it not enough with Book ID and Author ID?

[fading patrol]

It needs to have its own unique ID for each row also

Normally an incremented integer

[young vigil]

And the JunctionID is the Primary Key in that table?

[fading patrol]

Exactly

[young vigil]

So does this look correct? https://pastebin.com/raw/a2SygbQG

[fading patrol]

You don't need to add a field for each genre, you just need an id column (integer) and a genre column (string)

[fading patrol]

And presumably you'll want at least one more field for the movie column, like a title string

[young vigil]

Title would presumably also be many-to-many, due to AKAs / translated titles.

[young vigil]

Trying to wrap my head around this one. Do you have an example?

[fading patrol]

Hmm, .good thinking, but without any sort of name or title it might get confusing to know what movie 48 actually is

[young vigil]

I would assume that's where joining tables come in.

[fading patrol]

Your table would be like:

Id,genre
1, action
2, horror

And so on

[fading patrol]

Yes, for alternate titles that will be good. But your movie table should be a meaningful list in itself like the book and author titles above. You can look at those tables individually and understand what they represent... Not just a list of integers with no obvious meaning

Time for me to sleep but I hope that helps, if you have more questions hopefully someone else can jump in for now

[young vigil]

Just looked at the IMDb dataset, they use a title_basics table with original title and english title - and a separate table called title_akas

[young vigil]

Past 6 in the morning, I should sleep as well. Many thanks for the help. :)

[paper flower]

Hmm, you don't need id on your junction table 🤔, just author_id and book_id should be enough

[dark herald]

so guys I have a API that gives 5 cars infos and all of them have the same dict but first one

I dont want to do this

how can i make this simple

[crimson tangle]

???????????

every thing is correct

[paper flower]

I don't quite understand what you want to do, also it's not related to #databases

[fading patrol]

My guess is a network/permission issue. I don't think AWS opens RDS to the public internet by default

[frosty frigate]

Is it possible to make columns that take values ​​automatically relative to each other, for example:
Let the Y column be 10 times the X column, the X column will automatically change when the Y column changes

[grim vault]

Some databases support generated column values.

eg SQLite: https://www.sqlite.org/gencol.html

[brittle bolt]

Why would you do this? You would be storing additional data for nothing. Just do the scaling calculation when you need the data. Storing more data than required has cost in terms of disk space and time (more data means longer time to find what you need), these may be negligible on a small dataset but data grows.

[obtuse berry]

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

Traceback (most recent call last):
  File "Z:\beege\Documents\VisualStudioCodeProjects\Github\EconomyDiscordBot\venv\lib\site-packages\discord\ext\commands\bot.py", line 1330, in invoke
    await ctx.command.invoke(ctx)
  File "Z:\beege\Documents\VisualStudioCodeProjects\Github\EconomyDiscordBot\venv\lib\site-packages\discord\ext\commands\core.py", line 995, in invoke
    await injected(*ctx.args, **ctx.kwargs)  # type: ignore
  File "Z:\beege\Documents\VisualStudioCodeProjects\Github\EconomyDiscordBot\venv\lib\site-packages\discord\ext\commands\core.py", line 209, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: SyntaxError: syntax error at end of input
LINE 1: SELECT bank FROM users WHERE id = ?
                                           ^

Database connection closed.
```Anyone able to explain why this is happening? I'm using VSCode

This normally works in Pycharm, but I'm not sure what I'm doing wrong here

testSQLstatement = """SELECT bank FROM users WHERE id = ?"""
dbconnection.cur.execute(testSQLstatement, (326903703422500866))
```Here is the code

[grim vault]

You're missing the comma to make it a tuple:

dbconnection.cur.execute(testSQLstatement, (326903703422500866,))

[obtuse berry]

I've changed that, but I believe it's because I have the wrong form of query interpolation for PostgreSQL

Would you happen to know what it is? The solutions online look pretty complicated.

[grim vault]

I think PostgreSQL is using %s instead of ?

which db module are you using?

[fluid lava]

What are the top interviewing/mentorship platforms you all would recommend? I want high-level help but not looking to pay a fortune like you see on interviewing.io

[fading patrol]

#career-advice seems more relevant unless.youre looking for DB help specifically?

[obtuse berry]

psycopg2

[fading patrol]

So %s should do it per https://www.psycopg.org/docs/usage.html

[obtuse berry]

Thank you very much. Another question, is there a better way to print the first result of a query than this, or is this generally what is used?

results = dbconnection.cur.fetchone()[0]
print(results)

[brazen charm]

Still irks me that it uses %s 😅

IMO though people should start considering switching to https://www.psycopg.org/psycopg3/docs/ v3 now that it's been around for a lil while

[fading patrol]

Not sure if there's any formal guidance on this but I would prefer:

results = dbconnection.cur.fetchone()
print(results[0])

You may also need some error handling in case of no result

[obtuse berry]

Thanks!

[soft gorge]

CREATE TABLE IF NOT EXISTS Subscriptions(
    indx INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
    subscriptionId INTEGER UNIQUE,
    subscriptionStatus BIT
);


CREATE TABLE IF NOT EXISTS Clients(
    indx INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
        email VARCHAR(20),
        subscriptionId INTEGER,
        subscriptionStatus BIT
);

Hello, I have two tables one that will store the subscriptions for a clients products purchased. I am trying to create an AFTER UPDATE Trigger that will update the subscriptionStatus value if my client webhook updates the value so it can be reflected on the Clients Table. I'm not sure how I would write the Trigger statement to ensure I am updating the correct row and with the correct value if anyone can help me out that'd be great!

[lean plover]

I am wondering how to pass the following psql connection command to psycopg 3: psql -h pg.pg4e.com -p 5432 -U pg4e_fd2b876 pg4e_fd2b876

[unkempt prism]

I'm thinking your DSN is postgresql://[e-mail]:5432/pg4e_fd2b876

So you can

DSN="postgresql://[e-mail]:5432/pg4e_fd2b876"

with psycopg.connect(DSN) as conn:
    ... 

[real forum]

Hey, I have a question, I have a MySQL database and I have the following problem when executing a command (discord)
there is no error, but it doesn't work either (only rarely)

then the following error message appears:

Cursor is not connected

Does anyone know what it could be? and how to fix it?

conn = mysql.connector.connect(host="eu02-sql.pebblehost.com", user="customer_20981_lara", database="customer_20981_lara", password="...")
cursor = conn.cursor(dictionary=True)```

[fading patrol]

If it ever works even rarely, that sounds like a network connectivity issue or problem with the server to me

[fluid lava]

Noted, thank you @fading patrol !

[tranquil shoal]

HI, I have a questin, how would I structure a database where I can have mutiple tags?

As an example, this would work if I would only have one Tag but I don't know how to handle a dynamic amount of tags.
https://i.imgur.com/2KIX74H.png

[grim vault]

You use a third table:

[fiery thunder]

I am trying to upload .txt files from AWS s3 into a dynamoDB table as a string, but when I upload it it goes into the table in byte form. e.g. b'Game 1: Tails'

is there any way to upload it as a string

[willow wadi]

How are you reading the contents of the .txt file? And what are you using to write to your db? It'd be helpful to see a snippet of code

[fiery thunder]

i figured it out

i had to take the item that was and attach ".decode(utf-8)" onto it and it reverted back to a string

in case anyone was curious

[stable ibex]

Hello, I need help with my SQL assignment.

[stable ibex]

They want me to use the EXISTS value

I copied the program from a previous task but only this time it wanted me to use the EXISTS value

This is the code I wrote.

  FROM CUSTOMER
WHERE EXISTS (SELECT CUST_ID FROM INVOICES WHERE INVOICE_DATE = '2021-11-15');

It want's the results to be this ```
CUST_ID FIRST_NAME LAST_NAME
125 Joey Smith
227 Sandra Pincher

But instead it shows this ```
CUST_ID FIRST_NAME LAST_NAME
125 Joey Smith
182 Billy Rufton
227 Sandra Pincher
294 Samantha Smith
314 Tom Rascal
375 Melanie Jackson
435 James Gonzalez
492 Elmer Jackson
543 Angie Hendricks
616 Sally Cruz
721 Leslie Smith
795 Randy Blacksmith

[whole widget]

As a sanity check, does the result of just the inner query make sense?
SELECT CUST_ID FROM INVOICES WHERE INVOICE_DATE = '2021-11-15'

[stable ibex]

@whole widget No.

[whole widget]

You mentioned you're doing the same task from a different problem. Have you repeated all of the prior steps that might have limited the query results down to just twoCUST_ID?

[stable ibex]

@whole widget The instructions specifically says Repeat Task 4, but this time use the EXISTS operator in your answer.

The code I mention was from task 4 only this time I replaced the IN value with the EXISTS VALUE.

[whole widget]

Can you share the query used in task 4?

[lean plover]

connection to server at "pg.pg4e.com" (3.101.11.254), port 5432 failed: FATAL:  password authentication failed for user "pg4e_fd2b376"

[stable ibex]

@whole widget SELECT CUST_ID, FIRST_NAME, LAST_NAME FROM CUSTOMER WHERE CUST_ID IN (SELECT CUST_ID FROM INVOICES WHERE INVOICE_DATE = '2021-11-15')

This was from task 4.

@whole widget Are you still there?

[whole widget]

Unfortunately not really. I've been pulled away. There's a difference in the way the IN and EXISTS works that I can't remember specifically. They aren't interchangeable.

[river matrix]

Hey guys.

Anyone know where I can find a repository for an employee register?

[keen minnow]

What's your db schema so far?

[grim vault]

You need to move the CUST_ID check into the sub-select. An EXISTS will be true or false, so:
... EXISTS (SELECT CUST_ID FROM INVOICES WHERE INVOICE_DATE = '2021-11-15')
will be true for all rows if there is at least one entry with that invoice_date.

Also, in an EXISTS select, the selected columns don't matter. It will be only checked if there would be an result or not.

[paper flower]

If you want that to be stored in your db then you could use computed values

otherwise you can just calculate that in your python code

[unkempt prism]

I guess you get prompted for the password when you use psql

So you can include it using a : after the username before the @

postgresql://pg4e_fd2b876:[e-mail]:5432/pg4e_fd2b876

Please use environment variables to keep secrets outside of py that may be accidental shared.

[paper flower]

https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

🤷‍♂️

[digital bluff]

This is a table I have.

I need a select query which gives the whole table along with a new column "Win_Ratio"
the values in Win_Ratio need to be like: Wins/Games_Played

Lastly the whole table need to be in descending order of win ratio

[grim vault]

So? Just add it in the select list (with an alias for the order by). SQL can do math.

[digital bluff]

umm can u gimme query pls

I am new to mysql

[grim vault]

select *, Wins / Games_played as Win_Ratio from High_Scores order by Win_Ratio DESC```

[digital bluff]

thanks a bunch

Its working ty @grim vault

[grim vault]

added the DESC keyword in the order by section, overlooked the descending part.

[digital bluff]

yea i noticed

[lilac lake]

is it possible for a row in one table to be related with an entire column in another table ?

[paper flower]

How would you use that?

[lilac lake]

Grades table:
student_id | sub1 | sub2 | sub3
001        | 10   | 09   | 09
002        | 09   | 10   | 09

Students table:
student_id | name | email
001        | Mark | [e-mail]
002        | John | [e-mail]

Courses table:
course_code | course_name | taught_by
AB1         | sub1        | Someone
AB2         | sub2        | Someoneelse

The row AB1 from course table, can it be related to the grades table with sub1

is this a thing?

[lilac lake]

I don’t have a use case in mind, I was just thinking of joining tables and this thought occurred to me. And I was curious if this is a thing and if it is how can I use it.

[grim vault]

You would use a foreign key in the grades table, not one column per course:

Grades table:
student_id | course_code | grade
001        | AB1         | 10
001        | AB2         | 09
001        | AB3         | 09
002        | AB1         | 09
002        | AB2         | 10
002        | AB3         | 09```

[torn sphinx]

I'm getting this error about 30% of the time I run the code, why?```py
File "/home/container/cogs/services.py", line 42, in callback
await c.execute("INSERT INTO Tickets (channelid, ttype, memberid, membername, claimedby, created_on, review_status) VALUES (%s, %s, %s, %s, %s, %s, %s)", (chan.id, self.ttype, self.member.id, str(self.member), 0, datetime.now(), False))
File "/home/container/.local/lib/python3.8/site-packages/aiomysql/cursors.py", line 239, in execute
await self._query(query)
File "/home/container/.local/lib/python3.8/site-packages/aiomysql/cursors.py", line 457, in _query
await conn.query(q)
File "/home/container/.local/lib/python3.8/site-packages/aiomysql/connection.py", line 469, in query
await self._read_query_result(unbuffered=unbuffered)
File "/home/container/.local/lib/python3.8/site-packages/aiomysql/connection.py", line 672, in _read_query_result
await result.read()
File "/home/container/.local/lib/python3.8/site-packages/aiomysql/connection.py", line 1153, in read
first_packet = await self.connection._read_packet()
File "/home/container/.local/lib/python3.8/site-packages/aiomysql/connection.py", line 641, in _read_packet
packet.raise_for_error()
File "/home/container/.local/lib/python3.8/site-packages/pymysql/protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File "/home/container/.local/lib/python3.8/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')

[fading patrol]

https://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im

[torn sphinx]

How to fix the code that killed the transaction? Also what transaction?

[fading patrol]

The transaction is your attempt to insert data in the DB. The various answers to that question suggest ways to troubleshoot the issue. It could be anything, you need to check out what's going on. Or maybe if you share a link to your code someone might spot something there (but I've never used mysql or await myself)

[livid coyote]

Is it possible to have a single SQLAlchemy model that is usable in two different databases? I don't mean to extend relations between them, but just to share the same base model between two databases with identical schemas

[fading patrol]

Yes, that should be simple enough

[livid coyote]

It was simple enough, ha. I was able to write a small context manager that basically scopes the model under a session and runs queries against it. 👍

[still rivet]

can someone help answer my question on indexes
https://discord.com/channels/267624335836053506/696888170167664691

[tawdry finch]

Hi! I have a problem with SQLITE data base...
Im actually trying to make a video game
I find how to write in the sqlite database :
But now i want to add the last_pos, in the line with user id 3. But i don't find how...

[paper flower]

update users
set last_pos = your_value
where users.id = 3

[tawdry finch]

Like this??

[paper flower]

no, you need to execute it, it's not python code

also you have to replace your_value with, well, your value 🙂

[tawdry finch]

Like this?

[paper flower]

you need to substitute a value instead of last_pos

https://docs.python.org/3/library/sqlite3.html#how-to-use-placeholders-to-bind-values-in-sql-queries

[paper flower]

This should work 🤔

[tawdry finch]

Yeah!!! It works thanks you a lot!

[fading patrol]

The most secure option would be to have a separate API where your private code runs and your shared code can make requests to it. Sooner other ideas here: https://stackoverflow.com/questions/3344115/how-to-obfuscate-python-code-effectively

[sacred solstice]

Hi, i have a problem, Im trying to make a video game with a save system on sqlite.
Here im trying a fonction that send me back the save_name but this is what i got :

I got, ('Papi,') and ('TEst1,')
But i can't write that in my game, it's ugly... How can I using thats juste write Papi and TEst1 ?

[livid coyote]

I'm trying to clone a database (MSSQL) to a local SQLite database. I understand some of the basic caveats (i.e. MSSQL uses dot-separated multipart names, SQLite doesn't use schemas), but I'm spinning my wheels. No matter what I do, I'm getting sqlite3.OperationalError: no such table: crm.Customer. On inspection, the table is indeed Customer with a schema defined as crm, and I've tried various ways of modifying the insert query, adjusting the table attributes, etc. all to no avail. I made some headway by making a deepcopy of the table, modifying it, and passing that during the before_execute event but that just resulted in a dict error down the road.

¯_(ツ)_/¯

[livid coyote]

Do you understand what data types you're getting back from that function?

[sacred solstice]

No, is a tuple?

[low rain]

anyone awake in here?

[ionic pecan]

No, everyone here is sleeping

[real forum]

PostgreSQL

Hey, I have a question, how can I update columns that have a value of zero, i.e. that they are no longer zero

[ionic pecan]

use a WHERE condition

UPDATE users
SET tier = 1
WHERE tier = 0

[hybrid moth]

hi i can't get this to work :(

try:
    cursor.execute('use books_db')
except mtc.errors.InterfaceError:
    print('Couldn\'t connect to database!')
except:
    cursor.execute('create database books_db')
    cursor.execute('use books_db')
    cursor.execute('create table books(isbn int primary key, name varchar(99), author varchar(99), tags varchar(99), issued varchar(99), issue_count int)')
    connection.commit()```

i've shut down the server on purpose but i can't get it to print the 'couldn't connect' statement

and that's the error that I'm getting

[ionic pecan]

you should change that bare except: to catch an exception and print it

[hybrid moth]

alright

[rough lichen]

quick tsql question :
i have 22 records , 1 fielders,

i have to squeeze all them in to 10 lines, divided using any symbol, lets say pipe |
so it would look something like
1 | 11 | 21
2 | 12 | 22
3 | 13
4 | 14
5 | 15
6 | 16
7 | 17
8 | 18
9 | 19
10 | 20

how would i go around doing this ?

[woven dragon]

Hi guys, trying to write a context manager for a mysql database connection - this is what I have so far:

import contextlib
import logging

import mysql.connector


@contextlib.contextmanager
def db_connection(db_config):
    logging.debug(f"Connection config: {db_config=={}}")
    conn = None
    try:
        conn = mysql.connector.connect(host="localhost", # TODO substitute with env vars
                                       port=30003,
                                       user="user",
                                       password="123",
                                       database="my_servic")
        yield conn
    except Exception as e:
        logging.exception("Encountered an exception while attempting "
                          f"to connect with the database - {e.args}")
    finally:
        if conn:
            conn.close()
        else:
            return

Is this pythonic enough? What can I do to improve it?

If the conn object isn't yielded, I get a RuntimeError: generator didn't yield which is expected, but is there a better way to handle this? Should I be creating a custom exception?

[fading patrol]

I think you may want to return, not yield. And if possible I would specify the Exception type you are expecting. Otherwise this looks good to me

[woven dragon]

I've been following various example docs and they all yeild the connection object instead of return

[fading patrol]

Ah, I see, thanks... maybe I should try it ☺️

[forest raptor]

Hello! I was wondering if:

A. It's possible to query a python variable in a database (probably is)
B. If anyone knows a publicly accessible English Dictionary Databases to compare words to through my Python script and how I would use them?

[fading patrol]

A) pretty much anything you would store in a Python variable can be stored in and retrieved from a DB, if this is what you mean

B) What data are you looking for exactly? Do you just need a list of words like this: https://github.com/dwyl/english-words

[forest raptor]

Tysm ❤️

[forest raptor]

How would I query this in Python? as it's one big 'statement'

[fading patrol]

Not sure how efficient this would be but you could load that to a list called wordlist and do if x in wordlist:

This is off-topic for this channel so give that a try and if you get stuck then follow #❓｜how-to-get-help

[harsh pulsar]

seems reasonable to me

actually... wait. no

connect to the db outside of the try

you aren't trying to catch errors there

if it blows up, the caller should handle that

you are trying to ensure that an existing connection is closed properly no matter what happens inside the block

so the try in this case should contain only yield

[woven dragon]

@harsh pulsar Yep - i'm doing just that

[harsh pulsar]

not in your current code!

[woven dragon]

my outer exception will handle any other exceptions raised by the connect

[harsh pulsar]

https://docs.python.org/3/library/contextlib.html look here

[harsh pulsar]

no, you don't want to handle that here

[woven dragon]

Oh I'm sorry, yep, I pivoted to what you're doing

@contextlib.contextmanager
def db_connection(db_config):
    host, port, user, secret, schema = get_db_config(db_config)
    conn = mysql.connector.connect(host=host,
                                   port=port,
                                   user=user,
                                   password=secret,
                                   database=schema)
    try:
        yield conn
    finally:
        conn.close()

^ this is what I'm doing now

[harsh pulsar]

yep, that's it

also i meant to send this link https://docs.python.org/3/library/contextlib.html#contextlib.contextmanager where that pattern is described

[woven dragon]

if the connect here throws any exception, i'm handling it globally in the application

[low rain]

Need a sqlite guru, i want to match a unique column and return the complete record for the row, what is the the syntax to fill cursor.fetchone(_)?

[burnt wraith]

fetchall?

cusor.fetchall() that is

[low rain]

@burnt wraith thanks for your attention, i misunderstood some reading so im reviewing it now

[sullen vessel]

What are some good modules for encrypting files, and databases?

[torn sphinx]

Hi guys

I need some help

with a pyspark dataframe

I need to loop through my values and assign 0 to values that are less than a certain value and 1 to values that are greater than that certain value

My value is 9400

I want to get the value for charges and in rate pool mark if it is greater than 9400, if it is then I write a 1 there, if it is not, then i write a zero

This is probably a lot, but any help would be much appreciated.

[lofty hull]

hello

CREATE TABLE CLIENT (
  P_Id varchar,
  productname TEXT,
  Manufacturer TEXT,
  price int,
  discount int
);


INSERT INTO CLIENT VALUES ('TP01', 'Talcum powder','LAK', 40);
INSERT INTO CLIENT VALUES ('FW05', 'Face Wash','ABC', 45,5);
INSERT INTO CLIENT VALUES ('BS01', 'Bath Soap','ABC', 55);
INSERT INTO CLIENT VALUES ('SH06', 'Shampoo','XYZ', 120,10);
INSERT INTO CLIENT VALUES ('FW06', 'Face Wash','XYZ', 95);```

can someone help

[low rain]

I'm a beginner myself but it would probably be helpful to others if you specify what sql this is

in sqlite for example, the column names are placed in the query and then a record is submitted using a dictionary

so, py c.execute("INSERT INTO stores VALUES (:name, :distance, :website, :contact)", { 'name': Add_S_Name_Entry.get(), 'distance': Add_S_Distance_Entry.get(), 'website': Add_S_Website_Entry.get(), 'contact': Add_S_Contact_Entry.get(), })

replace the ADD_S stuff with the source of your data

[cunning rapids]

you have provided only 4 values in TP01,BS01 and FW06

just use null if you want it to be empty

INSERT INTO CLIENT VALUES ('TP01', 'Talcum powder','LAK', 40,null);

[lofty hull]

ok ty

[lofty hull]

says this now

ERROR 1064 (42000) at line 1: 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 ',
productname TEXT NOT NULL,
Manufacturer TEXT NOT NULL,
price int NOT NUL' at line 2

CREATE TABLE CLIENT (
  P_Id varchar,
  productname TEXT NOT NULL,
  Manufacturer TEXT NOT NULL,
  price int NOT NULL,
  discount int
);


INSERT INTO CLIENT VALUES ('TP01', 'Talcum powder','LAK', 40,NULL);
INSERT INTO CLIENT VALUES ('FW05', 'Face Wash','ABC', 45,5);
INSERT INTO CLIENT VALUES ('BS01', 'Bath Soap','ABC', 55,NULL);
INSERT INTO CLIENT VALUES ('SH06', 'Shampoo','XYZ', 120,10);
INSERT INTO CLIENT VALUES ('FW06', 'Face Wash','XYZ', 95,NULL);
SELECT * from CLIENT;```

[cunning rapids]

SELECT * FROM CLIENT;

[lofty hull]

yh i did that

it gives the same error

[cunning rapids]

CREATE TABLE CLIENT (
  P_Id varchar,
  productname TEXT,
  Manufacturer TEXT,
  price int,
  discount int
);


INSERT INTO CLIENT VALUES ('TP01', 'Talcum powder','LAK', 40,NULL);
INSERT INTO CLIENT VALUES ('FW05', 'Face Wash','ABC', 45,5);
INSERT INTO CLIENT VALUES ('BS01', 'Bath Soap','ABC', 55,NULL);
INSERT INTO CLIENT VALUES ('SH06', 'Shampoo','XYZ', 120,10);
INSERT INTO CLIENT VALUES ('FW06', 'Face Wash','XYZ', 95,NULL);

SELECT * FROM CLIENT;```

don't use not null

[lofty hull]

ok still

ERROR 1064 (42000) at line 1: 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 ',
productname TEXT,
Manufacturer TEXT,
price int,
discount int
)' at line 2

i used ur code

[cunning rapids]

it's working for me

[lofty hull]

is that programiz

what site is that

[cunning rapids]

lol yes

[lofty hull]

can u link me that

[cunning rapids]

https://www.programiz.com/sql/online-compiler/

[lofty hull]

yes man problem with the online compiler works now ty

[cunning rapids]

👍

[obsidian basin]

In python can I go if x == IntegrityError:

[summer pier]

Hi everyone,
a Chemistry Student from Germany here, who has absolutely no clue of coding, but does not want to make his Data Analysis on Paper anymore.
Does anyone here has a Program i could use, that converts a Data Plot into a Trend Function (linear, polynomial or exponential) and then does the curve discussion for me, so differentiates the function two times and shows me maxima/minima or the turning-point instantly?
Greatings

[harsh pulsar]

Honestly i recommend starting with Excel / Google Sheets (or LibreOffice Calc), of course R and Python are very popular but it takes a lot longer to learn

[odd saddle]

can someone help me my sqlite3 database looks like this anyone know how to fix it? FYI it does work but kinda looks weird

[harsh pulsar]

Excel is still probably the best, followed by LibreOffice, and with Google Sheets in last place (although it has some particular interesting features like the ability to use javascript to program in it)

[harsh pulsar]

sqlite databases are binary files. they will always look like gibberish if you try to interpret them as text.

[odd saddle]

ok but is there a way to format it into something more legible like on excel?

[fading patrol]

Use dbeaver or something

[odd saddle]

k

[summer pier]

Actually i should do it in Excel, but i do not find any good Calculus functions there. So I would be again forced to do the math by hand...

[harsh pulsar]

it has regression models built in

[summer pier]

yes