I have been searching the internet for solutions to the following problem in a nutshell:
After I store a long string (of DNA sequences e.g. 6000-12000 characters) in the Database which works, when I use SQLAlchemy to retrieve such sequence, I was initially only getting 255 characters back. After some troubleshooting, I am now getting 4096 characters back. The rest gets silently truncated. My setup: Python 3.6 SQLAlchemy 1.12.11 FreeTDS 1.11 [also installed pyODBC as an alternate way to connect to the DB] RedHat Linux 7 I have tried to seek help on StackOverflow here: https://stackoverflow.com/questions/46678030/why-is-my-varchar-truncated-to-255-characters-how-do-i-fix-this But here are the core observations: 1.) If I use SQLAlchemy orm: > # Using SQLAlchemy full-stack.Session = sessionmaker() > > s = Session(bind=s_con) > fs_res = s.query(DNAPtsSeq).filter().all()for row in fs_res: > print(row) > print(len(row.nt_seq)) > > I am getting back 4096 base pairs, and the length of the object string object is 4096. 2.) If I am using SQLAlchemy with SQL statement strings: # Using SqlAlchemy connection with direct SQL query. > eoi_engine = > create_engine("mssql+pyodbc://user:somepw@db:1234/test?driver=FreeTDS") > s_con = eoi_engine.connect() > s_res = s_con.execute('SELECT Text.my_text, len(Text.my_text) FROM > [test].[dbo].[Text]')for row in s_res: > print(row) > print(len(row[0]) > > I am getting back a row that claims to be say 6000 bp long. But string object is only 4096 long. 3.) If I use pyODBC only without SQLAlchemy: # Connecting via pyodbc direct connection using just some helper functions > to make things more convenient. con_str = create_connection_string(DATABASE='test') cur = make_connection_db(connection_str=con_str) for row in cur.execute('SELECT Text.my_text, len(Text.my_text) FROM > [test].[dbo].[Text]'): print(row) print(len(row[0])) > I am getting back a string that is 6000 bp long. The string object really is 6000 characters long. Initially, I was going to do the workaround and connect to the database separately just to get the DNA sequences, but I have found that when I connect to the DB with SQLAlchemy, and then also connect to it using only pyODBC, queries to the DB hang for a long time. And just for reference, I have already tried zzzeek <https://stackoverflow.com/users/34549/zzzeek>'s proposed solution <https://stackoverflow.com/questions/17844667/sqlalchemy-truncating-varcharmax?noredirect=1&lq=1> of modifying tdsconf.ini. I have increased the limit to the 2 GB limit...without success. So now I am really stuck and at wits end. I have to get the 4096 character limit removed. Can anyone here help? -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
