我正在编写一些Python代码来创建和删除一些数据库表。创建表的代码工作正常，但是我有删除表代码的问题。这是我的堆栈信息:
Python 3.8- SQLAlchemy 1.3.23
- Postgres 12
有一个模式(asia)和一个表(sales)，我试图使用以下python代码从asia删除销售:
from sqlalchemy import MetaData
from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL
from sqlalchemy.ext.declarative import declarative_base
# Import log configuration file
import os
import sys
sys.path.append('../..')
from conf import dbconfig
from conf import alchemy_config
# Import log configuration file
sys.path.append('../..')
from conf import alchemy_config
def drop_table(schema_name, table_name):
# engine = create_engine(URL(**DATABASE))
# engine = create_engine(alchemy_config.DATABASE_URI_UK, echo=False)
engine = create_engine(alchemy_config.DATABASE_URI_UK, echo=True)
connection = engine.connect()
# base = declarative_base()
meta = MetaData(schema=schema_name)
meta.reflect(bind=engine)
table = meta.tables.get(table_name)
# table = Table(table_name, metadata)
if (table is not None):
print(f'Deleting {table_name} table')
# base.metadata.drop_all(engine, table, checkfirst=True)
base.metadata.drop_all(engine)
else:
print(f"Table {table_name} could not be found")
def main():
table_name = 'sales'
schema_name = 'asia'
drop_table(schema_name, table_name)
if __name__ == "__main__":
main()
此代码无法定位表，输出如下:
2021-03-01 18:13:06,830 INFO sqlalchemy.engine.base.Engine select version()
2021-03-01 18:13:06,830 INFO sqlalchemy.engine.base.Engine {}
2021-03-01 18:13:06,831 INFO sqlalchemy.engine.base.Engine select current_schema()
2021-03-01 18:13:06,831 INFO sqlalchemy.engine.base.Engine {}
2021-03-01 18:13:06,832 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2021-03-01 18:13:06,832 INFO sqlalchemy.engine.base.Engine {}
2021-03-01 18:13:06,833 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2021-03-01 18:13:06,833 INFO sqlalchemy.engine.base.Engine {}
2021-03-01 18:13:06,833 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2021-03-01 18:13:06,833 INFO sqlalchemy.engine.base.Engine {}
2021-03-01 18:13:06,842 INFO sqlalchemy.engine.base.Engine SELECT c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = %(schema)s AND c.relkind in ('r', 'p')
2021-03-01 18:13:06,843 INFO sqlalchemy.engine.base.Engine {'schema': 'asia'}
2021-03-01 18:13:06,845 INFO sqlalchemy.engine.base.Engine
SELECT c.oid
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE (n.nspname = %(schema)s)
AND c.relname = %(table_name)s AND c.relkind in
('r', 'v', 'm', 'f', 'p')
2021-03-01 18:13:06,846 INFO sqlalchemy.engine.base.Engine {'schema': 'asia', 'table_name': 'sales'}
2021-03-01 18:13:06,847 INFO sqlalchemy.engine.base.Engine
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum
AND a.atthasdef)
AS DEFAULT,
a.attnotnull, a.attnum, a.attrelid as table_oid,
pgd.description as comment,
a.attgenerated as generated
FROM pg_catalog.pg_attribute a
LEFT JOIN pg_catalog.pg_description pgd ON (
pgd.objoid = a.attrelid AND pgd.objsubid = a.attnum)
WHERE a.attrelid = %(table_oid)s
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
2021-03-01 18:13:06,847 INFO sqlalchemy.engine.base.Engine {'table_oid': 17161}
2021-03-01 18:13:06,850 INFO sqlalchemy.engine.base.Engine
SELECT t.typname as "name",
pg_catalog.format_type(t.typbasetype, t.typtypmod) as "attype",
not t.typnotnull as "nullable",
t.typdefault as "default",
pg_catalog.pg_type_is_visible(t.oid) as "visible",
n.nspname as "schema"
FROM pg_catalog.pg_type t
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE t.typtype = 'd'
2021-03-01 18:13:06,850 INFO sqlalchemy.engine.base.Engine {}
2021-03-01 18:13:06,851 INFO sqlalchemy.engine.base.Engine
SELECT t.typname as "name",
-- no enum defaults in 8.4 at least
-- t.typdefault as "default",
pg_catalog.pg_type_is_visible(t.oid) as "visible",
n.nspname as "schema",
e.enumlabel as "label"
FROM pg_catalog.pg_type t
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
LEFT JOIN pg_catalog.pg_enum e ON t.oid = e.enumtypid
WHERE t.typtype = 'e'
ORDER BY "schema", "name", e.oid
2021-03-01 18:13:06,851 INFO sqlalchemy.engine.base.Engine {}
2021-03-01 18:13:06,854 INFO sqlalchemy.engine.base.Engine
SELECT a.attname
FROM pg_attribute a JOIN (
SELECT unnest(ix.indkey) attnum,
generate_subscripts(ix.indkey, 1) ord
FROM pg_index ix
WHERE ix.indrelid = %(table_oid)s AND ix.indisprimary
) k ON a.attnum=k.attnum
WHERE a.attrelid = %(table_oid)s
ORDER BY k.ord
2021-03-01 18:13:06,854 INFO sqlalchemy.engine.base.Engine {'table_oid': 17161}
2021-03-01 18:13:06,856 INFO sqlalchemy.engine.base.Engine
SELECT conname
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = %(table_oid)s AND r.contype = 'p'
ORDER BY 1
2021-03-01 18:13:06,856 INFO sqlalchemy.engine.base.Engine {'table_oid': 17161}
2021-03-01 18:13:06,859 INFO sqlalchemy.engine.base.Engine
SELECT r.conname,
pg_catalog.pg_get_constraintdef(r.oid, true) as condef,
n.nspname as conschema
FROM pg_catalog.pg_constraint r,
pg_namespace n,
pg_class c
WHERE r.conrelid = %(table)s AND
r.contype = 'f' AND
c.oid = confrelid AND
n.oid = c.relnamespace
ORDER BY 1
2021-03-01 18:13:06,859 INFO sqlalchemy.engine.base.Engine {'table': 17161}
2021-03-01 18:13:06,860 INFO sqlalchemy.engine.base.Engine
SELECT
i.relname as relname,
ix.indisunique, ix.indexprs, ix.indpred,
a.attname, a.attnum, c.conrelid, ix.indkey::varchar,
ix.indoption::varchar, i.reloptions, am.amname,
ix.indnkeyatts as indnkeyatts
FROM
pg_class t
join pg_index ix on t.oid = ix.indrelid
join pg_class i on i.oid = ix.indexrelid
left outer join
pg_attribute a
on t.oid = a.attrelid and a.attnum = ANY(ix.indkey)
left outer join
pg_constraint c
on (ix.indrelid = c.conrelid and
ix.indexrelid = c.conindid and
c.contype in ('p', 'u', 'x'))
left outer join
pg_am am
on i.relam = am.oid
WHERE
t.relkind IN ('r', 'v', 'f', 'm', 'p')
and t.oid = %(table_oid)s
and ix.indisprimary = 'f'
ORDER BY
t.relname,
i.relname
2021-03-01 18:13:06,860 INFO sqlalchemy.engine.base.Engine {'table_oid': 17161}
2021-03-01 18:13:06,863 INFO sqlalchemy.engine.base.Engine
SELECT
cons.conname as name,
cons.conkey as key,
a.attnum as col_num,
a.attname as col_name
FROM
pg_catalog.pg_constraint cons
join pg_attribute a
on cons.conrelid = a.attrelid AND
a.attnum = ANY(cons.conkey)
WHERE
cons.conrelid = %(table_oid)s AND
cons.contype = 'u'
2021-03-01 18:13:06,863 INFO sqlalchemy.engine.base.Engine {'table_oid': 17161}
2021-03-01 18:13:06,864 INFO sqlalchemy.engine.base.Engine
SELECT
cons.conname as name,
pg_get_constraintdef(cons.oid) as src
FROM
pg_catalog.pg_constraint cons
WHERE
cons.conrelid = %(table_oid)s AND
cons.contype = 'c'
2021-03-01 18:13:06,864 INFO sqlalchemy.engine.base.Engine {'table_oid': 17161}
2021-03-01 18:13:06,864 INFO sqlalchemy.engine.base.Engine
SELECT
pgd.description as table_comment
FROM
pg_catalog.pg_description pgd
WHERE
pgd.objsubid = 0 AND
pgd.objoid = %(table_oid)s
2021-03-01 18:13:06,865 INFO sqlalchemy.engine.base.Engine {'table_oid': 17161}
Table sales could not be found
我做错了什么?请让我知道。
谢谢!
------------------------ 解决方案w/Gord的帮助 ------------------------
import logging
from sqlalchemy import MetaData
from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL
from sqlalchemy.ext.declarative import declarative_base
import os
import sys
sys.path.append('../..')
from conf import dbconfig
from conf import alchemy_config
sys.path.append('../..')
from conf import alchemy_config
def drop_table(schema_name, table_name):
engine = create_engine(alchemy_config.DATABASE_URI_UK, echo=True)
connection = engine.connect()
base = declarative_base()
meta = MetaData(schema=schema_name)
meta.reflect(bind=engine)
table = meta.tables.get(".".join([schema_name, table_name]))
print(f" Table {table} data type is: {type(table)}")
if (table is not None):
print(f'Deleting {table_name} table')
base.metadata.drop_all(engine, [table], checkfirst=True)
else:
print(f"Table {table_name} could not be found")
def main():
table_name = 'sales'
schema_name = 'asia'
drop_table(schema_name, table_name)
if __name__ == "__main__":
main()
调用meta.reflect(bind=engine)后，不可变字典meta.tables包含由schema_name + "." + table_name键控的表(例如，"asia.sales")，因此，而不是
table = meta.tables.get(table_name)
你会想要使用像
这样的东西table = meta.tables.get(".".join([schema_name, table_name]))