Python - Parse a SQL and print statements

Question

I want to parse a SQL file and print only the create table statements.

Example SQL file:

--
-- Name: film_actor; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.film_actor (
    actor_id smallint NOT NULL,
    film_id smallint NOT NULL,
    last_update timestamp without time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.film_actor OWNER TO postgres;

--
-- Name: film_category; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.film_category (
    film_id smallint NOT NULL,
    category_id smallint NOT NULL,
    last_update timestamp without time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.film_category OWNER TO postgres;

Here, I just want to get the complete create table statement for the first table and then print, then go for the next table.

I tried to use it with DDLparse and SQLparse tools, but not exactly parse the complete SQL file. So basically once I grep the Create table statement then I can use SQLparse to do other stuff.

Could someone help me with this?

Wazaki · Accepted Answer · 2020-08-10 08:14:56Z

1

I'm not sure about parsers or parsing tools, but you could do a workaround using regex. What I did is basically take all the text between "CREATE" and ";" and added them to a list, then I manually added "CREATE" and ";" to complete the SQL queries.

Take a look at this:

import re

Test = """
--
-- Name: film_actor; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.film_actor (
    actor_id smallint NOT NULL,
    film_id smallint NOT NULL,
    last_update timestamp without time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.film_actor OWNER TO postgres;

--
-- Name: film_category; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.film_category (
    film_id smallint NOT NULL,
    category_id smallint NOT NULL,
    last_update timestamp without time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.film_category OWNER TO postgres;"""

#search(r'Part 1\.(.*?)Part 3', s)

results = re.findall ( 'CREATE(.*?);', Test, re.DOTALL)

newresults = []

for x in results:
    newresults.append("CREATE "+x+";")

for y in newresults:
    print(y)

answered Aug 10, 2020 at 8:14

Wazaki

8991 gold badge9 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TheDataGuy Over a year ago

If I have it a large SQL file and If I use read lines, will it eat more memory?

Wazaki Over a year ago

It depends on how big your file is and how much memory your machine has (Also what other programs are using your memory). You could also process each result by itself inside the loop without appending it to the newresults list to prevent using more memory

Lucaash · Accepted Answer · 2020-08-10 08:24:37Z

0

You can use library like sqlparse

import sqlparse

with open('test.sql') as input:
  statements = sqlparse.split(input.read())

for statement in statements:
  if 'create table' in statement.lower():
    print(sqlparse.format(statement, strip_comments=True))

answered Aug 10, 2020 at 8:24

Lucaash

1311 silver badge6 bronze badges

2 Comments

TheDataGuy Over a year ago

If I have 5GB of this SQL file, will it read on hold it on memory? Or just read line by one and process on the fly?

Lucaash Over a year ago

It will read it all into memory and then process from it. The questions you ask imply you are working on a DB migration and you do not want to migrate data, just the schema. Is that right? In that case you might want to rethink how you want to approach this migration. Working with raw SQL is often asking for security trouble. I'd recommend migrating to established ORM like SQLAlchemy. You can generate declarative schema from existing DB using a tool like sqlacodegen and then manage migrations with alembic

Collectives™ on Stack Overflow

Python - Parse a SQL and print statements

Example SQL file:

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Example SQL file:

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related