1. Home

“Maximum number of parameters” error with filter .in_(list) using pyodbc

One of our queries that was working in Python 2 + mxODBC is not working in Python 3 + pyodbc; it raises an error like this: Maximum number of parameters in the sql query is 2100. while connecting to SQL Server. Since both the printed queries have 3000 params, I thought it should fail in both environments, but clearly that doesn’t seem to be the case here. In the Python 2 environment, both MSODBC 11 or MSODBC 17 works, so I immediately ruled out a driver related issue. So my question is: Is it correct to send a list as

Flask SQLAlchemy reflection ignoring most of tables on Redshift

I’m creating the engine and the Metadata as follows Yields AttributeError: packages, and dir(Base.classes) returns no attributes with that name, neither with the orders name. Taking the only=[‘orders’, ‘packages’] off makes it reflect only a few random tables. Now, when using the inspector that comes with native SQLAlchemy the table actually works (link to documentation): Is this a bug, or am I overlooking something here? Thanks! Installed package versions: Answer SQLAlchemy’s automap extension only reflects tables which have defined primary keys. From the note in the docs: […] for a table to be mapped, it must specify a primary key.

asyncpg.exceptions.DataError: invalid input for query argument $1: 217027642536 (value out of int32 range)

I’m working on a project that uses FastAPI alongside Pydantic and SQLAlchemy. I’m also using encode/databases to manage database connections. But for some weird reason, I get asyncpg.exceptions.DataError: invalid input for query argument $1: 217027642536 (value out of int32 range) anytime I try saving to the database. Here’s what my code looks like: database.py database_manager.py endpoints.py models.py services.py I can’t see anything wrong with this. Someone, please tell me what the hell is going on? Answer You are basically having Integer Overflow, Int32 represents 2^31 – 1, that means it can store the values in range -2147483648 to 2147483648 but

Python flask-sqlalchemy: Do I have to commit session after a query?

I am writing an app in python flask-sqlalchemy with MySQL DB (https://flask-sqlalchemy.palletsprojects.com/en/2.x/) and I am wondering if I have to make “db.session.commit() or db.session.rollback()” after GET call, which only query DB . For example: Answer orders = Order.query.all() is a SELECT query that could be extended to include additional filters (WHERE etc.). It doesn’t alter the database, it simply reads values from it. You don’t need to commit on a read for precisely that reason – what would you store other than “I just read this data”? Databases are concerned about this in other ways i.e. access permissions and logs.

Delete sql table rows from python

I can successfully connect my python notebook with sql server this way : Let’s take this sample dataframe : I have a sql table with the same columns, named table_name. Objective : I want to delete in table_name all the rows where a row in df has the same Name, same Date, same Status, and Max = 0 (only in table_name, not necessary in df) I tried the following but it doesn’t work : Could you please help me to understand what is wrong ? Answer Different libraries use different symbols for their placeholders. The one you are using apparently

I want to add data to my table using ORM in python

I want to add data in my database using ORM, and i am new to ORM in python. I am confused in SQLAlchemy and Flask-SQLAlchemy.”I tried this but don’t know how to proceed further Answer Flask-SqlAlchemy is commonly used for flask applications, otherwise just plain SqlAlchemy would should be enough. Flask-SqlAlchemy is a wrapper over SqlAlchemy and gives you following advantages: A preconfigured scoped session, engine and metadata. Model base class which is configured declarative base. Model base class has query attribute attached to it which can be used to query the model. You don’t need to remove the session

How to access “count” value as dict/property in sqlalchemy with group_by?

I am making a very simple query using ORM SQLAlchemy, in which I expect to get a column (type) as well as its occurences for each value (count with group by). I can access the type column by accessing the type property on the server object (as shown in the code provided). I can also access the count column by accessing the index 0 on the server object. My question is: is there a simple way to access the count column as a dict/property? For example: server.count I’ve extensively inspected the server object using dir(), but I couldn’t find any

Specifying pyODBC options (fast_executemany = True in particular) using SQLAlchemy

I would like to switch on the fast_executemany option for the pyODBC driver while using SQLAlchemy to insert rows to a table. By default it is of and the code runs really slow… Could anyone suggest how to do this? Edits: I am using pyODBC 4.0.21 and SQLAlchemy 1.1.13 and a simplified sample of the code I am using are presented below. Answer Try this for pyodbc

Write Large Pandas DataFrames to SQL Server database

I have 74 relatively large Pandas DataFrames (About 34,600 rows and 8 columns) that I am trying to insert into a SQL Server database as quickly as possible. After doing some research, I learned that the good ole pandas.to_sql function is not good for such large inserts into a SQL Server database, which was the initial approach that I took (very slow – almost an hour for the application to complete vs about 4 minutes when using mysql database.) This article, and many other StackOverflow posts have been helpful in pointing me in the right direction, however I’ve hit a