IndexError when attempting to drop a constraint on MySQL
I'm attempting to drop a unique constraint on MySQL 5.6.20, 64-bit, Python 2.7.6, SQLA 0.9.7, Alembic 0.6.6: The table's very simple: CREATE TABLE `author` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(75) CHARACTER SET utf8 NOT NULL, `foo` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4; But when I attempt to drop the unique constraint on foo using op. drop_constraint(u'uq_author_foo', 'author', type_='unique'), I get an error: https://gist.github.com/urschrei/541fec05a3a82d71cbe9 Manually removing it, both on the command line and in Sequel Pro, works fine. -- s -- You received this message because you are subscribed to the Google Groups sqlalchemy-alembic group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] PyDev throwing errors in the SQLAlchemy Library
I've never used PyDev, but if it effectively adds the folder that you've specified in that screenshot to PYTHONPATH, then I suspect that is the problem. What happens if you remove that External Library, and try to run the following script: import sqlalchemy print sqlalchemy.__file__ Simon On Sat, Aug 16, 2014 at 2:41 PM, Zakaria Boulouard zboulou...@gmail.com wrote: Hello Michael and thanks for your help, If there is anyway to make it work in PyDev that would be great because I want to integrate a similar code into a bigger project and PyDev would be more suitable. I guess importing the SQLAlchemy lib was the way it should be, I even put it as forced lib, you can check the attached picture. If it wouldn't bother you, I would like to invite you to take a look using teamviewer and see what is going on. Thanks again! 2014-08-16 13:25 GMT+01:00 Michael Bayer mike...@zzzcomputing.com: how are you running the script? there’s an environmental issue that is causing SQLAlchemy to be imported improperly. This is probably some PyDev issue. If you install SQLAlchemy in a virtualenv normally and run the script from the console it should be fine (runs over here OK). On Aug 16, 2014, at 4:13 AM, Zakaria Boulouard zboulou...@gmail.com wrote: Hello guys, I am new to SQLAlchemy and I wanted to try an example using a MySQL database and PyDev as an IDE but when I run it, PyDev gives me the following error : Traceback (most recent call last): File /usr/lib/python2.7/site.py, line 68, in module import os File /usr/lib/python2.7/os.py, line 49, in module import posixpath as path File /usr/lib/python2.7/posixpath.py, line 17, in module import warnings File /usr/lib/python2.7/warnings.py, line 8, in module import types File /home/zakaria/Bureau/Python/SQLAlchemy-0.9.7/lib/sqlalchemy/types.py, line 21, in module from .sql.type_api import ( ValueError: Attempted relative import in non-package What did I do wrong? And how can I fix it? Please find attached the test file Personne.py and thanks for your help! -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. Personne.py -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/eJ2Z8QBAEzU/unsubscribe. To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- Zakaria BOULOUARD Ingénieur d'État en Génie Informatique École Nationale des Sciences Appliquées Khouribga N° Téléphone: (+212)662193321 / (+212)668695440 -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] PyDev throwing errors in the SQLAlchemy Library
Hi Simon and thanks for your help. I just tried what you said and it doesn't work! What I did though (AND IT WORKED!) is that I have updated my OS (Ubuntu) from 12.04 to 14.04 which has Python3 as default and I run my project with it. 2014-08-18 10:31 GMT+01:00 Simon King si...@simonking.org.uk: I've never used PyDev, but if it effectively adds the folder that you've specified in that screenshot to PYTHONPATH, then I suspect that is the problem. What happens if you remove that External Library, and try to run the following script: import sqlalchemy print sqlalchemy.__file__ Simon On Sat, Aug 16, 2014 at 2:41 PM, Zakaria Boulouard zboulou...@gmail.com wrote: Hello Michael and thanks for your help, If there is anyway to make it work in PyDev that would be great because I want to integrate a similar code into a bigger project and PyDev would be more suitable. I guess importing the SQLAlchemy lib was the way it should be, I even put it as forced lib, you can check the attached picture. If it wouldn't bother you, I would like to invite you to take a look using teamviewer and see what is going on. Thanks again! 2014-08-16 13:25 GMT+01:00 Michael Bayer mike...@zzzcomputing.com: how are you running the script? there’s an environmental issue that is causing SQLAlchemy to be imported improperly. This is probably some PyDev issue. If you install SQLAlchemy in a virtualenv normally and run the script from the console it should be fine (runs over here OK). On Aug 16, 2014, at 4:13 AM, Zakaria Boulouard zboulou...@gmail.com wrote: Hello guys, I am new to SQLAlchemy and I wanted to try an example using a MySQL database and PyDev as an IDE but when I run it, PyDev gives me the following error : Traceback (most recent call last): File /usr/lib/python2.7/site.py, line 68, in module import os File /usr/lib/python2.7/os.py, line 49, in module import posixpath as path File /usr/lib/python2.7/posixpath.py, line 17, in module import warnings File /usr/lib/python2.7/warnings.py, line 8, in module import types File /home/zakaria/Bureau/Python/SQLAlchemy-0.9.7/lib/sqlalchemy/types.py, line 21, in module from .sql.type_api import ( ValueError: Attempted relative import in non-package What did I do wrong? And how can I fix it? Please find attached the test file Personne.py and thanks for your help! -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. Personne.py -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/eJ2Z8QBAEzU/unsubscribe. To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- Zakaria BOULOUARD Ingénieur d'État en Génie Informatique École Nationale des Sciences Appliquées Khouribga N° Téléphone: (+212)662193321 / (+212)668695440 -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/eJ2Z8QBAEzU/unsubscribe. To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- Zakaria BOULOUARD Ingénieur d'État en Génie Informatique École Nationale des Sciences Appliquées Khouribga N° Téléphone: (+212)662193321 / (+212)668695440 -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this
Re: [sqlalchemy] cannot access tables
On Friday, August 15, 2014 8:28:41 PM UTC-5, Michael Bayer wrote:
On Aug 15, 2014, at 5:03 PM, Greg Silverman g...@umn.edu javascript:
wrote:
Then, I thought, what if this is an SQLAlchemy issue. Looks to be. I ran
the following script as a test:
import pyodbc
import sqlalchemy
from sqlalchemy.engine import reflection
from sqlalchemy.engine.reflection import Inspector
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=ip_address;'
'DATABASE=db_name;UID=test;PWD=test;port=1433;'
'TDS_Version=9.1;')
engine = sqlalchemy.create_engine('mssql://', creator=connect)
conn = engine.connect()
print conn
for row in engine.execute('select 6 * 7 as [Result];'):
print row.Result
insp = reflection.Inspector.from_engine(engine)
table_name = 'irb_desc'
table_names = insp.get_table_names()
if table_name not in table_names:
print 'A'
Again, I am connecting fine with the database create.engine method (that
is '42' is printing as expected), but when I run the
inspector.get_table_names method with the given conditional it is printing
the 'A' (I have tried other table names in the same database to which I
added 'irbd_balance,' all with the same result.
what is the SQL output if you set echo=‘debug’; then, take the SQL you
see and take a look at what it’s SELECTing so you can see what might be
wrong. Probably some schema name setting or something like that.
Thanks, I did not realize this was an option (actually, it is echo=True,
but at least I can see the SQL being sent). Hopefully this will lead me to
an answer.
Greg--
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] cannot access tables
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
On Mon, Aug 18, 2014 at 9:47 AM, Horcle g...@umn.edu wrote:
On Friday, August 15, 2014 8:28:41 PM UTC-5, Michael Bayer wrote:
On Aug 15, 2014, at 5:03 PM, Greg Silverman g...@umn.edu wrote:
Then, I thought, what if this is an SQLAlchemy issue. Looks to be. I ran
the following script as a test:
import pyodbc
import sqlalchemy
from sqlalchemy.engine import reflection
from sqlalchemy.engine.reflection import Inspector
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=ip_address;'
'DATABASE=db_name;UID=test;PWD=test;port=1433;'
'TDS_Version=9.1;')
engine = sqlalchemy.create_engine('mssql://', creator=connect)
conn = engine.connect()
print conn
for row in engine.execute('select 6 * 7 as [Result];'):
print row.Result
insp = reflection.Inspector.from_engine(engine)
table_name = 'irb_desc'
table_names = insp.get_table_names()
if table_name not in table_names:
print 'A'
Again, I am connecting fine with the database create.engine method (that
is '42' is printing as expected), but when I run the
inspector.get_table_names method with the given conditional it is printing
the 'A' (I have tried other table names in the same database to which I
added 'irbd_balance,' all with the same result.
what is the SQL output if you set echo=‘debug’; then, take the SQL you
see and take a look at what it’s SELECTing so you can see what might be
wrong. Probably some schema name setting or something like that.
Thanks, I did not realize this was an option (actually, it is echo=True,
but at least I can see the SQL being sent). Hopefully this will lead me to
an answer.
Greg--
--
You received this message because you are subscribed to a topic in the
Google Groups sqlalchemy group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/YSjU_Ohsyvw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
Greg M. Silverman
Senior Developer Analyst
Cardiovascular Informatics http://www.med.umn.edu/cardiology/
University of Minnesota
612-626-0919
g...@umn.edu
› flora-script http://flora-script.grenzi.org/ ‹
› grenzi.org ‹
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] cannot access tables
On Mon, Aug 18, 2014 at 3:47 PM, Horcle g...@umn.edu wrote:
On Friday, August 15, 2014 8:28:41 PM UTC-5, Michael Bayer wrote:
On Aug 15, 2014, at 5:03 PM, Greg Silverman g...@umn.edu wrote:
Then, I thought, what if this is an SQLAlchemy issue. Looks to be. I ran
the following script as a test:
import pyodbc
import sqlalchemy
from sqlalchemy.engine import reflection
from sqlalchemy.engine.reflection import Inspector
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=ip_address;'
'DATABASE=db_name;UID=test;PWD=test;port=1433;'
'TDS_Version=9.1;')
engine = sqlalchemy.create_engine('mssql://', creator=connect)
conn = engine.connect()
print conn
for row in engine.execute('select 6 * 7 as [Result];'):
print row.Result
insp = reflection.Inspector.from_engine(engine)
table_name = 'irb_desc'
table_names = insp.get_table_names()
if table_name not in table_names:
print 'A'
Again, I am connecting fine with the database create.engine method (that
is '42' is printing as expected), but when I run the
inspector.get_table_names method with the given conditional it is printing
the 'A' (I have tried other table names in the same database to which I
added 'irbd_balance,' all with the same result.
what is the SQL output if you set echo=‘debug’; then, take the SQL you
see and take a look at what it’s SELECTing so you can see what might be
wrong. Probably some schema name setting or something like that.
Thanks, I did not realize this was an option (actually, it is echo=True, but
at least I can see the SQL being sent). Hopefully this will lead me to an
answer.
echo='debug' will show you more information than echo=True (it
shows the rows coming back as well as the query that is sent)
Simon
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] cannot access tables
Indeed!
Here is the output:
gms$ python test_connect.py
2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine SELECT
user_name()
2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,097 DEBUG sqlalchemy.engine.base.Engine Col ('',)
2014-08-18 10:17:28,098 DEBUG sqlalchemy.engine.base.Engine Row (u'dbo', )
2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine (u'dbo',)
2014-08-18 10:17:28,101 DEBUG sqlalchemy.engine.base.Engine Col
('default_schema_name',)
2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x101839490
2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine select 6 * 7 as
[Result];
2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Col ('Result',)
2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine SELECT
[TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine (u'dbo', 'BASE
TABLE')
2014-08-18 10:17:28,394 DEBUG sqlalchemy.engine.base.Engine Col
('TABLE_NAME',)
A
Not quite sure how to parse this?
For example, is u'dbo' what is being used as the schema?
Greg--
On Monday, August 18, 2014 10:05:09 AM UTC-5, Simon King wrote:
On Mon, Aug 18, 2014 at 3:47 PM, Horcle g...@umn.edu javascript:
wrote:
On Friday, August 15, 2014 8:28:41 PM UTC-5, Michael Bayer wrote:
On Aug 15, 2014, at 5:03 PM, Greg Silverman g...@umn.edu wrote:
Then, I thought, what if this is an SQLAlchemy issue. Looks to be. I
ran
the following script as a test:
import pyodbc
import sqlalchemy
from sqlalchemy.engine import reflection
from sqlalchemy.engine.reflection import Inspector
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=ip_address;'
'DATABASE=db_name;UID=test;PWD=test;port=1433;'
'TDS_Version=9.1;')
engine = sqlalchemy.create_engine('mssql://', creator=connect)
conn = engine.connect()
print conn
for row in engine.execute('select 6 * 7 as [Result];'):
print row.Result
insp = reflection.Inspector.from_engine(engine)
table_name = 'irb_desc'
table_names = insp.get_table_names()
if table_name not in table_names:
print 'A'
Again, I am connecting fine with the database create.engine method
(that
is '42' is printing as expected), but when I run the
inspector.get_table_names method with the given conditional it is
printing
the 'A' (I have tried other table names in the same database to which I
added 'irbd_balance,' all with the same result.
what is the SQL output if you set echo=‘debug’; then, take the SQL
you
see and take a look at what it’s SELECTing so you can see what might be
wrong. Probably some schema name setting or something like that.
Thanks, I did not realize this was an option (actually, it is echo=True,
but
at least I can see the SQL being sent). Hopefully this will lead me to
an
answer.
echo='debug' will show you more information than echo=True (it
shows the rows coming back as well as the query that is sent)
Simon
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] cannot access tables
I think you are seeing, for each query:
1. The query itself
2. The parameters being passed in to the query
3. The names of the columns being returned
4. The returned rows, if any.
So for example, the first thing that happens is:
SELECT user_name()
with no parameters
()
returning a result set containing a single column with no name:
('',)
followed by a single row containing a single value, the unicode string dbo:
(u'dbo', )
The second query goes like this:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
Note the bound parameter (name = ?). The next line tells us that SA
is sending the unicode string dbo for that parameter:
(u'dbo',)
The result set has a single column called 'default_schema_name':
('default_schema_name',)
...but there are no matching rows.
Then a bit further along, SA is running this query:
SELECT [TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
...passing the parameters dbo and BASE TABLE, but not getting any
results back.
I don't know anything about MS-SQL so can't tell you why that is, but
perhaps you've got enough information to carry on digging?
Hope that helps,
Simon
On Mon, Aug 18, 2014 at 4:27 PM, Horcle g...@umn.edu wrote:
Indeed!
Here is the output:
gms$ python test_connect.py
2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine SELECT
user_name()
2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,097 DEBUG sqlalchemy.engine.base.Engine Col ('',)
2014-08-18 10:17:28,098 DEBUG sqlalchemy.engine.base.Engine Row (u'dbo', )
2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine (u'dbo',)
2014-08-18 10:17:28,101 DEBUG sqlalchemy.engine.base.Engine Col
('default_schema_name',)
2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x101839490
2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine select 6 * 7 as
[Result];
2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Col ('Result',)
2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine SELECT
[TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine (u'dbo', 'BASE
TABLE')
2014-08-18 10:17:28,394 DEBUG sqlalchemy.engine.base.Engine Col
('TABLE_NAME',)
A
Not quite sure how to parse this?
For example, is u'dbo' what is being used as the schema?
Greg--
On Monday, August 18, 2014 10:05:09 AM UTC-5, Simon King wrote:
On Mon, Aug 18, 2014 at 3:47 PM, Horcle g...@umn.edu wrote:
On Friday, August 15, 2014 8:28:41 PM UTC-5, Michael Bayer wrote:
On Aug 15, 2014, at 5:03 PM, Greg Silverman g...@umn.edu wrote:
Then, I thought, what if this is an SQLAlchemy issue. Looks to be. I
ran
the following script as a test:
import pyodbc
import sqlalchemy
from sqlalchemy.engine import reflection
from sqlalchemy.engine.reflection import Inspector
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=ip_address;'
'DATABASE=db_name;UID=test;PWD=test;port=1433;'
'TDS_Version=9.1;')
engine = sqlalchemy.create_engine('mssql://', creator=connect)
conn = engine.connect()
print conn
for row in engine.execute('select 6 * 7 as [Result];'):
print row.Result
insp = reflection.Inspector.from_engine(engine)
table_name = 'irb_desc'
table_names = insp.get_table_names()
if table_name not in table_names:
print 'A'
Again, I am connecting fine with the database create.engine method
(that
is '42' is printing as expected), but when I run the
inspector.get_table_names method with the given conditional it is
printing
the 'A' (I have tried other table names in the same database to which I
added 'irbd_balance,' all with the same result.
what is the SQL output if you set echo=‘debug’; then, take the SQL
you
see and take a look at what it’s SELECTing so you can see what might be
wrong. Probably some
Re: [sqlalchemy] cannot access tables
Thanks, this does help. I was wondering why the return results had no
values given.
Greg--
On Monday, August 18, 2014 11:17:48 AM UTC-5, Simon King wrote:
I think you are seeing, for each query:
1. The query itself
2. The parameters being passed in to the query
3. The names of the columns being returned
4. The returned rows, if any.
So for example, the first thing that happens is:
SELECT user_name()
with no parameters
()
returning a result set containing a single column with no name:
('',)
followed by a single row containing a single value, the unicode string
dbo:
(u'dbo', )
The second query goes like this:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
Note the bound parameter (name = ?). The next line tells us that SA
is sending the unicode string dbo for that parameter:
(u'dbo',)
The result set has a single column called 'default_schema_name':
('default_schema_name',)
...but there are no matching rows.
Then a bit further along, SA is running this query:
SELECT [TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
...passing the parameters dbo and BASE TABLE, but not getting any
results back.
I don't know anything about MS-SQL so can't tell you why that is, but
perhaps you've got enough information to carry on digging?
Hope that helps,
Simon
On Mon, Aug 18, 2014 at 4:27 PM, Horcle g...@umn.edu javascript:
wrote:
Indeed!
Here is the output:
gms$ python test_connect.py
2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine SELECT
user_name()
2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,097 DEBUG sqlalchemy.engine.base.Engine Col ('',)
2014-08-18 10:17:28,098 DEBUG sqlalchemy.engine.base.Engine Row (u'dbo',
)
2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine (u'dbo',)
2014-08-18 10:17:28,101 DEBUG sqlalchemy.engine.base.Engine Col
('default_schema_name',)
2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test
plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test
unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x101839490
2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine select 6 * 7
as
[Result];
2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Col
('Result',)
2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine SELECT
[TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine (u'dbo',
'BASE
TABLE')
2014-08-18 10:17:28,394 DEBUG sqlalchemy.engine.base.Engine Col
('TABLE_NAME',)
A
Not quite sure how to parse this?
For example, is u'dbo' what is being used as the schema?
Greg--
On Monday, August 18, 2014 10:05:09 AM UTC-5, Simon King wrote:
On Mon, Aug 18, 2014 at 3:47 PM, Horcle g...@umn.edu wrote:
On Friday, August 15, 2014 8:28:41 PM UTC-5, Michael Bayer wrote:
On Aug 15, 2014, at 5:03 PM, Greg Silverman g...@umn.edu wrote:
Then, I thought, what if this is an SQLAlchemy issue. Looks to be. I
ran
the following script as a test:
import pyodbc
import sqlalchemy
from sqlalchemy.engine import reflection
from sqlalchemy.engine.reflection import Inspector
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=ip_address;'
'DATABASE=db_name;UID=test;PWD=test;port=1433;'
'TDS_Version=9.1;')
engine = sqlalchemy.create_engine('mssql://', creator=connect)
conn = engine.connect()
print conn
for row in engine.execute('select 6 * 7 as [Result];'):
print row.Result
insp = reflection.Inspector.from_engine(engine)
table_name = 'irb_desc'
table_names = insp.get_table_names()
if table_name not in table_names:
print 'A'
Again, I am connecting fine with the database create.engine method
(that
Re: [sqlalchemy] cannot access tables
It looks like the code that runs the SELECT default_schema_name
query has changed since the version you are running:
https://bitbucket.org/zzzeek/sqlalchemy/commits/1fb4ad75a38c
It might be worth upgrading to the latest release.
Simon
On Mon, Aug 18, 2014 at 5:22 PM, Horcle g...@umn.edu wrote:
Thanks, this does help. I was wondering why the return results had no values
given.
Greg--
On Monday, August 18, 2014 11:17:48 AM UTC-5, Simon King wrote:
I think you are seeing, for each query:
1. The query itself
2. The parameters being passed in to the query
3. The names of the columns being returned
4. The returned rows, if any.
So for example, the first thing that happens is:
SELECT user_name()
with no parameters
()
returning a result set containing a single column with no name:
('',)
followed by a single row containing a single value, the unicode string
dbo:
(u'dbo', )
The second query goes like this:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
Note the bound parameter (name = ?). The next line tells us that SA
is sending the unicode string dbo for that parameter:
(u'dbo',)
The result set has a single column called 'default_schema_name':
('default_schema_name',)
...but there are no matching rows.
Then a bit further along, SA is running this query:
SELECT [TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
...passing the parameters dbo and BASE TABLE, but not getting any
results back.
I don't know anything about MS-SQL so can't tell you why that is, but
perhaps you've got enough information to carry on digging?
Hope that helps,
Simon
On Mon, Aug 18, 2014 at 4:27 PM, Horcle g...@umn.edu wrote:
Indeed!
Here is the output:
gms$ python test_connect.py
2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine SELECT
user_name()
2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,097 DEBUG sqlalchemy.engine.base.Engine Col ('',)
2014-08-18 10:17:28,098 DEBUG sqlalchemy.engine.base.Engine Row (u'dbo',
)
2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine (u'dbo',)
2014-08-18 10:17:28,101 DEBUG sqlalchemy.engine.base.Engine Col
('default_schema_name',)
2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test
plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test
unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x101839490
2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine select 6 * 7
as
[Result];
2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Col
('Result',)
2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine SELECT
[TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine (u'dbo',
'BASE
TABLE')
2014-08-18 10:17:28,394 DEBUG sqlalchemy.engine.base.Engine Col
('TABLE_NAME',)
A
Not quite sure how to parse this?
For example, is u'dbo' what is being used as the schema?
Greg--
On Monday, August 18, 2014 10:05:09 AM UTC-5, Simon King wrote:
On Mon, Aug 18, 2014 at 3:47 PM, Horcle g...@umn.edu wrote:
On Friday, August 15, 2014 8:28:41 PM UTC-5, Michael Bayer wrote:
On Aug 15, 2014, at 5:03 PM, Greg Silverman g...@umn.edu wrote:
Then, I thought, what if this is an SQLAlchemy issue. Looks to be. I
ran
the following script as a test:
import pyodbc
import sqlalchemy
from sqlalchemy.engine import reflection
from sqlalchemy.engine.reflection import Inspector
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=ip_address;'
'DATABASE=db_name;UID=test;PWD=test;port=1433;'
'TDS_Version=9.1;')
engine = sqlalchemy.create_engine('mssql://', creator=connect)
conn = engine.connect()
print conn
for row in engine.execute('select 6 * 7 as [Result];'):
print row.Result
insp = reflection.Inspector.from_engine(engine)
table_name = 'irb_desc'
Re: [sqlalchemy] cannot access tables
Thanks for the heads up. Unfortunately, it did not help. In any case, the
issue appears to be that while the last query DOES return a record set when
run as straight up SQL on the server, it does not work as desirecd through
SQLAlchemy.
More digging to be done.
Greg--
On Monday, August 18, 2014 11:30:39 AM UTC-5, Simon King wrote:
It looks like the code that runs the SELECT default_schema_name
query has changed since the version you are running:
https://bitbucket.org/zzzeek/sqlalchemy/commits/1fb4ad75a38c
It might be worth upgrading to the latest release.
Simon
On Mon, Aug 18, 2014 at 5:22 PM, Horcle g...@umn.edu javascript:
wrote:
Thanks, this does help. I was wondering why the return results had no
values
given.
Greg--
On Monday, August 18, 2014 11:17:48 AM UTC-5, Simon King wrote:
I think you are seeing, for each query:
1. The query itself
2. The parameters being passed in to the query
3. The names of the columns being returned
4. The returned rows, if any.
So for example, the first thing that happens is:
SELECT user_name()
with no parameters
()
returning a result set containing a single column with no name:
('',)
followed by a single row containing a single value, the unicode string
dbo:
(u'dbo', )
The second query goes like this:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
Note the bound parameter (name = ?). The next line tells us that SA
is sending the unicode string dbo for that parameter:
(u'dbo',)
The result set has a single column called 'default_schema_name':
('default_schema_name',)
...but there are no matching rows.
Then a bit further along, SA is running this query:
SELECT [TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
...passing the parameters dbo and BASE TABLE, but not getting any
results back.
I don't know anything about MS-SQL so can't tell you why that is, but
perhaps you've got enough information to carry on digging?
Hope that helps,
Simon
On Mon, Aug 18, 2014 at 4:27 PM, Horcle g...@umn.edu wrote:
Indeed!
Here is the output:
gms$ python test_connect.py
2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine SELECT
user_name()
2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,097 DEBUG sqlalchemy.engine.base.Engine Col ('',)
2014-08-18 10:17:28,098 DEBUG sqlalchemy.engine.base.Engine Row
(u'dbo',
)
2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine (u'dbo',)
2014-08-18 10:17:28,101 DEBUG sqlalchemy.engine.base.Engine Col
('default_schema_name',)
2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test
plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test
unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x101839490
2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine select 6 *
7
as
[Result];
2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Col
('Result',)
2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Row (42,
)
42
2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine SELECT
[TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine (u'dbo',
'BASE
TABLE')
2014-08-18 10:17:28,394 DEBUG sqlalchemy.engine.base.Engine Col
('TABLE_NAME',)
A
Not quite sure how to parse this?
For example, is u'dbo' what is being used as the schema?
Greg--
On Monday, August 18, 2014 10:05:09 AM UTC-5, Simon King wrote:
On Mon, Aug 18, 2014 at 3:47 PM, Horcle g...@umn.edu wrote:
On Friday, August 15, 2014 8:28:41 PM UTC-5, Michael Bayer wrote:
On Aug 15, 2014, at 5:03 PM, Greg Silverman g...@umn.edu
wrote:
Then, I thought, what if this is an SQLAlchemy issue.
Re: [sqlalchemy] cannot access tables
try the query as stated along with a pyodbc connection (e.g. conn =
pyodbc.connect(...); cursor = conn.cursor(); cursor.execute(the statement);
cursor.fetchall()). the way pyodbc is connecting might be changing things.
On Aug 18, 2014, at 12:59 PM, Horcle g...@umn.edu wrote:
Thanks for the heads up. Unfortunately, it did not help. In any case, the
issue appears to be that while the last query DOES return a record set when
run as straight up SQL on the server, it does not work as desirecd through
SQLAlchemy.
More digging to be done.
Greg--
On Monday, August 18, 2014 11:30:39 AM UTC-5, Simon King wrote:
It looks like the code that runs the SELECT default_schema_name
query has changed since the version you are running:
https://bitbucket.org/zzzeek/sqlalchemy/commits/1fb4ad75a38c
It might be worth upgrading to the latest release.
Simon
On Mon, Aug 18, 2014 at 5:22 PM, Horcle g...@umn.edu wrote:
Thanks, this does help. I was wondering why the return results had no
values
given.
Greg--
On Monday, August 18, 2014 11:17:48 AM UTC-5, Simon King wrote:
I think you are seeing, for each query:
1. The query itself
2. The parameters being passed in to the query
3. The names of the columns being returned
4. The returned rows, if any.
So for example, the first thing that happens is:
SELECT user_name()
with no parameters
()
returning a result set containing a single column with no name:
('',)
followed by a single row containing a single value, the unicode string
dbo:
(u'dbo', )
The second query goes like this:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
Note the bound parameter (name = ?). The next line tells us that SA
is sending the unicode string dbo for that parameter:
(u'dbo',)
The result set has a single column called 'default_schema_name':
('default_schema_name',)
...but there are no matching rows.
Then a bit further along, SA is running this query:
SELECT [TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
...passing the parameters dbo and BASE TABLE, but not getting any
results back.
I don't know anything about MS-SQL so can't tell you why that is, but
perhaps you've got enough information to carry on digging?
Hope that helps,
Simon
On Mon, Aug 18, 2014 at 4:27 PM, Horcle g...@umn.edu wrote:
Indeed!
Here is the output:
gms$ python test_connect.py
2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine SELECT
user_name()
2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,097 DEBUG sqlalchemy.engine.base.Engine Col ('',)
2014-08-18 10:17:28,098 DEBUG sqlalchemy.engine.base.Engine Row (u'dbo',
)
2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine (u'dbo',)
2014-08-18 10:17:28,101 DEBUG sqlalchemy.engine.base.Engine Col
('default_schema_name',)
2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test
plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test
unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x101839490
2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine select 6 * 7
as
[Result];
2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Col
('Result',)
2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine SELECT
[TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine (u'dbo',
'BASE
TABLE')
2014-08-18 10:17:28,394 DEBUG sqlalchemy.engine.base.Engine Col
('TABLE_NAME',)
A
Not quite sure how to parse this?
For example, is u'dbo' what is being used as the schema?
Greg--
On Monday, August 18, 2014 10:05:09 AM UTC-5, Simon King wrote:
On Mon,
Re: [sqlalchemy] cannot access tables
It ended up being a unicode issue. I had to set this:
supports_unicode_binds=False (see Unicde Binds here
http://docs.sqlalchemy.org/en/rel_0_9/dialects/mssql.html#module-sqlalchemy.dialects.mssql.pyodbc),
in order to get it to work. Annoying, to say the least!
python test_connect.py
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE principal_id=database_principal_id()
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,613 DEBUG sqlalchemy.engine.base.Engine Col
('default_schema_name',)
2014-08-18 16:15:06,614 DEBUG sqlalchemy.engine.base.Engine Row (u'dbo', )
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x101877ed0
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine select 6 * 7 as
[Result];
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Col ('Result',)
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine SELECT
[TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine ('dbo', 'BASE
TABLE')
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Col
('TABLE_NAME',)
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row
(u'irb_desc', )
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u'irbd', )
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_irb', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_status', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_status_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_subject', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'subj_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'subj_status_desc', )
2014-08-18 16:15:06,665 DEBUG sqlalchemy.engine.base.Engine Row
(u'subject_status', )
2014-08-18 16:15:06,665 DEBUG sqlalchemy.engine.base.Engine Row
(u'sysdiagrams', )
Thanks all!
Greg--
On Monday, August 18, 2014 1:08:55 PM UTC-5, Michael Bayer wrote:
try the query as stated along with a pyodbc connection (e.g. conn =
pyodbc.connect(…); cursor = conn.cursor(); cursor.execute(the statement);
cursor.fetchall()). the way pyodbc is connecting might be changing things.
On Aug 18, 2014, at 12:59 PM, Horcle g...@umn.edu javascript: wrote:
Thanks for the heads up. Unfortunately, it did not help. In any case, the
issue appears to be that while the last query DOES return a record set when
run as straight up SQL on the server, it does not work as desirecd through
SQLAlchemy.
More digging to be done.
Greg--
On Monday, August 18, 2014 11:30:39 AM UTC-5, Simon King wrote:
It looks like the code that runs the SELECT default_schema_name
query has changed since the version you are running:
https://bitbucket.org/zzzeek/sqlalchemy/commits/1fb4ad75a38c
It might be worth upgrading to the latest release.
Simon
On Mon, Aug 18, 2014 at 5:22 PM, Horcle g...@umn.edu wrote:
Thanks, this does help. I was wondering why the return results had no
values
given.
Greg--
On Monday, August 18, 2014 11:17:48 AM UTC-5, Simon King wrote:
I think you are seeing, for each query:
1. The query itself
2. The parameters being passed in to the query
3. The names of the columns being returned
4. The returned rows, if any.
So for example, the first thing that happens is:
SELECT user_name()
with no parameters
()
returning a result set containing a single column with no name:
('',)
followed by a single row containing a single value, the unicode string
dbo:
(u'dbo', )
The second query goes like this:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
Note the bound parameter (name = ?). The next line tells us that SA
is sending the unicode string dbo for that parameter:
(u'dbo',)
The result set has a single column called 'default_schema_name':
('default_schema_name',)
...but there are
Re: [sqlalchemy] Speed up bulk inserts
Hi Michael, I checked your post on the stackoverflow, could you help me figure something out regarding usage of Core. In the example you provided you have a version with test_sqlite3 which uses customer user supplied query so it issues only one insert statement with all rows in values clause. I wonder if this can be achieved by using Core. I tried this approach: session.connection().execute( mytable.insert(), raw_list ) where raw_list is a list of dictionaries to insert and this issues a separate statement for every row, which is probably expected. But then I came across this docs page http://docs.sqlalchemy.org/en/latest/core/tutorial.html#executing-multiple-statements in the example with a list of dictionaries there is a single insert statement issues with multi-row values clause. I wonder if I can achieve the same behavior. I am using PostgreSQL 9.3.4 and SQLAlchemy==0.9.4 Thanks! Best, Anton. On Wednesday, November 6, 2013 12:58:53 PM UTC-8, Michael Bayer wrote: I wrote a full post regarding this topic on stackoverflow at http://stackoverflow.com/questions/11769366/why-is-sqlalchemy-insert-with-sqlite-25-times-slower-than-using-sqlite3-directly/11769768#11769768 . If you start with this, I can answer more specific questions. On Nov 6, 2013, at 10:28 AM, Achim Domma do...@procoders.net javascript: wrote: Hi, I want to speed up my SqlAlchemy bulk inserting code and yes, I'm aware that this is not the main purpose of SqlAlchemy and all databases have faster low level import tools. The background is the following: We import data from various sources and apply various mappings. Currently I'm willing to trade raw runtime for much simpler code, which is much easier to maintain. But I still want my code to run as fast as it's possible with those assumptions. There are two scenarios which I want to optimize: 1. Flat inserts without relations, but with unique ids generated inside the database: In that case, SqlAlchemy retrieves the unique ids from the database, but those ids are never used in my import process. I thought about generating an insert statement out of an object. Obviously SqlAlchemy has to do that too, so there might be some existing tool for that? The other option would be, to tell SqlAlchemy to ignore the generated id an to not retrieve it from the database. Is that possible? 2. Inserts using relations and unique ids generated in the database: I think SqlAlchemy is already quite good at optimizing this and sending objects as batches. If there are any tweeks and tricks to speed up inserts having relations, I would be happy to hear them. cheers, Achim -- 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 sqlalchemy+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
